Link to home
Start Free TrialLog in
Avatar of Allan
AllanFlag for United States of America

asked on

how would you excute a sproc on another database on the fly

Hello Experts!

This is for SQL Server 2012; how would you excute a sproc on another database on the fly?

We've in our database instance:

vendor_test
vendor_test_custom

vendor_test2
vendor_test2_custom

vendor_test3
vendor_test3_custom

We want to write a script that when it's deployed to any of the testx_custom it will be
able to execute the sproc on vendor_testx

For example, deploying to vendor_test_custom we want to execute the sproc on vendor_test

In our script we tried:

Declare @DatabaseName as varchar(50) = db_name()
declare @DbVar as varchar(50)

IF @DatabaseName = 'vendor_test_custom' select @DbVar = 'vendor_test'
IF @DatabaseName = 'vendor_test2_custom' select @DbVar = 'vendor_test2'
IF @DatabaseName = 'vendor_test3_custom' select @DbVar = 'vendor_test3'

exec @DbVar.dbo.[SomeProcedure]

Open in new window

And it failed with this:

Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '.'.

Any help or idea is greatly appreciated!
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Allan

ASKER

Hi Arnold.

it is linked; so we could do this: exec vendor_test.dbo.[SomeProcedure]

Saw this social msdn: "EXECUTE AT" with Dynamic Linked Server Name

User generated image
is it possible to dynamically update yourdb?

Or the work-around is to determine where it should go by db_name and then:

IF @DatabaseName = 'vendor_test_custom'  SELECT @sp_executesql = @linkedserver + '.vendor_test.sys.sp_executesql'
IF @DatabaseName = 'vendor_test2_custom'  SELECT @sp_executesql = @linkedserver + '.vendor_test2.sys.sp_executesql'
IF @DatabaseName = 'vendor_test3_custom'  SELECT @sp_executesql = @linkedserver + '.vendor_test3.sys.sp_executesql'
  

Open in new window


Will that work?

Thanks!
Your code is almost correct. Just execute proc in the following way:

exec(@DbVar.dbo.[SomeProcedure])
Avatar of Allan

ASKER

Thank you MD MAMUNUR RASHID; I will try it now!
Avatar of Allan

ASKER

sorry; didn't work; same error...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As noted when they are linked, you have to reference the full linkeddb.dbo.

Not sure what you mean by dynamically linked.

Usually you would need to reference the linkeddb.dbo.database.sproc
Yes, you can build the variable that will identify the linked db,
Avatar of Allan

ASKER

yes MD MAMUNUR RASHID dynamic sql would work ... that would be ,

DECLARE @SQL AS VARCHAR(100)

SELECT @SQL = 'vendor_test.dbo.[SomeProcedure]'

EXEC @SQL

Open in new window


I'm going to play around with this some more over the weekend.

Thanks guys for your help thus far!
Avatar of Allan

ASKER

We ended up with this; thanks and splitting points!

DECLARE @DatabaseName AS VARCHAR(50)= DB_NAME()
DECLARE @DbVar AS VARCHAR(50)

IF @DatabaseName = 'vendor_test_custom'  SELECT @DbVar = 'vendor_test'
IF @DatabaseName = 'vendor_test2_custom' SELECT @DbVar = 'vendor_test2'
IF @DatabaseName = 'vendor_test3_custom' SELECT @DbVar = 'vendor_test3'

DECLARE @sp_executesql AS NVARCHAR(1000)
DECLARE @SQL AS NVARCHAR(100)
SELECT @SQL = 'dbo.[SomeProcedure]'

SELECT @sp_executesql = @DbVar+'.sys.sp_executesql'
EXEC @sp_executesql     @sql

Open in new window