July 22, 2012 at 6:52 am
Hello,
im with a problem in this code,
if i have any table like [dbo].['xxx'] it gives me the following error
Msg 102, Sev 15, State 1, Line 1 : Incorrect syntax near 'xxx'. [SQLSTATE 42000]
for the rest of tables without '' in the table name works ok
what can i do ?
thanks for your attention guys 🙂
begin
set @message = convert(varchar(200), getdate(), 113) + ' ' + @dbname
RAISERROR (@message, 0, 1) WITH NOWAIT
declare @sql varchar(4000)
declare @sql2 varchar(4000)
set @sql = 'use [' + @dbname + ']; exec sp_msforeachtable ''declare @message_msforeach varchar(400); set @message_msforeach = convert(varchar(200), getdate(), 113) + '''' Updating Statistics [' + @dbname + '].?''''; raiserror(@message_msforeach, 0, 1) with nowait; update statistics [' + @dbname + '].? with fullscan;'';'
exec (@sql)
July 22, 2012 at 11:19 am
Best advice: change the name of the table to not have single quotes in it.
2nd best advice: don't use sp_msforeachtable. Write your own code to loop through the tables.
July 22, 2012 at 11:23 am
Thanks for the answer Robert.
About the 1ºst advice, im not allowed to change it :/
2ºnd advice, yeah 😉
but tell me, using the current syntax, theres any expression or anything i can do, without redefine whole code ?
July 22, 2012 at 12:07 pm
I tried several ways to try to escape the embedded single quotes and the call to sp_msforeachtable errors out before those attempts can be made. Writing your own looping mechanism will allow you to escape it. Also, you need to account for the schema as well in the object name.
Something like this works:
Declare @TBName sysname,
@MaxID int,
@CurrID int,
@dbname sysname,
@SQL nvarchar(500);
Declare @Tables Table (TBID int identity(1, 1) not null primary key,
TBName sysname not null)
Set @dbname = DB_NAME();
Insert Into @Tables
Select name
From sys.tables;
Select @MaxID = MAX(TBID),
@CurrID = 1
From @Tables;
While @CurrID <= @MaxID
Begin
Select @TBName = TBName
From @Tables
Where TBID = @CurrID;
Set @SQL = N'use [' + @dbname + '];
declare @message_msforeach varchar(400);
set @message_msforeach = convert(varchar(200), getdate(), 113) + '' Updating Statistics [' + @dbname + ']..[' + Replace(@TBName, '''', '''''') + ']'';
raiserror(@message_msforeach, 0, 1) with nowait;
update statistics [' + @dbname + ']..[' + @TBName + '] with fullscan;';
--Print @SQL
Exec sp_executesql @SQL;
Set @CurrID = @CurrID + 1;
End
July 22, 2012 at 12:16 pm
Robert THANKS 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply