Hi, I have the foll statements that are calling a dts package. I deliberately misspelt the table name on the last update statement but my transaction doesnot rollback why the DTS is suppose to rollback SET NOCOUNT ON BEGIN TRANSACTION Select @doc_no = str_inv_no from dbo.arctlfil...more >>

need help getting this work trying to create dynamic whereclause to be executed ========================================= create table proj ( projID INT PRIMARY KEY , projtitle varchar(20) NOT NULL ) INSERT proj (projID,projtitle) VALUES (1,'A') INSERT proj (projID,projtitle) VALUES (2,'...more >>

How? I am wanting to export a db. It is unlikely that i will have access to a destination server via a network or internet connection. So i am intending to export my db to an sql script. I have used the generate sql script option previously, but have found it only exports the db object...more >>

Hai, I want to Kill a Server Process. I have logged in as an user who is not a member of Process Administrator Role. Can this be done? How to do this? Regards, Sevugan.C ...more >>

I am trying to retrieve the latest Order Entry date for a given Order Type for a given Customer. Using MAX this almost works but it returns mutiple records when the dates are not unique. I tried TOP 1 but that did not help. Is there a way to return just one of the MAX records? Cust ----- ...more >>

why query analyzer is not giving me the correct value back? See code below... "it don't matter if the subdomain exist or not it return zero when I test in query analyzer" sp.......... CREATE PROCEDURE CheckSubDomain ( @SubDomain varchar(30) ) AS IF EXISTS ( SELECT SubDomain FROM Ac...more >>

Hi, I have a table that stores the next bill number avail for usage and a proc that select the that bill number prepares some bills and then update the table with a new bill number for later usage. I need a way to lock the table after I select the bill then relase the table after I update it ...more >>

Hello All I use the following code to load a XML file to SQL Server Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBL.ConnectionString = "provider=SQLOLEDB.1; &_ data source=Serv01;database=mydb;uid=xx;pwd=xx" objBL.ErrorLogFile = "c:\error.log" objBL.Execute "c:\temp...more >>

I have records in a table field with the following (Note the number value after the 4 characters could increase or decrease): ENG\djke2456 ENG\dgut57689 housingtobe\jkli13456 housingtobe\dgyt9021 housingtobe\prgh028765 I would like to know if there is a way within a SQL statement to remove...more >>

All I have a Stock on Hand table (DDL below) that I am trying to populate using the example XML below. Can anyone assist with the Mapping Schema File so that I can load the XML file using XML Bulk Load as per KB article http://support.microsoft.com/?scid=316005. Thanks for any assistance. ...more >>

I have loaded disk drive information by computername in a SQL DB. I am interested in Freespace. When the data is loaded into DB its in (K). How can I display the freespace in MB? Query: select SystemName,Win32_DiskDrive_Model,Win32_LogicalDisk_FreeSpace from SystemInfo The result is...more >>

Hi, I have SQL Server code that calls a COM+ component (which is written in ..NET). SQL Server stored procs make the calls by way of sp_OACreate etc. It runs _really_ slowly on one particular machine. The only difference I can find is that the slow machine is locked down tighter in ter...more >>

need some help converting this to a string which can be executed in a stored procedure ================================================================ SELECT @List = COALESCE(@List + ', ', '') + CAST(InvoiceID AS varchar(5)) FROM invoices, Projects WHERE Projects.ProjectID = Invoices.IDProj...more >>

damn touchpads....... I have a query: SELECT id, name, rank, serial_number from status this will return many rows...if all values are null for a single row, i need to be able to assign a unique "id" for that row (PK constraint on another table) is there a way to do this so that for each r...more >>

looking for an example of how to correctly build up a dynamic where clause based upon input parameters ...more >>

--Hi! --Using SQL Server2000 --I have trouble with a query which is similar to the (simplified and --constructed) example below. create table Salary (pnr int not null, SomeDate datetime, salary int) insert into Salary (pnr, SomeDate, salary) values (1, '2005-06-01', 10) insert into Salary...more >>

I have this code that I am using to populate the number of visits by each day and cost center. I have to create a temp table as first I filled all the days along with cost center and then trying to update the visits. In this way I can get all the cost centers even if some of them had no visits...more >>

Hello, all. How can I substract 16 hours from today (ie rightnow, getdate)? Sounds simple, but I can't seem to get it. Thanks Roz...more >>

I have update statement: UPDATE r SET r.comment=case when r.comment is null then e.Parcel_No else r.comment+';'+e.Parcel_No end FROM table1 r INNER JOIN table2 e ON r.[ID]=e.[Invoice_No] If I have 2 or more rows in the table2 with the same Invoice_No, I get always one Parcel_No into fi...more >>

Hello I have a SQL Server SP4 instalation and a SQL Server SP3a instalation. I have a store procedure that runs fine on the SQL Server SP3a but when I run the same storeprocedure on the SQL Server SP4 I got the error message: Server: Msg 301, Level 16, State 2, Line 13 Query contains an ...more >>

Hi experts, I have a variable with type decimal(9, 3). Is it possible to return the values without 0 at the end when it is unecessary ? Examples: 1.000 should return 1 0.750 should return 0.75 0.500 should return 0.5 0.125 should return 0.125 Or should I write the function for that con...more >>

I have a database that stores survey information. I've been asked to add information to it to test scalability. Well, its' hurting. What I have is a 'sample' table or the people that took the survey and then several related tables. Lets' say the sample table looks something like tblData_S...more >>

I have the following table defined: Not my design/idea and I can't change it) CREATE TABLE [dbo].[Task] ( [TaskID] [ROWIDENTIFIER] NOT NULL , [Name] [SHORTNAME] NULL , [Description] [SHORTDESCRIPTION] NULL , [PreviousTaskID] [ROWIDENTIFIER] NULL , [NextTaskID] [ROWIDENTIFIER] NULL , ...more >>

Hi, Can anyone explain me how to use the format_string in sql server 2000? I want to format dates for e.g. month(getdate()) gives me 6, but i want it to format it as june. Anyone knows the solution? ...more >>

Hi, I am running this query below which aims to consolidate the data from a table called Fin0AllAustria into a table called Fin0Austria. Fin0AllAustria's primary key is SE (an ID number), RS (which can be 'R' or 'S') and Quarter combined, while Fin0Austria's primary key is SE. Therefo...more >>

If you have an job interview question of how do handle SQL locks and blocks whats your answer?...more >>

Hi there, I've seen a lot of David Gugick's posts regarding a problem using= stored procedure local variables and non-clustered indexes. It's= definitely a cause for concern having seen how many stored= procedures actually use local variables to pass in a value into= a query's search clau...more >>

Hi to everybody I'm having some trouble while trying to solve this issue. I have this table: DAY HOUR MINUTE ID FIX_VALUE_A FIX_VALUE_B VARIABLE_C VARIABLE_D VARIABLE_E 103 897544 750916 26 17 04 ...more >>

Hi, In the BOL is says that DBO and sysadmin role can execute sp_updatestats. However, the database owner gets this error: Server: Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 14 User does not have permission to perform this action. I've found that I can only run it as sy...more >>

Good morning everyone, I would like to create a query that returns merged rows using a comma delimited list. I've seen many post regarding this point and I found very usefull information. The thing in my case is that I do not want only 1 row to be returned but many. Let me show first a da...more >>

I am in the process of writing a backup utility for a client. I’m trying to create a pickup form that will let the user to select a backup file from SQL Server local directory, similar to Enterprise Manager Backup Wizard filename pickup form. I’ve got list of Directories from SQLDMO.Serv...more >>

If I have a data set that contains a date field, and I want to return the one earliest in the results would this work best (and all the time?)... Select Top 1 fldClientInv, fldFilingDate From tblPriority Where fldClientInv = '14095.0005' Order By fldFilingDate In the result set above, there...more >>

I am trying to pass a variable to an openquery stmt within a proc. The following worked a couple of times, but I am receiving the error below now ? Any suggestions on a method to pass the variable into the OPENQUERY Statement ??? CREATE PROCEDURE usp_GetData (@REQUEST_ID as varchar(10)) ...more >>

Example: A table: Field1 varchar Field2 varchar Now, I want to retrieve all Field1's in this table based upon the following criteria: If Field2 = "X" then retrieve record Else If Field2 = "T" then retrieve record Else If Field2 = "A" then retrieve record Else Don't retrieve record...more >>

Dear gurus, I’ve created a link between an A server (sql2000k) and an B server (sql2000k). They belong to different NT domains as well as different countries. That link has been created from one third server-country but I think that doesn’t matter so that’s not strange at all but th...more >>

I've been working on a new client-site with over 4000 stored procedures that were reverse-engineered from an Oracle application (into SQL 2k sp3), and have been handling the task of tuning many of the under performers. One quirk to the reverse-engineering solution they used is that the syntax...more >>

-- In SQL Server 2000 --When run from Query Analyzer I correctly get identification of line numbers having duplicate values of SKU_NameUsedBySCS: -- LineNumber1 LineNumber2 -- 2 5 -- but when I try to create a stored procedure having this same code I get: -- Invalid object n...more >>

Following advice in this group, I've been reading ISO 11179-5 for element naming. They give an example where redundant words in the representation term are deleted e.g. Employee_LastName_Name becomes Employee_LastName. When this column appears in my Employees table, is it acceptable to remo...more >>

Hello experts, I'm having a problem showing the result of two concatenated nvarchar variables which retrieve data from an ntext column, is this a known problem? is there any solution for that? declare @a as nvarchar(4000) declare @b as nvarchar(4000) Select top 1 @a = SUBSTRING(ntextcol, ...more >>

Hi i have a problem that i can´t solve! I have three tables last_id, customer, prospect. The "last_id" table have one colum that stores the last used id number (int), customer tables stores info about the customer and a "last_id" number and the prospect stores new customers! I want to make ...more >>

Hi, I hope this is the correct place for this. I need to make a copy of the files that make up a database. I am working in C#. I do something along the lines of the following not syntactically correct thing: SqlConnection(...Database=myDatabase...) SqlCommand(sp_helpfile) //to get the nam...more >>

hi, i have 4 tables: Contract, PaymentRequest, CreditCard, BankAccount CREATE TABLE Contract ( ContractId int identity(1,1) primary key, Duration int, TotalAmount money ) CREATE TABLE PaymentRequest ( ContractId int references Contract(ContractId), BankRefId int, ...more >>

I'm looking for some CD-Rom based SQL Server training for some of my .NET developers and am wondering if anyone has used the Silico-Magnetic CDs? If so, are they any good? Can anyone recommend any other training materials. Here's the amazon.com link to the Silico-Magnetic training: htt...more >>

hi, i am trying to connect to oracle 9i using sqlserver 2000 linked server. here the linked server name is intersql. when i put this statement in begin and end transaction block and try to execute it is giving the following error Server: Msg 7391, Level 16, State 1, Procedure ihmtproc_em...more >>

Executing exec sp_spaceused <my_table> I notice this: reserved --> 4483504 KB data --> 2536720 KB unused --> 1896984 KB I have to "free" that space and I tried the following: 1) DBCC DBREINDEX (<my_table>) 2) DBCC SHRINKDATABASE ('<my_db>') but the didn't work. How can I do...more >>

hi what is a workload file? ...more >>