1

I am running this openrowset query about jobs on my current server and I see that the result set is not correct.

I was expecting a bunch of jobs but I only get one.

when I run the following piece of code I can see why:

select [my current server] = @@servername

select *
from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;',
'select [my current server] = @@servername') AS a

that gives me the following result:

enter image description here

'MATHURA is my server name (machine name) 'MATHURA\SQL2016 is the server name\instance name where I am executing this query

obviously when I pass Server=(local); to the openrowset it takes it to be my server name, but I am running on the mathura\sql2016 instance.

the question is:

how can I pass the currently named instance to the openrowset?

Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320

1 Answers1

3

If you want to "dynamically" reference the current server's instance name within OPENROWSET, then you'll need to use dynamic SQL to build the query statement to run. Unfortunately, OPENROWSET won't accept a variable for its own "query" parameter, so you need to build the entire OPENROWSET line dynamically, and then EXECute that line. Eg.

DECLARE @sql varchar(1000);

SELECT @sql = 'SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @@SERVERNAME + ';Trusted_Connection=yes;'',''SELECT 1 --your query here;'') AS [a];';


EXEC (@sql);
Craig
  • 298
  • 1
  • 7