January 9, 2009 at 8:47 pm
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
January 9, 2009 at 8:55 pm
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
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy