Solved

How to change database context dynamically

Posted on 2014-10-06
4
390 Views
Last Modified: 2014-10-07
Hey folks,

I have a script I need to run against ~300 databases. using stored procedures (sp_msforeachdb, exec) aren't viable because of batch separators in the form of GO statements throughout the script.

What's the best way to dynamically context and then run the script verbatim? The script is 1200+ lines, so I'm trying to avoid having to edit the script and dynamically assign fully qualified server/db names throughout.

Let me know if I didn't provide enough information.

basically I'm looking to do something like:

for each (@db in dbs)
 use @db;
run rest of script
0
Comment
Question by:FroggedUp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40365354
Like this:
DECLARE @MySQLStatement nvarchar(MAX);
DECLARE @MySQLScript nvarchar(MAX);

for each (@db in dbs) 
SET @MySQLScript = 'code of rest of script here'
SET @MySQLStatement = N'use ' + @db + '; EXEC sp_executesql N''' + @MySQLScript  + ''''
EXEC (@MySQLStatement )

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 40366458
Create the script as a proc in the master db, naming it "sp_<whatever>", and marking it as a "system" proc.

Then, when you invoke it, it will run in the context of the current db (which, as always, can be modified by specifying the db name on the EXEC).

EXEC db1.dbo.sp_yourproc
EXEC db2.dbo.sp_yourproc
EXEC db3.dbo.sp_yourproc
...
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40366548
>> using stored procedures (sp_msforeachdb, exec) aren't viable because of batch separators in the form of GO statements throughout the script. <<

If it's generated from SSMS, you can turn off the "GO"s being generated into the script (the option is probably not labeled what you'd expect it to be though: it's the very first option, "Delimit individual statements").

Other than a stored proc(s), you'd probably have to use sqlcmd or Powershell.
0
 

Author Comment

by:FroggedUp
ID: 40367014
Thanks guys. I ended up running it through sqlcmd and building the db statement dynamically and executing via xp_cmdshell.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question