I need to execute some tsql at another server that is being passed into a procedure. Like this code.
DECLARE @ServerName SYSNAME = N'MTGDESKTOP\SQL2012'; DECLARE @GetEventTargetPathFile NVARCHAR(4000) = CONCAT(N'EXEC (''SELECT CAST(SERVERPROPERTY(N''''ErrorLogFileName'''') AS NVARCHAR(4000));'') AT [' ,@ServerName ,'];'); PRINT @GetEventTargetPathFile; EXEC @GetEventTargetPathFile;
This code provides me with this message
EXEC ('SELECT CAST(SERVERPROPERTY(N''ErrorLogFileName'') AS NVARCHAR(4000));') AT [MTGDESKTOP\SQL2012];
Msg 203, Level 16, State 2, Line 8
The name 'EXEC ('SELECT CAST(SERVERPROPERTY(N''ErrorLogFileName'') AS NVARCHAR(4000));') AT [MTGDESKTOP\SQL2012];' is not a valid identifier.
How can I dynamically execute at another sql server instance?Thanks.