When I run a stored proc in QA, it runs successfully, however when I run the
code against BizTalk I get the following error:

The adapter failed to transmit message going to send port "SQL://database/".
It will be retransmitted after the retry interval specified for this Send
Port. Details:"HRESULT="0x80004005" Description="Transaction count after
EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is
missing. Previous count = 1, current count = 0."

Is there a tool that I can find out why I am getting this error, and why
would I only be getting this with BizTalk, why isn't QA coming back with the
same error?

Thanks

Show quote Hide quote From QA start a transaction before running the procedure, and you should see
the same thing.

Does the stored procedure issue a ROLLBACK statement?

David

Yes it does
{a bunch of code here}...

SELECT @PrescriberId AS PrescriberId, @ResponseMSG AS Response --for xml
raw--, xmldata

COMMIT TRANSACTION

PROBLEM:

IF(@intErrorCode=1)

BEGIN

SELECT @PrescriberId AS PrescriberId, @ResponseMSG AS Response --for xml
raw--, xmldata

ROLLBACK TRANSACTION

END

Show quoteHide quote

If you need transaction handling in your procedure and also need to be
called from client code that uses transactions, you can use the following
pattern to preserve the transaction state of the client transaction.

create procedure foo
as
begin transaction
save transaction local

  //do work here

commit transaction
return 0
error:
  rollback transaction local
  commit transaction
  return 1


David

Show quoteHide quote

So as a beginner, in my code, where does return 1 go?

{a bunch of code here}...
SELECT @PrescriberId AS PrescriberId, @ResponseMSG AS Response
COMMIT TRANSACTION
RETURN 0

PROBLEM:
IF(@intErrorCode=1)
BEGIN
SELECT @PrescriberId AS PrescriberId, @ResponseMSG AS Response
ROLLBACK TRANSACTION
RETURN 1 --HERE?
END

Show quoteHide quote

The critical thing is to never issue

ROLLBACK TRANSACTION

This will rollback the client's transaction and cause the error you saw.


At the top of the procedure you need

BEGIN TRANSACTION
SAVE TRANSACTION local

Then instead of rolling back completely, issue

ROLLBACK TRANSACTION local

to undo your partial work, and then

COMMIT TRANSACTION

to close out the transaction you started at the beginning.

RETURN 1

Just exits the procedure with a failure code.


If you post the complete procedure, I'll mark it up for you.

David

Show quoteHide quote

ALTER PROCEDURE dbo.uspPrescriberInterfaceUpdate
(
@LastName varchar(30),
@FirstName varchar(30),
@Address1 varchar(60),
@Address2 varchar(60),
@City varchar(20),
@StateID smallint=0,
@StateDesc varchar(50),
@ZIPCode varchar(10),
@Email varchar(80),
@SpecialtyID int=0,
@SpecialtyDesc varchar(50),
@DEANumber varchar(10),
@DEAExpirationDate datetime,
@StatusID smallint=1,
@UpdateNTID varchar(50),
@UpdateDate datetime,
@InterfacePrescriberId varchar(25),
@AllPhoneInfo varchar(4000),  -- PhoneTypeDESC~PhoneNumber[(123)
123-1234]~Extension
@AllFacilityInfo
rchar(4000),   --FacilityId~FacilityName~PrescriberFacilityPriveledge
@AllStateLicenseInfo
varchar(4000), --StateLicenseStateDesc~LicenseNumber~LicenseExpirationDate
@StatusDesc varchar (20)
)

AS
BEGIN TRANSACTION
--print @@TRANCOUNT
SET NOCOUNT ON
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ResponseMSG Varchar(400)
DECLARE @intErrorCode INT
SET @intErrorCode=0

SET @UpdateDate=GetDate()

DECLARE @PrescriberId INT
SET @PrescriberId = 0

--GET STATE ID
SELECT @StateId = StateId FROM tblState WHERE Code = @StateDesc OR Name =
@StateDesc

--GET STATUS ID
SELECT @StatusId = StatusId FROM tblStatus WHERE Description = @StatusDesc

--GET SPECIALTY DEGREE ID
SELECT @SpecialtyId = SpecialtyId FROM tblPrescriberSpecialty WHERE
SpecialtyName = @SpecialtyDesc

--FIND EXISTING PRESCRIBER ON INTERFACE PRESCRIBE ID AND FIRST AND LAST NAME
SELECT @PrescriberId = PrescriberId FROM tblPrescriber WHERE
InterfacePrescriberId = @InterfacePrescriberId AND LastName = @LastName AND
FirstName = @FirstName


--VERIFY VALID ZIP
CODE======================================================================
DECLARE @ValidZip INT
IF @ZipCode LIKE '[0-9][0-9][0-9][0-9][0-9]' OR @ZipCode LIKE
'[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR @ZipCode LIKE
'[A-Z][0-9][A-Z] [0-9][A-Z][0-9]'
      BEGIN
            SET @ValidZip = 1
            --SELECT @@TRANCOUNT
      END
ELSE --NOT VALID ZIP CODE
      BEGIN
            SET @ZipCode = '' --SET ZIP CODE TO EMPTY STRING
            SET @ValidZip = 0
            --SELECT @@TRANCOUNT
      END
--===========================================================================================




--VERIFY VALID DEA
NUMBER===================================================================

DECLARE @ValidDEA INT

IF LEN(@DEANumber) > 0
      BEGIN
            DECLARE @Sum135 DECIMAL
            DECLARE @Sum246 DECIMAL

            --VERIFY FIRST LETTER
            IF SUBSTRING(@DEANumber, 1,1) LIKE 'A' OR SUBSTRING(@DEANumber,
1,1) LIKE 'B' OR SUBSTRING(@DEANumber, 1,1) LIKE 'M'
                  BEGIN
                        --VERIFY SECOND LETTER
                        IF SUBSTRING(@DEANumber, 2, 1) LIKE '[A-Z]'
                              BEGIN
                                    --VERIFY REMAINING DIGITS 1, 3, AND 5
ARE DIGITS
                                    IF SUBSTRING(@DEANumber, 3,1) LIKE
'[0-9]' AND SUBSTRING(@DEANumber, 5,1) LIKE '[0-9]' AND
SUBSTRING(@DEANumber, 7,1) LIKE '[0-9]'
                                          BEGIN
                                                --SET SUM OF 1, 3, AND 5
                                                SET @SUM135 =
(CONVERT(decimal, SUBSTRING(@DEANumber, 3, 1))) + (CONVERT(decimal,
SUBSTRING(@DEANumber, 5, 1)))+(CONVERT(decimal, SUBSTRING(@DEANumber, 7,
1)))

                                                --VERIFY REMAINING DIGITS 2,
4, AND 6 ARE DIGITS
                                                IF SUBSTRING(@DEANumber,
4,1) LIKE '[0-9]' AND SUBSTRING(@DEANumber, 6,1) LIKE '[0-9]' AND
SUBSTRING(@DEANumber, 8,1) LIKE '[0-9]'
                                                      BEGIN
                                                            --SET SUM OF 2,
4, AND 6
                                                            SET @SUM246 =
(CONVERT(decimal, SUBSTRING(@DEANumber, 4, 1))) + (CONVERT(decimal,
SUBSTRING(@DEANumber, 6, 1)))+(CONVERT(decimal, SUBSTRING(@DEANumber, 8,
1)))

                                                            --VERIFY CHECK
DIGIT
                                                            IF
(SUBSTRING(CONVERT(varchar(20), ((@SUM135 + (@SUM246) * 2))),
LEN(CONVERT(varchar(20), ((@SUM135 + (@SUM246) * 2)))),1)) =
SUBSTRING(@DEANumber, LEN(@DEANumber), 1)
                                                                  BEGIN
                                                                        SET
@ValidDEA = 1
                                                                  END
                                                            ELSE --INVALID
CHECK DIGIT
                                                                  SET
@ValidDEA = 0
                                                      END
                                                ELSE --INVALID DIGITS 2, 4,
AND 6
                                                      SET @ValidDEA = 0
                                          END
                                    ELSE --INVALID DIGITS 1, 3, AND 5
                                          SET @ValidDEA = 0
                              END
                        ELSE --INVALID SECOND LETTER
                              SET @ValidDEA = 0
                  END
            ELSE --INVALID FIRST LETTER
                  SET @ValidDEA = 0
      END
ELSE
      BEGIN --DID NOT PROVIDE DEA THEREFORE IT IS VALID
            SET @ValidDEA = 1
            SET @DEAExpirationDate = ''
      END

IF @ValidDEA = 0 --SET DEA NUMBER AND EXPIRATION DATE TO EMPTY STRINGS IF
NOT A VALID DEA
      BEGIN
            SET @DEANumber = ''
            SET @DEAExpirationDate = NULL
      END
--============================================================================================


--SELECT @@TRANCOUNT

--VERIFY VALID PRESCRIBER AND STATE WAS FOUND
IF @PrescriberId > 0 AND @StateId > 0 AND @SpecialtyId > 0
      BEGIN
             UPDATE dbo.tblPrescriber
             SET
                  LastName = @LastName,
                  FirstName = @FirstName,
                  Address1 = @Address1,
                  Address2 = @Address2,
                  City = @City,
                  StateID = @StateID,
                  ZIPCode = @ZIPCode,
                  Email = @Email,
                  SpecialtyID = @SpecialtyID,
                  DEANumber = @DEANumber,
                  DEAExpirationDate = @DEAExpirationDate,
                  StatusID = @StatusID,
                  UpdateNTID = @UpdateNTID,
                  UpdateDate = @UpdateDate,
                  InterfacePrescriberId = @InterfacePrescriberId
            WHERE
                  PrescriberId = @PrescriberId


            IF @ValidZip = 1 AND @ValidDEA = 1
                  SELECT @ResponseMSG =  'Updated Prescriber successfully. '
            ELSE IF @ValidZip = 0 AND @ValidDEA = 1
                  BEGIN
                    SELECT @ResponseMSG =  'Invalid Zip Code. Zip Code was
not updated.Updated Prescriber sucessfully. '
                    SET @intErrorCode=1
                    GOTO PROBLEM
                  END
            ELSE IF @ValidZip = 1 AND @ValidDEA = 0
                BEGIN
                     SELECT @ResponseMSG =  'Invalid DEA Number. DEA Number
was not updated. Updated Prescriber sucessfully. '
                     SET @intErrorCode=1
                     GOTO PROBLEM
                  END
            ELSE IF @ValidZip = 0 AND @ValidDEA = 0
                BEGIN
                      SELECT @ResponseMSG =  'Invalid Zip Code and DEA
Number. Zip Code and DEA Number were not updated. Updated Prescriber
sucessfully. '
                      SET @intErrorCode=1
                      GOTO PROBLEM


      END
END
ELSE IF @PrescriberId > 0 AND @StateId > 0
      BEGIN
             UPDATE dbo.tblPrescriber
             SET
                  LastName = @LastName,
                  FirstName = @FirstName,
                  Address1 = @Address1,
                  Address2 = @Address2,
                  City = @City,
                  StateID = @StateID,
                  ZIPCode = @ZIPCode,
                  Email = @Email,
                  --SpecialtyID = @SpecialtyID,
                  DEANumber = @DEANumber,
                  DEAExpirationDate = @DEAExpirationDate,
                  StatusID = @StatusID,
                  UpdateNTID = @UpdateNTID,
                  UpdateDate = @UpdateDate,
                  InterfacePrescriberId = @InterfacePrescriberId
            WHERE
                  PrescriberId = @PrescriberId


            IF @ValidZip = 1 AND @ValidDEA = 1
                  BEGIN
                      SELECT @ResponseMSG = 'Invalid Specialty Degree.
Prescriber Updated, specialty degree not updated. '
                      SET @intErrorCode=1
                      GOTO PROBLEM
                  END
            ELSE IF @ValidZip = 0 AND @ValidDEA = 1
                BEGIN
                     SELECT @ResponseMSG = 'Invalid Zip Code and Specialty
Degree. Zip Code and Specialty Degree were not updated.Updated Prescriber
sucessfully. '
                     SET @intErrorCode=1
                     GOTO PROBLEM
                  END
            ELSE IF @ValidZip = 1 AND @ValidDEA = 0
                  BEGIN
                     SELECT @ResponseMSG =  'Invalid DEA Number and
