October 26, 2009 at 8:06 am
Hey people,
I have an interesting issue that I cannot seem to figure out. I am using the undocumented stored proc. sp_msforeachdb to iterate through each database as opposed to a cursor or while loop to access sys.databases. I know I can resolve the issue by using a cursor or while loop, but I would like to see if there is an answer to this problem.
Points:
1) The syntax of the SQL statement inside the @statement variable is correct. I use a print @statement at the end and copy the contents in the message window to a new query window. It executes perfectly (after I substitute an actual database name for the [?])
2) The error I am receiving is Msg 102, Level 15, State 1, Line 47 Incorrect syntax near 'END'. This error repeats a number of times that is equal to the number of databases that exist (each iteration)
3) When you copy the print @statement results to a new query window, you will be able to see exactly where line 47 is to check the syntax.
4) I have played with semicolons etc, trying to produce the correct breaks or new lines in the code without success.
5) I have consulted my senior dba, and we are both at a loss as to what is happening. Maybe this is why sp_msforeachdb is undocumented?
Perhaps someone here could shed some light on what is happening.
Note: It looks like the double quotes in the code (which is correct) is playing around with how the forums interpret the code.. so half of the code appears red and the other half appears like normal sql coding hightlights. Ignore that.
DECLARE @statement varchar(MAX)
IF OBJECT_ID('DBA..Backup_Strategy') IS NOT NULL
BEGIN
DROP TABLE DBA..Backup_Strategy
END
CREATE TABLE DBA..Backup_Strategy
(
dbname varchar(50)
, [differential] bit default 0
)
--Using the undocumented stored procedure sp_msforeachdb, loop through all of the databases and
--1. collect the current size (db + log)
-- 2. compare the current size with the limit in sql_overview..master_settings
--3. if the current database lsn matches the last nightly backup lsn and today is not a designated
-- day for a full backup and the db size is over the limit, perform a differential, otherwise
-- perform a full backup
--4. record whether a diff. or full is taking place in dba..backup_strategy
--4. if a full backup, record the lsn for each db to sql_overview..last_nightlybackup_lsn
SELECT @statement = '
USE [?];
DECLARE @dbsize float;
DECLARE @gb float;
DECLARE @logsize float;
DECLARE @dbsizelimit smallint;
DECLARE @totalsize float;
DECLARE @ReturnDay varchar(30);
DECLARE @Day smallint;
DECLARE @DBName varchar(50);
SELECT @DBName = (SELECT DB_NAME());
DECLARE @LastNightlyLSN NUMERIC(25,0);
PRINT ''Database: '' + @DBName;
DECLARE @CurrentLSN NUMERIC(25,0);
SELECT @CurrentLSN = (SELECT DISTINCT differential_base_lsn FROM sys.master_files WHERE type = 0 AND database_id = DB_ID(@DBName));
PRINT ''CurrentLSN: '' + COALESCE(CONVERT(VARCHAR(25),@CurrentLSN),'''');
SELECT @LastNightlyLSN = (SELECT TOP 1 LSN FROM MSDB02.SQL_Overview.dbo.Last_NightlyBackup_LSN WHERE ([Server] = HOST_NAME() AND DBName = @DBName) ORDER BY ID DESC);
PRINT ''LastNightlyLSN: '' + COALESCE(CONVERT(VARCHAR(25),@LastNightlyLSN),'''');
SELECT @ReturnDay=DATEPART(dw,getdate());
PRINT ''ReturnDay: '' + CONVERT(VARCHAR(1),@ReturnDay);
SELECT @Day = (SELECT Full_Backup_Day FROM MSDB02.SQL_Overview.dbo.SSIS_ServerList WHERE ([Server] = HOST_NAME() + ''.RDC.domain.NET\'' + @@servicename OR [Server] = HOST_NAME() + ''.RDC.domain.NET''));
PRINT ''DesignatedDay: '' + CONVERT(VARCHAR(1),@Day);
SELECT @dbsizelimit = (SELECT Differential_DB_Size_Limit FROM MSDB02.SQL_Overview.dbo.Master_Settings);
SELECT @dbsize = (SELECT SUM(size) FROM dbo.sysfiles WHERE (status & 0x40) = 0);
SELECT @logsize =(SELECT SUM(size) FROM dbo.sysfiles WHERE (status & 0x40) <> 0);
SELECT @totalsize = @dbsize + @logsize;
SELECT @gb = (((@totalsize * 8) / 1024.00) / 1024.00);
IF @LastNightlyLSN <> @CurrentLSN OR @LastNightlyLSN IS NULL
BEGIN
INSERT INTO DBA..Backup_Strategy VALUES (@DBName, 0)
END
ELSE
BEGIN
IF (@ReturnDay = @Day)
BEGIN
INSERT INTO DBA..Backup_Strategy VALUES (@DBName, 0)
END
ELSE
BEGIN
IF @gb >= @dbsizelimit
BEGIN
INSERT INTO DBA..Backup_Strategy VALUES (@DBName, 1)
END
ELSE
BEGIN
INSERT INTO DBA..Backup_Strategy VALUES (@DBName, 0)
END
END
END
'
PRINT @statement
EXEC sp_MSForEachDB @command1 = @statement;
October 26, 2009 at 8:23 am
I guess the first question is, are you really running this on SQL 7 or 2000?
October 26, 2009 at 8:26 am
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
October 26, 2009 at 8:40 am
Sorry for the lame question but that was the forum this was in. I just wanted to make sure. This code works for me but I am using SQL 2008. I changed some of the bottom of the script to get rid of some of the BEGIN...END blocks. Not sure if that fixed it or what. Let me know if this helps.
DECLARE @statement VARCHAR(MAX)
IF OBJECT_ID('testingArea..Backup_Strategy') IS NOT NULL
BEGIN
DROP TABLE testingArea..Backup_Strategy
END
CREATE TABLE testingArea..Backup_Strategy
(
dbname varchar(50)
, [differential] bit default 0
)
SET @statement = 'use ?;
DECLARE @dbsize float;
DECLARE @gb float;
DECLARE @logsize float;
DECLARE @dbsizelimit smallint;
DECLARE @totalsize float;
DECLARE @ReturnDay varchar(30);
DECLARE @Day smallint;
DECLARE @DBName varchar(50);
SELECT @DBName = (SELECT DB_NAME());
DECLARE @LastNightlyLSN NUMERIC(25,0);
PRINT ''Database: '' + @DBName;
DECLARE @CurrentLSN NUMERIC(25,0);
SELECT @CurrentLSN = (SELECT DISTINCT differential_base_lsn FROM sys.master_files WHERE type = 0 AND database_id = DB_ID(@DBName));
PRINT ''CurrentLSN: '' + COALESCE(CONVERT(VARCHAR(25),@CurrentLSN),'''');
SELECT @LastNightlyLSN = (SELECT 1);
PRINT ''LastNightlyLSN: '' + COALESCE(CONVERT(VARCHAR(25),@LastNightlyLSN),'''');
SELECT @ReturnDay=DATEPART(dw,getdate());
PRINT ''ReturnDay: '' + CONVERT(VARCHAR(1),@ReturnDay);
SELECT @Day = (SELECT 1);
PRINT ''DesignatedDay: '' + CONVERT(VARCHAR(1),@Day);
SELECT @dbsizelimit = (SELECT 1);
SELECT @dbsize = (SELECT SUM(size) FROM dbo.sysfiles WHERE (status & 0x40) = 0);
SELECT @logsize =(SELECT SUM(size) FROM dbo.sysfiles WHERE (status & 0x40) <> 0);
SELECT @totalsize = @dbsize + @logsize;
SELECT @gb = (((@totalsize * 8) / 1024.00) / 1024.00);
IF @LastNightlyLSN <> @CurrentLSN OR @LastNightlyLSN IS NULL
INSERT INTO testingArea..Backup_Strategy VALUES (@DBName, 0)
ELSE
BEGIN
IF (@ReturnDay = @Day)
INSERT INTO testingArea..Backup_Strategy VALUES (@DBName, 0)
ELSE
BEGIN
IF @gb >= @dbsizelimit
INSERT INTO testingArea..Backup_Strategy VALUES (@DBName, 1)
ELSE
INSERT INTO testingArea..Backup_Strategy VALUES (@DBName, 0)
END
END
'
--PRINT @statement
EXEC sp_MSForEachDB @statement
October 26, 2009 at 8:48 am
That worked! I replaced my code with your code that removed the begin and end within the conditionals and replaced the testingarea database name with my original.
I always try to code formally, does begin and end throw off sp_msforeachdb?
October 26, 2009 at 8:52 am
I didn't think so but I usually don't run statements quite this long through that procedure. I was just trying to get a little bit better error messages but then it worked. I don't really have a reason for it working. 😀
October 26, 2009 at 9:13 am
That must be why it is undocumented. haha thanks for your help.
Moderator, could you please move this topic to the correct forum for sql2005? My apologies on the wrong location.
October 26, 2009 at 9:45 am
I'm going to go off on a limb and say that I thought the stored proc was a dll file. Well, it isn't and I opened it up. the @command1 variable was originally nvarchar(2000). I upped that baby to 8000. I think it was truncating the contents of @statement and when you removed all of those begin/end newline etc.. it made it just under 2000 characters.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply