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 «««12345

INDEX DEFRAGMENTATION SCRIPT Expand / Collapse
Author
Message
Posted Friday, May 24, 2013 3:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 11:12 AM
Points: 10, Visits: 57
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
Post #1456707
Posted Friday, May 24, 2013 4:01 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:48 AM
Points: 2,040, Visits: 1,667
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
Post #1456708
Posted Friday, May 24, 2013 4:18 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 484, Visits: 831
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.
Post #1456714
Posted Wednesday, May 29, 2013 7:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:16 AM
Points: 1,159, Visits: 4,656
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
Post #1457711
Posted Thursday, May 30, 2013 10:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 11:12 AM
Points: 10, Visits: 57
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
Post #1458325
Posted Thursday, May 30, 2013 10:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 11:12 AM
Points: 10, Visits: 57
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.

Post #1458328
Posted Friday, May 31, 2013 3:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:16 AM
Points: 1,159, Visits: 4,656
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
Post #1458574
Posted Monday, June 10, 2013 2:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 11:12 AM
Points: 10, Visits: 57
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?
Post #1461772
Posted Tuesday, June 18, 2013 6:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:16 AM
Points: 1,159, Visits: 4,656
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
Post #1464630
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse