December 26, 2008 at 12:30 pm
Hello
I have deployed one script on my centralized monitoring server that is checking agent status on all the servers . I have created all linked servers on the server.
On all the server i have activated the MSTDC service to allow remote connections and have configured the server properties to share MSDTC on network.
On few of the servers the script is running fine but it couldn't perform the operation on rest of the servers . I am getting the following alert message :
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
OS on some servers is windows server 2003 , windows 2000 server.
Please advise if the same can be done in some other way.
Thanks
December 26, 2008 at 12:51 pm
Hi...
chk whther firewal at those servers allows MSDTC connections...also hv a luk at this article....
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql
Regards,
[font="Verdana"]Sqlfrenzy[/font]
December 26, 2008 at 1:33 pm
Hi
Can anyone please tell me how to run the following command :
xp_cmdshell INSERT INTO test.dbo.SERVERAGENTSTATUS(ASTATUS) exec MASTER..XP_SERVICECONTROL 'QUERYSTATE','SQLSERVERAGENT'
Please help in making this dynamic command.
December 27, 2008 at 10:50 am
just remove xp_cmdshell.
--------
INSERT INTO SERVERAGENTSTATUS(ASTATUS)
exec MASTER..XP_SERVICECONTROL 'QUERYSTATE','SQLSERVERAGENT'
December 28, 2008 at 7:26 am
Can you plz post the script to check that sql agent is running or not in all servers. im also tring for that. My mail id :sanya.bandaru@gmail.com
December 28, 2008 at 7:54 pm
Use the below mentioned code:
Make sure first you create a table named servers under master database with servernames in it.
GO
/****** Object: StoredProcedure [dbo].[usp_linked_query] Script Date: 08/28/2008 19:41:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************
Use : Run queries in all linked servers
**********************************************************************/
Create PROCEDURE [dbo].[usp_linked_query]
AS
DECLARE @linkedserver varchar(800)
DECLARE @linked varchar(8000)
DECLARE @command nvarchar(1500)
DECLARE @count int
DECLARE @max-2 varchar(10)
DECLARE @max1 varchar(10)
DECLARE @SOURCESERVER VARCHAR(150)
DECLARE @STAT VARCHAR(150)
DECLARE @STAT1 VARCHAR(250)
DECLARE @STAT2 SYSNAME
Declare @statement nvarchar(4000)
Declare @statement1 nvarchar(4000)
Declare @statement2 nvarchar(4000)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[serveragentstatus]') AND type in (N'U'))
DROP TABLE [dbo].[serveragentstatus]
CREATE TABLE [dbo].[serveragentstatus](
[id][int] Identity(1,1),
[Servername] [nvarchar](50) NULL,
[AgentStatus] [nvarchar](50) NULL
) ON [PRIMARY]
truncate table master.dbo.serveragentstatus--Table that will be updated with backed up objects
set @max1=1
SET @SOURCESERVER=@@SERVERNAME--Source Server from where SP will be run
Set @statement1='Insert into ['+@@servername+'].master.dbo.SERVERAGENTSTATUS(AgentStatus)'
Set @statement='XP_SERVICECONTROL '+ '''QUERYSTATE'''+','+'''SQLSERVERAGENT'''
SET @count = 0
DECLARE linkedserver CURSOR FOR
SELECT srvname FROM master.dbo.servers ----(Table under master database on central server which will contain server names that needs to be checked)
--WHERE srvstatus not in (1609,1089) -- you can change this to filter the linked servers to be affected
order by srvname
OPEN linkedserver
FETCH NEXT FROM linkedserver INTO @linkedserver
WHILE (@@FETCH_STATUS = 0)
BEGIN
Print 'Server Is : '+@linkedserver
print '-----------------------------------------------------------------'
SET @STAT='EXEC MASTER.DBO.SP_ADDLINKEDSERVER @SERVER = N'''+@linkedserver+''',@SRVPRODUCT=N''SQL SERVER'''
Exec (@stat)
SET @linked = @statement1+'exec '+'"'+@linkedserver+'".master.dbo.'+@statement
exec (@linked)
Set @max-2=(select max(id) from master.dbo.serveragentstatus)
Select @STAT1='update ['+ @SOURCESERVER+'].master.dbo.serveragentstatus set servername='''
+@linkedserver
+''' where id ='+@max1
EXEC (@STAT1)
Set @max1=(select max(id) from master.dbo.serveragentstatus)
if @max1 is NULL
Set @max1=1
else
Set @max1=@max1+1
SET @count = @count+1
SET @STAT2='EXEC MASTER.DBO.SP_DROPSERVER @SERVER = N'''+@linkedserver+''''
Exec (@stat2)
print '@@@@@@@@@@@@@@@@@@@@@'
print 'Number of Affected Servers : '+convert(char(3),@count)
FETCH NEXT FROM linkedserver INTO @linkedserver
END
select * from serveragentstatus
CLOSE linkedserver
DEALLOCATE linkedserver
MJ
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply