SQL Compare "oddity"?

RichardMGreenRichardMGreen Posts: 24 Bronze 1
in SQL Compare
Hi all

I've just tried using SQL Compare to deploy a changed stored procedure that uses synonyms to go across a linked server.

When I tried to use both the "Deploy using SQL Compare" and the "Generate a script" option I get the an error.

This is the code:-
/*
Run this script on:

        DW-PROD.DataWarehouseStaging    -  This database will be modified

to synchronize it with:

        DW-DEV.DataWarehouseStaging

You are recommended to back up your database before running this script

Script created by SQL Compare version 13.8.0.12703 from Red Gate Software Ltd at 25/09/2019 14:15:00

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[usp_MT_Load_wtbl_Staff]'
GO

/*=====================================================================================================================
Gets updated records from Meditech based on the process list
=======================================================================================================================
Initials Date/Time of Change Change Made     
SL 18/01/2016 Initial Coding
RG 17/03/2016 Altered to use OUTER APPLY instead of LEFT JOINs to speed up the query
Also removed restriction on Active staff only to get all staff
SL 01/04/2016 Added fix for invalid specialty codes
SL 01/04/2016 Replaced DMisAbsServices & DMisAbsServiceTapeCode tables with MisSvc_Main, MisSvc_Codes,
MisSpec_Main & MisSpec_Codes tables.  These are on focus and link directly to the 
Registration Specialty & Sub-Specialty dictionaries in Meditech whereas the DMis tables
only showed values from the Sub-Specialty dictionary are therefore did not always show
the correct specialty national codes.
SL 17/08/2016 Made DMisUsers the main table as not all users are set up on meditech as providers
and removed restriction on staff type as we want everyone in here
SL 29/09/2017 Added code to change COLORECTAL specialty of 104 to GENERAL SURGERY (100) as 104 is
not a main specialty code
KW 19/07/2018 Added IsClinician field
RG 26/10/2018 Added link to DMisProviderType to get provider TypeName
Added link to DMisProfiles to get provider UserTypeDescription
Had to increase size of StaffTypeLocalCode from 15 characterss to 20
SL 29/01/2019 Put a LEFT(,8) round StaffNationalCode to restrict to the NHS Data Dictionary field length.
Also, put in code to remove carraige return, line feed and tab characters from the name
fields
KW 12/09/2019 Added joins to EC Staff and Duty roster to calculate IsClinician flag.
The flag in MisPerson_Main isn't always correct - use this as the last option.

=======================================================================================================================
Example call: exec [usp_MT_Load_wtbl_Staff]
=======================================================================================================================*/

ALTER PROCEDURE [dbo].[usp_MT_Load_wtbl_Staff]
AS
BEGIN
TRUNCATE TABLE dbo.wtbl_Staff;

