I have an application written in Delphi connecting to MSSQL 2000 via ADO. At the server, using the example pubs database I've created a stored procedure: CREATE PROCEDURE spAuthor AS select * from authors My application displays the resulting records in a grid. To my surprise, the ap...more >>

I am trying the following code in a trigger. exec master.dbo.xp_cmdshell 'net send UserName "Test' + char(13) + 'Line2"' However I can't get it to work. I have tried. ^T char(10) char(13) + char(10) leaving a line. leaving a line with the / character Leaving off the double quotes with a...more >>

how can i reset the identity.... lets say next row will be 1001 but i want it to start from 2001 from now on... ...more >>

How can I copy a table with all indexes and constraints, and create a new table ? I have one table with 25 index keys, do I have to recreate those indexes again or is there any better solution to do so ? Say, my Table is : Inv2003 and I want to create a new table called Inv2004 with same in...more >>

First, who decides just who can ask questions? It seemed odd that questions where raised about the possible miss use of the clr and I did not see one question on new t-sql additions. ...more >>

Hi All, I have SQL server 2000 installed on my machine. I have added a server say S1 as a linked server. I have read only rights on the databases on that server. I have written a stored procedure which fetches data from the tables on the linked server S1 using queries of the type : s...more >>

( Sorry for Reposting ) How can i rewrite the following trigger without using cursor CREATE TRIGGER test on abc for insert as declare @a varchar(10), @b varchar(20) begin declare curSelect cursor local for select abc , description from inserted open curSelect fetch curSelect int...more >>

create and/or insert into a table, specifying columns once. Please ignore the non-relevant aspects of the demonstration example below, since it is designed to [sortof] concisely demonstrate what I am looking for... Which is, to be able to specify the columns that make up the result table in a si...more >>

We have server 2003 with sql 2000. as a client getting data from an IBM iseries server with DB2 UDB. The connection is through a linked sever with ODBC connection to DB2. If we invoke a stored procedure to txfr data from DB2 to SQL 2000 the stored procedure completes fine but only about 128k of data...more >>

Hi group, my brain is not working today. How do I get the following done: I have TableA (userid, actionCount), TableB (userid, action) with TableA being parent of TableB. I want in a stored proc to do the following: declare @sql varchar(999) declare @userid set @userid = 111 -- this ...more >>

I have a Table with a bunch of posts.. I want to select all of them and adda field called "RowCounter" that will show the posistion number in the SELECT.. To simplifiy this.. I try with example.. I have...: Id Name 1 Peter 12 Michael 33 Anna But I want ...more >>

Is there any way to use a derived column elsewhere in a query (as in the example below)? In my app I derive a column using a CASE statment and want to use that result later on in the query to create another derived column indicating an alarm situation. Thanks. SELECT OrderID, DATEDIFF(...more >>

I need some help with writing an update statement. What I want to do is compare tablea with tableb if "any" of the information of "any" column in tableb does "not" match with any column in tablea then I want to update the information. I know the general general update statement update...more >>

Hi, I'm having a problem with idenifiers. I am running this code below which works fine \\\ INSERT _WORKINSTRUCTIONS (WICODE, WI) VALUES ('CAL','CALIBRATION PROCEDURE') /// The values are from fields in an application typed in by a user. However, if the user entered MANUFACTURE'S STAN...more >>

How can i rewrite the following trigger without using cursor CREATE TRIGGER test on abc for insert as declare @a varchar(10), @b varchar(20) begin declare curSelect cursor local for select abc , description from inserted open curSelect fetch curSelect into @a,@b while @@fetch_st...more >>

Any tip of different ways achieving a circular consistency check appreciated. ===================================================== Given a mothertable thus: create table dbo.BrokerOffices ( OrgNumber bigint not null, OfficeCode dec(5) not nul...more >>

Why does this situation occur?: On some applications, I have to set execute permission on all the stored procedures that a particular user runs, on others I don't. ...more >>

Is there a way to perform a count on multiple fields with different criteria? Select * from Count(gender=f) As GenderCount, Count(race=1) As RaceCount, etc.... basically using different criteria. Thanks...more >>

I have a "detail" table in a master/detail relationship that has over 250 million rows. This table is very busy and is constantly being both read and written as actions are occuring on the master records. I would like to reduce the contention that is occuring within my application for these re...more >>

Hey all, I am running SQL 2k on Win 2k. We are using Access as a front end for our SQL database. My Access app wants to talk to another Access database, and the way to do this through SQL is a linked server. True statement? I have added the linked server I want, and SQL queries it f...more >>

Hi, I have a dts package that copies an entire table from destination to local database. Now, what I would like to do is run an update/insert script to update existing data daily and insert new ones. Any help with code/idea would be highly appreciated. ...more >>

Hey all, ...more >>

The enterprise manager has the ability to generate a script of the schema for my database, but I can't find any facility to generate a script of the current data. I will also need to modify any such script to only back up certain rows of each table. My database contains multiple "application def...more >>

Hi, I use "backkup log dbname to disk='c:\a.log'" to back up transaction log. It take too long to finish. Is it a fast way to backup the transaction? Thanks. ...more >>

"Do something for each row of the query" The only way I know is: --- declare @C cursor set @C= cursor for select F from T where ... declare @F int open @C while 0=0 begin fetch next from @C into @F if not(@@FETCH_STATUS = 0) break exec myStoredProc @F end close @C deallocate ...more >>

Is there a reason ROUND is not working on my float columns? I have created a new database with a new table with a float column "floatCol" populated with these numbers: 2.3456 3.3355 1.2345 However if I say SELECT ROUND(floatCol,3) from TestRound I get these results: 2.3460000000...more >>

Hi I am trying to import some text files into MS SQL 2000. I want to import them into a table with the first column being the filename (excluding the extension) and the second colunm being the contents of the textfile, I want the entire textifle to fit into the one cell (ignoring any commas, ...more >>

Hi, I need a help on SQL query. Is there a way to capture the first missing number from the consecutive list of numbers from 1 to whatever maximum number? Cyont ...more >>

I sure hope this is a common question with a common answer... I have a SQL database that the users interact with using a VB application. They are able to build and run their own queries all day. We have noticed that a query they may run in the morning returns in seconds, but the same qu...more >>

I have a table where I want to mark one record per contact as primary, but allow multiple records per client to be not primary. tblClientLocations has a bunch of fields including ContactID (Int) and PCL (bit) [Primary Contact Location], as well as a bunch of address fields. I can't use a un...more >>

Hi, I'm working on an application from visual basic that does a search on a database, based on various criteria. Some of the options are not necessarily required, but allowed to narrow down their search. I have written the stored procedure to accept a varying amount of parameters, but I'm h...more >>

I have 2 tables A & B Table A is a new empty table. Table B has all my old data. Some of the columns in the two tables have the same name. I would like to be able to insert all the matching columns in table B into table A. in theory... INSERT INTO A (sp_columns B in sp_columns A) SELECT (...more >>

Ok, I have a access database(from ISA Server) It has a username and a time in HH:MM:SS format. It logs the time when the user goes on the internet, and when they aren't browsing no times are logged. What I want to do is find out how long they are one for. For example say the first time i h...more >>

I have an important table that I'd like to have a backup each time a row is modified or deleted. For the deleted, I simply created a trigger AFTER DELETE to SELECT * FROM DELETED and insert the row to my backup table. But what about UPDATE? There is no "UPDATED", how do I get the row that's be...more >>

whats the syntax the enter the name O'Toole into a varchar field in sql server? thanks ...more >>

How would I pull info from say the [PHONE].[Phone_Type] column into this select query? select * from address a where a.customer + a.addrtype_code in (select top 1 b.customer + b.addrtype_code from address b where a.customer = b.customer and b.addrtype_code in ('COMM', 'PRIM') ord...more >>

Hi, Using C#, I can trap any SqlException, and I wish to show the user (in the UI) which field is duplicated... However, the SqlException only offers a Number (2627) and a message. This message includes the name of the constraint being violated, but as the message can be translated, I can't rel...more >>

hi guys just a small doubt why does MAX(ISNULL([Sequence],0))+1 returns no row whereas ISNULL(MAX([Sequence]),0)+1 returns 1. would appreciate if someone can clarify the above eventhough it is a silly question thanx in advance bala...more >>

I have a table set up in transacational replication which is being replicated in 5 other servers. Now, I need to modify the table structure which of course I can't do while replication is on. Is there any way to modify the table structure while replicaiton is on without disabling the repli...more >>

THe problem is this, I have a procedure that needs to build a dynamic sql string and place the data into a temporary table. I htne need to loop through this data with a cursor and update a field accordingly. Sounds simple but it has proven to be a royal pain in the a**! I have found bits and ...more >>

I have the following table: CREATE TABLE [dbo].[TablesLastUpdate] ( [TableName] [char] (50) COLLATE Polish_CI_AS NOT NULL , [LastUpdate] [timestamp] NOT NULL ) ON [PRIMARY] GO and I use it to keep record of last update time of other tables. I have used a timestamp field instead of da...more >>

Hi All, I have the following requirement - in a listing page , the user can specify / configure the columns he can view . For this. 1. the columns (as it exists in teh DB) are stored in a Table. 2. I need to now select these columns (the fields are mapped in a view). Other than forming a d...more >>

Hello, I have a table with 2 columns. Col1, Col2. tbl1: Col1 Col2 a1 b1 a1 b2 a2 b1 a3 b3 a4 b2 a4 b3 Where multiple records exist in Col1 I want to concatenate the Col2 values and insert both into new table new table tbl2: Col1 Col2 a1 b1, b2 a2 b1 a3 b3 a4 b2,...more >>

Hi all, I have a simple update query that runs nightly. Last night I got this error MSG: Does anyone know what this means? Server: Msg 7391, Level 16, State 1, Procedure MelcoreplUPDDteSchd_SP, Line 13 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unabl...more >>

Hi, I have a table that holds contact details about people: ID Type value 1 mobile 0000 1 email fdfd@hotmail.com 1 home 000 2 e-mail h@hotmail.com 2 home 11 3 mobile 000 (Etc) I want to select from that table only a persons m...more >>

Hi: I have searched all over and haven't been able to find any documentation on: Package.GlobalVariables.Item("MyGlobalVariable").let_Value(pRetVal AS Object) I was wondering whether any of the members has run into or used this function. Regards, Saeed...more >>

Hi, This is bound to be simple, but is eluding me I'm required to write an sp which has a parameter to be used in an IN clause. This param has one or more items seperated by #'s. What I want to do is to be able to use this without using sp_executesql (the sql will be so large it will be hellish t...more >>

Hi: I have searched all over and haven't been able to find any documentation on: Package.GlobalVariables.Item("MyGlobalVariable").let_Value(pRetVal AS Object) I was wondering whether any of the members has run into or used this function. Regards, Saeed...more >>

Hello, I have two issues, hoping someone can help. Issue 1. I have various DTS packages that copy data from Progress Database to Sql Data Warehouse. The ODBC Connection is stable and packages have been auto scheduled by creating a job that is managed by the SQL Agent service to run daily ...more >>

hi i made a trigger on a table in wich i'm inserting moer than 400 rows i would like the trigger work for each row because i do that for each row i would like to know if a fields exists in another table "select .. from .. where .. not exist in select .. from inserted" so if i do that with mo...more >>

Hello, I have a dynamic sql select statement which i execute using sp_executesql and get the returned value into an output local variable. But I want to put the result into a cursor for the case that the select statement return more than one entries. Do sp_execute sql returns a cursor? ...more >>