Running statements that include dynamic database name query

Single-click script execution on multiple SQL Servers.

Moderators: JonathanWatts, Anu Deshpande, dlkj, Colin Millerchip, Michelle Taylor

Running statements that include dynamic database name query

Postby jmaumus » Wed Jul 24, 2013 9:47 pm

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
jmaumus
 
Posts: 3
Joined: Wed Jul 24, 2013 9:32 pm

Postby james.billings » Fri Jul 26, 2013 12:38 pm

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:

Code: Select all
select compatibility_level from sys.databases where name=db_name()
go

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

select compatibility_level from sys.databases where name=db_name()


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?
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

no it doesn't

Postby jmaumus » Fri Jul 26, 2013 8:05 pm

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).
jmaumus
 
Posts: 3
Joined: Wed Jul 24, 2013 9:32 pm

Postby james.billings » Mon Jul 29, 2013 11:55 am

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...
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

I think I resolved it

Postby jmaumus » Mon Jul 29, 2013 11:49 pm

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
jmaumus
 
Posts: 3
Joined: Wed Jul 24, 2013 9:32 pm

Postby james.billings » Tue Jul 30, 2013 12:51 pm

No worries- glad to hear it sounds like you're up and running now!
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.


Return to SQL Multi Script 1

Who is online

Users browsing this forum: No registered users and 1 guest