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.

 

Answer 1

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...

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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...

Answer 6

Naomi,

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

 

---

AMB

Some guidelines for posting questions...

Answer 7

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

Answer 8

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.

Answer 9

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...

Answer 10

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