Solved

How to change database context dynamically

Posted on 2014-10-06
4
445 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 51

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

631 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