Sql Source Control v2.2 Issue with MERGE?

EASwiedler

Everything was working fine, until I added the following stored procedure:

USE [RoamData]
GO
/****** Object:  StoredProcedure [Diagnostics].[Run]    Script Date: 09/16/2013 04:27:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Diagnostics].[Run]
(
	@TimeZoneId VARCHAR(50)
)
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @ReportDate DATE
	SET @ReportDate = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
	
	DECLARE @InstallationsToRun TABLE
	(
		InstallationID SMALLINT PRIMARY KEY
	)
	
	INSERT INTO @InstallationsToRun
		SELECT
				InstallationId
			FROM
				[Installation].[Header]
			WHERE
				TimeZoneId LIKE @TimeZoneId
				
	IF @@ROWCOUNT = 0
		RETURN
		
	MERGE
		[Diagnostics].[Queue] AS TargetTable
		USING @InstallationsToRun AS SourceTable
			ON
			(
				TargetTable.InstallationId = SourceTable.InstallationId
					AND TargetTable.ReportDate = @ReportDate
			)
			WHEN NOT MATCHED BY TARGET THEN
				INSERT
				(
					StateId,
					InstallationId,
					ReportDate,
					Result
				)
				VALUES
				(
					1,
					SourceTable.Installationid,
					@ReportDate,
					'Queued'
				)
			WHEN MATCHED THEN
				UPDATE
					SET
						TargetTable.StateId = 1,
						TargetTable.Result = 'Queued';
						
END

Ever sine then, I have been able to neither commit or get latest. The error I get is:

Errors occurred whilst parsing file C:\Users\eas01\AppData\Local\Red Gate\SQL Source Control 2\Transients-TFS\ifbxqk1j.lhq\Stored Procedures\Diagnostics.Run.sql
'[Diagnostics]' at line 1, column 782
'line 1:1226: unexpected token: ["(",<648>,line=1,col=1226] [char=789]'
'line 1:1226: unexpected token: ["(",<648>,line=1,col=1226] [char=789]'
'line 1:1884: unexpected token: ["SET",<864>,line=1,col=1884] [char=1009]'
'line 1:1884: unexpected token: ["SET",<864>,line=1,col=1884] [char=1009]'
'=' at line 1, column 1950
'line 1:2031: expecting "end", found ';''
'line 1:2090: expecting "conversation", found 'GO
''

Ii anyone can determine the source of the error, I would appreciate it. BTW, it runs every day without error.

Thanks,

Ed Swiedler

James B

Thanks for your post. I've just tried this in the latest release and I get a different error- but this may be down to me not having all the other objects that procedure needs.
It looks like some issues relating to MERGE were resolved in v10.2 of the SQL Compare engine, so ideally, you'd need to update to the latest 3.x release of SQL Source Control to see if that resolves it.

EASwiedler

Thanks...this is just what I thought the issue was. I have asked for the upgrade to the latest.

James B

Sure thing- if you're still having trouble, please do post back and we can do some more digging!