Using USE in a Stored Proc 2

[pmurch]

We have a number of stored procedures that need to run against a set of similar databases (~25). Our desire is a single proc that can step through all the databases, probably via a cursor.

Unfortunately, we run into issues because the USE statement does not allow a variable. The only solution we've come up with is to put the entire proc into a variable, inserting the target db into it, and using sp_sqlexec. Many of the procs are 100+ lines long, and the process of changing single-quotes to double-quotes and staying under the 4096 nvarchar limit for sp_sqlexec is time-consuming and tedious. And troubleshooting the query in the variable is a pain.

Ideal:
SET @dbn = 'MyDB' (or take @dbn from a cursor of database names)
USE @dbn

Only way I've found:
SET @cmd = 'USE ' + @dbn
+ ' ...rest of the script code which could be hundreds of lines...'
EXEC sp_sqlexec @cmd

I must be missing something, because it seems like this shouldn't be so hard. Does anyone know a better/easier way?

Thanks

 

[yelworcm]

No, you are not missing something. Having ~25 similar databases is (as you are finding out) not a best practice. In order to get through the current problem, though, I think you are going to end up using SQLCMD. I don't know the exact syntax for the DOS for loop, but you would put your database names in a file, and consume that list in a for loop running the script on each database with SQLCMD. Something like

for %%db in file (have to look up syntax)
sqlcmd /S server /E /d %db /i "script file goes here"

 

[JohnDuncanTB]

You can try creating the procedure in the master database and marking it as system object, then you should be able to run it from any user database in the context of that database.

Articles:
[ul][li]SQL Server Central[/li]
[li]SQL Team[/li]
[/ul]

--JD
"Behold! As a wild ass in the desert go forth I to my work."

 

[pmurch]

Thanks for the suggestion. Btw, we have many similar databases because we provide business services to that many separate businesses. Giving each their own database sandbox (so to speak) makes them feel more secure, and makes individualized customizations easier. But the downside is, as I've found here, managing these databases.

Using SQLCMD is a good suggestion and I'm sure will work for a number of cases. My initial thread simplified the issue somewhat, in that a number of these procs are being called from an Excel document (it's easier for the users to understand the interface). It's fairly easy to kick off a query from Excel VBA, but passing the database name means the proc receives it as a variable, and then I'm back in the mud of "USE <var>" which SQL won't allow.

I'll play with SQLCMD -- perhaps that can be kicked off from Excel VBA. Thanks for responding.

 

[pmurch]

Wow, fast responses -- I was replying to the first response when the second one came in!

I like the idea of the master database, presuming the "powers that be" will go along with it. I'll play with it in test and see what I can come up with.

Thanks again

 

[pmurch]

JohnDuncanTB,

Just realized that putting the proc in master doesn't solve the issue of changing databases within the proc. Yes, I could access the proc from anywhere else, but my issue is the inability within a single proc to change the active database programatically -- i.e., what I would have if I could put a variable in the USE statement. I could hardcode "USE db_x" for each database, but then I'd have to modify the scripts every time there's a new database in the mix. Programatically, I can pull the database names from SCHEMA_INFORMATION, and if I could then cursor through them and execute a USE, I'd be golden. But no luck so far.

Thanks again for responding.

 

[philhege]

A stored procedure lives in a database and, by design, should work on the objects in that database. Is it possible for you to use fully-qualified names of the objects? You'd still need to use dynamic SQL, but the procedure would end up being something you can create once and call from anywhere.

And look at using sp_executesql. Its limit is nvarchar(max).

-----------
With business clients like mine, you'd be better off herding cats.

 

[pmurch]

Thanks for the response. I think you've pointed me to a viable solution.

Yes, a proc works on objects in its database; however, what I'm trying to do is execute a script located in a central database against ~25 other databases. Using fully-qualified names means, as new databases are added, we'll have to modify the proc; I'd prefer to load a list of database names (we have a table of active databases), and insert them into the centralized query. It's here where the inability to say "USE <variable>" limits me.

So, I use dynamic SQL with a USE statement which, for each database from the active database table, sets the default db. Using sp_sqlexec (or sp_executesql as you suggested), the scripts run fine, until they hit the 4,096-character limit of sp_sqlexec. I did not know, however, that sp_executesql has a higher max length than sp_sqlexec, and that will likely solve my problem. A number of our scripts are over 4,096 bytes long, so we end removing white spaces, shortening variable names, etc., to make the code fit. That's what makes the process tedious. A higher max means we can test our code on a particular database, then simply do a replace of all single quotes with double quotes, insert the database name into the dynamic SQL, all should be well. Much simpler. Still wish it would be easier to troubleshoot where in the dynamic SQL a problem is occurring, but, hey -- can't have everything.

So thanks very much!

 

[philhege]

Glad to help.

When you have a problem with dynamic SQL statements, alter the SP to PRINT the statement instead of executing it. Then you can paste the generated code into another query window and analyze it. Yes, it's a bit more involved, but necessary.

-----------
With business clients like mine, you'd be better off herding cats.

 

[gmmastros]

There is a system stored procedure named [!]sp_msforeachdb[/!]. This procedure allows you to run commands against all the databases in your sql server instance.

The problem with this approach is that it won't limit the DB's to your ~25 databases. The other problem with this approach is that it is officially an "undocumented" procedure. It's been "undocumented" for at least 15 years now.

What I would do is to create your own version of sp_msforeachdb to suit your needs. You can get the code behind this procedure with this:

sp_helptext 'sp_msforeachdb'

Starting around line 30, there is a query to get all the databases. If you modify that query to limit it to just the ones you care about, and save this procedure to another name, it should probably go a long way towards solving your problem.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom