Distributed Transaction?

Question

Post reply

Distributed Transaction?

BSavoie

SSCertifiable

Points: 6309
More actions
February 6, 2010 at 8:47 pm

#210701

I need to check if a custom Windows Service is running at customer sites. Very easy thing to do with xp_serviceControl, and linked servers. Works great, BUT when I try to store the results of this handy little guy in a table, it fails because SQL Server wants to start a DTC transaction. How do I get around this? I just want to get my hands on the return value.
Here's the error:
"The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "AJLB" was unable to begin a distributed transaction."
Here's the TSQL:
DECLARE @SvcState TABLE(
SvcStatus varchar(50),
TimeChecked datetime default(getdate())
)
INSERT INTO @SvcState(SvcStatus)
exec("ajtb.master.dbo.xp_serviceControl 'querystate', 'SqlServerAGENT'")
SELECT * FROM @SvcState
.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 1

I'm not sure if this is the problem or not but make sure that MSDTC is running correctly on both machines.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BSavoie SSCertifiable Points: 6309 More actions · Answer 2

Thanks Jeff. actually, I've been doing a little research, I might be better off using SSIS here. I think I understand how to get the service state using SSIS/WMI, but I'm not sure I understand how to do the service start/stop using SSIS/WMI. I know this is the wrong forum for that, but if you have any thoughts on that approach, Id appreciate it.

.

Paul White SSC Guru Points: 150467 More actions · Answer 3

This is an old problem. INSERT...EXEC runs in an implicit local transaction (an INSERT statement must be atomic, remember) and SQL Server attempts to promote the local transaction to a distributed transaction if the procedure call is remote, for obvious reasons.

SQL Server 2008 introduces a new linked server configuration option: remote proc transaction promotion. You call it like this: EXEC sp_serveroption {linked-server-name}, N'remote proc transaction promotion', 'FALSE'; to turn off the transaction-promotion behaviour.

This option is described in detail, though in a different context, in the following blog entry by the SQL Server Programmability Team:

http://blogs.msdn.com/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx

I should stress that I have not tested this personally, but I think it should work. The blog entry is an excellent read by the way - and the subject of it is very interesting too.

edit: it is also well documented in Books Online http://technet.microsoft.com/en-us/library/ms178532.aspx

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 4

I would also never forgive myself if I didn't mention that another way to this is to use a SQLCLR procedure.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

BSavoie (2/6/2010)
Thanks Jeff. actually, I've been doing a little research, I might be better off using SSIS here. I think I understand how to get the service state using SSIS/WMI, but I'm not sure I understand how to do the service start/stop using SSIS/WMI. I know this is the wrong forum for that, but if you have any thoughts on that approach, Id appreciate it.

I believe that Paul may have sussed it above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BSavoie SSCertifiable Points: 6309 More actions · Answer 6

Thanks again Paul. Another great answer.

In my research on this, I came across an SSIS / WMI solution that might be better. I have to actually do this execute this service check / stop / start / upgrade stuff at 180 remote sites or so. I have the SSIS / WMI solution very close, I just cant figure out how to get a script task to authenticate on the remote system. I posted that question over in the SSIS forum.

Any thoughts on this approach would be appreciated.

.

BSavoie SSCertifiable Points: 6309 More actions · Answer 7

So I'm thinking I like the sound of that sqlclr solution best, IF I can get it to work the way I need!

If I can get "impersonation" to work on the remote site from inside the clrsproc, that could be a pretty clean solution with very little code.

.

BSavoie SSCertifiable Points: 6309 More actions · Answer 8

Hmm, wait a minute. If I go the sqlclr route, maybe I don't have to do impersonation? I already have all the linked servers setup. Could it be that simple?

.

BSavoie SSCertifiable Points: 6309 More actions · Answer 9

Argh! BUT if I implement this in SSIS, I can keep the load off the SQL Server, and since I will want this to be a sort of "service monitor" that runs a couple times a day in the background, SSIS is probably a better choice. So, I guess I need to figure out how to do impersonation from a Script Task.

.