Specialty Degree. DEA Number and Specialty Degree were not updated. Updated
Prescriber sucessfully. '
                     SET @intErrorCode=1
                     GOTO PROBLEM
                  END
            ELSE IF @ValidZip = 0 AND @ValidDEA = 0
                  BEGIN
                      SELECT @ResponseMSG =  'Invalid Zip Code, DEA Number
and Specialty Degree. Zip Code, DEA Number and Specialty Degree were not
updated. Updated Prescriber sucessfully. '
                      SET @intErrorCode=1
                      GOTO PROBLEM
                  END
END
ELSE if @StateId <=0 OR @StateId IS NULL
      BEGIN
            --SELECT @@TRANCOUNT
            SELECT  -1 AS PrescriberId,   'Valid State was not provided.
Unable to Update Prescriber. ' AS Description --FOR XML RAW,  XMLDATA
            SET @intErrorCode=1
            GOTO PROBLEM
            --RETURN
      END
ELSE
      BEGIN
            --SELECT @@TRANCOUNT
            SELECT  -1 AS PrescriberId,   'Could Not Find the Prescriber to
update. ' AS Description --FOR XML RAW,  XMLDATA
            SET @intErrorCode=1
            GOTO PROBLEM
            --RETURN
      END
--======================================================================================================================================================
--FACILITIES
--======================================================================================================================================================
DECLARE @OUTERINDEXFacility INT
DECLARE @OUTERSLICEFacility varchar(4000)
SET @OUTERINDEXFacility = 1
IF @AllFacilityInfo IS NULL BEGIN
      --SELECT @@TRANCOUNT
       SELECT -1 AS PrescriberId, 'No Valid Facility was supplied. Unable to
update prescriber. ' as DESCRIPTION --FOR XML RAW, XMLDATA
            SET @intErrorCode=1
            GOTO PROBLEM
            --RETURN
END
WHILE @OUTERINDEXFacility != 0
BEGIN

    DECLARE @InnerFacilityInfo varchar(4000)
    SET @OUTERINDEXFacility = CHARINDEX('|', @AllFacilityInfo)
    IF @OUTERINDEXFacility !=0
        SELECT @OUTERSLICEFacility =
LEFT(@AllFacilityInfo,@OUTERINDEXFacility - 1)
    ELSE
        SELECT @OUTERSLICEFacility = @AllFacilityInfo

   SELECT @InnerFacilityInfo = @OUTERSLICEFacility

   DECLARE @FacilityId int
   DECLARE @FacilityName varchar(50)
   DECLARE @PrescriberFacilityPriveledgeDesc varchar (50)
   DECLARE @MedicalDirector bit
   SET @MedicalDirector =0
   DECLARE @NonFormulary bit
   SET @NonFormulary=0
   DECLARE @Normal bit
   SET @Normal=0

    DECLARE @INDEXFacility INT
    DECLARE @SLICEFacility nvarchar(4000)
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    --     ERO FIRST TIME IN LOOP
    SET @INDEXFacility = 1
    -- following line added 10/06/04 as null
    --      values cause issues
    IF @InnerFacilityInfo IS NULL
      SET @INDEXFacility = 0
    WHILE @INDEXFacility !=0


        BEGIN
            -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
            SET @INDEXFacility = CHARINDEX('~',@InnerFacilityInfo)
            -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
            IF @INDEXFacility !=0
                  SELECT @SLICEFacility =
LEFT(@InnerFacilityInfo,@INDEXFacility - 1)
            ELSE
                  SELECT @SLICEFacility = @InnerFacilityInfo
            -- GET FacilityId
            SELECT @FacilityId = @SLICEFacility
            SELECT @InnerFacilityInfo =
RIGHT(@InnerFacilityInfo,LEN(@InnerFacilityInfo) - @INDEXFacility)

            SET @INDEXFacility = CHARINDEX('~',@InnerFacilityInfo)
            IF @INDEXFacility !=0
                  SELECT @SLICEFacility =
LEFT(@InnerFacilityInfo,@INDEXFacility - 1)
            ELSE
                  SELECT @SLICEFacility = @InnerFacilityInfo
            --GET Facility Name
            SELECT @FacilityName = @SLICEFacility
            SELECT @InnerFacilityInfo =
RIGHT(@InnerFacilityInfo,LEN(@InnerFacilityInfo) - @INDEXFacility)


            SET @INDEXFacility = CHARINDEX('~',@InnerFacilityInfo)
            IF @INDEXFacility !=0
                  SELECT @SLICEFacility =
LEFT(@InnerFacilityInfo,@INDEXFacility - 1)
            ELSE
                  SELECT @SLICEFacility = @InnerFacilityInfo
            --GET Facility Name
            SELECT @PrescriberFacilityPriveledgeDesc = @SLICEFacility
            SELECT @InnerFacilityInfo =
RIGHT(@InnerFacilityInfo,LEN(@InnerFacilityInfo) - @INDEXFacility)


            --SELECT @InnerFacilityInfo = RIGHT(@InnerFacilityInfo,
LEN(@InnerFacilityInfo) - @INDEXFacility)
            --SELECT @InnerPhoneInfo =
RIGHT(@InnerPhoneInfo,LEN(@InnerPhoneInfo) - @INDEX)
            --SET PRESCRIBER FACILITY PRIVELEDGE


            IF @PrescriberFacilityPriveledgeDesc = 'MedicalDirector'
            BEGIN
                  SET @MedicalDirector = 1
                  SET @Normal = 1
            END
            ELSE IF @PrescriberFacilityPriveledgeDesc = 'NonFormulary'
            BEGIN
                  SET @NonFormulary = 1
            END
            ELSE IF @PrescriberFacilityPriveledgeDesc =
'MedicalDirectorNonFormulary'
            BEGIN
                  SET @MedicalDirector = 1
                  SET @NonFormulary = 1
            END
            ELSE
            BEGIN
                  SET @Normal = 1
            END



      --VERIFY VALID FACILITY
      IF (SELECT COUNT (FacilityId) FROM tblFacility WHERE
tblFacility.FacilityId = @FacilityId AND tblFacility.Name = @FacilityName) >
0
      BEGIN
            IF(SELECT COUNT(FacilityPrescriberXRefId) FROM
tblFacilityPrescriberXRef WHERE FacilityId = @FacilityId AND PrescriberId =
@PrescriberId) > 0
                  BEGIN

                        DECLARE @FacilityPrescriberXRefId INT
                        SELECT @FacilityPrescriberXRefId =
FacilityPrescriberXRefId FROM tblFacilityPrescriberXRef WHERE PrescriberId =
@PrescriberId AND FacilityId = @FacilityId

                        --UPDATE STATEMENT
                         UPDATE dbo.tblFacilityPrescriberXref
                         SET
                               FacilityId = @FacilityId,
                               PrescriberId = @PrescriberId,
                               MedicalDirector = @MedicalDirector,
                               NonFormulary = @NonFormulary,
                               Normal = @Normal,
                               UpdateNTID = @UpdateNTID,
                               UpdateDate = @UpdateDate
                         WHERE
                               FacilityPrescriberXrefId =
@FacilityPrescriberXrefId
                  END
            ELSE
                  BEGIN
                        INSERT INTO dbo.tblFacilityPrescriberXref
                         (
                               FacilityId,
                               PrescriberId,
                               MedicalDirector,
                               NonFormulary,

                               Normal,
                               UpdateNTID,
                               UpdateDate
                         )
                         VALUES
                         (
                               @FacilityId,
                               @PrescriberId,
                               @MedicalDirector,
                               @NonFormulary,
                               @Normal,
                               @UpdateNTID,
                               @UpdateDate
                         )
                  END


      END
      ELSE
            SELECT  @ResponseMSG = @ResponseMSG +  'Invalid Facility. Unable
to associate prescriber with facility. '
      END

      SELECT @AllFacilityInfo =
RIGHT(@AllFacilityInfo,LEN(@AllFacilityInfo) - @OUTERINDEXFacility)
      IF LEN(@AllFacilityInfo) = 0 BREAK


END
--===============================================================================================================================
--PHONE NUMBERS
--===============================================================================================================================
DECLARE @OUTERINDEX INT
DECLARE @OUTERSLICE varchar(4000)
SET @OUTERINDEX = 1
IF @AllPhoneInfo IS NULL
BEGIN SET @OUTERINDEX = 0 END

WHILE @OUTERINDEX != 0
BEGIN

    DECLARE @InnerPhoneInfo varchar(4000)
    SET @OUTERINDEX = CHARINDEX('|', @AllPhoneInfo)
    IF @OUTERINDEX !=0
        SELECT @OUTERSLICE = LEFT(@AllPhoneInfo,@OUTERINDEX - 1)
    ELSE
        SELECT @OUTERSLICE = @AllPhoneInfo

      SELECT @InnerPhoneInfo = @OUTERSLICE

    DECLARE @PhoneTypeId smallint
    SET @PhoneTypeId=0
    DECLARE @PhoneTypeDesc varchar (50)
    DECLARE @PhoneNumber varchar(25)
    DECLARE @Extension varchar(10)

    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    --     ERO FIRST TIME IN LOOP
    SET @INDEX = 1
    -- following line added 10/06/04 as null
    --      values cause issues
    IF @InnerPhoneInfo IS NULL
      SET @InnerPhoneInfo = 0
    WHILE @INDEX !=0


        BEGIN
            -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
            SET @INDEX = CHARINDEX('~',@InnerPhoneInfo)
            -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
            IF @INDEX !=0
                  SELECT @SLICE = LEFT(@InnerPhoneInfo,@INDEX - 1)
            ELSE
                  SELECT @SLICE = @InnerPhoneInfo
            -- GET PHONE NUMBER
            SELECT @PhoneNumber = @SLICE
            SELECT @InnerPhoneInfo =
RIGHT(@InnerPhoneInfo,LEN(@InnerPhoneInfo) - @INDEX)

            SET @INDEX = CHARINDEX('~',@InnerPhoneInfo)
            IF @INDEX !=0
                  SELECT @SLICE = LEFT(@InnerPhoneInfo,@INDEX - 1)
            ELSE
                  SELECT @SLICE = @InnerPhoneInfo
            --GET EXTENSION
            SELECT @Extension = @SLICE
            SELECT @InnerPhoneInfo =
RIGHT(@InnerPhoneInfo,LEN(@InnerPhoneInfo) - @INDEX)
            SET @INDEX = CHARINDEX('~',@InnerPhoneInfo)
            IF @INDEX !=0
                  SELECT @SLICE = LEFT(@InnerPhoneInfo,@INDEX - 1)
            ELSE
                  SELECT @SLICE = @InnerPhoneInfo
            --GET PHONE TYPE
            SELECT @PhoneTypeDesc = @SLICE
            SELECT @InnerPhoneInfo =
RIGHT(@InnerPhoneInfo,LEN(@InnerPhoneInfo) - @INDEX)

            --IF LEN(@InnerPhoneInfo) = 0 BREAK

            SELECT @PhoneTypeId = PhoneTypeId from tblPhoneType where
Description = @PhoneTypeDesc

            --VERIFY PHONE TYPE ID IS VALID
            IF @PhoneTypeId > 0
                  BEGIN
                        --CHECK TO SEE IF PHONE TYPE ALREADY EXISTS FOR
PRESCRIBER
                        IF (SELECT COUNT(PrescriberPhoneId) FROM
tblPrescriberPhone WHERE PrescriberId = @PrescriberId AND PhoneTypeId =
@PhoneTypeId) > 0
                              BEGIN

                                    DECLARE @PrescriberPhoneId int
                                    SELECT @PrescriberPhoneId =
PrescriberPhoneId FROM tblPrescriberPhone WHERE PrescriberId = @PrescriberId
AND PhoneTypeId = @PhoneTypeId

                                    --UPDATE STATEMENT
                                    UPDATE dbo.tblPrescriberPhone
                                    SET
                                          PrescriberID = @PrescriberID,
                                          PhoneTypeId = @PhoneTypeId,
                                          PhoneNumber = @PhoneNumber,
                                          Extension = @Extension,
                                          UpdateNTID = @UpdateNTID,
                                          UpdateDate = @UpdateDate
                                    WHERE
                                          PrescriberPhoneId =
@PrescriberPhoneId

                              END --PHONE TYPE EXISTS FOR PRESCRIBER

                        ELSE --PHONE TYPE DOES NOT ALREADY EXIST FOR
