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


Enhanced Variable Declaration in SQL Server 2005 Mode


Enhanced Variable Declaration in SQL Server 2005 Mode

Author
Message
Michael Fried
Michael Fried
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 62
In SQL Server 2008 the new enhanced variable declaration functionality is working nicely (allowing you to assign a value to a variable in the DECLARE Statement). However, this new functionality also works successfully in a database running in SQL Server 2005 mode (Database Options) on a SQL Server 2008 server.

Our DEV and QA environments are SQL Server 2008 R2. We have a number of databases that are running in SQL Server 2005 mode. Our production system is still running SQL Server 2005 for various business reasons. There are plans to eventually update Production to SQL Server 2008, but not for a while. We have SQL Scripts as part of our development projects that sometimes contain the new enhanced variable declaration functionality. These scripts pass all testing in DEV and QA. When they are deployed in Production errors are encountered.

It seems there are issues with a database running in SQL Server 2005 mode on a SQL Server 2008 server. This issue seems to defeat one of the purposes of running a database in a previous mode.

My questions are:
1) Is this a known issue? If so, is there a workaround with any other settings?
2) Are there other code issues with the same affect (SQL Server 2008 specific functionality that works in SQL Server 2005 mode, but not on a true SQL Server 2005 server)?

We are currently inspecting SQL Scripts for this issue (to find and fix the issue before it gets to Production). But it seems there should be a better way.

Thanks for your responses!

Mike



anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10192 Visits: 6378
Running a database in a different versions compatability mode doesnt make the DB a that particular modes type of database.

Compatability level provides partial backwards compatability only certain things will act as they did in the previous version.

This is one of the things that is not covered in the partial backwards compatability.


Database Compatibility Level Option

To set certain database behavior to be compatible with a specified earlier version of Microsoft SQL Server




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


bleroy
bleroy
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 739
I hate to point out the bleedin' obvious, but why on Earth would you have a QA environment that is different from your production environment?? Isn't the point of a QA/test env that it replicates as closely as possible the prod env? the only differences really should be possibly at hardware level where your QA server only has (e.g.) 32 Gb of memory whilst your prod server has 128 ....
Licence-wise, it makes makes no difference really .. you have obviously acquired a 2008 licence, this ought to give you the right to install 2005 on the machine currently hosting your QA 2008 env ... my advice (unrelated to your actual question :heheSmile is to quickly get 2005 on your QA server ... so that next time you develop something on 2008 and you want to see if it works in 2005, you won't have to wait until it hits the prod server Cool

B - I had a spare tuppence - L
Michael Fried
Michael Fried
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 62
Agreed that it is not optimal. However, that is a business decision and I have to deal with the current situation. There are technical reasons that an upgrade to SQL Server 2008 in Prod enviroment has been delayed. The use of Table Variables and Temp Tables has changed quite a bit in SQL Server 2008 (as far as the processing engine) and large reports that run in 20-30 seconds on SQL Server 2005 now run in 20-30 minutes (or more) on SQL Server 2008 (obviously not acceptable). We have a large number of reports that have to be re-factored. This and other issues have delayed the production upgrade.



Michael Fried
Michael Fried
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 62
Is there a list of these partially backwards compatible functionality changes documented anywhere?



GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89147 Visits: 45284
The only thing that the compat level does is make certain query constructs behave as they did in the lower version. This does not mean that new functionality is disabled, only that functionality that has changed behaves the old way and not the new and that things which are not permitted in the new version still work in the lower compat mode (like *= only works with compat mode 80)

New functionality will work 90% of the time in the lower compat mode. This is intentional and by design.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Michael Fried
Michael Fried
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 62
OK, thanks for the response. I guess there are two potential views on this setting:

1) Functionality that has been deprecated (or changed) in a newer version will be reverted back to working in the compatibility mode version.
2) Functionality of a given version in compatibility mode will actually function like that version.

I guess I would expect it to function more like a Microsoft Office program. When you save a document in Office 2003 Compatibility mode all 2007 or above features are removed from the document so the document can be opened successfully by Office 2003.

Mike



GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89147 Visits: 45284
Michael Fried (12/13/2012)
I guess I would expect it to function more like a Microsoft Office program. When you save a document in Office 2003 Compatibility mode all 2007 or above features are removed from the document so the document can be opened successfully by Office 2003.


If it did, then what would happen with a nice large database (let's say 2TB) with a bunch of compressed tables (SQL 2005 and above feature) when someone sets the DB into compat mode 80?

Compat mode is not 'make this DB into a database of the lower version' it 'behave as if you were a lower version when running queries in this DB'. A database's version always matches that of the server it's attached to. With compat modes you can freely change them as often as you like. It's not a case of 'change up only' as there is with the actual database version.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Michael Fried
Michael Fried
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 62
I understand your overall point.

behave as if you were a lower version when running queries in this DB


I would argue that SQL Server is not doing this since the query syntax is different in the lower version than the newer version. It is fact not behaving like it was a lower version when running this query in the database.



GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24063 Visits: 9730
Michael Fried (12/13/2012)
OK, thanks for the response. I guess there are two potential views on this setting:

1) Functionality that has been deprecated (or changed) in a newer version will be reverted back to working in the compatibility mode version.
2) Functionality of a given version in compatibility mode will actually function like that version.

I guess I would expect it to function more like a Microsoft Office program. When you save a document in Office 2003 Compatibility mode all 2007 or above features are removed from the document so the document can be opened successfully by Office 2003.

Mike


Emphasis added.

Yes, but when you open your 2003 document in Word 2007, you still see a ribon at the top of the screen, right?

It's the same thing. Some of it will work, because of the engine, some of it won't. You'll need to research specific features.

For example, I just created a database in Compat 80 (SQL 2000) on my SQL 2008 R2 Dev Edition instance. I then created a table in that database using the Date datatype, which is new to SQL 2008. It worked. You would't think so, but it does.

The number of times I've seen people set a database to an earlier compatibility, then back it up, and then try to restore it on a copy of the earlier engine, and get confused as to why it's "broken", is pretty significant. They're expecting "I saved it as Word 2003, so Word 2003 can open it" to apply to databases, but it really doesn't.

The main thing to know is that compatibility doesn't actually change the fact that it's an SQL 2008 (or whatever) database. It just changes how certain features work, mainly to allow backwards code-compatibility. It's not really intended to keep newer code from working, it's intended to keep older code working even when you shouldn't use it any more.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
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