| Author |
Topic |
|
sfpm82a
Starting Member
4 Posts |
 Posted - 2004-03-25 : 10:21:32
|
| For a variety of reasons, the main one being that I will have 100 databases with the same structure (by client), I want to keep my SP's in a central database (1 copy instead of 100) and run them from the local databases. The problem is that the SP assumes the objects referenced by it are in the same database as the procedure unless qualified with a database name. I can get around this in most cases by passing a database name to the procedure and using dynamic sql to build the table names (I know about the pitfalls of dynamic sql but these are batch procedures and building a new plan once per day is not an issue, and only a batch userid will have execute privilege for each database so security is not an issue for us either). The problem is that I cannot find a way to use a variable table name in a cursor and I am doing some stuff that requires them. I'm all about using set-based queries when possible but I can't get around the need for them in a couple of my procedures. Is there a way to use a cursor with variable table names?As a last resort, I would have to copy my procedures to each database each time I made a change or added a database. If there is an alternative, I would at least like to give it a try.Thanks! |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2004-03-25 : 10:30:23
|
| Why dont you copy the records into a temporary table (using dynamic SQL ) before declare cursor and use that #table in the cursor ??- Sekar |
 |
|
|
sfpm82a
Starting Member
4 Posts |
Posted - 2004-03-25 : 13:43:12
|
| Thanks, that seems to work outside of a procedure but once I put it into a procedure I get "Invalid object name" on the temp table in the cursor. Any idea why?Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-25 : 13:45:27
|
| Have you tried a global temporary table? CREATE TABLE ##Temp1(...)Tara |
|
|
|
sfpm82a
Starting Member
4 Posts |
Posted - 2004-03-25 : 14:03:11
|
| I need for the temp table to be local since loads for different databases could run simultaneously.I was wrong about it working outside a procedure...it apparently only worked because the temp table existed prior to me running the code. Once I dropped the temp table it would not work outside the procedure either, even though I am creating the temp table prior to declaring and opening the cursor...still stumped! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-25 : 14:10:50
|
| Please post the code.Tara |
|
|
|
sfpm82a
Starting Member
4 Posts |
Posted - 2004-03-25 : 18:02:11
|
| I think I have figured out that the problem is the combination of dynamic sql and temp tables or local cursors. It seems that executing dynamic sql to create a temp table or local cursor does not give the procedure access to those objects. It must be because a separate process is 'forked' to execute the sql and the objects are created 'local' to that process. I'll just find another way to accomplish what I want to do. Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-25 : 18:07:03
|
| Yes, that is why I mentioned the global temporary table, which of course can cause problems if someone else is using the global table already.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-25 : 20:06:36
|
| One way to do this (I can't believe I'm telling someone this) is to capture the SPID on each query and use a global temp table. Inside the cursor you will call your temp table ##name + @@spid. You will need to create it with dynamic SQL for this to work. This will insure that the temp table is unique. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-28 : 11:49:17
|
quote:
As a last resort, I would have to copy my procedures to each database each time I made a change or added a database.
This is a lot easier than you think, if you can use a few command-line calls (these are case-sensitive):bcp "select name from master..sysdatabases where dbid>4" queryout c:\dbs.txt -T -Sserver -cfor /F %a in(c:\dbs.txt) do osql -E -Sserver -d%a < myproc.sqlIf you have the stored procedure saved in myproc.sql, this code will:a. Extract all the non-system database names from the sysdatabases table to a fileb. Iterate through the file contents and for each database, execute the myproc.sql file using osqlThe only thing to look out for is any database-specific references in the myproc.sql file (USE database, for example, or qualified object names)BTW, it is very easy to extend this to deploy hundreds or thousands of stored procedures to hundreds of databases. Each procedure would be stored in its own file but in the same folder. You can nest the for commands to iterate through all the files in that folder, and have it connect to each database in turn as it does so. |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-28 : 14:01:12
|
| Sooner or later you'll need such roll-out routines anyway. As Rob said, this isn't magic and is a cleaner solution, IMHO.To answer your original question http://www.sommarskog.se/dynamic_sql.html#cursor --Frankhttp://www.insidesql.de |
|
|
|
|
Variable table names in cursors