ServerProperty and OPENDATASOURCE

• Adam Bean

  One Orange Chip

  Points: 26631

  More actions

  January 13, 2009 at 12:36 pm

  #129965

  Hey all,

  Is it possible to run a server side function such as ServerProperty using OPENDATASOURCE? If so, how? Also, can you use OPENDATASOURCE without specifying a three part name when using a system function?

  What I am running is returning local results.

  SELECT

  CAST(ServerProperty('ProductVersion') AS VARCHAR)

  ,CAST(ServerProperty('Edition') AS VARCHAR)

  ,CAST(ServerProperty('ProductLevel') AS VARCHAR)

  FROM OPENDATASOURCE('SQLNCLI',

  'Data Source=ServerNameHere;Integrated Security=SSPI')

  .master.dbo.sysdatabases

  Thanks

• Kyle Neier ,

  SSCrazy

  Points: 2252

  More actions

  January 13, 2009 at 1:58 pm

  #926225

  I don't think it can be done with opendatasource, however, it could be done with openquery (if you want to make a linked server):

  select * from openquery(ServerNameHere, 'select serverproperty(''ProductVersion'')')

  or openrowset (if you don't):

  select * from openrowset('SQLOLEDB', 'server=ServerNameHere;trusted_connection=yes;',

  'select serverproperty(''productversion'')')

  Kyle

• Adam Bean

  One Orange Chip

  Points: 26631

  More actions

  January 13, 2009 at 2:21 pm

  #926246

  Thanks Kyle, openrowset is what I was looking for.