Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Curious Case of .. Syntax Error. Or may be not? Expand / Collapse
Author
Message
Posted Sunday, May 26, 2013 10:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:23 PM
Points: 424, Visits: 1,549
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

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
Post #1456871
Posted Sunday, May 26, 2013 10:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1456873
Posted Sunday, May 26, 2013 11:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:23 PM
Points: 424, Visits: 1,549
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[size="5"][/size]
Post #1456875
Posted Sunday, May 26, 2013 11:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 42,458, Visits: 35,518
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 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

Post #1456886
Posted Sunday, May 26, 2013 12:22 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:23 PM
Points: 424, Visits: 1,549
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
Post #1456894
Posted Sunday, May 26, 2013 12:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1456895
Posted Sunday, May 26, 2013 12:42 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:23 PM
Points: 424, Visits: 1,549
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
Post #1456898
Posted Sunday, May 26, 2013 12:57 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 42,458, Visits: 35,518
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 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

Post #1456901
Posted Sunday, May 26, 2013 1:02 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:23 PM
Points: 424, Visits: 1,549
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
Post #1456905
Posted Sunday, May 26, 2013 1:39 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 42,458, Visits: 35,518
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 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

Post #1456911
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse