May 26, 2013 at 10:18 am
Not sure- to laugh or cry :-)
I created small SP to rebuild/reorganize indexes on sql server (2008 R2) DBs (only for indexes that require this action certainly)
Essentially it’s simple cursor- during each iteration (let say 100 indexes to rebuild from 5 different DBs) db_name/tbl_name/index are extracted from ahead prepared table (tblMain) to create dynamic string and execute it. Each iteration completes with tblMain update to report particular index action completion (done = 1, start/end = GETDATE()). Simple like truth.
Now the tricky part.
When SP complets (all 100 indexes rebuilt or reorganized successfully, i.e. every one has done = 1) SP returns.. error: “Msg 102, Level 15, State 1, Line 1; Incorrect syntax near '('.”
Oops :w00t:
If there is syntax error then SP should not start at all- correct?
Where is this mystery ‘(‘ coming from?
Funny enough but the only place where this ‘(‘ bracket used is GETDATE() function.
But it was used 100 times for 100 indexes to update start/end date in tblMain without any problem?!
It’s certainly not syntax error IMO, but what is it?
Intensive Google search returns nothing
Looking for a help (any clue) from the “best of the best” (dead serious)
Thanks in advance
Yuri
May 26, 2013 at 10:50 am
Well, since we can't see what you see it is a little hard to provide you with any help. How about posting the code for the stored procedure.
May 26, 2013 at 11:06 am
Here we are (sorry, if this is wrong way to post script):
******************************************************************
[font="Arial"]USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================================
--rebuild index using dbo.index_rebuild_log ahead prepared data
-- ============================================================
CREATE PROCEDURE [dbo].[usp_IndexRebuild]
AS
BEGIN
SET NOCOUNT ON;
declare @start datetime
declare @id int, @dbname varchar(50), @tblname varchar(100), @indexname varchar(100), @schname varchar(20), @action char(10)
declare @sql nvarchar(1000) = ''
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT id, dbname, schname, tblname, indexname, [action]
FROM dbo.index_rebuild_log --ahead prepared fragmentation data
WHERE index_done = 0
ORDER BY id
OPEN curDB
FETCH NEXT FROM curDB INTO @id, @dbname, @schname, @tblname, @indexname, @action
WHILE @@FETCH_STATUS = 0
BEGIN
set @start = GETDATE()
set @sql = 'ALTER INDEX ' + @indexname + ' ON ' + @dbname + '.' + @schname + '.' + @tblname + ' ' + @action
--print @sql
exec sp_executesql @sql
update dbo.index_rebuild_log --report action completion
set
index_done = 1,
index_start = @start,
index_end = GETDATE()
where
id = @id
FETCH NEXT FROM curDB INTO @id, @dbname, @schname, @tblname, @indexname, @action
END
CLOSE curDB
DEALLOCATE curDB
END[/font]
May 26, 2013 at 11:36 am
The syntax error is within some dynamic SQL and hence only caught when the dynamic SQL is executed. You'll need to add a PRINT statement to print the dynamic SQL to the client so that you can see exactly what is trying to be executed and failing.
Since you're not enclosing any of the index or table names in [], probably there's an index or table somewhere that has ( in its name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2013 at 12:22 pm
Gail,
But I printed all statements and do not see any '(' in any one
Moreover, as all statements were executed successfully, how SP can return error-
it suppose to fail on any if there is syntax error?
In addition I checked all values used in dynamic string for '(' existence- nothing
Or am I missing something?
Thanks,
Yuri
May 26, 2013 at 12:26 pm
Yuri55 (5/26/2013)
Here we are (sorry, if this is wrong way to post script):
******************************************************************
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================================
--rebuild index using dbo.index_rebuild_log ahead prepared data
-- ============================================================
CREATE PROCEDURE [dbo].[usp_IndexRebuild]
AS
BEGIN
SET NOCOUNT ON;
declare @start datetime
declare @id int, @dbname varchar(50), @tblname varchar(100), @indexname varchar(100), @schname varchar(20), @action char(10)
declare @sql nvarchar(1000) = ''
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT id, dbname, schname, tblname, indexname, [action]
FROM dbo.index_rebuild_log --ahead prepared fragmentation data
WHERE index_done = 0
ORDER BY id
OPEN curDB
FETCH NEXT FROM curDB INTO @id, @dbname, @schname, @tblname, @indexname, @action
WHILE @@FETCH_STATUS = 0
BEGIN
set @start = GETDATE()
set @sql = 'ALTER INDEX ' + @indexname + ' ON ' + @dbname + '.' + @schname + '.' + @tblname + ' ' + @action
--print @sql
exec sp_executesql @sql
update dbo.index_rebuild_log --report action completion
set
index_done = 1,
index_start = @start,
index_end = GETDATE()
where
id = @id
FETCH NEXT FROM curDB INTO @id, @dbname, @schname, @tblname, @indexname, @action
END
CLOSE curDB
DEALLOCATE curDB
END -- << Is there anything after this END?
Is there anything following the last END statement when you script the stored procedure using Object Explorer?
May 26, 2013 at 12:42 pm
Only GO command 🙂
I also originally thought there is something extra in text and therefore hard printed whole SP- nothing
Funny enough but when I tried to reproduce error I cannot do it.
I do not mean to rerun whole SP- too expensive for 100 indexes.
But after whole SP for 100 indexes was run and I got error I updated couple indexes (last in the ORDER by ID) completion flag (is_done = 0). Then I started SP again- it suppose to rebuild only these 2 indexes. Guess what? No error.
But every week when all indexes were rebuilt- same story- SP completed successfully but same error returned every time
Thanks,
Yuri
May 26, 2013 at 12:57 pm
Yuri55 (5/26/2013)
Moreover, as all statements were executed successfully, how SP can return error-it suppose to fail on any if there is syntax error?
Because the syntax error was in the dynamic SQL, hence SQL can't see it when it parses the procedure, only when that piece of dynamic SQL executes. The syntax error will cause that piece of dynamic SQL to fail (with the error), the rest of the procedure will carry on running.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2013 at 1:03 pm
Make sense, thanks
But what confuses me is that ALL statements completed successfully (i.e. seems no single failed)
I saw some posts regarding mysterious syntax errors that were results of different DBs compatibility level (CL)- certainly not sure it's my case. SP runs on DB with CL 100 (2008) but some of affected indexes live on DBs with CL 80 and 90 (i.e. 2000 and 2005).
But frankly I have no idea how it can result in this error
Thanks,
Yuri
May 26, 2013 at 1:39 pm
Change the proc to print not execute the dynamic SQL and run it with exactly the parameters that result in the error every 2 weeks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2013 at 2:30 pm
Actually I did this already- when yesterday night SP returned error I rerun it again (same indexes) with Print only (no execute)- no errors, all printed statements looks good (no '(')
This is the reason I called- Curious Case 🙂
Anyhow, thanks for help
May 26, 2013 at 5:05 pm
Can you post all the printed statements?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2013 at 9:12 pm
Seems I do not need to post all 82 statements as while preparing them and carefully reading one after one I found 2 indexes (same DB and table- probably same creator) with names like :
"ind1 (Non-Unique, Non-Clustered)" and "ind2 (Non-Unique, Non-Clustered)".
No comments :w00t:
My bad- I did not find them till today.
As I found they were ReBuilt/ReOrganized every week- therefore SP failed every week run
(as these 2 indexes somewhere in the middle of total index list looks like sql failed on them keeping in mind error, proceeded with other indexes but at the very end returned this error)
Anyhow I renamed these 2 indexes (removing "(Non-Unique, Non-Clustered)" from name) and hope next run everything should be fine
Thanks everybody for help (as always)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply