Msg 208, Level 16, State 0, Line 1

Question

Msg 208, Level 16, State 0, Line 1

rockybhai

SSCommitted

Points: 1574
More actions
May 9, 2012 at 1:54 am

#257680

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

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

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

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/