Sql Source Control v2.2 Issue with MERGE?

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace

Sql Source Control v2.2 Issue with MERGE?

Postby EASwiedler » Mon Sep 16, 2013 12:30 pm

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

Code: Select all
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
EASwiedler
 
Posts: 2
Joined: Mon Sep 16, 2013 12:24 pm

Postby james.billings » Wed Sep 18, 2013 7:18 pm

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby EASwiedler » Wed Sep 18, 2013 8:04 pm

Thanks...this is just what I thought the issue was. I have asked for the upgrade to the latest.
EASwiedler
 
Posts: 2
Joined: Mon Sep 16, 2013 12:24 pm

Postby james.billings » Wed Sep 18, 2013 8:11 pm

Sure thing- if you're still having trouble, please do post back and we can do some more digging!
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.


Return to SQL Source Control 2

Who is online

Users browsing this forum: No registered users and 0 guests