Been trying to link to a password-protected MS Access database (mdb) using MS SQL Server Management Studio Express (MSSSMSE). Keep getting errors. Here are two examples. ----- Attempt 1 ----- EXEC sp_addlinkedserver @server = 'TRACKS', @srvproduct= 'OLE DB Provider for JET', @pr...more >>

Hi! When I try to insert a record to another database from a INSERT trigger, it failed to insert any record. It also did not raise any error message. It only success when i use the same database. Please advice! Thanks! note:Both database are stored in same server. Below is the trigger :- ...more >>

Hi, I have a SQL Express DB that I've used for local development. I now want to move this to an online sql server. Is there a simple way to generate scripts or a batch file to re-create the database online. Online I only have access to creating stored procedures, and I don't want to retype...more >>

Hi all. I want to check and find the name of the constraint and drop it before I drop the column. Or, just drop the column without the errormessage that there are depending objects. Any ideas? Now I have to drop the column, look at the errormessage, copy/paste into a drop constraint and then...more >>

Howdy, I just accidently wiped out a table that had some pretty important data in it. Can I restore it from the transaction log? Here's what I've tried so far and I get the errors as stated below. I did do "net pause mssqlserver" before running it. USE master RESTORE DATABASE cpts FROM ...more >>

I've been through Erland's wonderful articles on error handling pretty carefully now. I just need clarification on one issue right now. He says "Triggers differ from stored procedures in some aspects. If you are lazy, you can actually skip error checking in triggers, because as soon as an erro...more >>

Hi all I have a SP that beahves strange. Originally it takes about 20 milliseconds to complete, but sometimes it starts going slow and take about 5-7 seconds. When this happens, it keeps going slow. I tried to run the SQL body of the SP in the Query analyzer, and it runs fast (20 ms), while...more >>

Stuff like SELECT, UPDATE, INSERT, DELETE. I'll worry about things like GroupBy and having when she asks. No references to Books Online please. She does not have SQL Server -- just wants to learn the language. And I don't have any decent links for an end user studying SQL as a language. ...more >>

Hello - I have the following table: tblSecurity SecurityID int UserName varchar 20 Password varchar 20 AccessID int ...other columns I have a VB 6 application with a Sql Server 2000 back end. I have used stored prpocedures throughout the application. I need to...more >>

Hi, Currently using SQL Server 2000, and would appreciate some insight as to the best way to achieve the following: Let's say, hypothetically, I have a table of sales figures, called Sales, as follows: SalespersonID int -- salesperson's employee ID StateID char(2) -- the standard...more >>

I am trying to set up a cursor and pass a parameter to it but am having difficulty. Is there a way to do this in Transact-SQL Declare cs_RT_Extract Cursor for Select Branch, ORDERNUM, MODELNUM LocalServiceDataCheck WHERE COMDATE Is Not Null And COMDATE = + '' @dtStartDate + '' and got th...more >>

Question. I am sending a list of comma separated codes as a parameter to stored procedure. As I parse the list, I put them in the table variable (or temp table) using insert statement. For example if the parameter is 1001,3233,4444,2223,4452,5523, after the parsing, the table variable w...more >>

