November 21, 2008 at 9:45 am
I am attempting to sweep all our database servers to get information about our sql agent jobs in one place. I am trying to run the same code across all servers, just changing the linked server name. The code is running through the SQL Server 2000 machine - machine definitions are at the bottom of the message.
I can do the following and get a result back in query analyzer in about 1 second.
exec db01.master.dbo.xp_sqlagent_enum_jobs 1,'dummy';
exec db02.master.dbo.xp_sqlagent_enum_jobs 1,'dummy';
exec db03.master.dbo.xp_sqlagent_enum_jobs 1,'dummy';
The problem occurs when I try and put the results into a table, temporary or permanent - it didn't make a difference.
create table #AgentStatus (
job_id uniqueidentifier NOT NULL,
last_run_date int NOT NULL,
last_run_time int NOT NULL,
next_run_date int NOT NULL,
next_run_time int NOT NULL,
next_run_schedule_id int NOT NULL,
requested_to_run int NOT NULL,
request_source int NOT NULL,
request_source_id sysname NULL,
running int NOT NULL,
current_step int NOT NULL,
current_retry_attempt int NOT NULL,
job_state int NOT NULL
);
Insert Into #AgentStatus
exec db01.master.dbo.xp_sqlagent_enum_jobs 1,'dummy';
Insert Into #AgentStatus
exec db02.master.dbo.xp_sqlagent_enum_jobs 1,'dummy';
Insert Into #AgentStatus
exec db03.master.dbo.xp_sqlagent_enum_jobs 1,'dummy';
DB01 and DB02 work fine but DB03 (sql 2005) hangs and takes about 6-7 minutes to cancel. I thought it may be the 64-bit version of SQL Server - integers 8 bytes instead of 4, etc. I did an sp_help on tables and the definitions (Type/Length) were the same. I tried moving the code execution to DB01 with the same results. When I moved the code to DB03, I got "linked server was unable to start a distributed transaction" for DB01 and DB02 and "transaction context in use by another session" for DB03.
My machine setup is:
DB01 - Microsoft SQL Server 7 (SP3) Standard Edition (WIN 2000 SP4)
DB02 - Microsoft SQL Server 2000 (SP4) Enterprise Edition (WIN 2000 SP4)
DB03 - Microsoft SQL Server 2005 Enterprise Edition (64-bit) (SP2) (WIN 2003 SP2)
Any help you can give would be greatly appreciated.
November 21, 2008 at 11:21 am
In the 2005 Server run this:
create table AgentStatus (
job_id uniqueidentifier NOT NULL,
last_run_date int NOT NULL,
last_run_time int NOT NULL,
next_run_date int NOT NULL,
next_run_time int NOT NULL,
next_run_schedule_id int NOT NULL,
requested_to_run int NOT NULL,
request_source int NOT NULL,
request_source_id sysname NULL,
running int NOT NULL,
current_step int NOT NULL,
current_retry_attempt int NOT NULL,
job_state int NOT NULL);
go
create proc jobstatus
with execute as 'dbo'
as
begin
truncate table AgentStatus
INSERT INTO AgentStatus
exec xp_sqlagent_enum_jobs 1,'dummy';
select * from AgentStatus
end
In SQL 2000 with linked server setup to 2005 run this:
exec db03.master.dbo.jobstatus
November 21, 2008 at 11:40 am
That works great. Thanks Alex
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply