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].

Do you have that Trusted Connection set up on the server you're calling from? And does the User you're running this query as have permissions to access both? — Shawn
– Shawn, Commented Mar 18, 2019 at 18:42
You are declaring quite a few varchars here but NONE of them have the length specified. Don't rely on defaults, be specific. Do you know the default length can change based on how you use it? sqlblog.org/2009/10/09/… — Sean Lange
– Sean Lange, Commented Mar 18, 2019 at 18:45

Thom A · Accepted Answer · 2019-03-18 18:47:46Z

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;

answered Mar 18, 2019 at 18:47

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gabriel Franco Over a year ago

I changed a little bit your solution, but i got it !! cheers mate

Ana Iuliana Tuhasu · Accepted Answer · 2019-03-18 19:34:38Z

1

This is the declaration of the variables :

DECLARE @SERVERNM VARCHAR(100)

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

edited Mar 18, 2019 at 19:34

answered Mar 18, 2019 at 18:44

Ana Iuliana Tuhasu

2371 silver badge6 bronze badges

2 Comments

Gabriel Franco Over a year ago

@ENT_NR_VERSAO is a variable inside Sp P750106119, so I don't need declare it, just set.

Thom A Over a year ago

This won't solve the problem of 'SERVER=@SERVERNM;Trusted_Connection=yes;'. Unlike some languages, T-SQL does not inline replace variable names within a literal string with the variables value. DECLARE @var varchar(10) = 'abc'; SELECT '123@var'; won't return '123abc', it'll return '123@var'. Plus the fact that you should be applying the logic of declaring your length not only to the variable declaration, but to the data types within the CAST expressions as well.

Gabriel Franco · Accepted Answer · 2019-03-18 19:57:38Z

0

SOLUTION:

DECLARE @SERVERNM sysname;
SET @SERVERNM = 'SERVER='+CAST(CONNECTIONPROPERTY('local_net_address') AS sysname)+'\'+CAST(SERVERPROPERTY('InstanceName') AS sysname)+';Trusted_Connection=yes;'
DECLARE @TEST NVARCHAR(MAX)
SET @TEST = QUOTENAME(@SERVERNM,'''')

DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT *' + NCHAR(13) + NCHAR(10) +
           N'FROM OPENROWSET(''SQLOLEDB'',' + NCHAR(13) + NCHAR(10) +
           N'                ' + @TEST + ',' + NCHAR(13) + NCHAR(10) +
           N'                ''set fmtonly off;exec DW..P750106119 @ENT_NR_VERSAO=1'');'

EXEC sp_executesql @SQL;

answered Mar 18, 2019 at 19:57

Gabriel Franco

133 bronze badges

Collectives™ on Stack Overflow

SQL Server - OPENROWSET Server name error using parameters

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related