--use CTE to grab the additional specialties
WITH AdditionalSpecialties AS
(
SELECT
PROV.SourceID
,PROV.ProviderID
,AddSpecialtyLocalCode = AddSpec.AbsServiceID
,AddSpecialtyLocalDescription = ADDSPECCODE.Name
,AddSpecialtyNationalCode = CASE LEFT(ADDSPECTC.Code, 3) --Some of the MT specialty mappings 
WHEN '103' THEN
'100' --bring out Treatment Function Codes
WHEN '107' THEN
'100' --so a case statement is needed to
WHEN '328' THEN
'300' --fix these
WHEN '655' THEN
'130'
WHEN '104' THEN
'100'
ELSE
LEFT(ADDSPECTC.Code, 3)
END
,RN = ROW_NUMBER() OVER (PARTITION BY
AddSpec.SourceID
,AddSpec.ProviderID
ORDER BY
AddSpec.AbsServiceID
)
FROM
dbo.DMisProvider AS PROV
INNER JOIN dbo.DMisProviderAddAbsSpecServices AS AddSpec
ON PROV.SourceID = AddSpec.SourceID
AND PROV.ProviderID = AddSpec.ProviderID
AND PROV.ServiceID != AddSpec.AbsServiceID

LEFT JOIN dbo.MisSvc_Main AS ADDSPECCODE
ON PROV.SourceID = ADDSPECCODE.SourceID
AND PROV.ServiceID = ADDSPECCODE.MisSvcID

LEFT JOIN dbo.MisSvc_Codes AS ADDSPECTC
ON PROV.SourceID = ADDSPECTC.SourceID
AND PROV.ServiceID = ADDSPECTC.MisSvcID
AND ADDSPECTC.SubmissionType_MisSubmTypeID = 'NHS CDS'
)
INSERT INTO dbo.wtbl_Staff
(
StaffLocalID
,StaffNationalCode
,StaffFirstName
,StaffLastName
,StaffFullName
,IsClinician
,StaffTypeLocalCode
,StaffTypeLocalDescription
,MainSpecialtyLocalCode
,MainSpecialtyLocalDescription
,MainSpecialtyNationalCode
,AddSpecialtyLocalCodes
,AddSpecialtyLocalDescriptions
,AddSpecialtyNationalCodes
,SYSSourceSystem
)
SELECT
StaffLocalID = Users.UserID
,StaffNationalCode = LEFT(CASE
WHEN COALESCE(PROV.Number, PROV.LicenseNumber) LIKE '%9998' THEN
NULL
ELSE
COALESCE(PROV.Number, PROV.LicenseNumber)
END,8)
,StaffFirstName = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ISNULL(PROV.FirstName
,CASE
WHEN Users.Name LIKE '% %' THEN
LEFT(Users.Name, CHARINDEX(' ', Users.Name, 1) - 1)
END
), CHAR(13), ''), CHAR(10), ''),CHAR(9),' ')))
,StaffLastName = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ISNULL(PROV.LastName
,CASE
WHEN Users.Name LIKE '% %' THEN
REVERSE(LEFT(REVERSE(Users.Name), CHARINDEX(' ', REVERSE(Users.Name), 1)))
END
), CHAR(13), ''), CHAR(10), ''),CHAR(9),' ')))
,StaffFullName = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ISNULL(PROV.Name, Users.Name), CHAR(13), ''), CHAR(10), ''),CHAR(9),' ')))
,IsClinician = CAST
(CASE
WHEN ED_Doctors.Doctor_UnvUserID IS NOT NULL THEN 1
WHEN ED_ML_Clinicians.MiddleLevelProvider_UnvUserID IS NOT NULL THEN 1
WHEN ED_Nurses.Nurse_UnvUserID IS NOT NULL THEN 0
WHEN mpm.IsProvider = 'Y' THEN 1 
ELSE 0
END AS BIT)
,StaffTypeLocalCode = ISNULL(PROV.ProviderTypeID, Users.UserProfileID)
,StaffTypeLocalDescription = ISNULL(dmpt.Name, dmp.Description)
,MainSpecialtyLocalCode = PROV.ServiceID
,MainSpecialtyLocalDescription = MAINSPEC.Name
,MainSpecialtyNationalCode = CASE LEFT(MAINSPECTC.Code, 3) --Some of the MT specialty mappings 
WHEN '103' THEN
'100' --bring out Treatment Function Codes
WHEN '107' THEN
'100' --so a case statement is needed to
WHEN '328' THEN
'300' --fix these
WHEN '655' THEN
'130'
ELSE
LEFT(MAINSPECTC.Code, 3)
END
,AddSpecialtyLocalCodes = ISNULL(add1.AddSpecialty1LocalCode, '') + ';' + ISNULL(add1.AddSpecialty2LocalCode, '') + ';'
+ ISNULL(add1.AddSpecialty3LocalCode, '') + ';' + ISNULL(add1.AddSpecialty4LocalCode, '') + ';'
+ ISNULL(add1.AddSpecialty5LocalCode, '') + ';' + ISNULL(add1.AddSpecialty6LocalCode, '') + ';'
+ ISNULL(add1.AddSpecialty7LocalCode, '') + ';' + ISNULL(add1.AddSpecialty8LocalCode, '') + ';'
+ ISNULL(add1.AddSpecialty9LocalCode, '') + ';' + ISNULL(add1.AddSpecialty10LocalCode, '')
,AddSpecialtyLocalDescriptions = ISNULL(add1.AddSpecialty1LocalDescription, '') + ';' + ISNULL(add1.AddSpecialty2LocalDescription, '') + ';'
+ ISNULL(add1.AddSpecialty3LocalDescription, '') + ';' + ISNULL(add1.AddSpecialty4LocalDescription, '') + ';'
+ ISNULL(add1.AddSpecialty5LocalDescription, '') + ';' + ISNULL(add1.AddSpecialty6LocalDescription, '') + ';'
+ ISNULL(add1.AddSpecialty7LocalDescription, '') + ';' + ISNULL(add1.AddSpecialty8LocalDescription, '') + ';'
+ ISNULL(add1.AddSpecialty9LocalDescription, '') + ';' + ISNULL(add1.AddSpecialty10LocalDescription, '')
,AddSpecialtyNationalCodes = ISNULL(add1.AddSpecialty1NationalCode, '') + ';' + ISNULL(add1.AddSpecialty2NationalCode, '') + ';'
+ ISNULL(add1.AddSpecialty3NationalCode, '') + ';' + ISNULL(add1.AddSpecialty4NationalCode, '') + ';'
+ ISNULL(add1.AddSpecialty5NationalCode, '') + ';' + ISNULL(add1.AddSpecialty6NationalCode, '') + ';'
+ ISNULL(add1.AddSpecialty7NationalCode, '') + ';' + ISNULL(add1.AddSpecialty8NationalCode, '') + ';'
+ ISNULL(add1.AddSpecialty9NationalCode, '') + ';' + ISNULL(add1.AddSpecialty10NationalCode, '')
,SYSSourceSystem = 'Meditech'
FROM
dbo.DMisUsers AS Users
LEFT JOIN dbo.DMisProfiles AS dmp
ON Users.SourceID = dmp.SourceID
AND Users.UserProfileID = dmp.ProfileID

