Rebuild Index errors via SQL2K5 agent job?
in [Server Configuration]
|
Prev: Trouble getting execution plan
Next: SQL Server
From: Bero on 21 Jul 2010 16:46 I have done a rebuild indexes using a new stored procedure (with a cursor) for some of the user databases via SQL agent and it failed after few hours. It gives me this msg.. " Executed as a user:.... Incorrect syntax near the key word user. [SQL State 42000]. Incorrect syntax near the key word WITH, a CTE or XML name space clause. The previous statement must be terminated. [SQL state:42000, error 319]. Step failed.. I am trying to understand is there a problem with my SP or in one of the databases index script encountered something it does not like? My error log does not show any error msgs and can not even tell what databases it already indexed either. How serious is this error? How do I trouble shoot this or should I rebuild indexes using MP for each databse to figure out this issue?
From: Dan Guzman on 21 Jul 2010 18:03 > How serious is this error? How do I trouble shoot this or should I > rebuild indexes using MP for each databse to figure out this issue? My guess is that the error is because you have an identifier (database, table or index name) that does not conform to normal identifier naming rules. Non-conforming names must be enclosed in square brackets or quotes. In T-SQL, you can use the QUOTENAME function to do this. For example: SET @RebuildIndexStatement = N'ALTER INDEX ALL ON ' + QUOTENAME(@TableSchenaName) + 'N'.' + QUOTENAME(@TableName) + N' REBUILD;' If the desired functionality is available with a MP, you might as well use it. But I think it is still good to know the underlying DDL and see no problem doing the job yourself. I recall there were issues with MPs in older SQL versions that didn't enclose identifiers and resulted in basically the same error. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "Bero" <robertatnova(a)gmail.com> wrote in message news:6d4fd07d-7fc4-4365-bfad-b680e8437ad2(a)c10g2000yqi.googlegroups.com... > I have done a rebuild indexes using a new stored procedure (with a > cursor) for some of the user databases via SQL agent and it failed > after few hours. It gives me this msg.. > > " Executed as a user:.... Incorrect syntax near the key word user. > [SQL State 42000]. Incorrect syntax near the key word WITH, a CTE or > XML name space clause. The previous statement must be terminated. [SQL > state:42000, error 319]. Step failed.. > > I am trying to understand is there a problem with my SP or in one of > the databases index script encountered something it does not like? My > error log does not show any error msgs and can not even tell what > databases it already indexed either. > > How serious is this error? How do I trouble shoot this or should I > rebuild indexes using MP for each databse to figure out this issue?
From: Dan on 22 Jul 2010 11:53 "Bero" <robertatnova(a)gmail.com> wrote in message news:6d4fd07d-7fc4-4365-bfad-b680e8437ad2(a)c10g2000yqi.googlegroups.com... > I have done a rebuild indexes using a new stored procedure (with a > cursor) for some of the user databases via SQL agent and it failed > after few hours. It gives me this msg.. > > " Executed as a user:.... Incorrect syntax near the key word user. > [SQL State 42000]. Incorrect syntax near the key word WITH, a CTE or > XML name space clause. The previous statement must be terminated. [SQL > state:42000, error 319]. Step failed.. > > I am trying to understand is there a problem with my SP or in one of > the databases index script encountered something it does not like? My > error log does not show any error msgs and can not even tell what > databases it already indexed either. > > How serious is this error? How do I trouble shoot this or should I > rebuild indexes using MP for each databse to figure out this issue? Do you by any chance have a string in your proc that contains "Executed as a user", maybe as part of some dynamic SQL ? If so, check for correct string termination as it appears that this being interpreted as a statement rather than a part of a string, I'd take a stab that if you're constructing SQL statements on the fly that you may have an apostrophe in one or more values when you're putting the strings together. -- Dan
From: Bero on 23 Jul 2010 22:05 On Jul 22, 11:53 am, "Dan" <n...(a)worldofspack.com> wrote: > "Bero" <robertatn...(a)gmail.com> wrote in message > > news:6d4fd07d-7fc4-4365-bfad-b680e8437ad2(a)c10g2000yqi.googlegroups.com... > > > > > > > I have done a rebuild indexes using a new stored procedure (with a > > cursor) for some of the user databases via SQL agent and it failed > > after few hours. It gives me this msg.. > > > " Executed as a user:.... Incorrect syntax near the key word user. > > [SQL State 42000]. Incorrect syntax near the key word WITH, a CTE or > > XML name space clause. The previous statement must be terminated. [SQL > > state:42000, error 319]. Step failed.. > > > I am trying to understand is there a problem with my SP or in one of > > the databases index script encountered something it does not like? My > > error log does not show any error msgs and can not even tell what > > databases it already indexed either. > > > How serious is this error? How do I trouble shoot this or should I > > rebuild indexes using MP for each databse to figure out this issue? > > Do you by any chance have a string in your proc that contains "Executed as a > user", maybe as part of some dynamic SQL ? If so, check for correct string > termination as it appears that this being interpreted as a statement rather > than a part of a string, I'd take a stab that if you're constructing SQL > statements on the fly that you may have an apostrophe in one or more values > when you're putting the strings together. > > -- > Dan- Hide quoted text - > > - Show quoted text - This is the SP I am using. It ran for several hours and then quit giving the above msg. Please let me know what is the issue and where I need to make a coding change? DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 90 DECLARE DatabaseCursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','distrbution') ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' -- create table cursor EXEC (@cmd) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN -- SQL 2005 command SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@cmd) FETCH NEXT FROM TableCursor INTO @Table END CLOSE TableCursor DEALLOCATE TableCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor
From: Erland Sommarskog on 24 Jul 2010 06:53 Bero (robertatnova(a)gmail.com) writes: >> > " Executed as a user:.... Incorrect syntax near the key word user. >> > [SQL State 42000]. Incorrect syntax near the key word WITH, a CTE or >> > XML name space clause. The previous statement must be terminated. [SQL >> > state:42000, error 319]. Step failed.. > > This is the SP I am using. It ran for several hours and then quit > giving the above msg. Please let me know what is the issue and where I > need to make a coding change? >... > SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH > (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' Change @Table to quotename(@Table). The problem is probably that you have a table name which is not a standarad identifier, for instance have a space in the name. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
Pages: 1 Prev: Trouble getting execution plan Next: SQL Server |