Change from SQL 2008 to Comp level SQL 2005

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Change from SQL 2008 to Comp level SQL 2005

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-08-11 : 11:38:10

[code]Hi,

I am running SQL Server 2008 R2 on the desktop and need to change the db to compatibility level to
SQL Server 2005 mode because some of our clients had SQL 2005 and 2008. I run the following command
in my testdb but not sure if it's work. How can I test and make sure it works?. Do I have
to stop the server and restart again to take affective.

Any help would greatly appreciate.

--sql 2005
EXECute sp_dbcmptlevel 'Dev', 90;
go

-- checking
-- properties, options, compatibility level, SQL Server 2005 (90)

SELECT @@version

/*
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
*/

IF OBJECT_ID('TestTable ', 'u') IS NOT NULL
DROP TABLE TestTable
GO
CREATE TABLE TestTable
(
InsertDt DATETIME NULL,
ActiveAgent CHAR(3) NULL,
ActiveVendor CHAR(1) NULL
)
GO

INSERT INTO TestTable VALUES ('01/01/1900', 'Yes', 'Y'), ('02/16/2008', 'Yes', 'N'), ('01/01/1900', 'No', 'N'),
('06/15/2010', 'Yes', 'N'), ('01/01/1900', 'Yes', 'Y');

go

-- Why is insert successfully when it's compatibility level 90?

(5 row(s) affected)

select *
from TestTable;
go

InsertDt ActiveAgent ActiveVendor
----------------------- ----------- ------------
1900-01-01 00:00:00.000 Yes Y
2008-02-16 00:00:00.000 Yes N
1900-01-01 00:00:00.000 No N
2010-06-15 00:00:00.000 Yes N
1900-01-01 00:00:00.000 Yes Y

-- this should fail as well.

DECLARE @InsertDt DATE
SET @InsertDt = SYSDATETIME()

select @InsertDt

----------------------------------------------------------------
print db_name()

--change it back to sql 2008
ALTER DATABASE dm_asarum_Hagerman417
SET COMPATIBILITY_LEVEL = 100;
go[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 11:42:42

did you try changing compatibility level first and then doing the rest in a new connection?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-08-11 : 11:59:51

yes, I run the following command first before I run the insert statement.

ALTER DATABASE Dev
SET COMPATIBILITY_LEVEL = 90;
GO

quote:
Originally posted by visakh16

did you try changing compatibility level first and then doing the rest in a new connection?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 12:06:40

nope. i was asking if you did insert bit in a separate connection

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-08-11 : 12:22:09

[code]Yes. But when I run the following codes in query analyzer, it should give me an error but not.

DECLARE @InsertDt date
SET @InsertDt = SYSDATETIME()
select @InsertDt

----------
2011-08-11[/code]

quote:
Originally posted by visakh16

nope. i was asking if you did insert bit in a separate connection

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-11 : 12:42:01

Why should it give you an error?

Compat mode doesn't disable new features, it just makes certain T-SQL constructs behave like they did on SQL 2005.

--
Gail Shaw
SQL Server MVP

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-08-11 : 14:50:36

[code]I have about 200 scripts (SPs and Scripts) which Developers created and test on SQL 2008 R2 but the Clients using SQL 2005 and 2008 and the scripts are failing on sql 2005 because the new features on 2008. How can Developers change the mode to work on both versions without uninstalling the SQL Server 2008 r2.

Thanks.[/code]

quote:
Originally posted by GilaMonster

Why should it give you an error?

Compat mode doesn't disable new features, it just makes certain T-SQL constructs behave like they did on SQL 2005.

--
Gail Shaw
SQL Server MVP

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-11 : 14:53:20

You're going to need to install the target versions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources