Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
xpandre
Posting Yak Master
212 Posts |
 Posted - 2014-11-10 : 12:14:52
|
| Hi,We had a stored proc in 2005 which had a CTE(not the 1st statement of the proc) and it compiled well and is executing successfully.we migrated the proc to 2012 and now its throwing an error during compilation due to the missing semi colon after the statement before the CTE.Does anyone know why it might have been working in 2005 (compatibility level 80)..like do we have any database setting which allows CTE without a semi colon in previous statement?ThanksSam |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-10 : 12:28:34
|
| I guess it was a bug. The page for CTEs for 2005 specifies a preceding colon:http://technet.microsoft.com/en-us/library/ms175972(v=sql.90).aspx |
 |
|
|
xpandre
Posting Yak Master
212 Posts |
Posted - 2014-11-10 : 13:21:45
|
| Thanks..to summarize my situation.we have a service oriented architecture, where in we might have 20 different versions of a single service calling the database..with each service call calling different versions of same Sp(with minor changes)EG : service ServiceGetDataV1 to V20 calling SP getDataV1 to V20 respectively..This is required due to the fact that we have around 30 applications calling these services and none are in sync with each other..so @ one point of time, we might have 20 applications calling 20 different versions of the service.likewise, I might have many services..And a CTE loving developer had developed almost ALL these SP's without considering the trailing semi colon, and we missed it in our review.now If we need to migrate to 2012, how badly are we screwed by this missing semi colon?:-(..I am talking about close to 200-300 procs.Is there any Database setting which handles this for sql server?Thanks |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2014-11-10 : 13:25:52
|
| No database setting will handle that for you - you need to update every procedure. |
|
|
|
xpandre
Posting Yak Master
212 Posts |
Posted - 2014-11-10 : 13:51:04
|
| got the root cause...the compatibility level was 80 on our 2005 database..so without semi-colon worked.., and now after migration, the DBA's moved it up to 90.we would have to update all procs, as moving back to 80 would be not possible :-(Thanks.. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-10 : 14:29:57
|
| Why not script the procs to a text file, edit the text file and change all "with" to ";with" then run the file to replace the procs? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2014-11-12 : 13:39:26
|
quote:
Originally posted by gbritton
Why not script the procs to a text file, edit the text file and change all "with" to ";with" then run the file to replace the procs?
Be careful with this kind of change - any table hints would break with that change. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-12 : 13:45:27
|
| True, though with judicious use of regular expressions, those breaks can be avoided. (e.g. only match on WITH not followed by an opening parenthesis -- make it as bullet-proof as needed) |
|
|
|
|
|
|
|
|
CTE