Msg 208, Level 16, State 0, Line 1

  • Hi Friends,

    I have created a SP - Dynamic Merge for SCD type 1 and Type 2.

    When I execute the SP - I get the following error:

    Msg 208, Level 16, State 0, Line 1

    Invalid object name '##SCDTABLE11'.

    I checked multiple times - My DB is correct and i tried also "USE DB_NAME" - but no use...

    SET @SQLTABLE = ' USE DB_NAME GO SELECT * INTO ##SCDTABLE11 FROM (SELECT * from ' .........

    The created SP is below :

    USE [AFA2_DATAWAREHOUSE_DEV]

    GO

    /****** Object: StoredProcedure [DWH].[SPW_AFTER_MAPPING_SCD_PERFORMANCE] Script Date: 05/09/2012 09:06:33 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- ===========================================================

    -- Create date: 24-04-2012

    -- Description:To implement SCD type 1 and type 2 using SQL.

    -- Review Command:EXEC [DWH].[SPW_AFTER_MAPPING_SCD] 805

    -- ===========================================================

    ALTER proc [DWH].[SPW_AFTER_MAPPING_SCD_PERFORMANCE] @Source_ID int

    AS

    BEGIN

    SET NOCOUNT ON

    ---------------------------------- DROP ALL THE TEMP TABLES CREATED FOR THE SP ---------------------------------------

    IF EXISTS

    (

    SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE ID = OBJECT_ID(N'tempdb..##SCDBASE')

    )

    BEGIN

    DROP TABLE ##SCDBASE

    END

    IF EXISTS

    (

    SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE ID = OBJECT_ID(N'tempdb..##SCDTABLE11')

    )

    BEGIN

    DROP TABLE ##SCDTABLE11

    END

    ----------------------------------- Gets the Dimension table name / DWH table name ----------------------------------

    DECLARE @DWHTABLE NVARCHAR(MAX)

    SET @DWHTABLE = TEC.Get_source_param('FACT',@source_id)

    ----------------------------------- Declare the necessary variables ----------------------------------------------------

    DECLARE @SQLTABLE NVARCHAR(MAX)

    DECLARE @crlf char(2)

    DECLARE @Field varchar(255)

    DECLARE @SQL Nvarchar(max)

    SET @crlf = CHAR(13)

    ----------------------------------- TO LOOP THROUGH THE N NUMBER OF NEW ROWS WITH LAST FLG = 2 -------------------

    DECLARE @SQLBASE NVARCHAR(MAX)

    SET @SQLBASE = ' SELECT * INTO ##SCDBASE FROM

    (

    SELECT * from (

    SELECT RANK() OVER (ORDER BY FROM_DAT) ROWID, *

    FROM ' +@DWHTABLE+ ' Where LAST_FLG = 2

    ) B

    )A;'

    BEGIN TRANSACTION

    EXEC sp_executesql @SQLBASE

    SET XACT_ABORT ON

    IF @@ERROR<>0

    BEGIN

    ROLLBACK

    RAISERROR('Error while Creating - Copy of data from Dimension table to ##SCDBASE(where LAST_FLG = 2)',16,1)

    GOTO DROPTEMPTABLES

    END

    COMMIT

    DECLARE @RC INT

    DECLARE @ID INT

    SET @ID = 1

    SELECT @RC =COUNT(*) FROM ##SCDBASE

    ---------------------------------- While Loop Begins --------------------------------------------------------------------

    WHILE (@ID < = @RC)

    BEGIN

    ---------------------------------- Capturing the Business keys from the RXD_DIM table -----------------------------------

    CREATE TABLE #Businesskey_Cursor

    (

    --Number INT IDENTITY(1,1), --Auto incrementing Identity column

    BK_ColumnName VARCHAR(300) --The string value

    )

    INSERT INTO #Businesskey_Cursor

    SELECT

    --Weight,

    FUNCT_1_CPL AS COLUMN_NAME_IN_DWH

    --INTO #Business_key

    FROM RXD.RXD_DIM

    WHERE FUNCT_7_CPL = 'Y' AND CLASS_ID =

    (

    -- Find the CLASS_ID for the DWH table of a specific source

    SELECT TECH_5_CPL AS DWH_CLASS_REFERENCE

    FROM RXD.RXD_DIM

    WHERE CLASS_ID = 12 AND OBJ_ID = @SOURCE_ID

    )

    ORDER BY OBJ_ID ASC

    CREATE TABLE #Businesskey

    (

    Number INT IDENTITY(1,1), --Auto incrementing Identity column

    BK_ColumnName VARCHAR(300) --The string value

    )

    INSERT INTO #Businesskey

    SELECT

    --Weight,

    FUNCT_1_CPL AS COLUMN_NAME_IN_DWH

    --INTO #Business_key

    FROM RXD.RXD_DIM

    WHERE FUNCT_7_CPL = 'Y' AND CLASS_ID =

    (

    -- Find the CLASS_ID for the DWH table of a specific source

    SELECT TECH_5_CPL AS DWH_CLASS_REFERENCE

    FROM RXD.RXD_DIM

    WHERE CLASS_ID = 12 AND OBJ_ID = @SOURCE_ID

    )

    ORDER BY OBJ_ID ASC

    ------------- Section - 2.1.5.3 in the requirement doc -> Special case of duplicates on business key ----------------

    DECLARE @Field_BK varchar(255)

    --DECLARE PhysicalKey_BK CURSOR for

    --select * from #Businesskey_Cursor

    --OPEN PhysicalKey_BK

    --FETCH NEXT FROM PhysicalKey_BK INTO @Field_BK

    --if (@@FETCH_STATUS>=0)

    -- BEGIN

    --DECLARE @Field_BK varchar(255)

    Declare @N_BK INT

    Declare @Count_BK INT

    --Initialize the loop variable

    SET @N_BK = 1

    --Determine the number of rows in the Table

    SELECT @Count_BK=COUNT(*) from #Businesskey

    --print @Count_BK

    --print @SQLTABLE

    SET @SQLTABLE = 'SELECT * INTO ##SCDTABLE11 FROM (SELECT * from

    (

    SELECT *,ROW_NUMBER() OVER (PARTITION BY '

    ---------------- Partition by all the Business Keys in the DWH table ----------------------------------------------------

    IF (@N_BK=1)

    BEGIN

    SELECT @Field_BK = BK_ColumnName FROM #Businesskey WHERE Number = @N_BK

    SET @SQLTABLE = @SQLTABLE + @Field_BK

    END

    SET @N_BK = 2

    --print @N_BK

    WHILE (@N_BK > 1 and @N_BK <= @Count_BK)

    BEGIN

    --SET @N=2

    SELECT @Field_BK = BK_ColumnName FROM #Businesskey WHERE Number = @N_BK

    SET @SQLTABLE = @SQLTABLE + ', ' + @Field

    print @SQLTABLE

    set @N_BK = @N_BK+1;

    END

    -- SET @SQLTABLE = @SQLTABLE + ' ' + @Field_BK

    -- FETCH NEXT FROM PhysicalKey_BK INTO @Field_BK

    -- END

    --WHILE (@@FETCH_STATUS<>-1)

    --BEGIN

    -- IF (@@FETCH_STATUS<>-2)

    -- SET @SQLTABLE = @SQLTABLE + ',' + @Field_BK

    -- FETCH NEXT FROM PhysicalKey_BK INTO @Field_BK

    --END

    --CLOSE PhysicalKey_BK

    SET @SQLTABLE = @SQLTABLE + ' ORDER BY FROM_DAT ) ROW_NUM FROM ##SCDBASE Where LAST_FLG = 2) B where B.ROW_NUM =1 )A;'

    --DEALLOCATE PhysicalKey_BK

    print @SQLTABLE

    --BEGIN TRANSACTION

    EXEC sp_executesql @SQLTABLE

    --SET XACT_ABORT ON

    --------------------------------------- Error handling ------------------------------------------------------------------

    --IF @@ERROR<>0

    --BEGIN

    --ROLLBACK

    --RAISERROR('Error while doing - Special case of duplicates on business key operations - Section 2.1.5.3',16,1)

    --GOTO DROPTEMPTABLES

    --END

    --COMMIT

    --------------------------------- END OF Special case of duplicates on business key -----------------------------------

    ------------------------------------- Initializing Last Flg in the Temp table -------------------------------------------

    SET @SQLTABLE = 'UPDATE ##SCDTABLE11 SET LAST_FLG = 1; '

    SET @SQLTABLE = @SQLTABLE + ' DELETE FROM ' + @DWHTABLE + ' WHERE LAST_FLG = 2 ;'

    BEGIN TRANSACTION

    EXEC sp_executesql @SQLTABLE

    SET XACT_ABORT ON

    IF @@ERROR <>0

    BEGIN

    ROLLBACK

    RAISERROR('Error in inserting records to temp table and deleting from DWH table',16,1)

    GOTO DROPTEMPTABLES

    END

    COMMIT

    ----------------------------------- END OF INTIALIZATION ----------------------------------------------------------------

    ---------------- GETTING LIST OF ALL THE COLUMNS IN THE DIMENSION TABLE / DWH TABLE -------------------------------------

    CREATE TABLE #ListOfColumns

    (

    Number INT IDENTITY(1,1), --Auto incrementing Identity column

    ColumnName VARCHAR(300) --The string value

    )

    INSERT INTO #ListOfColumns

    SELECT

    FUNCT_1_CPL AS COLUMN_NAME_IN_DWH

    --INTO #ListOfColumns

    FROM RXD.RXD_DIM

    WHERE FUNCT_1_CPL is not null and CLASS_ID =

    (

    -- Find the CLASS_ID for the DWH table of a specific source

    SELECT TECH_5_CPL AS DWH_CLASS_REFERENCE

    FROM RXD.RXD_DIM

    WHERE CLASS_ID = 12 AND OBJ_ID = 805

    )

    ORDER BY OBJ_ID ASC

    INSERT INTO #ListOfColumns VALUES('FORM_ID')

    INSERT INTO #ListOfColumns VALUES('CREATION_DAT')

    INSERT INTO #ListOfColumns VALUES('CREATION_ID')

    INSERT INTO #ListOfColumns VALUES('SOURCE_ID')

    ----------------------------------- CAPTURING SCD TYPE 1 COLUMNS -----------------------------------------------

    CREATE TABLE #Type1Columns

    (

    Number INT IDENTITY(1,1), --Auto incrementing Identity column

    ColumnName VARCHAR(300) --The string value

    )

    INSERT INTO #Type1Columns

    SELECT

    FUNCT_1_CPL AS COLUMN_NAME_IN_DWH

    --INTO #SCD_type_1

    FROM RXD.RXD_DIM

    WHERE TECH_1_CPL = '1' AND CLASS_ID =

    (

    -- Find the CLASS_ID for the DWH table of a specific source

    SELECT TECH_5_CPL AS DWH_CLASS_REFERENCE

    FROM RXD.RXD_DIM

    WHERE CLASS_ID = 12 AND OBJ_ID = @Source_ID

    )

    ORDER BY OBJ_ID ASC

    -------------------------------- CAPTURING SCD TYPE 2 COLUMNS --------------------------------------------------

    CREATE TABLE #Type2Columns

    (

    Number INT IDENTITY(1,1), --Auto incrementing Identity column

    ColumnName VARCHAR(300) --The string value

    )

    INSERT INTO #Type2Columns

    SELECT

    FUNCT_1_CPL AS COLUMN_NAME_IN_DWH

    --INTO #SCD_type_2

    FROM RXD.RXD_DIM

    WHERE TECH_1_CPL = '2' AND CLASS_ID =

    (

    -- Find the CLASS_ID for the DWH table of a specific source

    SELECT TECH_5_CPL AS DWH_CLASS_REFERENCE

    FROM RXD.RXD_DIM

    WHERE CLASS_ID = 12 AND OBJ_ID = @Source_ID

    )

    ORDER BY OBJ_ID ASC

    -------------------- HANDLING SCD TYPE 1 COLUMNS USING MERGE -----------------------------------------------------

    SET @SQL = 'MERGE '+ @DWHTABLE +' AS Target'

    SET @SQL = @SQL + ' USING ##SCDTABLE11 AS Source '

    Declare @N INT

    Declare @Count INT

    --Initialize the looper variable

    SET @N = 1

    --Determine the number of rows in the Table

    SELECT @Count=COUNT(*) from #Businesskey

    IF (@N=1)

    BEGIN

    SELECT @Field = BK_ColumnName FROM #Businesskey WHERE Number = @N

    SET @SQL = @SQL + ' ON Target.' + @Field + ' = Source.' + @Field

    END

    SET @N = 2

    WHILE (@N > 1 and @N <= @Count)

    BEGIN

    SELECT @Field = BK_ColumnName FROM #Businesskey WHERE Number = @N

    SET @SQL = @SQL + ' AND Target.' + @Field + ' = Source.' + @Field

    set @N = @N+1;

    END

    SET @SQL = @SQL + ' WHEN MATCHED '

    ------- Type 1 columns info

    declare @Ntype1 int

    declare @Counttype1 int

    set @Ntype1 = 1

    SELECT @Counttype1 = COUNT(*) from #Type1Columns

    Declare @FieldType1 varchar(300)

    IF (@Ntype1 = 1)

    BEGIN

    SELECT @FieldType1 = ColumnName from #Type1Columns where Number = @Ntype1

    SET @SQL = @SQL + ' AND (Target.' + @FieldType1 + ' <> Source.' + @FieldType1

    END

    SET @Ntype1 = 2

    While (@Ntype1 > 1 and @Ntype1 < = @Counttype1)

    BEGIN

    SELECT @FieldType1 = ColumnName from #Type1Columns where Number = @Ntype1

    SET @SQL = @SQL + ' OR Target.' + @FieldType1 + ' <> Source.' + @FieldType1

    SET @Ntype1 = @Ntype1 + 1;

    END

    SET @SQL = @SQL + @crlf + ' )'

    SET @SQL = @SQL + ' THEN UPDATE SET '

    set @Ntype1 = 1

    SELECT @Counttype1 = COUNT(*) from #Type1Columns

    IF (@Ntype1 = 1)

    BEGIN

    SELECT @FieldType1 = ColumnName from #Type1Columns where Number = @Ntype1

    SET @SQL = @SQL + ' Target.' + @FieldType1 + ' = Source.' + @FieldType1

    END

    SET @Ntype1 = 2

    While (@Ntype1 > 1 and @Ntype1 < = @Counttype1)

    BEGIN

    SELECT @FieldType1 = ColumnName from #Type1Columns where Number = @Ntype1

    SET @SQL = @SQL + ' , Target.' + @FieldType1 + ' = Source.' + @FieldType1

    SET @Ntype1 = @Ntype1 + 1;

    END

    SET @SQL = @SQL +' WHEN NOT MATCHED THEN'

    SET @SQL = @SQL + @crlf + ' INSERT ('

    declare @N_allColumns int

    declare @Count_allColumns int

    SET @N_allColumns = 1

    SELECT @Count_allColumns = COUNT(*) from #ListOfColumns

    WHILE (@N_allColumns <= @Count_allColumns - 1)

    BEGIN

    SELECT @FieldType1 = ColumnName from #ListOfColumns where Number = @N_allColumns

    SET @SQL = @SQL + @FieldType1 + ', '

    SELECT @N_allColumns = @N_allColumns+1

    END

    IF (@N_allColumns = @Count_allColumns)

    BEGIN

    SELECT @FieldType1 = ColumnName from #ListOfColumns where Number = @N_allColumns

    SET @SQL = @SQL + @FieldType1

    END

    SET @SQL = @SQL + @crlf + ' ) VALUES ('

    SET @N_allColumns = 1

    SELECT @Count_allColumns = COUNT(*) from #ListOfColumns

    WHILE (@N_allColumns <= @Count_allColumns - 1)

    BEGIN

    SELECT @FieldType1 = ColumnName from #ListOfColumns where Number = @N_allColumns

    SET @SQL = @SQL + 'Source.'+@FieldType1 + ', '

    SELECT @N_allColumns = @N_allColumns+1

    END

    IF (@N_allColumns = @Count_allColumns)

    BEGIN

    SELECT @FieldType1 = ColumnName from #ListOfColumns where Number = @N_allColumns

    SET @SQL = @SQL +'Source.' +@FieldType1

    END

    SET @SQL = @SQL + @crlf + ' ) ;'

    --print @SQL

    -------------=========================================== TYPE 2 SCD ==============================================================================

    DECLARE @SQL_type2 Nvarchar(max)

    set @SQL_type2 = 'INSERT INTO ' + @DWHTABLE + ' ( '

    SET @N_allColumns = 1

    SELECT @Count_allColumns = COUNT(*) from #ListOfColumns

    WHILE (@N_allColumns <= @Count_allColumns - 1)

    BEGIN

    SELECT @FieldType1 = ColumnName from #ListOfColumns where Number = @N_allColumns

    SET @SQL_type2 = @SQL_type2 + @FieldType1 + ', '

    SELECT @N_allColumns = @N_allColumns+1

    END

    IF (@N_allColumns = @Count_allColumns)

    BEGIN

    SELECT @FieldType1 = ColumnName from #ListOfColumns where Number = @N_allColumns

    SET @SQL_type2 = @SQL_type2 + @FieldType1

    END

    set @SQL_type2 = @SQL_type2 + ' ) '

    set @SQL_type2 = @SQL_type2 + ' select '

    SET @N_allColumns = 1

    SELECT @Count_allColumns = COUNT(*) from #ListOfColumns

    WHILE (@N_allColumns <= @Count_allColumns - 1)

    BEGIN

    SELECT @FieldType1 = ColumnName from #ListOfColumns where Number = @N_allColumns

    SET @SQL_type2 = @SQL_type2 + @crlf + ' MERGE2.' + @FieldType1 + ','

    SELECT @N_allColumns = @N_allColumns+1

    END

    IF (@N_allColumns = @Count_allColumns)

    BEGIN

    SELECT @FieldType1 = ColumnName from #ListOfColumns where Number = @N_allColumns

    SET @SQL_type2 = @SQL_type2 + @crlf + ' MERGE2.' + @FieldType1

    END

    set @SQL_type2 = @SQL_type2 + ' FROM ('

    set @SQL_type2 = @SQL_type2 + ' MERGE ' + @DWHTABLE + ' AS Target USING ##SCDTABLE11 AS Source '

    --- Businesss key info

    SET @N = 1

    --Determine the number of rows in the Table

    SELECT @Count=COUNT(*) from #Businesskey

    IF (@N=1)

    BEGIN

    SELECT @Field = BK_ColumnName FROM #Businesskey WHERE Number = @N

    SET @SQL_Type2 = @SQL_Type2+ ' ON Target.' + @Field + ' = Source.' + @Field

    END

    --print @N

    SET @N = 2

    --print @N

    WHILE (@N > 1 and @N <= @Count)

    BEGIN

    --SET @N=2

    SELECT @Field = BK_ColumnName FROM #Businesskey WHERE Number = @N

    SET @SQL_Type2 = @SQL_Type2 + ' AND Target.' + @Field + ' = Source.' + @Field

    set @N = @N+1;

    END

    SET @SQL_type2 = @SQL_type2 + ' AND TARGET.LAST_FLG = 1 AND SOURCE.FROM_DAT > = TARGET.FROM_DAT '+' WHEN MATCHED '

    set @Ntype1 = 1

    SELECT @Counttype1 = COUNT(*) from #Type2Columns

    IF (@Ntype1 = 1)

    BEGIN

    SELECT @FieldType1 = ColumnName from #Type2Columns where Number = @Ntype1

    SET @SQL_type2 = @SQL_type2 + @crlf + ' AND (Target.' + @FieldType1 + ' <> Source.' + @FieldType1

    END

    SET @Ntype1 = 2

    While (@Ntype1 > 1 and @Ntype1 < = @Counttype1)

    BEGIN

    SELECT @FieldType1 = ColumnName from #Type2Columns where Number = @Ntype1

    SET @SQL_type2 = @SQL_type2 + ' OR Target.' + @FieldType1 + ' <> Source.' + @FieldType1

    SET @Ntype1 = @Ntype1 + 1;

    END

    SET @SQL_type2 = @SQL_type2 + @crlf + ' )'

    SET @SQL_type2 = @SQL_type2 + ' THEN UPDATE SET TARGET.Last_flg = 0, target.to_dat = source.from_dat'

    SET @SQL_type2 = @SQL_type2 + @crlf + ' OUTPUT $Action Action_Out, '

    SET @N_allColumns = 1

    SELECT @Count_allColumns = COUNT(*) from #ListOfColumns

    WHILE (@N_allColumns <= @Count_allColumns - 1)

    BEGIN

    SELECT @FieldType1 = ColumnName from #ListOfColumns where Number = @N_allColumns

    SET @SQL_type2 = @SQL_type2 + @crlf + ' Source.' + @FieldType1 + ','

    SELECT @N_allColumns = @N_allColumns+1

    END

    IF (@N_allColumns = @Count_allColumns)

    BEGIN

    SELECT @FieldType1 = ColumnName from #ListOfColumns where Number = @N_allColumns

    SET @SQL_type2 = @SQL_type2 + @crlf + ' Source.' + @FieldType1

    END

    SET @SQL_type2 = @SQL_type2 + @crlf + ' ) as MERGE2 '

    SET @SQL_type2 = @SQL_type2 + @crlf + ' where MERGE2.Action_Out = ''UPDATE''; '

    --print @SQL_type2

    ----------------------------------- END OF SCD TYPE 1 ---------------------------------------------------------

    ----------------------------------- HANDLING SCD TYPE 2 COLUMNS USING MERGE -- OLD CODE -----------------------------------

    /*

    DECLARE @SQL_type2 Nvarchar(max)

    set @SQL_type2 = 'INSERT INTO ' + @DWHTABLE + ' ( '

    OPEN AllColumns

    FETCH NEXT FROM AllColumns INTO @Field

    if (@@FETCH_STATUS>=0)

    BEGIN

    SET @SQL_type2 = @SQL_type2 + @crlf + ' ' + @Field

    FETCH NEXT FROM AllColumns INTO @Field

    END

    WHILE (@@FETCH_STATUS<>-1)

    BEGIN

    IF (@@FETCH_STATUS<>-2)

    SET @SQL_type2 = @SQL_type2 + @crlf + ' ,' + @Field

    FETCH NEXT FROM AllColumns INTO @Field

    END

    CLOSE AllColumns

    set @SQL_type2 = @SQL_type2 + ' ) '

    set @SQL_type2 = @SQL_type2 + ' select '

    OPEN AllColumns

    FETCH NEXT FROM AllColumns INTO @Field

    if (@@FETCH_STATUS>=0)

    BEGIN

    SET @SQL_type2 = @SQL_type2 + @crlf + ' MERGE2.' + @Field

    FETCH NEXT FROM AllColumns INTO @Field

    END

    WHILE (@@FETCH_STATUS<>-1)

    BEGIN

    IF (@@FETCH_STATUS<>-2)

    SET @SQL_type2 = @SQL_type2 + @crlf + ' ,MERGE2.' + @Field

    FETCH NEXT FROM AllColumns INTO @Field

    END

    CLOSE AllColumns

    set @SQL_type2 = @SQL_type2 + ' FROM ('

    set @SQL_type2 = @SQL_type2 + ' MERGE ' + @DWHTABLE + ' AS Target USING ##SCDTABLE11 AS Source '

    OPEN PhysicalKey

    FETCH NEXT FROM PhysicalKey INTO @Field

    if (@@FETCH_STATUS>=0)

    BEGIN

    SET @SQL_type2 = @SQL_type2 + ' ON Target.' + @Field + ' = Source.' + @Field

    FETCH NEXT FROM PhysicalKey INTO @Field

    END

    WHILE (@@FETCH_STATUS<>-1)

    BEGIN

    IF (@@FETCH_STATUS<>-2)

    SET @SQL_type2 = @SQL_type2 + ' AND Target.' + @Field + ' = Source.' + @Field

    FETCH NEXT FROM PhysicalKey INTO @Field

    END

    CLOSE PhysicalKey

    DEALLOCATE PhysicalKey

    SET @SQL_type2 = @SQL_type2 + ' AND TARGET.LAST_FLG = 1 AND SOURCE.FROM_DAT > = TARGET.FROM_DAT '

    SET @SQL_type2 = @SQL_type2 + ' WHEN MATCHED '

    DECLARE TYPE2COLUMNS CURSOR FOR

    SELECT * FROM @Type2Columns

    OPEN TYPE2COLUMNS

    FETCH NEXT FROM TYPE2COLUMNS INTO @Field

    if (@@FETCH_STATUS>=0)

    BEGIN

    SET @SQL_type2 = @SQL_type2 + ' AND (Target.' + @Field + ' <> Source.' + @Field

    FETCH NEXT FROM TYPE2COLUMNS INTO @Field

    END

    WHILE (@@FETCH_STATUS<>-1)

    BEGIN

    IF (@@FETCH_STATUS<>-2)

    SET @SQL_type2 = @SQL_type2 + ' OR Target.' + @Field + ' <> Source.' + @Field

    FETCH NEXT FROM TYPE2COLUMNS INTO @Field

    END

    CLOSE TYPE2COLUMNS

    DEALLOCATE TYPE2COLUMNS

    SET @SQL_type2 = @SQL_type2 + @crlf + ' )'

    SET @SQL_type2 = @SQL_type2 + ' THEN UPDATE SET TARGET.Last_flg = 0, target.to_dat = source.from_dat'

    SET @SQL_type2 = @SQL_type2 + @crlf + ' OUTPUT $Action Action_Out, '

    OPEN AllColumns

    FETCH NEXT FROM AllColumns INTO @Field

    if (@@FETCH_STATUS>=0)

    BEGIN

    SET @SQL_type2 = @SQL_type2 + @crlf + ' SOURCE.' + @Field

    FETCH NEXT FROM AllColumns INTO @Field

    END

    WHILE (@@FETCH_STATUS<>-1)

    BEGIN

    IF (@@FETCH_STATUS<>-2)

    SET @SQL_type2 = @SQL_type2 + @crlf + ' ,SOURCE.' + @Field

    FETCH NEXT FROM AllColumns INTO @Field

    END

    CLOSE AllColumns

    DEALLOCATE AllColumns

    SET @SQL_type2 = @SQL_type2 + @crlf + ' ) as MERGE2 '

    SET @SQL_type2 = @SQL_type2 + @crlf + ' where MERGE2.Action_Out = ''UPDATE''; '

    ----------------------------------------- END OF SCD TYPE 2 -----------------------------------------------

    */

    --------------------------------------- EXECUTING SCD TYPE 1 WITH TRANSACTION -----------------------------

    BEGIN TRANSACTION

    EXEC sp_executesql @SQL

    SET XACT_ABORT ON

    IF @@ERROR<>0

    BEGIN

    ROLLBACK

    RAISERROR('Error while doing SCD TYPE 1 operations',16,1)

    GOTO DROPTEMPTABLES

    END

    COMMIT

    ----------------------------------------- END OF EXECUTION - SCD TYPE 1 ----------------------------------

    ---------------------------- EXECUTING SCD TYPE 2 WITH TRANSACTION ---------------------------------------

    BEGIN TRANSACTION

    EXEC sp_executesql @SQL_type2

    SET XACT_ABORT ON

    DELETE FROM ##SCDBASE WHERE ROWID IN

    (SELECT ROWID FROM ##SCDTABLE11)

    --drop table ##SCDTABLE11

    Drop table #Businesskey

    Drop table #Type1Columns

    Drop table #Type2Columns

    Drop table #ListOfColumns

    drop table #Businesskey_Cursor

    IF @@ERROR <>0

    BEGIN

    ROLLBACK

    RAISERROR('Error while doing SCD TYPE 2 operations',16,1)

    GOTO DROPTEMPTABLES

    END

    COMMIT

    SELECT @ID = @ID + 1

    END

    ---------------------------------------------- While loop ends ----------------------------------------

    --------------------------------- END OF EXECUTION - SCD TYPE 2 ----------------------------------------

    --------------------------------- DROP ALL THE TEMP TABLES CREATED -------------------------------------

    DROPTEMPTABLES:

    IF EXISTS

    (

    SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE ID = OBJECT_ID(N'tempdb..#Businesskey')

    )

    BEGIN

    DROP TABLE #Businesskey

    END

    IF EXISTS

    (

    SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE ID = OBJECT_ID(N'tempdb..#Type1Columns')

    )

    BEGIN

    DROP TABLE #Type1Columns

    END

    IF EXISTS

    (

    SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE ID = OBJECT_ID(N'tempdb..#Type2Columns')

    )

    BEGIN

    DROP TABLE #Type2Columns

    END

    IF EXISTS

    (

    SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE ID = OBJECT_ID(N'tempdb..#ListOfColumns')

    )

    BEGIN

    DROP TABLE #ListOfColumns

    END

    IF EXISTS

    (

    SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE ID = OBJECT_ID(N'tempdb..#Businesskey_Cursor')

    )

    BEGIN

    DROP TABLE #Businesskey_Cursor

    END

    IF EXISTS

    (

    SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE ID = OBJECT_ID(N'tempdb..##SCDTABLE11')

    )

    BEGIN

    DROP TABLE ##SCDTABLE11

    END

    IF EXISTS

    (

    SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE ID = OBJECT_ID(N'tempdb..##SCDBASE')

    )

    BEGIN

    DROP TABLE ##SCDBASE

    END

    END

    Please help me.....

  • You are using dynamic SQL to construct the SQL Statement that is creating the temporary table. If you are concatenating a variable that is null, the whole statement is null. In that case you don't get any error message when you use the variable that has null as it's value with sp_executesql. Could be that this is your problem.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply