November 3, 2013 at 8:11 pm
I recently had to take a snapshot for replication of a client database, this failed because the database contained an invalid data type. The client is running SQL 2008R2, but the database is in SQL 2005 (90) Compatibility mode. The offending data type is a column of type DATE, which was only introduced in SQL 2008.
Some experimentation shows that it is possible to create a database on a SQL 2008R2 server, set the compatibility level down (it worked at 80 level) and then create a table with 100 level data types. You can also first create the table and then downgrade the database. At the lower level the you can still do CRUD operations on the table.
This seems a bit odd, particularly being able to create the table once the database is at the lower level. I can understand not wanting to break a feature on a downgrade, but to allow the feature to be applied seems to fly in the face of the compatibility functionality. Also, although the data type worked in the database, it was then causing a problem later on when we tried to taker a snapshot to reinitialise the subscriber. The only work around for this seems to be to reinitialise with backup and restore.
When we tried to change the DB to 100 mode, the application crashed because of deprecated features being used, and we had to take it down again.
Has anyone seen advanced "features" in lower compatibility levels like this? Does anyone know if there's a list of features that would work in this way or is it just hit and miss? Has anyone at Microsoft got an explanation of why this is allowed to work in this way?
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 3, 2013 at 9:55 pm
All new features (well, vast majority) work in lower compatibility levels. Compat level just affects how the parser and query processor handle certain T-SQL structures that have changed over the versions. It doesn't, never has and is not intended to prevent new stuff from being used, it just forces old behaviour when the behaviour has changed
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
November 4, 2013 at 7:02 pm
GilaMonster (11/3/2013)
All new features (well, vast majority) work in lower compatibility levels. Compat level just affects how the parser and query processor handle certain T-SQL structures that have changed over the versions. It doesn't, never has and is not intended to prevent new stuff from being used, it just forces old behaviour when the behaviour has changed
I've never played with compat level except where its broken something, so I've found this enlightening to say the least. The problem I do have is that the replication snapshot failed, and it seems a bit odd to let the data type work in the database, but then have the snapshot fail, since the snapshot is in essence just a BCP this doesn't really make sense.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 4, 2013 at 9:47 pm
Are you sure it failed because of the compat level of the publisher?
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
November 5, 2013 at 7:49 pm
Good question, I'm depending on the client for this, so we'll never know. I've not been able to find anything in the log to confirm this. The next step is to test in my lab and see.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply