I have a series of scheduled stored procedures that need to be run against different databases that have identical schemas (think multiple instances of an application). So for instance, every database will always have the table member which will always have the columns member_number, name, and dob. The stored procedure find_new_members() would use exactly the same SQL no matter which database it was executed against.

I've found the command CREATE SYNONYM which looks like it will do the trick if I do something like this:

DECLARE @database NVARCHAR(16) = 'ClientA';
DECLARE @statement NVARCHAR(MAX) = 'CREATE SYNONYM CurrentDB FOR ' + @database;
EXECUTE sp_executesql @statement;
// Run generic statements

This allows me to farm the generic SQL out to a stored proc, and then just pass in the target database name. The problem is, it looks like the SYNONYM is scoped at a database level, so this means I can't have an SSIS package that runs the scripts against each client in their own stream. Not without the SYNONYM changing and affecting all scripts.

Is it possible to scope a SYNONYM statement to a user-level or (ideally) a PID-level, so that I can at least kick off multiple jobs at once, each using a different value for the SYNONYM CurrentDB?

EDIT: To explain my use case a little more, we are running queries from a database we have full read-write-execute on, against multiple linked databases we only have read rights on. Those databases are snapshots of source systems that we have no control over, and are simply databases sitting in a data lake. If we start throwing around `sp_executesql statements on the master scripts, we're going to lose a lot of refactoring and code control aspects in our IDEs. Ideally, we're looking at "aliasing" a linked database at a PID level.

  • 2
    Maybe I'm missing something - why can't you just create the same procedure on each DB and then run them? Also please include your version of SQL server. – George.Palacios Nov 27 '18 at 8:27
  • Or could you just do the job in ssis without a stored proc? – Sir Swears-a-lot Nov 27 '18 at 9:17
  • @George.Palacios Sorry I wasn't clear about our use case, I've edited my question above. We're using RO linked servers as the data source. – e_i_pi Nov 27 '18 at 20:02

Just switch the database context.

A handy way to do this is to call sp_executesql in the target database:

EG, with a static reference to the target database:

exec ClientA.sys.sp_executesql @job;

or dynamically:

declare @dbname sysname = 'ClientA';

declare @job nvarchar(max) = 'select db_name()';

declare @sql nvarchar(max) = concat('exec ', quotename(@dbname), '.sys.sp_executesql @job;');

--print @sql
exec sp_executesql @sql, N'@job nvarchar(max)', @job = @job;
| improve this answer | |
  • Thanks for the answer, I probably wasn't clear in my question initially. We're running these queries against linked databases, and are having to rewrite the query each time in order to fully qualify against known database names. I want to alias the database name so that the query can be generic and just "point" to a different database at run time. I'd prefer not to use something like @job because we lose refactoring and code completion in our IDEs. – e_i_pi Nov 27 '18 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.