SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INDEX DEFRAGMENTATION SCRIPT


INDEX DEFRAGMENTATION SCRIPT

Author
Message
Brian Seib
Brian Seib
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 91
Good afternoon -- I have an odd question - I have been using this scipt on a few of my servers for a while now, and just recentyl I have been getting an error when the job runs some days and not other days. The error is as follows:
Incorrect syntax near '('. [SQLSTATE 42000] (Error 102). The step failed.

I would say it is just a syntax error, but it runs one day and not another - so I do not really understand what parameter is changing on my database some days to make this error out..

Any ideas??

Thanks,
Brian
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3635 Visits: 1717
Is someone changing the compatibility level/mode of the database?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Balance
Balance
Mr or Mrs. 500
Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)

Group: General Forum Members
Points: 578 Visits: 1048
I've had errors like this and have always found that the original code I've worked from overlooked something causing the error. I would check the database is online, you are correctly handling schemas, names that need enclosing in [ ] etc.
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1985 Visits: 4941
If that's case you can add additional condition within the script. Run a select statement one by one and check which is causing an issue.

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

Brian Seib
Brian Seib
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 91
So here is an odd update. It fails when it is running at night (normal time), but just for kicks I ran it this morning and it acually ran fine..? Very strange behavior - could it be that it is butting heads with a nightly process? I do not know this script that well, so I am not too sure.

Thanks,
Brian
Brian Seib
Brian Seib
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 91
Looking at another server that is runnig the same script and the error it is getting is "COUNT field incorrect or syntax error [SQLSTATE 07002] (Error 0). The step failed". Keep in mind these were working a few months ago, just started getting errors. Very strange.
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1985 Visits: 4941
Brian Seib (5/30/2013)
Looking at another server that is runnig the same script and the error it is getting is "COUNT field incorrect or syntax error [SQLSTATE 07002] (Error 0). The step failed". Keep in mind these were working a few months ago, just started getting errors. Very strange.



Error: 07002 Number of columns does not match number of host variables.

http://msdn.microsoft.com/en-us/library/aa176407%28v=sql.80%29.aspx
Have a look how many columns you selected for an insert.

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

Brian Seib
Brian Seib
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 91
Very strange - so I am was looking at one of the servers that this is running on and failing - and after looking through the history is turns out that somedays it works and other days it does not. So that pretty much takes any syntax issues out of the running. So this apparently is requesting information that is hanging some days?. I do not fully understand what the query is doing step by step, but it apparenly relies on the information on the database as it i works one hay and not the other, without my naking any changes to the database. Any ideas?
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1985 Visits: 4941
Brian Seib (6/10/2013)
Very strange - so I am was looking at one of the servers that this is running on and failing - and after looking through the history is turns out that somedays it works and other days it does not. So that pretty much takes any syntax issues out of the running. So this apparently is requesting information that is hanging some days?. I do not fully understand what the query is doing step by step, but it apparenly relies on the information on the database as it i works one hay and not the other, without my naking any changes to the database. Any ideas?


I suggest you to do an error handling (Just insert an error into another table) and find out what it's.

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13768 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search