Using sp_run_xml_proc inside transaction

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mark Wilden (mark_at_mwilden.com)
Date: 11/17/04 

Date: Wed, 17 Nov 2004 15:14:36 -0800

I'm trying to use Ken Henderson's sp_run_xml_proc to get the XML results of
a stored procedure. (If you're not familiar with it and want to be, google.)
The sproc creates a SQL Server COM object basically to provide client-side
capabilities to T-SQL.

My problem is in trying to call this procedure inside a transaction, and I
have a feeling that this isn't possible. I'm trying to unit test some code,
wherein I start a transaction, call a few sprocs, call another sproc via
sp_run_xml_proc to get some XML to see if things worked, then rollback the
transaction to set the test bed back to a known state.

However, it seems that this creates a block, whereby the transaction creates
locks, then waits on the sp_run_xml_proc's process to complete, which it
can't because it's waiting for the transaction locks to clear. (Obviously,
I'm not an expert at this stuff!!)

Can I just forget about accessing database objects both from within a trx
and from another connection that the trx waits on? (Think I just answered my
own question!). Given that, is there another way to write tests so as to
interrogate the results of sprocs that return XML?

Relevant Pages

  • RE: wish to not receive a response
    ... Their is no SPROC, ... I am mapping my schema to the SQLService.xsd schema. ... > XML to SQL, and it was taking one of the element names in my bad XML and ... > In your case he thinks the stored procedure is 'spTest'. ...
    (microsoft.public.biztalk.general)
  • RE: how to send an xml dataset to a Stored Procedure in mssql
    ... > how to send an xml dataset to a Stored Procedure in mssql ... > I have an xml file that I read into a dataset. ... > that inserts this xml dataset into an sql table via OpenXML. ... > dataset to the SPROC. ...
    (microsoft.public.vsnet.general)
  • Re: Using sp_run_xml_proc inside transaction
    ... > a stored procedure. ... > The sproc creates a SQL Server COM object basically to provide client-side ... > My problem is in trying to call this procedure inside a transaction, ...
    (microsoft.public.sqlserver.programming)
  • how to send an xml dataset to a Stored Procedure in mssql
    ... how to send an xml dataset to a Stored Procedure in mssql ... I have an xml file that I read into a dataset. ... that inserts this xml dataset into an sql table via OpenXML. ... dataset to the SPROC. ...
    (microsoft.public.vsnet.general)
  • creating jobs programmatically causes deadlocks
    ... I'm using the transactional sproc below to create jobs in an automated ... On another server dropping just 10 of the trigger files causes ... The transaction is necessary since the ... DECLARE @ReturnCode INT ...
    (microsoft.public.sqlserver.server)