January 11, 2010 at 2:21 pm
I'm running this code in a job step of the SQL Server Agent:
-- Cursor on all databases (except tempdb)
DECLARE cDatabases CURSOR FOR
SELECT d.name
FROM sys.databases d
WHERE d.name NOT LIKE 'tempdb'
AND d.state = 0
ORDER BY d.name
DECLARE @DBName SYSNAME
DECLARE @QueryToExecute NVARCHAR(4000)
-- Loop through all the databases
OPEN cDatabases
FETCH NEXT FROM cDatabases INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @QueryToExecute =
'PRINT ''sp_updatestats results for [' + @DBName+ ']''' + CHAR(13) + CHAR(10) +
'EXECUTE ' + @DBName + '.dbo.sp_updatestats' + CHAR(13) + CHAR(10)
EXECUTE (@QueryToExecute)
FETCH NEXT FROM cDatabases INTO @DBName
END
CLOSE cDatabases
DEALLOCATE cDatabases
GO
Normally, in Management Studio, the name of each index is listed after the table like this:
Updating [dbo].[Versions]
[Versions_PK], update is not necessary...
[_WA_Sys_00000002_7D78A4E7], update is not necessary...
0 index(es)/statistic(s) have been updated, 2 did not require update.
But my output file of the job step is this:
Updating [dbo].[Versions] [SQLSTATE 01000]
, update is not necessary... [SQLSTATE 01000]
, update is not necessary... [SQLSTATE 01000]
0 index(es)/statistic(s) have been updated, 2 did not require update. [SQLSTATE 01000]
What's up with the missing text and [SQLSTATE 01000] ?!?
___________________________________
I love you but you're standing on my foot.
January 11, 2010 at 2:31 pm
That procedure needs to be run in the context of the database where you want the stats updated. Basically, you are running your procedure over and over in the same database where the process is executing.
Change your code and put a USE statement to change to the appropriate database and it should work with no problems.
Also, you don't need to add anything to the end of the string being executed. Instead of including the print statement inside the dynamic SQL - just print out the information. Set the dynamic SQL to something like:
SET @QueryToExecute = 'USE ' + quotename(@DBname) + '; EXECUTE dbo.sp_updatestats;';
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 11, 2010 at 2:39 pm
Thanks for the heads up on the QUOTENAME and context.
I have change my code to this and still, the ouput is still missing the name of the indexes:
PRINT 'sp_updatestats results for ' + QUOTENAME(@DBName)
SET @QueryToExecute = 'USE ' + QUOTENAME(@DBname) + '; EXECUTE dbo.sp_updatestats;'
EXECUTE (@QueryToExecute)
___________________________________
I love you but you're standing on my foot.
January 11, 2010 at 2:54 pm
Well, after some testing, I found that all lines begining with spaces get trimmed on the left and all lines begining with a quotations get the quote truncated as well.
This is very disturbing... I will have to change my code so that I use UPDATE STATISTICS instead?
Anyone else have another idea?
___________________________________
I love you but you're standing on my foot.
January 11, 2010 at 4:18 pm
Here is the code I use:
Declare @dbname sysname
,@execSql varchar(max);
Declare dbname Cursor Local Static For
Select Name
From sys.databases
Where database_id > 4;
Open dbname;
Fetch Next From dbname Into @dbname;
While @@fetch_status = 0
Begin;
Set @execSql = 'Use ' + quotename(@dbname)
+ '; Execute sp_updatestats @resample = ''RESAMPLE''';
Print @execSql; Execute(@execSql);
Fetch Next From dbname Into @dbname;
End;
Close dbname;
Deallocate dbname;
Go
Not sure why you are having problems - must be something in the way you are building your script.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 13, 2010 at 7:13 am
Well, both scripts run fine. It's just the output that's missing text.
That is a strange issue. I might open a case with Microsoft.
___________________________________
I love you but you're standing on my foot.
January 13, 2010 at 1:33 pm
I don't follow - what output are you expecting and what output is missing?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 13, 2010 at 1:39 pm
At the end of my first post, you can see the differences. The output is missing the name of the indexes on which it updated the statistics. It actually does the update, it's just missing the text in the output.
Because I couldn't have the right text anyway, I ended up doing this instead :
-- Cursor on all databases (except system databases)
DECLARE cDatabases CURSOR FOR
SELECT d.name
FROM sys.databases d
WHERE d.name NOT IN ('master', 'model', 'msdb', 'tempdb')
AND d.state = 0
ORDER BY d.name
DECLARE @DBName SYSNAME
DECLARE @QueryToExecute NVARCHAR(4000)
-- Loop through all the databases
OPEN cDatabases
FETCH NEXT FROM cDatabases INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @QueryToExecute = 'PRINT ''USE ' + QUOTENAME(@DBname) + ';''; USE ' + QUOTENAME(@DBname) + '; EXECUTE sp_MSforeachtable ''PRINT ''''UPDATE STATISTICS ? WITH FULLSCAN;''''; UPDATE STATISTICS ? WITH FULLSCAN'';'
EXECUTE (@QueryToExecute)
FETCH NEXT FROM cDatabases INTO @DBName
END
CLOSE cDatabases
DEALLOCATE cDatabases
GO
___________________________________
I love you but you're standing on my foot.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply