1

I am currently investigating TST (SQL automation test tool) which will faciliate database regression testing. I've been able to create a basic test case, which is below:

/* 
    Verification of stored proc - FOH_Status
    Returns online availabity status for specified date
*/
/****** Object:  StoredProcedure [dbo].[SQLTest_Status]    Script Date:      11/23/2011 16:56:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.SQLTest_Status
AS
BEGIN
    ---CREATE A TEMP TABLE
    CREATE TABLE #ControlValue (
        ControlMode TINYINT
    )

    INSERT INTO #ControlValue EXEC dbo.Status 17254, 3 ,'2011-11-20   00:00:00' 
    DECLARE @CONTROL TINYINT
    SET @CONTROL = (SELECT TOP 1* FROM #ControlValue)

    EXEC TST.Assert.Equals 'ControlMode enabled for specified date', 1, @CONTRO
END 
GO

I then proceed to link to our QA SQL server, which was successful. Then modified the stored procedure to call a stored procedure on the QA SQL server:

INSERT INTO #ControlValue EXEC [X.X.X.X].databasename.dbo.Status 17254, 3 ,'2011-11-20 00:00:00'

Upon running the test from TST runner will fail with DTC transaction fail message:

OLE DB provider "SQLNCLI10" for linked server "X.X.X.X" returned message "The transaction manager has disabled its support for remote/network transactions.".

Error: 7391, The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "X.X.X.X" was unable to begin a distributed transaction.

I've already confirmed that MS DTC will allow remote connections, inbound and outbound calls on the QA SQL server.

In addition, run the stored procedure in query window and returns the correct result set

EXEC [X.X.X.X].databasename.dbo.Status 17254, 3 ,'2011-11-20 00:00:00'

Any ideas as to how rectify this issue?

Should I be modifying the querying to include transaction, since the returned error includes the 'was unable to begin a distributed transaction'

Community
  • 1
  • 1
Nobody
  • 549
  • 1
  • 10
  • 24

2 Answers2

1

Have you tried every step described in Distributed Transaction Issue for Linked Server in SQL Server 2008?

Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
  • Altered MS DTC settings with the exception of the registry and I'm still getting the same error. Will it be possible to setup a linked server within the stored proc forcing it to make the connection rather than default setup in the local server – Nobody Nov 28 '11 at 11:36
  • Have rebooted the server aslo after reading - http://stackoverflow.com/questions/2931957/linked-servers-sqlncli-problem-no-transaction-is-active. failed to work – Nobody Dec 01 '11 at 16:50
0

I'm interested that you're using a SQL Server unit testing framework. Here at Red Gate we're doing some research into the use of such tools and we hope to release a graphical unit test runner in SSMS fairly soon. See www.sql-test.com . This tool builds on the tSQLt open source framework. Is this one that you've come across?

Might you be interested in helping us by giving us feedback on your unit testing requirements?

David Atkinson
  • 5,759
  • 2
  • 28
  • 35