SQL Server - OPENROWSET Server name error using parameters

Question

I'm having trouble with this query...Any idea how to declare variables and make it visible to OPENROWSET query?

  DECLARE @SERVERNM VARCHAR;

  SET @SERVERNM = (SELECT(CAST(CONNECTIONPROPERTY('local_net_address') AS VARCHAR) + '\' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR)))

  SELECT 
      * 
  FROM
      OPENROWSET('SQLOLEDB','SERVER=@SERVERNM;Trusted_Connection=yes;',
  'set fmtonly off;exec DW..P750106119 @ENT_NR_VERSAO=1')

Error:

Named Pipes Provider: Could not open a connection to SQL Server [53].

Answer 1

You can't use a variable within a literal string. In your connection you're stating you want to connect to a server actually called "@SERVERNM", not the value of @SERVERNM. Also, as you have DECLARE @SERVERNM VARCHAR, which is the same as DECLARE @SERVERNM VARCHAR(1). I suspect this might work but assumes @SERVERNM will have a value of less than or equal to 128 characters:

DECLARE @SERVERNM sysname;
SELECT @SERVERNM = CAST(CONNECTIONPROPERTY('local_net_address') AS sysname)+'\'+CAST(SERVERPROPERTY('InstanceName') AS sysname);
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT *' + NCHAR(13) + NCHAR(10) +
           N'FROM OPENROWSET(''SQLOLEDB'',' + NCHAR(13) + NCHAR(10) +
           N'                ''SERVER=' + QUOTENAME(@SERVERNM,'"') + N';Trusted_Connection=yes;'',' + NCHAR(13) + NCHAR(10) +
           N'                ''set fmtonly off;exec DW..P750106119 @ENT_NR_VERSAO=1'');'
EXEC sp_executesql @SQL;