SQL Backward Compatibility?

[kdd21]

I get the impression that osql (or somewhere in the sql processing) precompiles the entire script before it executes anything. In particular, this is a problem because it means you can't use IF statements to bracket new features in a script designed to be run on both old and new versions of SQL Server. I'm trying to handle an issue whereby I need to use "CREATE LOGIN" on SQL Server 2005 because I need to set CHECK_POLICY = OFF, and you can't do that with sp_addlogin. However, on SQL Server 2000, while I can't use CREATE LOGIN, I don't need to because the default password policy is such that the password being used does not fail without it (as it does in SQL Server 2005, and is why we need to set CHECK_POLICY), so I can simply use sp_addlogin to create the user w/o a CHECK_POLICY setting.

It appears however, due to the way that SQL is processed, it is impossible to create an SQL script of this nature that will work under both SQL Server 2000 and SQL Server 2005. I added code to check the Product Version, and can successfully bracket the code necessary with IF statements, but even though the IF statement would cause the CREATE LOGIN code to not be executed on SQL Server 2000, it errors anyway apparently because it is preparsing the script and of course, SQL Server 2000 doesn't have CREATE LOGIN. Consequently, checking Product Version is useless in this case. It looks like we'll have to do the version check outside of SQL and invoke script A for SQL Server 2000 and script B for SQL Server 2005.

Unless that is, I misunderstand the error I get from SQL Server 2000, or if there's some other way to compatibly do such a conditional. Here's an example script that runs fine under SQL Server 2005:

---------------
declare @ProductVersion as integer
set @ProductVersion = cast(left(cast(serverproperty('productversion')
as varchar(30)),1) as integer)

print 'Product Version = ' + cast(@ProductVersion as char)

IF @ProductVersion < 9 exec sp_addlogin 'testuser', 'fubar', 'master'
IF @ProductVersion > 8
BEGIN
CREATE LOGIN testuser WITH PASSWORD = 'fubar',
CHECK_POLICY = OFF, DEFAULT_DATABASE = [master]
END
----------------

On SQL Server 2000, @ProductVersion gets set to 8, but I get the following error:

Msg 170, Level 15, State 1, Server TESTSVR, Line 10
Line 10: Incorrect syntax near 'LOGIN'.


Any thoughts?


--

Sync

 

[ptheriault]

Does this work?

[COLOR=blue]IF[/color] @ProductVersion < 9 
[COLOR=blue]BEGIN[/color]
[COLOR=blue]exec[/color] sp_addlogin [COLOR=red]'testuser'[/color], [COLOR=red]'fubar'[/color], [COLOR=red]'master'[/color]
[COLOR=blue]END[/color]
[COLOR=blue]ELSE[/color]
[COLOR=blue]BEGIN[/color]
        [COLOR=blue]CREATE[/color] LOGIN testuser [COLOR=blue]WITH[/color]      PASSWORD = [COLOR=red]'fubar'[/color],
           CHECK_POLICY = [COLOR=blue]OFF[/color], DEFAULT_DATABASE = [master]
[COLOR=blue]END[/color]

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

Before SQL executes the code it checks the syntax of the code to ensure that it "looks" correct.

If you put the SQL 2005 commands within dynamic SQL (as much as dyanamic SQL sucks) it should fix your problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kdd21]

Yeah, shortly after I posted the message I thought about that-- and by setting a string variable to the CREATE LOGIN and using EXECUTE to run it I can get around the problem. Kludgy but it works and I don't need to support two scripts. Thanks....

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com