PRESCRIBER
                              BEGIN
                                     INSERT INTO dbo.tblPrescriberPhone
                                       (
                                           PrescriberID,
                                           PhoneTypeId,
                                           PhoneNumber,
                                           Extension,
                                           UpdateNTID,
                                           UpdateDate
                                    )
                                    VALUES
                                    (
                                          @PrescriberID,
                                          @PhoneTypeId,
                                          @PhoneNumber,
                                          @Extension,
                                          @UpdateNTID,
                                          @UpdateDate
                                    )
                              END --PHONE TYPE DOES NOT ALREADY EXIST FOR
PRESCRIBER



            END
      ELSE
            SELECT  @ResponseMSG = @ResponseMSG +  'Invald Phone Type.
Unable to insert/update prescriber phone information. '

      END

      SELECT @AllPhoneInfo = RIGHT(@AllPhoneInfo,LEN(@AllPhoneInfo) -
@OUTERINDEX)
      IF LEN(@AllPhoneInfo) = 0 BREAK

END

--===================================================================================================================
--STATE LICENSES
--===================================================================================================================

DECLARE @OUTERINDEXSL INT
DECLARE @OUTERSLICESL varchar(4000)
SET @OUTERINDEXSL = 1
IF @AllStateLicenseInfo IS NULL BEGIN SET @OUTERINDEXSL = 0 END
WHILE @OUTERINDEXSL != 0
BEGIN

    DECLARE @InnerSLInfo varchar(4000)
    SET @OUTERINDEXSL = CHARINDEX('|', @AllStateLicenseInfo)
    IF @OUTERINDEXSL !=0
        SELECT @OUTERSLICESL = LEFT(@AllStateLicenseInfo,@OUTERINDEXSL - 1)
    ELSE
        SELECT @OUTERSLICESL = @AllStateLicenseInfo

   SELECT @InnerSLInfo = @OUTERSLICESL

   DECLARE @StateLicenseStateId INT
   SET @StateLicenseStateId = 0
   DECLARE @StateLicenseStateDesc VARCHAR(50)
   DECLARE @StateLicense VARCHAR(15)
   DECLARE @ExpirationDate DATETIME

    DECLARE @INDEXSL INT
    DECLARE @SLICESL nvarchar(4000)
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    --     ERO FIRST TIME IN LOOP
    SET @INDEXSL = 1
    -- following line added 10/06/04 as null
    --      values cause issues
    IF @InnerSLInfo IS NULL
      SET @InnerSLInfo = 0
    WHILE @INDEXSL !=0


        BEGIN
            -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
            SET @INDEXSL = CHARINDEX('~',@InnerSLInfo)
            -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
            IF @INDEXSL !=0
                  SELECT @SLICESL = LEFT(@InnerSLInfo,@INDEXSL - 1)
            ELSE
                  SELECT @SLICESL = @InnerSLInfo
            -- GET STATE
            SELECT @StateLicenseStateDesc = @SLICESL
            SELECT @InnerSLInfo = RIGHT(@InnerSLInfo,LEN(@InnerSLInfo) -
@INDEXSL)

            SET @INDEXSL = CHARINDEX('~',@InnerSLInfo)
            IF @INDEXSL !=0
                  SELECT @SLICESL = LEFT(@InnerSLInfo,@INDEXSL - 1)
            ELSE
                  SELECT @SLICESL = @InnerSLInfo
            --GET STATE LICENSE NUMBER
            SELECT @StateLicense = @SLICESL
            SELECT @InnerSLInfo = RIGHT(@InnerSLInfo,LEN(@InnerSLInfo) -
@INDEXSL)


            SET @INDEXSL = CHARINDEX('~',@InnerSLInfo)
            IF @INDEXSL !=0
                  SELECT @SLICESL = LEFT(@InnerSLInfo,@INDEXSL - 1)
            ELSE
                  SELECT @SLICESL = @InnerSLInfo
            --GET STATE LICENSE EXPIRATION
            SELECT @ExpirationDate = @SLICESL
            SELECT @InnerSLInfo = RIGHT(@InnerSLInfo,LEN(@InnerSLInfo) -
@INDEXSL)

            SELECT @StateLicenseStateId = StateId FROM tblState WHERE Name =
