I have multiple external databases that i want to connect to, from a stored procedure. What server the database is located on is dynamic (a record in a table). All of the external databases are LinkedServers, but also here i need to be able to define what server dynamicly.

It works when i use EXEC to connect to the remote servers, example:

DECLARE @SQLinkedTable AS VARCHAR(50)
DECLARE @SQL NVARCHAR(4000)
DECLARE @Quantity AS INT

SET @SQLinkedTable='LINKEDSERVER1.DB1.dbo'
SELECT @SQL = 'SELECT @Quantity=COUNT(*) FROM '+@SQLinkedTable+'.[CampaignSettings] WHERE SQLID=1'
EXEC sp_executesql @SQL, N'@Quantity INT OUTPUT', @Quantity OUTPUT

In this code it returns a count to me from the external database, but i whould like to to more than that, for example UPDATES from the current database, and i think making a cursor to loop all the records is a bit messy.

These don't work:

DECLARE @SQLinkedTable AS VARCHAR(50)

SET @SQLinkedTable='LINKEDSERVER1.DB1.dbo'
SELECT * FROM @SQLinkedTable.[CampaignSettings])

DECLARE @ExternalDB AS VARCHAR(200)
SET @ExternalDB = 'Server=111.111.111.111;Database=DB1;UID=USR;Pwd=PASS;'
SELECT * FROM OPENROWSET ('SQLNCLI',@ExternalDB, 'SELECT * FROM [CampaignSettings]')

I know that everyone says don't do dynamic table linking, but in this case i have no option.

Any other Ideas?

Anyone with an idea?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.