I have searched tirelessly for how to execute the EXEC AT command with a variable and I have not found it.
SQL SERVER 2008 R2
Example:
DECLARE @ServInst Varchar(100) = 'SQLSERV\INST01'
DECLARE @Query nVarchar(100) = 'SELECT * FROM BASE.DBO.TABLA'
EXEC(@Query) AT [@ServInst] -- Pero esto no me lo permite
I know that the instance could be included in the same Query ... but I do not need it that way, I need to add it in AT .
Any ideas?
According to the documentation, the clause at of SQL Server command exec acts only on linked servers.
In principle, this prevents you from executing directly on a server whose name you have in a variable. But nothing prevents you from creating a linked server, execute the command and go.
For example, following the line of your example, something like:
DECLARE @ServInst Varchar(100) = 'SQLSERV\INST01';
EXEC sp_addlinkedserver
@server=N'REMOTO',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=@ServInst;
go
DECLARE @Query nVarchar(100) = 'SELECT * FROM BASE.DBO.TABLA';
EXEC(@Query) AT Remoto
go
exec sp_dropserver 'REMOTO';
go
Warning: I have written the sentences right here in the editor of StackOverflow, there is no guarantee that they work without making any adjustments, but I'll leave you with the idea.