Click here to monitor SSC
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 (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 1714
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 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)

Group: General Forum Members
Points: 506 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 4840
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 (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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 (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 4840
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 (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 4840
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
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8704 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