Hi experts, I'm quite confused with the using of @@error in Microsoft SQL 2000. For instance, Select * From Table1 If @@error <> 0 print 'Got error!!!' I can have the @@error to capture the error number 208 (Invalid object name 'Table1') if the specific table is not found inside data...more >>

Hi, On an SQL 2000, which of the following queries will have better performance: select * from Phones where ContactID in (select ID From Contacts where ClientID = 14) Or select * from Phones inner join Contacts on Phones.ContactID = Contacts.ID inner join Clients on Contacts.ClientID ...more >>

Hi, How can I add the VISIT Coloumn in the following tables, any pointers, help is highly appreciated. Tbl 1 Tbl 2 memno visits memno visits A1 1 A1 1 A2 1 A2 ...more >>

Hi, Is there a way to create an aggeregate function in MS SQL? For example, if you wanted to re-implement fucntion SUM(), how would you do that? Any ideas would be highly appreciated! Thank you, Andrey...more >>

Hello, I'm stucked with trying to identify records that are already present in a table when queried from another table.... For example, if I have 2 tables (Table A and Table B), Table B has 3 fields (a,b,c) out of which (a,b) are primary keys. I'm trying to insert records into Table B from Tab...more >>

Hi, I just wonder if someone could help me here: Say, i have a table with one column: 'Name' Name1 Name2 Name3 .... I need to get the string of "Name1, Name2, Name3, ..." The only way i see is to use cursor - but i know cursors aren't the best way if an alternative exists... So is...more >>

I have this query in ACCESS VBA: SELECT DISTINCT qryRCP.RCP_VendorID, Sum(IIf(nz([RCP_ReceiptQty],0)=0,0,1)) AS fldNbrRcpts, Sum(IIf(nz([SumOfRCQ_RejectQty]+[SumOfRCQ_ScrapQty],0)=0,0,1)) AS fldNbrRejScrap FROM qryRCP WHERE qryRCP.RCP_ReceiptQty >=0 GROUP BY qryRCP.RCP_VendorID, qryRC...more >>

Good evening, Does anyone have advice on how to trim down excessive latch wait time? One particular query (stored procedure) is to blame for almost all of it. This query is joining 7 tables... 1 of which is joined twice... another of which has several million rows in it. The query has nolocks t...more >>

How can I add a default value to an existing column via T-SQL? I tried the following but not successful: Alter Table MyTbl Alter Column MyCol Int Default 0.8 TIA ...more >>

I am writing a script that would give me databases of all servers. I also need to know whats the database size fo each database on each server. The way i get teh first one working was set up linked server to all databases that i need the information from. Then for each server get the databases f...more >>

I'm getting this error message in one of my procedures... Has anyone ever seen this, I've searched the group's most messages I found mention a firehose cursor which I know I'm not using... Here's the procedure thats generating the error, this is the first time I've seen this error the applicat...more >>

Hi All. I'm running SQL Server 2000 sp3 over a Windows 2003 Server, and my client machine is a XP Pro sp2, with SQL Clients sp3. I cannot debug stored procedure from my client machine and i recieve this error: Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1 [Microsoft][...more >>

Hello all. I have this code: DECLARE @a varchar(500) SELECT @a = (SELECT DISTINCT ProgrammName FROM Programmes) I got an error because the query returns more than one row. But the main problem is that I want with the result from that SQL to do some operations. How can i get the individua...more >>

GROUP BY Clause Specifies the groups into which output rows are to be placed and, if aggregate functions are included in the SELECT clause <select list>, calculates a summary value for each group. When GROUP BY is specified, either each column in any non-aggregate expression in the select lis...more >>

say SELECT error, severity, description FROM master..sysmessages ~~~why two dots here? but replace it by "master.sysmessages" will generate an error ORDER BY error Thanks in advance:) ...more >>

in tsql, given the table or view myfoo(date1, date2) how do i list out the differences number of miliseconds, seconds and minutes, hours and days between date1 and date2 for each row? ...more >>

I'm working with sqlserver2000 on a server2003. My e-mail via xp_sendmail is working good. But I have one problem --> every mail I send via xp_sendmail does not appear in de sent-items van the account of the mapi on my server. Can someone give a hint to solve this problem ? thanks Gee...more >>

Hi, I'm not sure what the correct newsgroup is to post this to, if this is not right please let me know. I'm very new to writing SQL queries and I have a couple questions. My situation: I have a table that has a ton of applications in it listed by name, manufacturer, version, and the P...more >>

Could someone elaborate on typical scenarios in which a struct is valuable or necessary in a db-design ? ...more >>

I work for an insurance agency that needs to run a multitude of checks on applications when they are added to our database. A large union query in a stored procedure works just fine, but is slow. I want to create a temp table, transfer the data into it, then run the union query against it. Thi...more >>

Can someone tell me how to format my XML string so when I load it into my table the field actually has a NULL value and not an empty space? My XML string is formated like this. <reimbursement> <tbl_Reimbursement> <Type10Rec Provider_Type='INSTIT' Provider_SubType='11' ReimDesc="" Status='P...more >>

What is the simpliest way to output from a SQL statement using a table that has a field with the smalldatetime format to output to 'mmddyy' format. Please help me with this issue. Thanks,...more >>

Greetings All: If I have the following code snippet (in a stored procedure): ----------- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANS SET @key = MAX(id) FROM app SET @newkey = @key + 1 INSERT INTO app(id) VALUES (@newkey) COMMIT TRANS RETURN @newkey ----------- and...more >>

I am trying to import an Excel spreadsheet into SQL Server. Why can I import an empty Excel column only if it is a text field? If the field is a number or date field, I get a conversion error. i.e. create table labresults ( analyte varchar(50), sampledate datetime, result numeric(19...more >>

Is it a known issue that DTC will fail using SQL OLE defined links from and to the same server? If I change servers within the link properties all is well. If I link to myself, or another instance on myself, this is what I get: Server: Msg 7391, Level 16, State 1, Line 1 The operation could...more >>

Hello I'm performing a query against a db with "for xml auto, elements" clause Something like Table Field 0 Setas Primary Key Field 1 Field 2 SELECT field1, field2 FROM table FOR XML AUTO,ELEMENTS If I run the query into Query Analyzer I get the result I aspect <table> <field1>v...more >>

I am bringing data from a file using bcp as below in a stored procedure. SET @SQLString = 'BCP STAGING IN "' + @file_path + '" -c -F2 -Uxyz -Pxyz' EXEC master..xp_cmdshell @SQLString The table has only one column varchar (1024). I need to add one more column called code which i can pass to...more >>

Is it a known issue that DTS will fail using SQL OLE defined links from and to the same server? If I change servers within the link properties all is well. If I link to myself, or another instance on myself, this is what I get: Server: Msg 7391, Level 16, State 1, Line 1 The operation co...more >>

Hi, I have an sp that creates temptables. I then add an identity column using alter table #Surcharge add [id] int identity(1,1) NOT NULL alter table #Extras add [id] int identity(1,1) not NULL If I Select * from the tables I can see the new column but if i try to reference it select ...more >>

Hello All, I have a table TableX with one column SQL varchar(8000). This column contains about 50 records (rows) like insert into tablename1 select datetime,...From Tbl1 Where.... insert into tablename2 select datetime,...From Tbl2 Where.... insert into tablename3 select datetime,...From Tbl...more >>

Using SQL 7: I have written a stored procedure which has significantly decreased query time. It is a straight-forward query. Anyway, at times it performs like a champ, and there are times when it runs as slow as the normal query, or slower. The system usage is the same at either instance...more >>

I am accessing a Linked Server. I create a #temp table with appropriate columns, and then put data into it like this: Exec RemoteServer.Server.Dbo.SearchSP '1' , '2' However, the column names and schema on the linked server changes and I have no control over it. Wha...more >>

I am trying to run a stored procedure that calls another stored procedure from within. The problem is that when it tries to execute the second procedure, that procedure gets stuck indefinitely in a locked status waiting for [compile] resource. I cannot kill it. If I try, the status says "...more >>

Hi Gurus, Is there any automated way to compare the contents of TWO rows in the same table of SQL Server ? Thanks in advance. Regards, Yog...more >>

Greetings All: I'm in the process of making a store procedures that generate unique application IDs for each application form submitted into the database. My stored procedure looks like the following: 1. Read in all existing application records 2. Find the max application ID, call it 'ID' ...more >>

Greetings group I wanted to expose my following case: I have an application that uses Microsoft SQl-Server like Backend but I am presented the following problem: I should keep and to recover in some columns files in format Word, Excel, PDF, Text, and graphics as Autocad or JPG. But the t...more >>

I need to get a value from a linked server based on a variable. To do this I have to use an exec call (as per Q314520). The problem is I want to get the value select by the exec call back into a variable in my Proc. How do I make this work: This works: set @TSQL= 'SELECT write FROM OPE...more >>

Hi, I am using the round function since i want two decimal places and it doesnt give me the correct results. The field in which i apply it is a Real with Auto number of decimal places. In my querries I have something like: value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34 ...more >>

Good morning, There is a job that runs daily to restore the production databases to a different server for testing purposes so that the test site can have live data on a daily basis. There is a script that runs after the database restore to ensure views/procedures/triggers are not referencing th...more >>

In languages such as C, VB, Javascript, etc, testing a condition before a looping statement, as shown below, is redundant (although I do see it sometimes); example: if (x < 0) { while (x < 0) { x += 1; } } With SQL, it would also be functionally redundant to surround an insert or ...more >>

Hi all, I want do a delete but defining the amount of candidate rows: delete from table1 where sinperiodid = 200 (returning 100 rows) I just delete the three first row of that set of results. How? Thanks a lot,...more >>

Does anyone have a script that can be used to return all users granted permission to a cretain table, it would be greatly appreciated! TIA, Jordan...more >>

hi, There is something i really don't understand. There is a remote server shows under Security --> Remote Servers and I just cannot delete it. I run sp_dropremotelogin --> it said "There are no remote users mapped to any local user from remote server 'repl_distributor' I run sp_dropse...more >>

Hi, I know that a stored procedure will return as many results sets as SELECT statements are in. I want to actually mark which selects should be returned as results sets and which are just internal. Is there a way of doing this? Thanks, George. ...more >>

I need to compare 2 fields, but the one field has zero's in the front of the number??? 0000872509 to 872509 SELECT tblTenureMaster.PersonnelNumber, dbo.tblDIMHREmployee.HREmpEmployeeNameLast FROM tblTenureMaster INNER JOIN dbo.tblDIMHREmployee ON tblTe...more >>

Apologies for the cross-post, but this truly is a two-sided question. Given the option of creating Looping statements within a stored proc of sql server or in the code-behind of an .net webpage, which would you choose and why? Reason I ask is I created a webpage which essentially runs throug...more >>

Fellows, I ask myself how many system tables are capable of be deleted (i mean, the contents, of course). For instance sysmessages apparently only contents disposable data. Thanks a lot for your support and best regards,...more >>

Hi, We have a number of SP's which have a couple of datetime params. We expect the format to be such as 'YYYYMMDD' to get around the issue of US vs UK dates. Unfortunately if an invalid date is passed, it errors straight away with (e.g.) Server: Msg 8114, Level 16, State 4, Procedure usp_Ge...more >>

Dear all, I am doing the following code EXEC sp_addlinkedserver 'MYSERVER', N'SQL Server' GO And getting this error Server: Msg 18452, Level 14, State 1, Line 12 Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. What am I doing w...more >>

Dear All I am trying to do the following code... insert into [SERVER].MyDatabase.dbo.MyTable (Param1, Param2, Param3) values (1, 1, 1 ), however I keep on getting the message Server: Msg 7411, Level 16, State 1, Line 1 Server 'SERVER' is not configured for DATA ACCESS. Can anyone tel...more >>

My table has some hexadecimal data but stored as string. I want to convert this to hex and then to int. How do I do this? Data in my table: 139D5 6374 63B2 620B ABC7 6391 6FA6 604A 139D6 This should be ideally 0x139D5 0x6374 0x63B2 0x620B 0xABC7 0x6391 0x6FA6 0x604A 0x139D6 ...more >>