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=22.214.171.124;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?