OpenRowset Only Getting Results from localhost

• MarvinTheAndriod

  SSCarpal Tunnel

  Points: 4972

  More actions

  September 15, 2008 at 7:27 am

  #193606

  I am running the following SQL Query:

  SELECT

  p.*

  FROM

  OPENROWSET

  ('SQLOLEDB', 'Trusted_Connection=yes; Integrated Security=SSPI;

  DataSource=ServerName;Initial_Catalog=master;'

  , 'SELECT @@SERVERNAME AS ServerName, SUBSTRING(s.name, 1, 40) AS Expr1, DATEDIFF(dd, b.backup_start_date, GETDATE())

  AS Expr3, GETDATE() AS RD, b.backup_start_date, b.type

  FROM master.dbo.sysdatabases s LEFT OUTER JOIN

  msdb.dbo.backupset b ON s.name = b.database_name') AS p

  But it queries the local server, not the Server specified at DataSource. Any one got any ideas why ?

• eharper

  SSC Eights!

  Points: 802

  More actions

  September 15, 2008 at 7:55 am

  #871738

  You said

  DataSource=ServerName

  Try

  Server=ServerName

• MarvinTheAndriod

  SSCarpal Tunnel

  Points: 4972

  More actions

  September 15, 2008 at 7:58 am

  #871743

  Still get the same result

• eharper

  SSC Eights!

  Points: 802

  More actions

  September 15, 2008 at 8:09 am

  #871758

  The other thing I did (which I didn't realise was significant) was to remove the line-break in the connection string.

  ('SQLOLEDB', 'Trusted_Connection=yes; Integrated Security=SSPI;Server=ServerName;Initial_Catalog=master;'

  Re-adding the line break causes the query to run against the local host.

• MarvinTheAndriod

  SSCarpal Tunnel

  Points: 4972

  More actions

  September 15, 2008 at 10:02 am

  #871857

  Thank you very much, this worked.