Red Gate forums :: View topic - Running statements that include dynamic database name query
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Multi Script 1
SQL Multi Script 1 forum

Running statements that include dynamic database name query

Search in SQL Multi Script 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
jmaumus



Joined: 24 Jul 2013
Posts: 3

PostPosted: Wed Jul 24, 2013 9:47 pm    Post subject: Running statements that include dynamic database name query Reply with quote

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
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1120
Location: My desk.

PostPosted: Fri Jul 26, 2013 12:38 pm    Post subject: Reply with quote

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 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?
Back to top
View user's profile Send private message
jmaumus



Joined: 24 Jul 2013
Posts: 3

PostPosted: Fri Jul 26, 2013 8:05 pm    Post subject: no it doesn't Reply with quote

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).
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1120
Location: My desk.

PostPosted: Mon Jul 29, 2013 11:55 am    Post subject: Reply with quote

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...
Back to top
View user's profile Send private message
jmaumus



Joined: 24 Jul 2013
Posts: 3

PostPosted: Mon Jul 29, 2013 11:49 pm    Post subject: I think I resolved it Reply with quote

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. Smile

James
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1120
Location: My desk.

PostPosted: Tue Jul 30, 2013 12:51 pm    Post subject: Reply with quote

No worries- glad to hear it sounds like you're up and running now!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group