Openrowset Error

• Sachin Diwakar

  SSC Eights!

  Points: 860

  More actions

  March 25, 2009 at 4:03 am

  #132292

  I am running the below query:

  insert into dbo.data_temp -- inserting records in temp table

  SELECT * FROM OPENROWSET('SQLNCLI','Server= anyserver;Trusted_Connection=yes; Initial Catalog=anydb,"exec sp_spaceused t_backup_info")

  *data_temp has the same structure as sp_spaceused's output.

  *same query works fine is I use any select statment in place of 'exec sp_spaceused t_backup_info'

  I am getting the below error when I run the only above query

  Msg 7314, Level 16, State 1, Line 1

  The OLE DB provider "SQLNCLI" for linked server "(null)" does not contain the table "exec sp_spaceused t_backup_info". The table either does not exist or the current user does not have permissions on that table.

  but when I run from SA id, i got this in error logs.

  Cannot process the object "exec sp_spaceused trWORecurJob". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

  Any help will be appreciated. I would be happy if some1 can suggest some other ways too..

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 26, 2009 at 4:09 am

  #966500

  Some of your commas are a little random. Try the first query, if this works, try the second.

  SELECT *

  FROM OPENROWSET('SQLNCLI',

  'Server= anyserver;Trusted_Connection=yes; Initial Catalog=anydb',

  'SELECT GETDATE()')

  SELECT *

  FROM OPENROWSET('SQLNCLI',

  'Server= anyserver;Trusted_Connection=yes; Initial Catalog=anydb',

  'exec sp_spaceused ''t_backup_info''')

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden