Strange SQL Server Behavior

Question

What I am trying to do is execute the stored procedure below automatically via a Maintenance Plan using the Execute T-SQL Statement Task. What I am seeing is that when this procedure runs as part of a maintenance plan, for some reason, the records added are doubled, as though the INSERT statement is run twice. Here's the kicker, when it is executed manually via Management Studio, it runs as expected and I get a single set of records added to the table. Using TRUNCATE TABLE as a work-around seems to solve the problem but I will be back to square one when it comes time to prepare the database for SQL mirroring.

Has anyone seen this sort of behavior before? Also, for the curious, the 100% fill factor on the rebuilt index is intentional because the only I/O happening on this table is the procedure below.

ALTER PROCEDURE [dbo].[usp_Populate_SearchTable]
AS
BEGIN
    UPDATE ut_Update_Status SET [status] = 1, lastUpdate = getdate();

    DELETE FROM ut_Permit_Lookup;
    DBCC CHECKIDENT ('dbo.ut_Permit_Lookup', RESEED, 0);
    -- OR
    --TRUNCATE TABLE ut_Permit_Lookup;

    INSERT INTO ut_Permit_Lookup (
        NUMBER_KEY,
        ADDR_FRACTION,
        PARCEL_NO,
        TYPE_TITLE,
        TypDesc,
        SUB_TYPE,
        Location,
        [DESCRIPTION],
        DATE_A,
        DATA_STATUS
    )
    SELECT a.NUMBER_KEY,
        a.ADDR_FRACTION,
        a.PARCEL_NO,
        a.TYPE_TITLE,
        a.TypDesc,
        a.SUB_TYPE,
        a.Location,
        SUBSTRING(a.[DESCRIPTION], 1, 275),
        a.DATE_A,
        a.DATA_STATUS
    FROM PERMPLUS.dbo.vw_Permit_WebLookup as a
    WHERE a.TYPE_CAT = 'BLDG'
        AND a.DATA_LEVEL = 'A'
        AND (a.CLASS <> 'NA' OR a.CLASS IS NULL);

    DBCC DBREINDEX ('dbo.ut_Permit_Lookup', idx_SearchIndex, 100);
    UPDATE ut_Update_Status SET [status] = 0;
END

After tracing it, it shows that both the DELETE and INSERT statements as starting twice and completing once. That confirms the doubling of records but I am still lost as to why. — Tehrab, Oct 03 '12 at 17:50
How are you calling the procedure in the execute task? You stated that it runs as part of a maintenance plan, does that mean other objects/tasks are running on success or failure of this task? — , Oct 03 '12 at 18:18
I am simply calling the procedure using EXEC. Also, it is the only task in the plan. — Tehrab, Oct 03 '12 at 18:46

score 0 · Answer 1 · answered Oct 03 '12 at 19:18

Well I guess to put a formal answer that I would suggest...

I have never heard of the problem. The maintenance plans I have had issues with, although not the same kind you are having, has been due to SSMS build number being lower than the instance I was working on. You did not state what build of SQL you are working with but if it is SQL 2008 or higher you might submit a connect item on http://connect.microsoft.com/sqlserver.

The only alternative I would probably use is to just schedule the procedure call with an SQL Agent job and see if the you get the same results. I would expect the SQL Agent job would give the same results as if you called it from SSMS in a query window.

Strange SQL Server Behavior

1 Answers1