none
Stored Procedure merge issue

    Question

  • Hello all,

    I don't think this is a merge issue but that is my primary procedure.  Here is my set up:

    I have a console app that calls a stored procedure that inserts data into a temp table.  At the end of that stored procedure, it calls another stored procedure that uses a merge statement to either add, update, or flag a record as deleted.  For example, if a record as been deleted from the Source (Temp table), the FlagID will be changed to a 3 in the Target table.  Finally, the Temp DB is purged and the process starts over.

    I have tested each piece individually and everything works great.  When I add items to temp, I get a flag of 1.  When something changes, the flag is changed to 2 in the Target table.  And, as stated before, when something is deleted, the flag is changed to 3.

    However, when I run everything together, all flags are set to 3 when the process finishes.  The only thing I can think of is it is purging before it finishes checking all of the records, therefore, the flag is set to 3 because the Source is empty.

    Is there anything I can do to prevent this from happening?  Let me know if i need to post any of my code.

     

    Thursday, March 10, 2011 2:56 PM

Answers

  • Use SQL Server Profiler to capture the call to the stored procedure(s). See if the values are being passed properly and also add debuging to your sql code. Be sure the table is being populated correctly.

    One suggestion is to always qualify the table with the schema.

    > Select @FileInfoID= FileInfoID
    > From Temp_FileInfo
    > Where @FullPath = FullPath
    >
    > If @FileInfoID IS NULL
    >    Insert Into dbo.Temp_FileInfo

    Select @FileInfoID= FileInfoID
    From dbo.Temp_FileInfo
    Where @FullPath = FullPath

    If @FileInfoID IS NULL
        Insert Into dbo.Temp_FileInfo


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Matt11380 Wednesday, March 16, 2011 1:46 PM
    Thursday, March 10, 2011 6:10 PM
    Moderator
  • I ran the Profiler and noticed that it was running through the Procs on every record which was causing the application to purge after every record.  I am no longer running each stored procedure from the other.  I changed them to run inside the console app and everything seems to be working fine now.

     

    Thank you for all of the help,

     

    Matt

    • Proposed as answer by Naomi NModerator Monday, March 14, 2011 2:54 PM
    • Marked as answer by Matt11380 Wednesday, March 16, 2011 1:47 PM
    Monday, March 14, 2011 2:34 PM

All replies

  • I am afraid that you will not get to many advices here, without showing us your code. Try to put yourself in our side and think if you will be able to answer this question.

    Help us to be able to help you.

     


    AMB

    Some guidelines for posting questions...

    Thursday, March 10, 2011 3:04 PM
    Moderator
  • Sorry.  Here is the Merge code.

    BEGIN

    Merge FileInfo AS Target
        Using Temp_FileInfo As Source
        ON (Target.FullPath = Source.FullPath)
        When Matched AND
            Target.DateModified <> Source.DateModified OR
            Target.FileSize <>Source.FileSize
        Then Update
            Set
            Target.FileName = Source.FileName,
            Target.DateCreated = Source.DateCreated,
            Target.DateModified = Source.DateModified,
            Target.FileSize = Source.FileSize,
            Target.FullPath = Source.FullPath,
            Target.FlagID = 2
        WHEN NOT MATCHED By Target THEN
            INSERT (FileName, DateCreated, DateModified, FileSize, FullPath, FlagID)
            VALUES (Source.FileName, Source.DateCreated, Source.DateModified, Source.FileSize, Source.FullPath, Source.FlagID)
        WHEN NOT MATCHED BY Source THEN
            UPDATE SET Target.FlagID=3;

    END

     

    And here is the Insert code to the Temp Table.

    @FileInfoID        int Output,
    @FileName        nvarchar(50),
    @DateCreated    datetime = NULL,
    @DateModified    datetime = NULL,
    @FileSize        int = NULL,
    @FullPath        nvarchar(150),
    @FlagID            int

    As
    Begin
    Set NOCOUNT ON

    Select @FileInfoID= FileInfoID
    From Temp_FileInfo
    Where @FullPath = FullPath

    If @FileInfoID IS NULL
        Insert Into dbo.Temp_FileInfo
        ([FileName],
        DateCreated,
        DateModified,
        FileSize,
        FullPath,
        FlagID)
        VALUES
        (@FileName,
        @DateCreated,
        @DateModified,
        @FileSize,
        @FullPath,
        @FlagID)

        Set @FileInfoID = SCOPE_IDENTITY()

        Set @FlagID = '1'
       
        EXEC sp_CheckFileInfo
       
        EXEC sp_PurgeTempDB

     

    The Purge is literally delete from temp table.

    Thursday, March 10, 2011 3:08 PM
  • Did you tried to debug application? If procedures are running great when you started it manually then maybe you are sending wrong parameter in app. Also, if you are using Entity create some Select expression at the end of the procedure and register that procedure returns object which you have been returned by select expression.

     


    If you can't fix something try with bigger hammer
    Thursday, March 10, 2011 3:11 PM
  • This is the problem

     When Matched AND 
            Target.DateModified <> Source.DateModified OR
            Target.FileSize <>Source.FileSize

    Take a close look at the MERGE command in BOL:

    Caution

    It is important to specify only the columns from the target table that are used for matching purposes. That is, specify columns from the target table that are compared to the corresponding column of the source table. Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, March 10, 2011 3:19 PM
    Moderator
  • Having table schema and sample data will be helpful to reproduce the problem in our side.

    Are you sure that the temporary table is being populated correctly?

    If for some reason the temporary table is empty by the time you execute the MERGE statement, then there will not be a matcg between the target and the source so the "when not matched by source" part will be executed for every row in taget.

    DECLARE @T TABLE (
    c1 int NOT NULL UNIQUE,
    c2 int NULL
    );
    
    DECLARE @S TABLE (
    c1 int NOT NULL UNIQUE
    );
    
    INSERT INTO @T(c1) VALUES (1);
    INSERT INTO @T(c1) VALUES (2);
    
    SELECT
    	*
    FROM
    	@T;
    
    MERGE @T AS T
    USING @S AS S
    ON T.c1 = S.c1
    WHEN NOT MATCHED BY TARGET THEN
    	INSERT (c1) VALUES(S.c1)
    WHEN NOT MATCHED BY SOURCE THEN
    	UPDATE SET T.c2 = 3;
    
    SELECT
    	*
    FROM
    	@T;
    GO
    

    AMB

    Some guidelines for posting questions...

    Thursday, March 10, 2011 3:34 PM
    Moderator
  • Naomi,

    I believe that the caution is for the ON clause and not for WHEN branches.

     


    AMB

    Some guidelines for posting questions...

    Thursday, March 10, 2011 3:35 PM
    Moderator
  • To answer the first post, the application runs fine.  I am defining my variables, creating the parameters, and adding values.  And then I call the second stored procedure that I posted.  When I run everything manually, I start with the console app, then run the merge, and then the purge.  The whole process is: Run console app which will run the add to temp table sp, the add sp will then call the merge sp, and the merge sp will finally call the purge sp.

     

    To answer the second post, how else would I check those two columns for changes?  Also, why does it work when run manually?

     

    Thanks,

    Matt

    Thursday, March 10, 2011 3:36 PM
  • As far as I know the Temp Table looks fine.  I set the FileInfoID as Output a long time ago which it does not need to be anymore but I don't think that is causing the issue.

     

    Here is my console app to help you reproduce the issue:

     

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO;
    using System.Configuration;
    using System.Data.SqlClient;
    using System.Net;
    using System.Web;

    namespace ConsoleApplication1
    {
        class Class1
        {

        public static void Main()
            {
                List<string> filepath = new List<string>();

                filepath.AddRange(Directory.GetFiles(@"C:\Test\"));

                foreach (string file in filepath)
                {
                    FileInfo f = new FileInfo(file);
                    long s1 = f.Length;

                    DateTime dt = Directory.GetCreationTime(file);
                    DateTime date = Directory.GetLastWriteTime(file);

                    String fileName = Path.GetFileName(file);
                    String fullpath = Path.GetFullPath(file);

                    SqlConnection myConnection = new SqlConnection("ConnectionString");
                    SqlCommand myCommand = new SqlCommand("sp_AddFileInfo", myConnection);
                   
                    try
                    {
                        myConnection.Open();
                        myCommand.CommandType = System.Data.CommandType.StoredProcedure;
                        myCommand.Parameters.Add("@FileInfoID", System.Data.SqlDbType.Int);
                        myCommand.Parameters["@FileInfoID"].Direction = System.Data.ParameterDirection.Output;
                        myCommand.Parameters.AddWithValue("@FileName", fileName);
                        myCommand.Parameters.Add("@DateCreated", System.Data.SqlDbType.DateTime).Value = dt;
                        myCommand.Parameters.Add("@DateModified", System.Data.SqlDbType.DateTime).Value = date;
                        myCommand.Parameters.Add("@FileSize", System.Data.SqlDbType.Int).Value = s1;
                        myCommand.Parameters.Add("@FullPath", System.Data.SqlDbType.NVarChar, 150).Value = fullpath;
                        myCommand.Parameters.Add("@FlagID", System.Data.SqlDbType.Int).Value = 1;

                        int rows = myCommand.ExecuteNonQuery();
                    }

                    finally
                    {
                        myConnection.Close();
                    }

                }   
            }
        }
    }

    My test folder has several test docs in it.  Also, I am using an add range because there will eventually be multiple folders to monitor.

    Thursday, March 10, 2011 3:41 PM
  • Use SQL Server Profiler to capture the call to the stored procedure(s). See if the values are being passed properly and also add debuging to your sql code. Be sure the table is being populated correctly.

    One suggestion is to always qualify the table with the schema.

    > Select @FileInfoID= FileInfoID
    > From Temp_FileInfo
    > Where @FullPath = FullPath
    >
    > If @FileInfoID IS NULL
    >    Insert Into dbo.Temp_FileInfo

    Select @FileInfoID= FileInfoID
    From dbo.Temp_FileInfo
    Where @FullPath = FullPath

    If @FileInfoID IS NULL
        Insert Into dbo.Temp_FileInfo


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Matt11380 Wednesday, March 16, 2011 1:46 PM
    Thursday, March 10, 2011 6:10 PM
    Moderator
  • I ran the Profiler and noticed that it was running through the Procs on every record which was causing the application to purge after every record.  I am no longer running each stored procedure from the other.  I changed them to run inside the console app and everything seems to be working fine now.

     

    Thank you for all of the help,

     

    Matt

    • Proposed as answer by Naomi NModerator Monday, March 14, 2011 2:54 PM
    • Marked as answer by Matt11380 Wednesday, March 16, 2011 1:47 PM
    Monday, March 14, 2011 2:34 PM