OPENQUERY Error on a linked server

• Scott Thornburg

  Hall of Fame

  Points: 3347

  More actions

  January 9, 2009 at 8:47 pm

  #924751

  David,

  My understanding is that OPENQUERY must return a result set, which is why it can be used wherever a able expression is appropriate, such as a FROM clause. I don't understand why direct calling of your procedure is not effective, but it looks like you have a permission error of some sort. My recommendation would be to see if you can resolve the error, and then call it via a 4-part name as you attemped:

  [SERVERNAME]...SP_Name 'email.email.com','SPPassword'

  Nonetheless, I found that with some test code, even though I received an error, an insert still occurred. This was tested in SQL2000 and SQL 2008.

  Here's some sample code:

  use tempdb

  create table dbo.myTemp

  (col1 int identity(1,1)

  ,col2 datetime default getdate()

  ,col3 varchar(10) )

  go

  create procedure dbo.test1

  (@myVal varchar(10) )

  as

  begin

  insert myTemp (col3)

  values(@myVal)

  end

  go

  -- !!! REPLACE myServerName with the actual name of your sql server instance

  select * from openquery([i]myServerName[/i], 'set fmtonly off; exec tempdb.dbo.test1 ''AA'' ')

  select * from tempdb.dbo.myTemp

  /* Cleanup when done

  drop proc dbo.test1

  drop table dbo.myTemp

  */

  Bottom line: Doing inserts in a proc via OPENQUERY does not seem especially robust. As I said above, try the 4-part name call first.

  Scott Thornburg

• Scott Thornburg

  Hall of Fame

  Points: 3347

  More actions

  January 9, 2009 at 8:55 pm

  #924754

  DFolz,

  Excellent use! I've done something similar, filtering by database or user or SPID. You can also use this to insert the results of SP_WHO2 into a table (have to pre-create the table or use a SELECT INTO, and watch out for the double SPID column).

  Glad this helped,

  Scott Thornburg