EXEC('SELECT COUNT(*) FROM sys.tables;') AT @ServerName; How

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.


August 29th, 2015 5:23pm

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;
Free Windows Admin Tool Kit Click here and download it now
August 29th, 2015 7:54pm

Hi Tom,

The problem is EXEC @GetEventTargetPathFile. Please change to EXEC (@GetEventTargetPathFile) it should work.

Please use below script,

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);

August 29th, 2015 8:10pm

Hi Tom,

Follow the below links to know detailed explanation on the error.

http://stackoverflow.com/questions/11991987/exec-failed-because-the-name-not-a-valid-identifier

http://stackoverflow.com/questions/22008859/the-name-is-not-a-valid-identifier-error-in-function

exec (@query)
EXECUTE sp_executesql @query

Thanks, SMSVikasK

Free Windows Admin Tool Kit Click here and download it now
August 30th, 2015 1:57am

Hi Tom,

Follow the below links to know detailed explanation on the error.

http://stackoverflow.com/questions/11991987/exec-failed-because-the-name-not-a-valid-identifier

http://stackoverflow.com/questions/22008859/the-name-is-not-a-valid-identifier-error-in-function

exec (@query)
EXECUTE sp_executesql @query

Thanks, SMSVikasK

  • Marked as answer by Tom Groszko 16 hours 36 minutes ago
August 30th, 2015 5:54am

Thanks
Free Windows Admin Tool Kit Click here and download it now
August 30th, 2015 10:33am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics