Programming SQL

Duplicate column name for OpenRowSet
I tried to do the following and got an error: Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "SPID" is a duplicate. But it is there only once and if I take it out, it works fine but SPID isn't there. Why is that? SELECT SPID, Status, Login,... 24 Mar 2010 20:33

First Function SQL Server 2000
(Steve Stephan) writes: Is there a work around for 2000 and the first function. My first thought was to use the Min but the explanation and solution for 2005 made sense to me. Let me know. Thanks. The query: WITH QueryB AS ( SELECT UniqueID, StartDate, UniqueID2 as NewUniqueID, ... 23 Mar 2010 19:31

How to get all constraints on all tables in a DB
Hi, How can I get all the constraints specified on all the tables in a given database? Thanks for your help. ... 23 Mar 2010 18:22

Where clause not working
I do this all the time but can't seem to see why this doesn't work This gives me back 4 rows when @UserID is not null. SELECT ChildGroupID = g.GroupID, ParentGroupID = NULL, Level=0 FROM UserGroup ug with (NOLOCK) INNER JOIN Group] g with (NOLOCK) ON ug.GroupID = g.GroupID WHERE (ug.UserID = @UserID AND g.Enab... 23 Mar 2010 16:00

Nesting Insert/EXEC
I just got a message trying to use Insert/Exec that said: An INSERT EXEC statement cannot be nested. Took me awhile to get what that meant. What it really means is that an INSERT EXEC cannot be in a nested Stored Procedure. So I can call a SP directly that has an INSERT EXEC but I can't call that same p... 23 Mar 2010 19:31

Sub Queries and Calculation
Hi, PO table PO Price 100 $2000 100-1 $150 100-2 $50 I want to write a query to sum Sub PO 100-1 and 100-2 (would be $150 + $50 = $200) then substract the sum to the main PO 100 of $2000 ($2000 - $200 = $1800) The result should be PO Price 100 $1800 I am ... 23 Mar 2010 18:22

SSIS Variables and variables. What is best practise.
Dear reader, At the moment I am strugling with the deployment and configuration of a set of SSIS packages. I have one package [A] which calls the packages A1, A2 and A3. Each package starts with reading the parameters from a database. I found a script on the web which does read all the matching parameters ... 23 Mar 2010 14:50

CTE and Case
I am trying to use the same SP to use Common Table Expressions with a different anchor (yes, Celko anchor). I think I can do this with a where clause but is there a way to do this with a Case statement? Just curious. With a Where Clause I might do something like: CREATE PROCEDURE GetGroups @UserID in... 23 Mar 2010 13:41

Bad column prefix, why does it work? (2000)
Hi group, Right now I'm busy migrating an old SQL Server 2000 SP4 database to 2008. I came across a query that worked in 2000, but not in 2008. I'm wondering why it worked all this time in 2000, because i.m.o. it should not. Here is a script that demonstrates the problem: CREATE TABLE dbo.T (ID INT) GO ... 23 Mar 2010 11:20

Help With PIVOT - Please
Using SQL2005 - Beginner Level Table 1 called Months2010 Columns - Month, BD, ED Example January 01/01/2010 01/31/2010 Table 2 called CM Columns - CMDate, CallID Example - 01/02/2010 123 SELECT Months2010.Month, COUNT(CM.CallID) AS Calls FROM CM CROSS JOIN Months2010... 23 Mar 2010 11:20