LEFT JOIN dbo.DMisProvider AS PROV
ON PROV.SourceID = Users.SourceID
AND PROV.ProviderID = Users.UserID

LEFT JOIN dbo.DMisProviderType AS dmpt
ON PROV.SourceID = dmpt.SourceID
AND PROV.ProviderTypeID = dmpt.ProviderTypeID

LEFT JOIN dbo.MisSvc_Main AS MAINSPEC
ON PROV.SourceID = MAINSPEC.SourceID
AND PROV.ServiceID = MAINSPEC.MisSvcID

LEFT JOIN dbo.MisSvc_Codes AS MAINSPECTC
ON PROV.SourceID = MAINSPECTC.SourceID
AND PROV.ServiceID = MAINSPECTC.MisSvcID
AND MAINSPECTC.SubmissionType_MisSubmTypeID = 'NHS CDS'

LEFT JOIN dbo.MisPerson_Main AS mpm
ON mpm.SourceID = Users.SourceID
AND mpm.UnvUserID = Users.UserID

-- Joins to EC Staff and Duty Roster :
LEFT JOIN dbo.EdmStaff_Doctors AS ED_Doctors
ON Users.SourceID = ED_Doctors.SourceID
AND Users.UserID = ED_Doctors.Doctor_UnvUserID

LEFT JOIN dbo.EdmStaff_Nurses AS ED_Nurses
ON Users.SourceID = ED_Nurses.SourceID
AND Users.UserID = ED_Nurses.Nurse_UnvUserID

LEFT JOIN dbo.EdmStaff_MidLevelProviders AS ED_ML_Clinicians
ON Users.SourceID = ED_ML_Clinicians.SourceID
AND Users.UserID = ED_ML_Clinicians.MiddleLevelProvider_UnvUserID

-- end of Joins to EC Staff and Duty Roster

