January 9, 2009 at 4:25 pm
Thanks.. This helped a bunch by allowing me to run sp_Who2 and limit the results only to blockers.
[font="Courier New"] SELECT
t.*
FROM
OPENQUERY(MyServerName, 'SET FMTONLY OFF; EXEC master.dbo.sp_Who2') t
WHERE
t.BlkBy != ' . '[/font]
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 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply