December 8, 2008 at 10:23 am
Having an issue with the datetime variable in the concatenation. I've tried it several ways and can't seem to be able to figure out SQL wants.
All I'm trying to do is to count all the entries in the BUG table for all the DB's ( about 600) that were opened in 2008.
Any suggestions would be great
I can get it to work against a single project DB but not inside the EXEC statement.
this works
DECLARE @BUGCOUNT as int
DECLARE @Startdate as datetime
SET @Startdate = 39446
SET @BUGCOUNT = (SELECT COUNT(*) FROM td.BUG WHERE CAST(BG_DETECTION_DATE as int) > CAST(@Startdate as int))
PRINT 'Total Defects in Project for 2008 ' + cast(@BUGCOUNT as Char)
This does not.
SET ANSI_NULLS ON
SET NOCOUNT ON
DROP TABLE BUGCOUNT
CREATE TABLE BUGCOUNT (BUG_TOTAL int)
-- create cursor to hold a list of all database names
DECLARE @BUGCOUNT as int
DECLARE @DBName as varchar(35)
DECLARE @Startdate as datetime
SET @Startdate = 39446
DECLARE DBNameCursor cursor for
SELECT name
FROM master.dbo.sysdatabases
WHERE name not in ('master', 'tempdb', 'model', 'msdb', 'pubs', 'qcsiteadmin_db', 'distribution', 'BofA_Export', 'QCDiscovery')
ORDER BY name
FOR read only
-- Calculate total of all defects in each project and add to table
OPEN DBNameCursor
FETCH NEXT FROM DBNameCursor INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC ('SET @BUGCOUNT = (SELECT COUNT(*) FROM ' + @DBName +'.td.BUG
WHERE CAST(BG_DETECTION_DATE as int) > CAST(@Startdate as int)')
PRINT 'Total Defects in ' + @DBName +' for 2008 ' + cast(@BUGCOUNT as Char)
INSERT INTO BUGCOUNT values (@BUGCOUNT)
FETCH NEXT FROM DBNameCursor INTO @DBName
END
DEALLOCATE DBNameCursor
-- Add all Project defect totals to get overall total defects for 2008
DECLARE @Total as int
DECLARE @ProjectTotal as int
DECLARE TotalCursor Cursor for
SELECT BUG_TOTAL FROM BUGCOUNT
FOR READ ONLY
SET @Total = 0
OPEN TotalCursor
FETCH NEXT FROM TotalCursor INTO @ProjectTotal
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Total = @Total + @ProjectTotal
FETCH NEXT FROM TotalCursor INTO @ProjectTotal
END
DEALLOCATE TotalCursor
PRINT 'Total Defects in all Projects is ' + cast(@Total as Char)
December 8, 2008 at 1:08 pm
I have a few comments:
- the main problem you are probably having is that your EXEC statement with dynamic SQL will be in a different scope than the @Startdate variable. Try looking up how to use SP_EXECUTESQL instead which allows you to pass in parameters to the dynamically executed string.
- Did you know casting a date value to int will do rounding? So if your BG_DETECTION_DATE column has times in it, values after 12:00 noon will be rounded up to the next day. If these have no time stored in them, you're OK.
- Is there a reason you need to CAST the date values to integer? Doing this will result in the query execution not using an index on this field if there is one available. I don't know how many years worth of data is in each database's td.BUG table, but I'm thinking you probably don't want to do a full table scan if there are more than 2 years worth of data.
- Your second cursor, TotalCursor looks like it can be replaced with the following:
SELECT @Total = SUM(BUG_TOTAL) FROM BUGCOUNT
or simpler yet, you could keep a running total in your loop for your first cursor and possibly not even need the temporary table BUGCOUNT.
December 10, 2008 at 2:35 pm
Thanks for your help on this. I have attempted to rewrite the script using your suggections and am getting closer. this is what I have at this point.
SET ANSI_NULLS ON
SET NOCOUNT ON
DROP TABLE BUGCOUNT
CREATE TABLE BUGCOUNT (DBName varchar(40), BUG_COUNT int)
-- create cursor to hold a list of all database names
DECLARE @BUGCOUNT as int
DECLARE @DBName as varchar(35)
DECLARE @Total as int
DECLARE @SQLString as nvarchar(500)
DECLARE @Startdate as datetime
SET @SQLString = N'SET @BUGCOUNT = (SELECT COUNT(*) FROM td.BUG WHERE CAST(BG_DETECTION_DATE as int) > 39446))'
DECLARE DBNameCursor cursor for
SELECT name
FROM master.dbo.sysdatabases
WHERE name not in ('master', 'tempdb', 'model', 'msdb', 'pubs', 'qcsiteadmin_db', 'distribution', 'QCDiscovery')
ORDER BY name
FOR read only
-- Calculate total of all defects in each project and add to table
OPEN DBNameCursor
FETCH NEXT FROM DBNameCursor INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @BUGCOUNT = 0
PRINT @DBName
EXEC sp_executesql @SQLString
PRINT 'Total Defects in ' + @DBName +' for 2008 ' + cast(@BUGCOUNT as Char)
INSERT INTO BUGCOUNT values (@DBName, @BUGCOUNT)
FETCH NEXT FROM DBNameCursor INTO @DBName
END
DEALLOCATE DBNameCursor
-- Add all Project defect totals to get overall total defects for 2008
SELECT @Total = SUM(BUG_COUNT) FROM BUGCOUNT
PRINT 'Total Defects in all Projects is ' + cast(@Total as Char)
I am still getting an error Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@BUGCOUNT".
I'm assuming this is an error in the @SQLString someplace but I have been unable to find it.
December 10, 2008 at 3:16 pm
I've made a couple of modifications:
SET ANSI_NULLS ON
SET NOCOUNT ON
DROP TABLE BUGCOUNT
CREATE TABLE BUGCOUNT (DBName varchar(40), BUG_COUNT int)
-- create cursor to hold a list of all database names
DECLARE @BUGCOUNT as int
DECLARE @DBName as varchar(35)
DECLARE @Total as int
DECLARE @SQLString as nvarchar(500)
DECLARE @ParmDefinition nvarchar(500)
DECLARE @Startdate as datetime
DECLARE DBNameCursor cursor for
SELECT name
FROM master.dbo.sysdatabases
WHERE name not in ('master', 'tempdb', 'model', 'msdb', 'pubs', 'qcsiteadmin_db', 'distribution', 'QCDiscovery')
ORDER BY name
FOR read only
-- Calculate total of all defects in each project and add to table
OPEN DBNameCursor
FETCH NEXT FROM DBNameCursor INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @BUGCOUNT = 0
PRINT @DBName
SET @SQLString = N'SET @BUGCOUNT = (SELECT COUNT(*) FROM ' + @DBName + '.td.BUG WHERE CAST(BG_DETECTION_DATE as int) > 39446))'
SET @ParmDefinition = N'@ThisBugCount int OUTPUT'
EXEC sp_executesql @SQLString, @ParmDefinition, @ThisBugCount = @BUGCOUNT OUTPUT
PRINT 'Total Defects in ' + @DBName +' for 2008 ' + cast(@BUGCOUNT as Char)
INSERT INTO BUGCOUNT values (@DBName, @BUGCOUNT)
FETCH NEXT FROM DBNameCursor INTO @DBName
END
DEALLOCATE DBNameCursor
-- Add all Project defect totals to get overall total defects for 2008
SELECT @Total = SUM(BUG_COUNT) FROM BUGCOUNT
PRINT 'Total Defects in all Projects is ' + cast(@Total as Char)
- added another variable @ParmDefinition, to setup the parameters for the SP_EXECUTESQL call
- moved the SET @SQLString into the loop and put back in the @DBName
- added the parameters to the SP_EXECUTESQL call
hope this helps, seems to work on my databases (with a different table of course)
December 10, 2008 at 4:44 pm
I'm getting closer but it still has issues when I run it.
Still getting
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@BUGCOUNT".
Total Defects in AdventureWorks for 2008 0
And when I try just
SET @SQLString = N'PRINT @DBName'
I get
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@DBName".
Total Defects in AdventureWorks for 2008 0
But it Prints tha DB name outside the sp_executesql
It doesn't seem to like any varable I put in that string.
Here the whole thing. Haven't really changed it.
SET ANSI_NULLS ON
SET NOCOUNT ON
DROP TABLE BUGCOUNT
CREATE TABLE BUGCOUNT (DBName varchar(40), BUG_COUNT int)
-- create cursor to hold a list of all database names
DECLARE @BUGCOUNT as int
DECLARE @DBName as varchar(35)
DECLARE @Total as int
DECLARE @SQLString as nvarchar(500)
DECLARE @ParmDefinition nvarchar(500)
DECLARE DBNameCursor cursor for
SELECT name
FROM master.dbo.sysdatabases
WHERE name not in ('master', 'tempdb', 'model', 'msdb', 'pubs', 'qcsiteadmin_db', 'distribution', 'QCDiscovery')
ORDER BY name
FOR read only
-- Calculate total of all defects in each project and add to table
OPEN DBNameCursor
FETCH NEXT FROM DBNameCursor INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @BUGCOUNT = 0
SET @SQLString = N'SET @BUGCOUNT = (SELECT COUNT(*) FROM ' + @DBName + '.td.BUG WHERE CAST(BG_DETECTION_DATE as int) > 39446)'
--SET @SQLString = N'PRINT @DBName'
SET @ParmDefinition = N'@ThisBugCount int OUTPUT'
EXEC sp_executesql @SQLString, @ParmDefinition, @ThisBugCount = @BUGCOUNT OUTPUT
PRINT 'Total Defects in ' + @DBName +' for 2008 ' + cast(@BUGCOUNT as Char)
INSERT INTO BUGCOUNT values (@DBName, @BUGCOUNT)
FETCH NEXT FROM DBNameCursor INTO @DBName
END
DEALLOCATE DBNameCursor
-- Add all Project defect totals to get overall total defects for 2008
SELECT @Total = SUM(BUG_COUNT) FROM BUGCOUNT
PRINT 'Total Defects in all Projects is ' + cast(@Total as Char)
December 10, 2008 at 7:57 pm
Heh... "We don need no stinkin' cursors"... 😛 ... especially in SQL Server 2005. Look at all the difficulties you're having making one do the things you want. Instead, the Set Based solution is shorter, faster, easier to code, easier to test, and easier to read if there's ever a problem...
--===== If the report table already exists, drop it.
-- Included just for rerun testing. Not required in a proc.
IF OBJECT_ID('TempDB..#BugCount','U') IS NOT NULL
DROP TABLE #BugCount
--===== Create the report table
CREATE TABLE #BugCount (DBName SYSNAME, BugCount INT)
--===== Declare local variables
DECLARE @SQL1 VARCHAR(MAX),
@SQL2 VARCHAR(MAX)
--===== Create the INSERT portion of the query
SELECT @SQL1 = 'INSERT INTO #BugCount (DBName, BugCount)'
--===== Create the necessary dynamic SQL, using a "pseudo-cursor"
-- or "set based loop" instead of a declared cursor.
SELECT @SQL2 = COALESCE(@SQL2 + CHAR(10) + 'UNION ALL','') + '
SELECT ''' + Name + ''',COUNT(*) FROM ' + Name + '.td.Bug WHERE BG_Detection_Date >= ''20080101'''
FROM Master.dbo.SysDatabases
WHERE Name NOT IN ('Master', 'TempDB', 'Model', 'MSDB', 'Pubs', 'QcSiteAdmin_DB', 'Distribution', 'QCDiscovery')
ORDER BY Name --LOOK! Comment this line out for production because it won't matter there!
--===== Execute the dynamic SQL
PRINT (@SQL1+@SQL2) --Comment out after you've verified the SQL works as expected
EXEC (@SQL1+@SQL2)
--===== Display the results
SELECT CASE WHEN GROUPING(DBName) = 0
THEN DBName
ELSE N'Total defects'
END AS DBName,
SUM(BugCount) AS BugCount
FROM #BugCount
GROUP BY DBName WITH ROLLUP
Of course, I couldn't actually test the code 100% for your app because I don't have the bug tables in my databases.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2008 at 8:03 pm
... and let me know if you still need a variable for the date...
And, never use INT for such a conversion because it rounds up to the next day for times after 12:00PM. Use DATEDIFF(dd,0,@datevariable) instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2008 at 8:45 am
:w00t: D'oh! Copy and paste error, it should have been:
SET @SQLString = N'SET @ThisBugCount = (SELECT COUNT(*) FROM ' + ...
since @ThisBugCount is the variable I setup as the parameter for sp_executesql.
Jeff's solution is a little more elegant though, of course.
December 11, 2008 at 9:05 am
It works!!!
I went with the original solution. It got what I needed but am going to try out Jeff's cursor'less version just to see how it works.
Thanks for the help guys.
December 11, 2008 at 9:52 am
jeffrey_bryant (12/11/2008)
It works!!!I went with the original solution. It got what I needed but am going to try out Jeff's cursor'less version just to see how it works.
Thanks for the help guys.
Jeff's version will be 'nasty fast' and is cursorless. Here's another version which uses a cursor behind the scenes but which is very simple:
CREATE TABLE #MyDBs (DBname VARCHAR(20), ColumnCount int)
DECLARE @SQL VARCHAR(200)
SET @SQL = 'IF ''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'', ''pubs'', ''qcsiteadmin_db'', ''distribution'', ''QCDiscovery'')
BEGIN
USE [?]
SELECT ''?'', COUNT(*) FROM syscolumns
END'
INSERT into #MyDBs EXEC master.dbo.sp_MSforeachdb @SQL
SELECT * FROM #MyDBs
DROP TABLE #MyDBs
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 11, 2008 at 8:32 pm
In all fairness, cursor solutions for such small rowcounts such as working on a list of databases will never become a performance problem unless you just want to consider the technical aspects of cursors. And, in this case, the performance differences between the cursor code and the setbased code are going to be negligible unless a whole lot of scale is added to the problem (ie, thousands of databases)...
...I just can't bring myself to writing a cursor and actually putting it into production. 😛
Just a couple of thoughts and then I'll get off the soap-box... 😀 As an added bonus to avoiding the cursor, compare the current cursor code in this thread to the set based solution... I wrote a piece of set based code to handle 1 database, tested each piece of it to be sure it worked as advertised, and the parameterized it with dynamic SQL and a simple self concatenating "psuedo cursor" variable... I didn't have to do anything fancy or worry about the scope of variables or any of the other problems that this thread addressed about the cursor method. And, it took me a whole lot less time, too! Heh... I still don't know where people get the idea that writing a cursor is easier than coming up with a proper set based solution.
Just say "No" to cursors and soon you'll have practiced enough so that when a "really big problem" comes up, you'll be able to look at it and say, "We don need no stinkin' cursors". 😛
Soap-box alert complete... regard all further alarms. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply