|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 12:47 PM
Points: 360,
Visits: 1,072
|
|
I run the following code which worked sql2k but does not work in sql2k5 and get the following error. I tried with and without semicolon, chaning quotations. Any help appreciated!
--Update Statistics on all tables in all DBs Set Nocount on Declare db Cursor For Select name from master.dbo.sysdatabases where dbid>=5 --Doesnt include system dbs
Declare @dbname varchar(60) Declare @execmd nvarchar(150)
Open db Fetch Next from db into @dbname While @@Fetch_status=0 begin if @dbname is null Begin Print 'null Value' end else Begin PRINT '###########################################################################' PRINT 'Update Statistics in ' + @dbname SELECT @execmd = 'USE ' + @dbname + ''Exec sp_MSForEachTable 'Update Statistics ''?'' with FULLSCAN''' EXEC(@execmd) PRINT '' End Fetch Next from db into @dbname end Close db Deallocate db GO
Error: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '[Production].[ProductProductPhoto]'. Msg 319, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string ''.
¤ §unshine ¤
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 5,201,
Visits: 11,153
|
|
sunshine (9/15/2008)
the previous statement must be terminated with a semicolon.
this message is fairly descriptive!! The USE statement and exec must be separated by a semi colon like so
SELECT @execmd = 'USE ' + @dbname + '';Exec sp_MSForEachTable 'Update Statistics ''?'' with FULLSCAN'''
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 37,686,
Visits: 29,939
|
|
Perry Whittle (9/16/2008)
sunshine (9/15/2008)
the previous statement must be terminated with a semicolon.
this message is fairly descriptive!! The USE statement and exec must be separated by a semi colon like so
That message is just saying that if the with is starting a CTE (which in this case it is not) the previous command needs to be terminated with a ; It's a generic message that is given any time there's a syntax error and the keywork 'with' is anywhere nearby. In this case, it's misleading as the problem has nothing to do with the 'with'
In this case, the problem is with the single quotes. Change the select to the following and it works (in my 2008 test instance)
SELECT @execmd = 'USE ' + @dbname + ' Exec sp_MSForEachTable ''Update Statistics ? with FULLSCAN'''
Gail Shaw Microsoft Certified Master: SQL Server 2008, MVP SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter We stand on the bridge and no one may pass
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 12:47 PM
Points: 360,
Visits: 1,072
|
|
I had originally tried the semi-colon and it did not work. Gail, your fix helped. Thank you so much for your help as always!
¤ §unshine ¤
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:33 AM
Points: 5,
Visits: 185
|
|
I am having job running SSIS package,which is having 4 steps as below 1.Database Integrity -- success 2.Clean Up History --success 3.Update Statistics --- failed 4.Reorganize Index--success
-->update statistics got failed due to below error..
Failed -1073548784) Executing the query "UPDATE STATISTICS [dbo].[PDE_RFEQP_MVMNT_EVENT_DN] WITH FULLSCAN " failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded. A severe error occurred on the current command. The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Can any one help me pls...
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Yesterday @ 1:12 PM
Points: 36,
Visits: 584
|
|
How could you use the following code using sample percent using variable to pass percentage?
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 1,654,
Visits: 8,563
|
|
See what message you receive when you try to run it under management studio...
MJ
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Tuesday, February 28, 2012 2:44 AM
Points: 3,
Visits: 124
|
|
sp_msforeachtable 'update statistics ? with all'
it will work fine
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 3,574,
Visits: 5,113
|
|
whenever I use sp_msforeach... I always SET QUOTED_IDENTIFIER OFF first. Then I can use DOUBLE quotes for the outer (executed) strings and then single quotes for inner string creation. Works like a champ, and I never have to worry with is it 1, 2, 3, 4, etc single quotes I have to put together to get the desired effect!!
Best,
Kevin G. Boles SQL Server Consultant SQL MVP 2007-2012 TheSQLGuru at GMail
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:22 PM
Points: 31,
Visits: 253
|
|
Set Nocount on Declare db Cursor For Select name from master.dbo.sysdatabases where dbid>=11 --Doesnt include system dbs
Declare @dbname varchar(60) Declare @execmd nvarchar(150)
Open db Fetch Next from db into @dbname While @@Fetch_status=0 begin if @dbname is null Begin Print 'null Value' end else Begin PRINT '###########################################################################' PRINT 'Update Statistics in ' + @dbname SELECT @execmd = 'USE ' + @dbname + ' Exec sp_msforeachtable ''Update Statistics ? with FULLSCAN''' EXEC(@execmd) PRINT '' End Fetch Next from db into @dbname end Close db Deallocate db GO
All,
i tried the above code and made a moification where the number of database being pulled for testing purpose is 2 When i run it, i get the following error message:
########################################################################### Update Statistics in xxx91B Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_msforeachtable'. ########################################################################### Update Statistics in xxx91C Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_msforeachtable'.
When i just run the following: it runs just fine
use xxx91C exec sp_MSforeachtable 'update statistics ? with fullscan' go
Any help would be appreciated.(SQL 08R2 RTM)
|
|
|
|