Allan
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:
Any help or idea is greatly appreciated!
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]
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your code is almost correct. Just execute proc in the following way:
exec(@DbVar.dbo.[SomeProce dure])
exec(@DbVar.dbo.[SomeProce
ASKER
Thank you MD MAMUNUR RASHID; I will try it now!
ASKER
sorry; didn't work; same error...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.spro c
Yes, you can build the variable that will identify the linked db,
Not sure what you mean by dynamically linked.
Usually you would need to reference the linkeddb.dbo.database.spro
Yes, you can build the variable that will identify the linked db,
ASKER
yes MD MAMUNUR RASHID dynamic sql would work ... that would be ,
I'm going to play around with this some more over the weekend.
Thanks guys for your help thus far!
DECLARE @SQL AS VARCHAR(100)
SELECT @SQL = 'vendor_test.dbo.[SomeProcedure]'
EXEC @SQL
I'm going to play around with this some more over the weekend.
Thanks guys for your help thus far!
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
ASKER
it is linked; so we could do this: exec vendor_test.dbo.[SomeProce
Saw this social msdn: "EXECUTE AT" with Dynamic Linked Server Name
is it possible to dynamically update yourdb?
Or the work-around is to determine where it should go by db_name and then:
Open in new window
Will that work?
Thanks!