Running statements that include dynamic database name query

jmaumus · July 24, 2013 4:47PM

Hi. I am only a "conversational SQL"-ist, so I apologize if I sound like a dope.

I am running MS SQL Server 2008 R2 (MSSMS 10.50.1600.1)

I have a script that I am running on about 20 or so databases. This script includes a CREATE PROCEDURE command with AS EXTERNAL in it. This requires that I change the Compatibility Level from 80 to 100 (we have it at 80 normally for backward compatibility of the apps that use these databases). Instead of doing that to each and every database, I added this line to my script:

DECLARE @strSQL_100 varchar(255)
SELECT @strSQL_100 = 'ALTER DATABASE ' + DB_NAME() + ' SET COMPATIBILITY_LEVEL = 100'
EXEC( @strSQL_100 )
GO

This creates the ALTER DATABASE command, then dynamically adds the database name and sets the Comp level to 100.

This part runs at the beginning of the script.

However, when it gets to the actual AS EXTERNAL command, I get an error message:

Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

What's weird is that the ALTER DATABASE command is actually setting the compatibility level to 100, but then the next part is failing.

When I run the script individually through SQL Query Analyzer on each database, it runs just fine.

Is this enough information for someone to get an idea of what I might be doing wrong? I am happy to provide more if necessary.

Thanks.
James

James B · July 26, 2013 7:38AM

Hi there.

I can't really see why this isn't working. I wondered if there was some sort of transaction wrapping the whole script up (like SQL Compare would do) as this can cause the behaviour you see - certain operations on the DB need to occur outside of the transaction for instance. I ran a quick test like this:

select compatibility_level from sys.databases where name=db_name&#40;&#41;
go

DECLARE @strSQL_100 varchar&#40;255&#41; 
SELECT @strSQL_100 = 'ALTER DATABASE ' + DB_NAME&#40;&#41; + ' SET COMPATIBILITY_LEVEL = 100' 
EXEC&#40; @strSQL_100 &#41; 
GO

select compatibility_level from sys.databases where name=db_name&#40;&#41;

And it appears to work fine though. Does it work if you put the procedure creation into a second script and run it after the first one?

jmaumus · July 26, 2013 3:05PM

Hi, James.

Actually, I have a script that I run in sequence before this one to SET QUOTED_IDENTIFIER OFF (because the main script has quotation marks in it that cause SQL to freak if you don't do that. I run a script with that one statement before the main one.

I added the ALTER DATABASE statement to that first script. It runs, but then the main script fails at the CREATE PROCEDURE step because of the AS EXTERNAL part.

I am completely stumped. I can show you the whole script if you want (although I'd rather do that via email).

James B · July 29, 2013 6:55AM

Sure, if you want to send it to support@red-gate.com quoting F0075425 in the subject line I can see if I can spot anything.

Multi-script really just runs whatever you give it, so it's definitely an odd one...

jmaumus · July 29, 2013 6:49PM

Well this is a bit embarrassing, but I think I resolved it.

I am the careful sort, so I have a tendency to Parse the SQL statement before I run it live. This collection of statements would not parse successfully because the Parse was seeing that the Compatibility Level was not set to 100. It was ignoring the fact that I was actually setting it earlier in the script, but that's understandable as it is only Parsing and not running it for real.

When I decided to "buck the tiger's odds" and run the collection of scripts live on a database, it ran just fine. It set the Compat Level to 100, created the procedures, and then set the Compat Level back to 80.

So it will work after all. It just won't Parse.

Thanks for listening, and I am sorry if I wasted your time.

James

James B · July 30, 2013 7:51AM

No worries- glad to hear it sounds like you're up and running now!

Running statements that include dynamic database name query

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk