Hello All, I would like learn Hierarchies in "SQL". I do have the Transact-SQL Cookbook by Jonathan Gennick. Are there any other book that talks about this topic that I should read. Also, if anyone can point to some links that would be great as well. Thanks, Gopi ...more >>

SELECT sum(a.curr_yr_indem_members) AS INDEM FROM tblHpUSA_health AS a UNION ALL SELECT sum(b.[indem insured]) FROM tblHpUSA_life AS b UNION ALL SELECT sum(c.indem_insured) FROM tblHpUSA_Property AS c produces 15164596.0 31876838.0 4975274.0 How can I produce one nu...more >>

I have a stored procedure below.. When I run it with a well parameter in query analyser 3 of select statements return me. (i named select statements 1,2,3) But in asp page when i call this procedure same as query analyser it returns me 2 select statements value? what is the problem ? CRE...more >>

hi what are the differences between bcp and bulk insert? Thanks ICHOR ...more >>

Can a field name be constructed, and if so, how? For example; Declare @Date SmallDateTime Set @Date = GetDate() I would like to create a field called 2005_Sales in a temp table called 2005_Sales by constructing it from: Convert( char(4), year( @Date) ) + '_Sales' If a field can no...more >>

hi 35. (35) You are a database developer for a hospital. You are designing a SQL Server 2000 database that will contain physician and patient information. This database will contain a table named Physicians and a table named Patients. Physicians treat multiple patients. Patients ...more >>

34. (34) You are a database developer for an SQL Server 2000 database. You are planning to add new indexes, drop some indexes, and change other indexes to composite and covering indexes. For documentation purposes, you must create a report that shows the indexes used by queries before and afte...more >>

Dear All, I am getting this error frequently and some times sql server is getting stopped automatically. [DBNETLIB][ConnectionWrite (WrapperWrite()).] General Network error. Check you network documentation. Can any one help me out to get rid of this. OS : Windows 2000 professional SQL...more >>

why is the answer B and E? and why isnt it C and E? 28. (28) You are a database developer for Proseware,Inc. You are creating a database named Human Resources for the company. This database will contain all employee records and demographics information. The company has 2,000 employees and ...more >>

hey all, i have 4 fields: fld1 fld2 fld3 fld4 Some values contains NULLs. and I'm trying to create a concatenated field. fld1 + '.' + fld2 + '.' + fld3 + '.' + fld4 AS CustomField Only 1 out of a 1000 records worked. I guess because all 4 fields weren't NULL. Any ideas? thanks...more >>

does anyone know of a way to force a stored procedure to issue a print statement every 25 seconds? im having a timeout issue on a stored procedure called from asp. the procedure has been optimized for speed, but it still takes around a minute to run. the current asp timeout is set at 30 seco...more >>

Hi all, I keep getting this error message: Server: Msg 156, Level 15, State 1, Line 10 Incorrect syntax near the keyword 'Else'. I just want to execute a stored procedure if something is returned by this: "Select hl7file from notes where FileWasCreated is null" could be a large text fil...more >>

I have this JOIN(attached below)....Obviously I want to get data based on a parent-child relationship. CONTACT1 being the parent and CONTHIST being the child. Obviously, I have MANY history records for a contact. I want to get data where history is out 21 days, but I also want to get data wher...more >>

if the kill command is executed on a process that has an open transaction, what happens to the transaction? does it rollback? ...more >>

I need to launch an exe application written in VB6 from stored procedure or trigger. I know I have to use os_oa* but I cannot find any document to help me build one. Many examples that I found have something to do with com but I only have exe. I know this is not the way to go but I was told to...more >>

Ladies and Gentelmen, here is my problem -- I am using SQLDMO.transfer facilities to move objects between databases. Since I only need to move certain objects, and not all, I have to add each object by name, and each one is handled individually -- a script is created, ran, and if necessary,...more >>

Hi I try to write a trigger that move one record to another table before the update take place...like this I think CREATE TRIGGER Table1 ON dbo.Table1 FOR UPDATE AS INSERT INTO Table2 SELECT * FROM OLD thanks for any help Paolo L....more >>

I want to return the ID of a table if the data exists in any column of the table Main === ID col1 icol2 col3.....col10 1 a;1 b;44 a;5 2 b;0 b;87 c;1 3 b;44 c;0 d;10 If I am looking for 'b;44' my result should be 1 3 I really appreciate your help...more >>

Hi guys.. small help.. When i execuet this I am getting this error SET IDENTITY_INSERT TABLE2 ON GO INSERT INTO TABLE2 SELECT * FROM TABLE1 GO SET IDENTITY_INSERT TABLE2 OFF Server: Msg 8101, Level 16, State 1, Line 1 An explicit value for the identity column in table 'TABLE2' can ...more >>

When copying files in DB2 (at least in the OS/400 version) you can use an option "MAP/DROP" on the copy command. What this does is copy the data from one table to another, but only for columns that have identical column names and data types. Can I do something similar to this in SQL Server...more >>

So we have this stored procedure that does an incredibly complex algorithm to sniff incoming records for possible matches. It's 7 pages of code. (probably 9 or 10 pages with word-wrapped printing) So recently, it was discovered that the thing needs to run in two different modes. So Bonn...more >>

1) Can this be done? 2) What does the syntax look like when using tables from multiple databases in a query? I get errors on any tablenames that aren't in the Database in the combo-box at the top of the QA command window. ...more >>

Thanks to Alejondro for posting a solution to my original post. My original post was this... << CREATE TABLE [dbo].[Employee] ( [EmployeeName] [varchar] (50), [ActiveFlag] [smallint], [ActiveTS] [datetime] ) The front end is a vb app. When a column gets updated to a non-zero, the cl...more >>

I have a child table with semicolon-delimited data in a single column. Based on what is in the first part of the semicolon-delimited data, I need to write a value to a column in the parent table and then remove that first part of the child-table column. I guess I want to do a ...more >>

Hi, I'm running this: DBCC CHECKDB ( '<database>', REPAIR_FAST ) WITH ALL_ERRORMSGS, TABLOCK And I'm getting this: Server: Msg 8649, Level 17, State 1, Line 2 The query has been canceled because the estimated cost of this query (13451) exceeds the configured threshold of 10800. Contact...more >>

Hi, When I debug a stored procedure, if one of the input parameters is datetime datatype, it doesn't matter what I type in, i got an error said "Invalid character value for cast specification" Any idea what value I need to key in? example create procedure ztest @Input smalldatetime ...more >>

Hi all, I have a table that describes bus routes. The table includes the following columns: ID - just an autonumber Line - The line number / bus number Way - A way number. Each line/bus can have multiple ways it travels on OrderNo - The order of how the stations will be visited (starting f...more >>

In VB6 I store PDF files as BLOBS in a TEXT column of a SQL table The VB6 code works and I'm trying to do the same in VB.NET without success. Any help? Dim vText As Variant, vLinesFromFile As Variant Dim nFile as Integer Dim sSQL as String sSQL = "SELECT * FROM CIP_WF_DOCUMENTS WHERE [FILE...more >>

I have this query...(Attached below) It does have a UNION in it and I need to expand this by probably adding even a couple more UNION clauses. My question is whether I should do this more efficiently using INNER and/or OUTER joins. Not sure how the efficiency Gods might react. And I am conc...more >>

Hi, I have an application that calls a strored procedure that return a record built from several tables joined. This data is then present to the user in application where they can then edit and save. The problem I have is that the as the user changes different bits of data, it will be...more >>

When I execute the below, the system hangs an no response. Am I missing anything? Exec master..xp_cmdshell '"c:\program files\winzip\winzip32.exe" -min -a -r -en F:\Users.zip F:\Users.txt' Madhivanan ...more >>

I need to be able to add an extra field to a table. A YTD figure. How can I do this - it will also need to be grouped by a code as well EG: Month Budget New Field?(YTD) 1 50 50 2 40 90 3 20 110 4 10 120 ...more >>

Hi, Sorry to trouble you all, but I am not getting anywhere! I need to copy the contents of 1 table into a new temporary table. What I am doing is : SELECT * FROM U_regulargiver WHERE Status ='I' OR Status ='N' INTO TABLE #U_T_Committed Obviously I'm doing something wrong, but I ca...more >>

Hi all I have a store proc that that does various bits of data manipulation, but the last part I have used a cursor to update 2 fields in a table. This sp is called by a VB front end and then the contents of the table in question displayed. The problem is, if I run the sp directly (with...more >>

Hi, I have written a procedure that takes a varchar argument. I pass a comma separated string to for a numeric field. acTest is numeric primary key. I try to select given records like 22,23,25 & I pass these values by a varchar value. The procedure is given below : CREATE PROCEDU...more >>

I have a proc that creates a temp table and then does a join to an order table. I don't understand why I'm getting a table scan on the permanent table, with the results merged with the temp table. The pkey on both tables is an int and it's indexed on both tables. there are 5,000 rows in...more >>

The pay period begins on a sunday and ends on a saturday. Som employees work on the weekends, some don't How can I calculate by week how many hours an employee has worked Date EmpNumber Hour 20050522(sun) 123 7. 20050523(mon) 12...more >>

Hi, I can see that by using the object ID rather that the object name, the following SQL query works. Has anybody got any idea what is causing the error? -- Works OK select o.id ,checksum_agg(binary_checksum(=ADm.text)) from sysobjects o ,syscomments m where o.id =3...more >>