Red Gate forums :: View topic - Sql Source Control v2.2 Issue with MERGE?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 2
SQL Source Control 2 forum

Sql Source Control v2.2 Issue with MERGE?

Search in SQL Source Control 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
EASwiedler



Joined: 16 Sep 2013
Posts: 2

PostPosted: Mon Sep 16, 2013 12:30 pm    Post subject: Sql Source Control v2.2 Issue with MERGE? Reply with quote

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

Code:
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
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1116
Location: My desk.

PostPosted: Wed Sep 18, 2013 7:18 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
EASwiedler



Joined: 16 Sep 2013
Posts: 2

PostPosted: Wed Sep 18, 2013 8:04 pm    Post subject: Reply with quote

Thanks...this is just what I thought the issue was. I have asked for the upgrade to the latest.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1116
Location: My desk.

PostPosted: Wed Sep 18, 2013 8:11 pm    Post subject: Reply with quote

Sure thing- if you're still having trouble, please do post back and we can do some more digging!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group