@StateLicenseStateDesc OR Code = @StateLicenseStateDesc



            --VERIFY THE STATE IS VALID
            IF @StateLicenseStateId > 0
                  BEGIN
                        --CHECK TO SEE IF STATE LICENSE EXISTS FOR THE
PRESCRIBER
                        IF (SELECT COUNT(PrescriberStateLicenseId) FROM
tblPrescriberStateLicense WHERE PrescriberId = @PrescriberId AND StateId =
@StateLicenseStateId) > 0
                              BEGIN

                                    DECLARE @PrescriberStateLicenseId INT
                                    SELECT @PrescriberStateLicenseId =
PrescriberStateLicenseId FROM tblPrescriberStateLicense WHERE PrescriberId =
@PrescriberId AND StateId = @StateLicenseStateId

                                     --UPDATE STATEMENT
                                    UPDATE dbo.tblPrescriberStateLicense
                                    SET
                                          PrescriberId = @PrescriberId,
                                          StateId = @StateLicenseStateId,
                                          StateLicense = @StateLicense,
                                          ExpirationDate = @ExpirationDate,
                                          UpdateNTID = @UpdateNTID,
                                          UpdateDate = @UpdateDate
                                    WHERE
                                          PrescriberStateLicenseId =
@PrescriberStateLicenseId
                              END
                        ELSE --STATE LICENSE DOES NOT EXIST FOR PRESCRIBER
                              BEGIN
                                    INSERT INTO
dbo.tblPrescriberStateLicense
                                    (
                                          PrescriberId,
                                          StateId,
                                          StateLicense,
                                          ExpirationDate,
                                          UpdateNTID,
                                          UpdateDate
                                    )
                                    VALUES
                                    (
                                          @PrescriberId,
                                          @StateLicenseStateId,
                                          @StateLicense,
                                          @ExpirationDate,
                                          @UpdateNTID,
                                          @UpdateDate
                                    )
                              END --END STATE LICENSE DOES NOT EXIST FOR
PRESCRIBER




                  END --END VALID STATE
            ELSE --NOT A VALID STATE
                  SELECT  @ResponseMSG = @ResponseMSG +  'Invalid State.
Unable to add/update license. '
            END

      SELECT @AllStateLicenseInfo =
RIGHT(@AllStateLicenseInfo,LEN(@AllStateLicenseInfo) - @OUTERINDEXSL)
      IF LEN(@AllStateLicenseInfo) = 0
            SET @intErrorCode=1
            GOTO PROBLEM
END
--print 'Trancount:' + cast(@@TRANCOUNT as varchar)
SELECT @@TRANCOUNT

IF @ValidZip = 0 AND @ValidDEA = 1
      BEGIN
            SET @intErrorCode=1
            GOTO PROBLEM
      END
ELSE IF @ValidZip = 1 AND @ValidDEA = 0
      BEGIN
            SET @intErrorCode=1
            GOTO PROBLEM
      END
ELSE IF @ValidZip = 0 AND @ValidDEA = 0
      BEGIN
            SET @intErrorCode=1
            GOTO PROBLEM
      END

SELECT @PrescriberId AS PrescriberId, @ResponseMSG AS Response --for xml
raw--, xmldata

--print 'Committed'
--SELECT @@TRANCOUNT
COMMIT TRANSACTION


--IF @@TRANCOUNT>0 COMMIT
PROBLEM:
IF(@intErrorCode=1)
BEGIN
SELECT @PrescriberId AS PrescriberId, @ResponseMSG AS Response --for xml
raw--, xmldata
ROLLBACK TRANSACTION
END
GO

After applying the changes, this is the error I am getting:
Event Type: Warning
Event Source: BizTalk Server 2004
Event Category: BizTalk Server 2004
Event ID: 5743
Date:  8/18/2006
Time:  4:41:27 PM
User:  N/A
Computer: SPP002
Description:
The adapter failed to transmit message going to send port "SQL://database".
It will be retransmitted after the retry interval specified for this Send
Port. Details:"HRESULT="0x80040e14" Description="Cannot use SAVE TRANSACTION
within a distributed transaction."
".

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

Show quoteHide quote

User defined functions and booleans?
tough request
How to combine several long strings and then insert them to a text field
Parse Text Problem
BCP .dbf file useing t-sql