Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to change database context dynamically

Posted on 2014-10-06
4
Medium Priority
?
538 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 52

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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 2000 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

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 the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

704 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