Hi I am trying to set up a cell calculation using DSO and can not figure out why it fails. The code is in VB6 (also tried in c#) but to no avail. Any help - thanks a lot --- Dim dsoServer As New DSO.Server Dim dsodb As Variant dsoServer.Name = "ServerName" dsoServer.Connect "ServerName"...more >>

Let's say I have a search screen in my application that allows users to do various AND OR conditions to about 14 pieces of criteria. That is a complicated query to build dynamically and will be super slow because it is dynamic. Is there an efficient way to do this type of quering? Thanks, ...more >>

I am copying a view to another location as a table. SQL 2000 is automatically setting the tables ID as an Indentity column. I do not want this to happen. It may be because the original table from whence the view works off has identity turned on. However the copy of the table I do not wish to h...more >>

I have the folloving code: SELECT ......(one select statement) create table #temp1 (staID int,quantity decimal(15,5)) INSERT INTO #temp1(staID,kolicina) values(5,10) drop table #temp1 It takes 470 mili seconds. If I execute just select statement it takes 40 mili seconds. If I ex...more >>

Hi, Running SQL Server 2000 and I'm wondering if there is a way to tell which tables are being modified by a client/server application? I am trying to make some custom reports on a product we use and there is a particular relationship I'm trying to decipher. I know there has to be an inte...more >>

I am needing to check for an error code after a SQL statement has run, but the error is being trapped internal to the Select statement. How do I make this work? I am trying to set up some code that will try the SQL statement and if it gets an error, it will wait 30 seconds and then try aga...more >>

Hello, given a sample data (simplified for demonstration purposes): col1 col2 col3 ----------- -------------------- -------------------- 10 bill smith 20 bill smith 30 ...more >>

Hi, I have a machine that has just SQL2005 (as default instance with no instance name.) I am trying to list all SQL 2005 server instances on the network using SmoApplication.EnumAvailableSqlServers( ) and trying to filter the servers based on version information. This machine shows up in th...more >>

I would like to extract for each tankID the record that contains the most current date/time. In the sample below, it's 4/19/2006 4:37:00 AM for tankID 0031021002-1 and 4/23/2006 5:24:00 AM for tankId 0031021023-1 What's the most efficient query syntax to accomplish this? Thanks...more >>

It sounds like it really has to do with how SQL Server, specifically, handles transactions. With Oracle, data is not locked when you issue a select, unless you specifically request a lock. In Oracle, rather than locking the data, the database uses the rollback space to make sure you see every...more >>

My code calculates a duration of a start and end dates. It then converts the duration into " HHMMSS" format. My sample returns 0:0:3 which means 3 seconds. Can someone help me modify my code so that the result would be 0:0:03 and add the extra "padding 0" when the hours, minutes, or seco...more >>

I have been given a utility to work with that moves log file data into a database. The source to this thing is not available and I really only need less than 5% of the info it is importing. I decided to place a trigger on insert to get rid of the unnesasary records. However this is failing sin...more >>

I have a query that returns results based on a count of tests done by period and year. The period can either be a calendar month or Fiscal Calendar month. As always thanks in advance! Select count( modelDesc)as CompCnt, TestYear as CalYear, TestMonth as CalMonth, FiscYear as FiscY...more >>

Hi, Since I don't know which group would be a right newsgroup to post for this problem and most likely that it would be involved with expertise coming from residents of different newsgroups, hope multiple-group posting is ok. Solving this problem means a lot to me and my team. Thank you v...more >>

SQL Query Help - How to get Multiple ID values. I have a tmp table that holds multiples of the same ID. The table may have many different PatID values. Example: FieldID: PatID: Value: ----------------------------------------------------------------- 10 44345 990 12 44345 JZ1 10 331...more >>

Hi all, I have always tried to stay away from UNIQUEIDENTIFIER column types, but I am working on a new project and I am thinking that maybe would be the best solution. In this project we are going to have around 200 tables, some of them may have about 20 million records. Most of the table...more >>

I have several log shipping restore jobs that are scheduled to start at the same time and repeat every 15 minutes. This has worked correctly until last week when some of the jobs would only run once. No errors were produced and Agent said the jobs completed successfully, no reason given as to wh...more >>

The Free Pages on our SQL server is consistently at 84 which I've read is low. Any suggestions on how to improve this number? ...more >>

This is probably a very easy question. When you have many tables to join, does the order of choosing the table matters in an inner join? I do know that the first table you choose is the main one where you want all the rows to show! But, I'm not sure about the rest. Thanks!...more >>

SELECT DISTINCT * FROM Products WHERE ProductID IN(1406,761,587,646,182) I need to Order by the "IN" 1. 1406 2. 761 3. 587 4. 646 5. 182 Does anyone know how to do that? From Mike ...more >>

Hi, I'm trying to create several insert statements in a stored procedures as in below, but it will insert the reocrds sequentially and i want the stored procedures to populate the fields that are parrallel. I also tried set rowcount 0 - it didn't accept that and I don't know how to use the ...more >>

I know I can do an INSERT INTO and use a SELECT statment to get the values but I'd also like to define some values that are not in the select statment. Example: INSERT INTO xTable ( Afield, Bfield, Cfield) VALUES ("testvalue", SELECT yTable ..etc..etc) Can something like this be done? ...more >>

I have multiple tables which all have similar structures. A 1 to 2 character CODE and a 10 to 75 character DETAIL. I want a stored procedure that will take the code and table to find it in as input and return the detail. Here is my code so far. CREATE PROCEDURE [dbo].[lookup_code] @mCODE...more >>

Thanks in Advance, MS Access has a flexible function called DateValue that will convert a valid string into a date. Does anyone know a method of parsing ntext values to detect and convert this strings into dates? The ntext values might look something like this: Established in 1974. Cre...more >>

I'm wondering if there is something I can do with TOP, or if I should just use WITH TIES and let the application stop when it hits the maximum number of rows. Okay, my rules are a little strange. We want the top 3 players for a game. Ties count - if three players tie for third, we want all...more >>

I need to generate daily aggregates (sums) from some hourly data and although I have query that works, I think I've gone overboard on the GROUP BY statement. Can anyone give me some feedback on this? I'm sure there is a better way to do this, but I'm at a loss. SELECT max(LP.NO_FINISH_...more >>

A wonderfull Monday morning to you all... I'm rather new at SQL2005, and I'm having a few issues using the UDFs. Here's what I'm trying to do... • I have a table setup that has Different Codes for Different Months.. (eg, the #2 Could Equal 'Feb' or 'February' or 'G') • I have a Tab...more >>

Hi, I'd be grateful if anyone could help with the following. I'm creating an indexed view to aid performance for a particular query. This indexed view contains a user defined scalar function returning a numeric data type. I can't create the indexed view however as it seems to think that the...more >>

Hi, My users upload a file (Word Doc, Excel sheet etc). I want to be able to save this file to SQL Server 2000 and later retrieve the details of the file and reconstitute the file. Can I do this in using SQL Server? How?...more >>

Anyone got a script to get the start-dates and end-dates for x number of weeks? The tricky thing is that the script needs to account for weeks where the monday date is in say April and the Friday date is in May. In this case the start-date is the monday but the end-date may be a wednesday or ...more >>

I was wondering if anyone could possibly confirm for me that I understand the comcept of binding tables in DDQ's, as I admit I'm slightly (but not totally) confused. I have 2 DDQ's, one to transfer data from some columns of my one source file (a csv) into 'table 1', and another DDQ to transfer...more >>

I have a really dummy question but can't figure it out and I'm losing my rag. I have two tables which are joined using a left outer join. Now foreign table holds a list of times what what kind of update happened to a row in the primary table. (It's not a SQL update it's something else) so it'...more >>

I need to store address information in the database for validating insurance risks. The problem with storing is that the information is hierarchical and is also dependent on the territory. UK Postcode system has four levels of hierarchy (Area, District, Sector and Unit), French postcode system h...more >>

greetings, i would like to ask how to implement string fucntion ove sql server 2000. i am more on using c language, which have a built in function like strcmp(). the motive i would like to use string compare is i am merging two similiar replicate table, which need to verify very same id...more >>

Hi all, I have a bit of a task... We are hitting an old VMS system with a sql statement through an Intersystems Cache ODBC driver (very old - not the most recent driver - not upgrade-able). I'm firing the query through a DTS. In leighmans (sp?) I seem to have to use very "simple" que...more >>

Hi Group Provided my application knows that a given index say, being defined out of two rows in a table exists, can I asume a sorting order acording to the existing index or do I HAVE to explicitly add an order by clause? Example: Table "order" is having among others row "sales-id" and "...more >>

Which query is faster? assuming condition1 results in table scan (say 10000 records). IF EXISTS(condition1) BEGIN operation1 END ELSE BEGIN operation2 END IF NOT EXISTS(condition1) BEGIN operation2 END ELSE BEGIN operation1 END ...more >>