OUTER APPLY
(
SELECT
AddSpecialty1LocalCode = MAX( CASE
WHEN [as].RN = 1 THEN
[as].AddSpecialtyLocalCode
END
)
,AddSpecialty1LocalDescription = MAX(CASE
WHEN [as].RN = 1 THEN
[as].AddSpecialtyLocalDescription
END
)
,AddSpecialty1NationalCode = MAX(CASE
WHEN [as].RN = 1 THEN
[as].AddSpecialtyNationalCode
END
)
,AddSpecialty2LocalCode = MAX( CASE
WHEN [as].RN = 2 THEN
[as].AddSpecialtyLocalCode
END
)
,AddSpecialty2LocalDescription = MAX(CASE
WHEN [as].RN = 2 THEN
[as].AddSpecialtyLocalDescription
END
)
,AddSpecialty2NationalCode = MAX(CASE
WHEN [as].RN = 2 THEN
[as].AddSpecialtyNationalCode
END
)
,AddSpecialty3LocalCode = MAX( CASE
WHEN [as].RN = 3 THEN
[as].AddSpecialtyLocalCode
END
)
,AddSpecialty3LocalDescription = MAX(CASE
WHEN [as].RN = 3 THEN
[as].AddSpecialtyLocalDescription
END
)
,AddSpecialty3NationalCode = MAX(CASE
WHEN [as].RN = 3 THEN
[as].AddSpecialtyNationalCode
END
)
,AddSpecialty4LocalCode = MAX( CASE
WHEN [as].RN = 4 THEN
[as].AddSpecialtyLocalCode
END
)
,AddSpecialty4LocalDescription = MAX(CASE
WHEN [as].RN = 4 THEN
[as].AddSpecialtyLocalDescription
END
)
,AddSpecialty4NationalCode = MAX(CASE
WHEN [as].RN = 4 THEN
[as].AddSpecialtyNationalCode
END
)
,AddSpecialty5LocalCode = MAX( CASE
WHEN [as].RN = 5 THEN
[as].AddSpecialtyLocalCode
END
)
,AddSpecialty5LocalDescription = MAX(CASE
WHEN [as].RN = 5 THEN
[as].AddSpecialtyLocalDescription
END
)
,AddSpecialty5NationalCode = MAX(CASE
WHEN [as].RN = 5 THEN
[as].AddSpecialtyNationalCode
END
)
,AddSpecialty6LocalCode = MAX( CASE
WHEN [as].RN = 6 THEN
[as].AddSpecialtyLocalCode
END
)
,AddSpecialty6LocalDescription = MAX(CASE
WHEN [as].RN = 6 THEN
[as].AddSpecialtyLocalDescription
END
)
,AddSpecialty6NationalCode = MAX(CASE
WHEN [as].RN = 6 THEN
[as].AddSpecialtyNationalCode
END
)
,AddSpecialty7LocalCode = MAX( CASE
WHEN [as].RN = 7 THEN
[as].AddSpecialtyLocalCode
END
)
,AddSpecialty7LocalDescription = MAX(CASE
WHEN [as].RN = 7 THEN
[as].AddSpecialtyLocalDescription
END
)
,AddSpecialty7NationalCode = MAX(CASE
WHEN [as].RN = 7 THEN
[as].AddSpecialtyNationalCode
END
)
,AddSpecialty8LocalCode = MAX( CASE
WHEN [as].RN = 8 THEN
[as].AddSpecialtyLocalCode
END
)
,AddSpecialty8LocalDescription = MAX(CASE
WHEN [as].RN = 8 THEN
[as].AddSpecialtyLocalDescription
END
)
,AddSpecialty8NationalCode = MAX(CASE
WHEN [as].RN = 8 THEN
[as].AddSpecialtyNationalCode
END
)
,AddSpecialty9LocalCode = MAX( CASE
WHEN [as].RN = 9 THEN
[as].AddSpecialtyLocalCode
END
)
,AddSpecialty9LocalDescription = MAX(CASE
WHEN [as].RN = 9 THEN
[as].AddSpecialtyLocalDescription
END
)
,AddSpecialty9NationalCode = MAX(CASE
WHEN [as].RN = 9 THEN
[as].AddSpecialtyNationalCode
END
)
,AddSpecialty10LocalCode = MAX(CASE
WHEN [as].RN = 10 THEN
[as].AddSpecialtyLocalCode
END
)
,AddSpecialty10LocalDescription = MAX( CASE
WHEN [as].RN = 10 THEN
[as].AddSpecialtyLocalDescription
END
)
,AddSpecialty10NationalCode = MAX( CASE
WHEN [as].RN = 10 THEN
[as].AddSpecialtyNationalCode
END
)
FROM
AdditionalSpecialties AS [as]
WHERE
[as].SourceID = PROV.SourceID
AND [as].ProviderID = PROV.ProviderID
GROUP BY
[as].SourceID
,[as].ProviderID
) AS add1;
END;
GO
@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
@ERROR <> 0 SET NOEXEC ON
GO
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
END
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
@TRANCOUNT > 0 ROLLBACK TRANSACTION
PRINT 'The database update failed'
END
GO

If I run the above code as-is, I get the following error:-
Altering [dbo].[usp_MT_Load_wtbl_Staff]
OLE DB provider "SQLNCLI11" for linked server "MEDITECHDR01-M1" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure usp_MT_Load_wtbl_Staff, Line 39 [Batch Start Line 28]
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "MEDITECHDR01-M1" was unable to begin a distributed transaction.
The database update failed


If I take out the SQL Compare bits and just run it as a standard ALTER PROCEDURE on the relevant server, it all works fine.

Is this a small bug or am I missing something?

Regards

Richard
Tagged:
· Share on Twitter

Answers

Sign In or Register to comment.