Distributed Transaction?

  • BSavoie

    SSCertifiable

    Points: 6309

    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

    .

  • Jeff Moden

    SSC Guru

    Points: 993784

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • BSavoie

    SSCertifiable

    Points: 6309

    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: 150341

    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

    SSC Guru

    Points: 150341

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

    Paul

  • Jeff Moden

    SSC Guru

    Points: 993784

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • BSavoie

    SSCertifiable

    Points: 6309

    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

    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

    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

    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.

    .

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

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