August 1, 2012 at 9:48 am
[/color]
No, it means the procedure alread exists and you are trying to create it again. Two things you can do. One, change the CREATE PROCEDURE to ALTER PROCEDURE. Two, add a DROP PROCEDURE before the CREATE PROCEDURE.
Ok I changed them to ALTER from CREATE. Now the problem I am running into is
Msg 208, Level 16, State 6, Procedure syd_StagedTablesNotInStage, Line 6
Invalid object name 'syd_StagedTablesNotInStage'.
Msg 208, Level 16, State 6, Procedure syd_StagedColsNotInStage, Line 6
Invalid object name 'syd_StagedColsNotInStage'.
These are probably making you crazy cause theyre simple but I just dont know how to fix them. Thanks, Lynn. I really appreciate it!
August 1, 2012 at 9:53 am
morepainot (8/1/2012)
[/color]No, it means the procedure alread exists and you are trying to create it again. Two things you can do. One, change the CREATE PROCEDURE to ALTER PROCEDURE. Two, add a DROP PROCEDURE before the CREATE PROCEDURE.
Ok I changed them to ALTER from CREATE. Now the problem I am running into is
Msg 208, Level 16, State 6, Procedure syd_StagedTablesNotInStage, Line 6
Invalid object name 'syd_StagedTablesNotInStage'.
Msg 208, Level 16, State 6, Procedure syd_StagedColsNotInStage, Line 6
Invalid object name 'syd_StagedColsNotInStage'.
These are probably making you crazy cause theyre simple but I just dont know how to fix them. Thanks, Lynn. I really appreciate it!
That means you dropped them already. You can't alter a procedure that has been dropped. You should keep your proc definition as create but before that add something like this.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourProcNameHere]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[YourProcNameHere]
GO
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2012 at 9:59 am
Should I do the same for this? I used your suggestion for the procedure and looks like the problem was solved. I have ONE more after this and I promise Ill stop bugging you
Msg 2714, Level 16, State 6, Line 8
There is already an object named 'syd_LogTable' in the database
August 1, 2012 at 10:00 am
morepainot (8/1/2012)
Should I do the same for this? I used your suggestion for the procedure and looks like the problem was solved. I have ONE more after this and I promise Ill stop bugging youMsg 2714, Level 16, State 6, Line 8
There is already an object named 'syd_LogTable' in the database
Well that one is a table so I can't say if you should drop it first or not. If you want to keep the data in it I would recommend that you not drop it. 😉 If you don't care about the data then it is ok.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2012 at 10:02 am
Sean Lange (8/1/2012)
morepainot (8/1/2012)
Should I do the same for this? I used your suggestion for the procedure and looks like the problem was solved. I have ONE more after this and I promise Ill stop bugging youMsg 2714, Level 16, State 6, Line 8
There is already an object named 'syd_LogTable' in the database
Well that one is a table so I can't say if you should drop it first or not. If you want to keep the data in it I would recommend that you not drop it. 😉 If you don't care about the data then it is ok.
What do you suggest I do?
August 1, 2012 at 10:04 am
That's because those Stored Procedures weren't created due to errors in the code.
For those you must use CREATE instead of ALTER.
The other option, would be to use CREATE on all and add the conditional DROP for each object.
Example:
IF OBJECT_ID( 'Stored_Procedure_Name') > 0
DROP PROCEDURE Stored_Procedure_Name
CREATE PROCEDURE Stored_Procedure_Name....
August 1, 2012 at 10:05 am
morepainot (8/1/2012)
Sean Lange (8/1/2012)
morepainot (8/1/2012)
Should I do the same for this? I used your suggestion for the procedure and looks like the problem was solved. I have ONE more after this and I promise Ill stop bugging youMsg 2714, Level 16, State 6, Line 8
There is already an object named 'syd_LogTable' in the database
Well that one is a table so I can't say if you should drop it first or not. If you want to keep the data in it I would recommend that you not drop it. 😉 If you don't care about the data then it is ok.
What do you suggest I do?
If you want to keep the data in it I would recommend that you not drop it. 😉 If you don't care about the data then it is ok.
Not sure what else I can offer here. Do you want to keep the data or not? I can't answer that or make a recommendation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2012 at 10:30 am
Okay, understood. This code is really long but Im going to post it with all the errors, mostly fixed. This included the procs usp_PreStageValidation and usp_PostStageValidation
ALTER PROCEDURE usp_PreStageValidation @ApplicationName VARCHAR(100)
,@SQL_str1 NVARCHAR(max)
,@SQL_str2 NVARCHAR(max)
,@SQL_str3 NVARCHAR(max)
,@SQL_str4 NVARCHAR(max)
,@SQL_str5 NVARCHAR(max)
AS
BEGIN
DECLARE @cat NVARCHAR(128)
,@tbl NVARCHAR(128)
,@col NVARCHAR(128)
,@msg NVARCHAR(128)
,@RecCount INT
,@ndx INT;
SET NOCOUNT ON;
CREATE TABLE #temp_table (
cat NVARCHAR(128)
,tbl NVARCHAR(128)
,col NVARCHAR(128)
);
--Fill Temporary Table with records of 1st Query
INSERT INTO #temp_table (
cat
,tbl
)
EXEC sp_executeSQL @SQL_str1
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,NULL
,N'This Stage table does not exist in this SOURCE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #1
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanStartEnd'
) --and this table is not existed in it
BEGIN
CREATE TABLE tbPlanStartEnd
(
--->Syd: code to be prepared to create the table 'tbPlanStartEnd' with required columns
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanStartEnd'
,NULL
,'This Table is Created Newly'
);
END;
--------------
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanGroup'
) --and this table is not existed in it
BEGIN
CREATE TABLE tbPlanGroup
(
--->Syd: code to be prepared to create the table 'tbPlanGroup' with required columns
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanGroup'
,NULL
,'This Table is Created Newly'
);
END
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanGroup'
AND COLUMN_NAME = 'grp_name'
) --and this column is not existed in it
BEGIN
--->Syd: code to be prepared to create the column 'grp_name' with required data type
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanGroup'
,NULL
,'This Table is added with column grp_name'
);
END;
END;
--Fill Temporary Table with records of 2nd Query
INSERT INTO #temp_table (
cat
,tbl
)
EXEC sp_executeSQL @SQL_str2
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,NULL
,N'This Stage table is not existed in this STAGE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #2
--Fill Temporary Table with records of 3rd Query
INSERT INTO #temp_table (
cat
,tbl
,col
)
EXEC sp_executeSQL @SQL_str3
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,col
,N'This Stage Column is not existed in this Table of this SOURCE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #3
--Fill Temporary Table with records of 4th Query
INSERT INTO #temp_table (
cat
,tbl
,col
)
EXEC sp_executeSQL @SQL_str4
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,col
,N'This Stage Column is not existed in this Table of this STAGE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #4
--Fill Temporary Table with records of 5th Query
INSERT INTO #temp_table (
cat
,tbl
,col
)
EXEC sp_executeSQL @SQL_str5
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,col
,N'The Data Type of this Stage Column of this Table is NOT same as in this SOURCE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #5
----end of usp_PreStageValidation procedure
-- --<<<<< Procedures which return Dynamic SQL for FIVE Queries
-- -- list stage Tables not in the source database
GO
ALTER PROCEDURE syd_StagedTablesNotInSource @ApplicationName VARCHAR(100)
,@SourceDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@SourceDatabaseName,TableName
FROM
(
select
ct.TableName
from
dbo.[Application] a
INNER JOIN dbo.CustomTable ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName
from
dbo.[Application] a
INNER JOIN dbo.StandardTable st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageTables
LEFT JOIN
(
SELECT
Table_Name
FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.tables
) CD
ON StageTables.tableName=CD.Table_name
WHERE CD.Table_Name IS NULL'
--list stage Tables not in the Stage database
GO
ALTER PROCEDURE syd_StagedTablesNotInStage @ApplicationName VARCHAR(100)
,@StageDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@StageDatabaseName,TableName
FROM
(
select
ct.TableName
from
dbo.[Application] a
INNER JOIN dbo.CustomTable ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName
from
dbo.[Application] a
INNER JOIN dbo.StandardTable st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageTables
LEFT JOIN
(
SELECT
Table_Name
FROM ' + @StageDatabaseName + '.INFORMATION_SCHEMA.tables
) CD
ON StageTables.tableName=CD.Table_name
WHERE CD.Table_Name IS NULL'
--list stage columns not in the source database
GO
ALTER PROCEDURE syd_StagedColsNotInSource @ApplicationName VARCHAR(100)
,@SourceDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@SourceDatabaseName,TableName,ColumnName
FROM
(
select
ct.TableName,ct.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.CustomColumn ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.StandardColumn st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name
FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL'
--list stage columns not in the Stage database
GO
ALTER PROCEDURE syd_StagedColsNotInStage @ApplicationName VARCHAR(100)
,@StageDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@StageDatabaseName,TableName, ColumnName
FROM
(
select
ct.TableName,ct.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.CustomColumn ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.StandardColumn st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name
FROM ' + @StageDatabaseName + '.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL'
--list stage columns whose data type is not same in the Source and Stage Database
GO
ALTER PROCEDURE syd_StagedCols_DT_Differ_SourceCols
@ApplicationName VARCHAR(100)
,@SourceDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT AS
SET @SQL_str = '
SELECT
@SourceDatabaseName,TableName,ColumnName
FROM
(
select
ct.TableName, ct.ColumnName, ct.DataType
from
dbo.[Application] a
INNER JOIN dbo.CustomColumn ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName, st.DataType
from
dbo.[Application] a
INNER JOIN dbo.StandardColumn st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name, Column_Name, Data_Type
FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName = CD.Column_name AND
WHERE CD.Column_Name IS NOT NULL AND
StageColumns.DataType <> CD.Data_Type'
GO
--Following code has extracted from "StagesColumns.txt"
--It gives only ONE ROW as ResultSet
--It contains SourceDatabaseName in the Variable @SourceDatabaseName and
-- StageDatabaseName in the Variable @StageDatabaseName
==========================================
--<<<<<<<<<<<<<<<<<<<<<< Extracted by Syd >>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- *******************************************************************
-- Finding names of Source and Stage Databases
-- *******************************************************************
use biCentral
GO
SET NOCOUNT ON
GO
--<<< ADDed by Syd >>>
-----------------------
-- LOG table
-----------------------
CREATE TABLE syd_LogTable (
L_database nvarchar(128),
L_table nvarchar(128),
L_column nvarchar(128),
L_message nvarchar(128)
);
GO
DECLARE
@ApplicationName VARCHAR(100)='CD'
, @ClientName VARCHAR(100)='ACHP'
, @ClientID INT
, @nMColum1 NVARCHAR(max) ---<<<< Syd: Added
, @nMColum2 NVARCHAR(max)
, @nMColum3 NVARCHAR(max)
, @nMColum4 NVARCHAR(max)
, @nMColum5 NVARCHAR(max) --- Syd: Added >>>>>>
, @SourceDatabaseName VARCHAR(100)
, @StageDatabaseName VARCHAR(100)
SELECT
@SourceDatabaseName=(SELECT DatabaseName FROM [BICentral].[dbo].DatabaseLocation WHERE DatabaseID=ca.SourceDatabaseID)
, @StageDatabaseName=(SELECT DatabaseName FROM [BICentral].[dbo].DatabaseLocation WHERE DatabaseID=ca.TargetDatabaseID)
FROM
[BICentral].[dbo].[Client] c
INNER JOIN [BICentral].[dbo].[ClientApplication] ca
ON c.ClientId=ca.ClientId
AND c.ClientName=@ClientName --->Syd: Extracting one set of Src and Tgt databases
INNER JOIN [BICentral].[dbo].[Application] a ---> for One Client "ACHP"
ON ca.ApplicationId=a.ApplicationId
AND a.[ApplicationName] =@ApplicationName ---> with one Application "CD"
--> Syd: Making SQL strings Ready
-----------------------------------
EXEC syd_StagedTablesNotInSource --->Syd: Check these Stored Procedures after the following
@ApplicationName, --- Validation Procedures
@SourceDatabaseName,
@SQL_str = @nMColumn1 OUT;
EXEC syd_StagedTablesNotInStage
@ApplicationName,
@StageDatabaseName,
@SQL_str = @nMColumn2 OUT;
EXEC syd_StagedColsNotInSource
@ApplicationName,
@SourceDatabaseName,
@SQL_str = @nMColumn3 OUT;
EXEC syd_StagedColsNotInStage
@ApplicationName,
@StageDatabaseName,
@SQL_str = @nMColumn4 OUT;
EXEC syd_StagedCols_DT_Differ_SourceCols
@ApplicationName,
@SourceDatabaseName,
@SQL_str = @nMColumn5 OUT;
--> Syd: Messages to Log Table
-------------------------------
EXEC usp_PreStageValidation --->Syd: Executing Final Procedure
@nMColumn1,
@nMColumn2,
@nMColumn3,
@nMColumn4,
@nMColumn5;
GO
--> Syd: Messages to SQL Console
---------------------------------
SELECT * FROM syd_LogTable;
GO
--> Syd: Creating final requested POST Validation Procedure
-------------------------------------------------------------
CREATE PROCEDURE usp_PostStageValidation
@StageDatabaseName VARCHAR(100),
@SourceDatabaseName VARCHAR(100)
AS
BEGIN
DECLARE
@STR nvarchar(1000)
, @cat nvarchar(128)
, @tbl nvarchar(128)
, @col nvarchar(128)
, @msg nvarchar(128)
, @RecCount int
, @ndx int;
CREATE TABLE #temp_table(
RowId int IDENTITY(1, 1), --IDENTITY column to identify each row in WHILE LOOP
cat nvarchar(128),
tbl nvarchar(128),
col nvarchar(128)
);
SET @STR = '
SELECT a.tablename,a.rowcnt
from
(
SELECT sc.name + ''.'' + ta.name TableName
,SUM(pa.rows) RowCnt
FROM ' +@StageDatabaseName +'.sys.tables ta
INNER JOIN ' +@StageDatabaseName +'.sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN ' +@StageDatabaseName +'.sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id < 2
GROUP BY sc.name,ta.name) as a
LEFT OUTER JOIN
(
SELECT sc.name + ''.'' + ta.name TableName
,SUM(pa.rows) RowCnt
FROM ' +@SourceDatabaseName +'.sys.tables ta
INNER JOIN ' +@SourceDatabaseName +'.sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN ' +@SourceDatabaseName +'.sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id < 2
GROUP BY sc.name,ta.name) as b
ON a.tablename = b.tablename
WHERE a.rowcnt <> b.rowcnt
';
--Fill Temporary Table with records of 1st Query
INSERT INTO #temp_table(cat, tbl)
EXEC sp_executeSQL @STR;
--After INSERT the system variable @@ROWCOUNT contains count of total records
--now Loop through each record of Temporary table
SET @RecCount = @@ROWCOUNT;
SET @ndx = 1;
WHILE @ndx <= @RecCount
BEGIN
SELECT @cat = @db1, @tbl = TableName
FROM #temp_table
WHERE RowId = @ndx; --Extract only one record based on Loop Index
@msg = 'This Stage table is not having same records as that of SOURCE table';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,NULL,@msg); --add an entry in LOG table
SET @ndx = @ndx + 1;
END;
DROP TABLE #temp_table;
END;
August 1, 2012 at 10:38 am
Okay, understood. This code is really long but Im going to post it with all the errors, mostly fixed. This included the procs usp_PreStageValidation and usp_PostStageValidation
You need step away from this for a few minutes. You have so many incredibly silly errors.
Start at line 43...
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanStartEnd'
) --and this table is not existed in it
BEGIN
CREATE TABLE tbPlanStartEnd
(
--->Syd: code to be prepared to create the table 'tbPlanStartEnd' with required columns
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanStartEnd'
,NULL
,'This Table is Created Newly'
);
END;
REALLY? You have insert statements nested inside of create table. This pattern has repeated itself around line 72 again.
There are dozens and dozens of errors in here. Many of them are not uncovered until you fix the previous ones.
My suggestion is to go get some lunch, relax, remember that coding frantically only creates errors. When your head is back on level again open this back up and try again.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2012 at 10:39 am
morepainot (8/1/2012)
Okay, understood. This code is really long but Im going to post it with all the errors, mostly fixed. This included the procs usp_PreStageValidation and usp_PostStageValidation
ALTER PROCEDURE usp_PreStageValidation @ApplicationName VARCHAR(100),@SQL_str1 NVARCHAR(max)
,@SQL_str2 NVARCHAR(max)
,@SQL_str3 NVARCHAR(max)
,@SQL_str4 NVARCHAR(max)
,@SQL_str5 NVARCHAR(max)
AS
BEGIN
DECLARE @cat NVARCHAR(128)
,@tbl NVARCHAR(128)
,@col NVARCHAR(128)
,@msg NVARCHAR(128)
,@RecCount INT
,@ndx INT;
SET NOCOUNT ON;
CREATE TABLE #temp_table (
cat NVARCHAR(128)
,tbl NVARCHAR(128)
,col NVARCHAR(128)
);
--Fill Temporary Table with records of 1st Query
INSERT INTO #temp_table (
cat
,tbl
)
EXEC sp_executeSQL @SQL_str1
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,NULL
,N'This Stage table does not exist in this SOURCE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #1
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanStartEnd'
) --and this table is not existed in it
BEGIN
CREATE TABLE tbPlanStartEnd
(
--->Syd: code to be prepared to create the table 'tbPlanStartEnd' with required columns
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanStartEnd'
,NULL
,'This Table is Created Newly'
);
END;
--------------
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanGroup'
) --and this table is not existed in it
BEGIN
CREATE TABLE tbPlanGroup
(
--->Syd: code to be prepared to create the table 'tbPlanGroup' with required columns
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanGroup'
,NULL
,'This Table is Created Newly'
);
END
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanGroup'
AND COLUMN_NAME = 'grp_name'
) --and this column is not existed in it
BEGIN
--->Syd: code to be prepared to create the column 'grp_name' with required data type
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanGroup'
,NULL
,'This Table is added with column grp_name'
);
END;
END;
--Fill Temporary Table with records of 2nd Query
INSERT INTO #temp_table (
cat
,tbl
)
EXEC sp_executeSQL @SQL_str2
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,NULL
,N'This Stage table is not existed in this STAGE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #2
--Fill Temporary Table with records of 3rd Query
INSERT INTO #temp_table (
cat
,tbl
,col
)
EXEC sp_executeSQL @SQL_str3
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,col
,N'This Stage Column is not existed in this Table of this SOURCE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #3
--Fill Temporary Table with records of 4th Query
INSERT INTO #temp_table (
cat
,tbl
,col
)
EXEC sp_executeSQL @SQL_str4
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,col
,N'This Stage Column is not existed in this Table of this STAGE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #4
--Fill Temporary Table with records of 5th Query
INSERT INTO #temp_table (
cat
,tbl
,col
)
EXEC sp_executeSQL @SQL_str5
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,col
,N'The Data Type of this Stage Column of this Table is NOT same as in this SOURCE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #5
----end of usp_PreStageValidation procedure
-- --<<<<< Procedures which return Dynamic SQL for FIVE Queries
-- -- list stage Tables not in the source database
GO
ALTER PROCEDURE syd_StagedTablesNotInSource @ApplicationName VARCHAR(100)
,@SourceDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@SourceDatabaseName,TableName
FROM
(
select
ct.TableName
from
dbo.[Application] a
INNER JOIN dbo.CustomTable ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName
from
dbo.[Application] a
INNER JOIN dbo.StandardTable st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageTables
LEFT JOIN
(
SELECT
Table_Name
FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.tables
) CD
ON StageTables.tableName=CD.Table_name
WHERE CD.Table_Name IS NULL'
--list stage Tables not in the Stage database
GO
ALTER PROCEDURE syd_StagedTablesNotInStage @ApplicationName VARCHAR(100)
,@StageDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@StageDatabaseName,TableName
FROM
(
select
ct.TableName
from
dbo.[Application] a
INNER JOIN dbo.CustomTable ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName
from
dbo.[Application] a
INNER JOIN dbo.StandardTable st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageTables
LEFT JOIN
(
SELECT
Table_Name
FROM ' + @StageDatabaseName + '.INFORMATION_SCHEMA.tables
) CD
ON StageTables.tableName=CD.Table_name
WHERE CD.Table_Name IS NULL'
--list stage columns not in the source database
GO
ALTER PROCEDURE syd_StagedColsNotInSource @ApplicationName VARCHAR(100)
,@SourceDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@SourceDatabaseName,TableName,ColumnName
FROM
(
select
ct.TableName,ct.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.CustomColumn ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.StandardColumn st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name
FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL'
--list stage columns not in the Stage database
GO
ALTER PROCEDURE syd_StagedColsNotInStage @ApplicationName VARCHAR(100)
,@StageDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@StageDatabaseName,TableName, ColumnName
FROM
(
select
ct.TableName,ct.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.CustomColumn ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.StandardColumn st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name
FROM ' + @StageDatabaseName + '.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL'
--list stage columns whose data type is not same in the Source and Stage Database
GO
ALTER PROCEDURE syd_StagedCols_DT_Differ_SourceCols
@ApplicationName VARCHAR(100)
,@SourceDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT AS
SET @SQL_str = '
SELECT
@SourceDatabaseName,TableName,ColumnName
FROM
(
select
ct.TableName, ct.ColumnName, ct.DataType
from
dbo.[Application] a
INNER JOIN dbo.CustomColumn ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName, st.DataType
from
dbo.[Application] a
INNER JOIN dbo.StandardColumn st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name, Column_Name, Data_Type
FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName = CD.Column_name AND
WHERE CD.Column_Name IS NOT NULL AND
StageColumns.DataType <> CD.Data_Type'
GO
--Following code has extracted from "StagesColumns.txt"
--It gives only ONE ROW as ResultSet
--It contains SourceDatabaseName in the Variable @SourceDatabaseName and
-- StageDatabaseName in the Variable @StageDatabaseName
==========================================
--<<<<<<<<<<<<<<<<<<<<<< Extracted by Syd >>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- *******************************************************************
-- Finding names of Source and Stage Databases
-- *******************************************************************
use biCentral
GO
SET NOCOUNT ON
GO
--<<< ADDed by Syd >>>
-----------------------
-- LOG table
-----------------------
CREATE TABLE syd_LogTable (
L_database nvarchar(128),
L_table nvarchar(128),
L_column nvarchar(128),
L_message nvarchar(128)
);
GO
DECLARE
@ApplicationName VARCHAR(100)='CD'
, @ClientName VARCHAR(100)='ACHP'
, @ClientID INT
, @nMColum1 NVARCHAR(max) ---<<<< Syd: Added
, @nMColum2 NVARCHAR(max)
, @nMColum3 NVARCHAR(max)
, @nMColum4 NVARCHAR(max)
, @nMColum5 NVARCHAR(max) --- Syd: Added >>>>>>
, @SourceDatabaseName VARCHAR(100)
, @StageDatabaseName VARCHAR(100)
SELECT
@SourceDatabaseName=(SELECT DatabaseName FROM [BICentral].[dbo].DatabaseLocation WHERE DatabaseID=ca.SourceDatabaseID)
, @StageDatabaseName=(SELECT DatabaseName FROM [BICentral].[dbo].DatabaseLocation WHERE DatabaseID=ca.TargetDatabaseID)
FROM
[BICentral].[dbo].[Client] c
INNER JOIN [BICentral].[dbo].[ClientApplication] ca
ON c.ClientId=ca.ClientId
AND c.ClientName=@ClientName --->Syd: Extracting one set of Src and Tgt databases
INNER JOIN [BICentral].[dbo].[Application] a ---> for One Client "ACHP"
ON ca.ApplicationId=a.ApplicationId
AND a.[ApplicationName] =@ApplicationName ---> with one Application "CD"
--> Syd: Making SQL strings Ready
-----------------------------------
EXEC syd_StagedTablesNotInSource --->Syd: Check these Stored Procedures after the following
@ApplicationName, --- Validation Procedures
@SourceDatabaseName,
@SQL_str = @nMColumn1 OUT;
EXEC syd_StagedTablesNotInStage
@ApplicationName,
@StageDatabaseName,
@SQL_str = @nMColumn2 OUT;
EXEC syd_StagedColsNotInSource
@ApplicationName,
@SourceDatabaseName,
@SQL_str = @nMColumn3 OUT;
EXEC syd_StagedColsNotInStage
@ApplicationName,
@StageDatabaseName,
@SQL_str = @nMColumn4 OUT;
EXEC syd_StagedCols_DT_Differ_SourceCols
@ApplicationName,
@SourceDatabaseName,
@SQL_str = @nMColumn5 OUT;
--> Syd: Messages to Log Table
-------------------------------
EXEC usp_PreStageValidation --->Syd: Executing Final Procedure
@nMColumn1,
@nMColumn2,
@nMColumn3,
@nMColumn4,
@nMColumn5;
GO
--> Syd: Messages to SQL Console
---------------------------------
SELECT * FROM syd_LogTable;
GO
--> Syd: Creating final requested POST Validation Procedure
-------------------------------------------------------------
CREATE PROCEDURE usp_PostStageValidation
@StageDatabaseName VARCHAR(100),
@SourceDatabaseName VARCHAR(100)
AS
BEGIN
DECLARE
@STR nvarchar(1000)
, @cat nvarchar(128)
, @tbl nvarchar(128)
, @col nvarchar(128)
, @msg nvarchar(128)
, @RecCount int
, @ndx int;
CREATE TABLE #temp_table(
RowId int IDENTITY(1, 1), --IDENTITY column to identify each row in WHILE LOOP
cat nvarchar(128),
tbl nvarchar(128),
col nvarchar(128)
);
SET @STR = '
SELECT a.tablename,a.rowcnt
from
(
SELECT sc.name + ''.'' + ta.name TableName
,SUM(pa.rows) RowCnt
FROM ' +@StageDatabaseName +'.sys.tables ta
INNER JOIN ' +@StageDatabaseName +'.sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN ' +@StageDatabaseName +'.sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id < 2
GROUP BY sc.name,ta.name) as a
LEFT OUTER JOIN
(
SELECT sc.name + ''.'' + ta.name TableName
,SUM(pa.rows) RowCnt
FROM ' +@SourceDatabaseName +'.sys.tables ta
INNER JOIN ' +@SourceDatabaseName +'.sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN ' +@SourceDatabaseName +'.sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id < 2
GROUP BY sc.name,ta.name) as b
ON a.tablename = b.tablename
WHERE a.rowcnt <> b.rowcnt
';
--Fill Temporary Table with records of 1st Query
INSERT INTO #temp_table(cat, tbl)
EXEC sp_executeSQL @STR;
--After INSERT the system variable @@ROWCOUNT contains count of total records
--now Loop through each record of Temporary table
SET @RecCount = @@ROWCOUNT;
SET @ndx = 1;
WHILE @ndx <= @RecCount
BEGIN
SELECT @cat = @db1, @tbl = TableName
FROM #temp_table
WHERE RowId = @ndx; --Extract only one record based on Loop Index
@msg = 'This Stage table is not having same records as that of SOURCE table';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,NULL,@msg); --add an entry in LOG table
SET @ndx = @ndx + 1;
END;
DROP TABLE #temp_table;
END;
If you double click on the error message in the Messages tab, it will take you to where the error is (or hopefully close to it).
August 1, 2012 at 12:17 pm
Sean Lange (8/1/2012)
Okay, understood. This code is really long but Im going to post it with all the errors, mostly fixed. This included the procs usp_PreStageValidation and usp_PostStageValidation
You need step away from this for a few minutes. You have so many incredibly silly errors.
Start at line 43...
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanStartEnd'
) --and this table is not existed in it
BEGIN
CREATE TABLE tbPlanStartEnd
(
--->Syd: code to be prepared to create the table 'tbPlanStartEnd' with required columns
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanStartEnd'
,NULL
,'This Table is Created Newly'
);
END;
REALLY? You have insert statements nested inside of create table. This pattern has repeated itself around line 72 again.
There are dozens and dozens of errors in here. Many of them are not uncovered until you fix the previous ones.
My suggestion is to go get some lunch, relax, remember that coding frantically only creates errors. When your head is back on level again open this back up and try again.
Oh thats only there cause I have to create a table there. Im not sure what columns to add to it so I left it blank for the time being. I know a lot of the errors are silly. What else did you see besides this particular one? Anything you can help me with?
August 1, 2012 at 12:19 pm
Lynn Pettis (8/1/2012)
morepainot (8/1/2012)
Okay, understood. This code is really long but Im going to post it with all the errors, mostly fixed. This included the procs usp_PreStageValidation and usp_PostStageValidation
ALTER PROCEDURE usp_PreStageValidation @ApplicationName VARCHAR(100),@SQL_str1 NVARCHAR(max)
,@SQL_str2 NVARCHAR(max)
,@SQL_str3 NVARCHAR(max)
,@SQL_str4 NVARCHAR(max)
,@SQL_str5 NVARCHAR(max)
AS
BEGIN
DECLARE @cat NVARCHAR(128)
,@tbl NVARCHAR(128)
,@col NVARCHAR(128)
,@msg NVARCHAR(128)
,@RecCount INT
,@ndx INT;
SET NOCOUNT ON;
CREATE TABLE #temp_table (
cat NVARCHAR(128)
,tbl NVARCHAR(128)
,col NVARCHAR(128)
);
--Fill Temporary Table with records of 1st Query
INSERT INTO #temp_table (
cat
,tbl
)
EXEC sp_executeSQL @SQL_str1
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,NULL
,N'This Stage table does not exist in this SOURCE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #1
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanStartEnd'
) --and this table is not existed in it
BEGIN
CREATE TABLE tbPlanStartEnd
(
--->Syd: code to be prepared to create the table 'tbPlanStartEnd' with required columns
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanStartEnd'
,NULL
,'This Table is Created Newly'
);
END;
--------------
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanGroup'
) --and this table is not existed in it
BEGIN
CREATE TABLE tbPlanGroup
(
--->Syd: code to be prepared to create the table 'tbPlanGroup' with required columns
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanGroup'
,NULL
,'This Table is Created Newly'
);
END
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanGroup'
AND COLUMN_NAME = 'grp_name'
) --and this column is not existed in it
BEGIN
--->Syd: code to be prepared to create the column 'grp_name' with required data type
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanGroup'
,NULL
,'This Table is added with column grp_name'
);
END;
END;
--Fill Temporary Table with records of 2nd Query
INSERT INTO #temp_table (
cat
,tbl
)
EXEC sp_executeSQL @SQL_str2
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,NULL
,N'This Stage table is not existed in this STAGE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #2
--Fill Temporary Table with records of 3rd Query
INSERT INTO #temp_table (
cat
,tbl
,col
)
EXEC sp_executeSQL @SQL_str3
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,col
,N'This Stage Column is not existed in this Table of this SOURCE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #3
--Fill Temporary Table with records of 4th Query
INSERT INTO #temp_table (
cat
,tbl
,col
)
EXEC sp_executeSQL @SQL_str4
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,col
,N'This Stage Column is not existed in this Table of this STAGE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #4
--Fill Temporary Table with records of 5th Query
INSERT INTO #temp_table (
cat
,tbl
,col
)
EXEC sp_executeSQL @SQL_str5
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,col
,N'The Data Type of this Stage Column of this Table is NOT same as in this SOURCE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #5
----end of usp_PreStageValidation procedure
-- --<<<<< Procedures which return Dynamic SQL for FIVE Queries
-- -- list stage Tables not in the source database
GO
ALTER PROCEDURE syd_StagedTablesNotInSource @ApplicationName VARCHAR(100)
,@SourceDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@SourceDatabaseName,TableName
FROM
(
select
ct.TableName
from
dbo.[Application] a
INNER JOIN dbo.CustomTable ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName
from
dbo.[Application] a
INNER JOIN dbo.StandardTable st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageTables
LEFT JOIN
(
SELECT
Table_Name
FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.tables
) CD
ON StageTables.tableName=CD.Table_name
WHERE CD.Table_Name IS NULL'
--list stage Tables not in the Stage database
GO
ALTER PROCEDURE syd_StagedTablesNotInStage @ApplicationName VARCHAR(100)
,@StageDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@StageDatabaseName,TableName
FROM
(
select
ct.TableName
from
dbo.[Application] a
INNER JOIN dbo.CustomTable ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName
from
dbo.[Application] a
INNER JOIN dbo.StandardTable st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageTables
LEFT JOIN
(
SELECT
Table_Name
FROM ' + @StageDatabaseName + '.INFORMATION_SCHEMA.tables
) CD
ON StageTables.tableName=CD.Table_name
WHERE CD.Table_Name IS NULL'
--list stage columns not in the source database
GO
ALTER PROCEDURE syd_StagedColsNotInSource @ApplicationName VARCHAR(100)
,@SourceDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@SourceDatabaseName,TableName,ColumnName
FROM
(
select
ct.TableName,ct.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.CustomColumn ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.StandardColumn st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name
FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL'
--list stage columns not in the Stage database
GO
ALTER PROCEDURE syd_StagedColsNotInStage @ApplicationName VARCHAR(100)
,@StageDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@StageDatabaseName,TableName, ColumnName
FROM
(
select
ct.TableName,ct.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.CustomColumn ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.StandardColumn st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name
FROM ' + @StageDatabaseName + '.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL'
--list stage columns whose data type is not same in the Source and Stage Database
GO
ALTER PROCEDURE syd_StagedCols_DT_Differ_SourceCols
@ApplicationName VARCHAR(100)
,@SourceDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT AS
SET @SQL_str = '
SELECT
@SourceDatabaseName,TableName,ColumnName
FROM
(
select
ct.TableName, ct.ColumnName, ct.DataType
from
dbo.[Application] a
INNER JOIN dbo.CustomColumn ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName, st.DataType
from
dbo.[Application] a
INNER JOIN dbo.StandardColumn st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name, Column_Name, Data_Type
FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName = CD.Column_name AND
WHERE CD.Column_Name IS NOT NULL AND
StageColumns.DataType <> CD.Data_Type'
GO
--Following code has extracted from "StagesColumns.txt"
--It gives only ONE ROW as ResultSet
--It contains SourceDatabaseName in the Variable @SourceDatabaseName and
-- StageDatabaseName in the Variable @StageDatabaseName
==========================================
--<<<<<<<<<<<<<<<<<<<<<< Extracted by Syd >>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- *******************************************************************
-- Finding names of Source and Stage Databases
-- *******************************************************************
use biCentral
GO
SET NOCOUNT ON
GO
--<<< ADDed by Syd >>>
-----------------------
-- LOG table
-----------------------
CREATE TABLE syd_LogTable (
L_database nvarchar(128),
L_table nvarchar(128),
L_column nvarchar(128),
L_message nvarchar(128)
);
GO
DECLARE
@ApplicationName VARCHAR(100)='CD'
, @ClientName VARCHAR(100)='ACHP'
, @ClientID INT
, @nMColum1 NVARCHAR(max) ---<<<< Syd: Added
, @nMColum2 NVARCHAR(max)
, @nMColum3 NVARCHAR(max)
, @nMColum4 NVARCHAR(max)
, @nMColum5 NVARCHAR(max) --- Syd: Added >>>>>>
, @SourceDatabaseName VARCHAR(100)
, @StageDatabaseName VARCHAR(100)
SELECT
@SourceDatabaseName=(SELECT DatabaseName FROM [BICentral].[dbo].DatabaseLocation WHERE DatabaseID=ca.SourceDatabaseID)
, @StageDatabaseName=(SELECT DatabaseName FROM [BICentral].[dbo].DatabaseLocation WHERE DatabaseID=ca.TargetDatabaseID)
FROM
[BICentral].[dbo].[Client] c
INNER JOIN [BICentral].[dbo].[ClientApplication] ca
ON c.ClientId=ca.ClientId
AND c.ClientName=@ClientName --->Syd: Extracting one set of Src and Tgt databases
INNER JOIN [BICentral].[dbo].[Application] a ---> for One Client "ACHP"
ON ca.ApplicationId=a.ApplicationId
AND a.[ApplicationName] =@ApplicationName ---> with one Application "CD"
--> Syd: Making SQL strings Ready
-----------------------------------
EXEC syd_StagedTablesNotInSource --->Syd: Check these Stored Procedures after the following
@ApplicationName, --- Validation Procedures
@SourceDatabaseName,
@SQL_str = @nMColumn1 OUT;
EXEC syd_StagedTablesNotInStage
@ApplicationName,
@StageDatabaseName,
@SQL_str = @nMColumn2 OUT;
EXEC syd_StagedColsNotInSource
@ApplicationName,
@SourceDatabaseName,
@SQL_str = @nMColumn3 OUT;
EXEC syd_StagedColsNotInStage
@ApplicationName,
@StageDatabaseName,
@SQL_str = @nMColumn4 OUT;
EXEC syd_StagedCols_DT_Differ_SourceCols
@ApplicationName,
@SourceDatabaseName,
@SQL_str = @nMColumn5 OUT;
--> Syd: Messages to Log Table
-------------------------------
EXEC usp_PreStageValidation --->Syd: Executing Final Procedure
@nMColumn1,
@nMColumn2,
@nMColumn3,
@nMColumn4,
@nMColumn5;
GO
--> Syd: Messages to SQL Console
---------------------------------
SELECT * FROM syd_LogTable;
GO
--> Syd: Creating final requested POST Validation Procedure
-------------------------------------------------------------
CREATE PROCEDURE usp_PostStageValidation
@StageDatabaseName VARCHAR(100),
@SourceDatabaseName VARCHAR(100)
AS
BEGIN
DECLARE
@STR nvarchar(1000)
, @cat nvarchar(128)
, @tbl nvarchar(128)
, @col nvarchar(128)
, @msg nvarchar(128)
, @RecCount int
, @ndx int;
CREATE TABLE #temp_table(
RowId int IDENTITY(1, 1), --IDENTITY column to identify each row in WHILE LOOP
cat nvarchar(128),
tbl nvarchar(128),
col nvarchar(128)
);
SET @STR = '
SELECT a.tablename,a.rowcnt
from
(
SELECT sc.name + ''.'' + ta.name TableName
,SUM(pa.rows) RowCnt
FROM ' +@StageDatabaseName +'.sys.tables ta
INNER JOIN ' +@StageDatabaseName +'.sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN ' +@StageDatabaseName +'.sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id < 2
GROUP BY sc.name,ta.name) as a
LEFT OUTER JOIN
(
SELECT sc.name + ''.'' + ta.name TableName
,SUM(pa.rows) RowCnt
FROM ' +@SourceDatabaseName +'.sys.tables ta
INNER JOIN ' +@SourceDatabaseName +'.sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN ' +@SourceDatabaseName +'.sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id < 2
GROUP BY sc.name,ta.name) as b
ON a.tablename = b.tablename
WHERE a.rowcnt <> b.rowcnt
';
--Fill Temporary Table with records of 1st Query
INSERT INTO #temp_table(cat, tbl)
EXEC sp_executeSQL @STR;
--After INSERT the system variable @@ROWCOUNT contains count of total records
--now Loop through each record of Temporary table
SET @RecCount = @@ROWCOUNT;
SET @ndx = 1;
WHILE @ndx <= @RecCount
BEGIN
SELECT @cat = @db1, @tbl = TableName
FROM #temp_table
WHERE RowId = @ndx; --Extract only one record based on Loop Index
@msg = 'This Stage table is not having same records as that of SOURCE table';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,NULL,@msg); --add an entry in LOG table
SET @ndx = @ndx + 1;
END;
DROP TABLE #temp_table;
END;
If you double click on the error message in the Messages tab, it will take you to where the error is (or hopefully close to it).
Ya I was doing that but some of the errors I had no idea how to approach. I fixed some of the syntax errors but I dont know what to do with the others.
August 1, 2012 at 12:24 pm
I'm going to go back to square one. I have been rereading this thread and I am just getting confused and I need some illumination.
What is the purpose of this code? What is being passed in as input and what is the expected output.
Right now, looking at the code, I would not want to be the person following you on this, as there is nothing telling me what is happening, and this code really needs that information. Not just for me, but for you as well. You'll probably need it during the walk through and also six months from now when you have to go back in and do some kind of maintenance on the code.
August 1, 2012 at 12:25 pm
Oh thats only there cause I have to create a table there. Im not sure what columns to add to it so I left it blank for the time being. I know a lot of the errors are silly. What else did you see besides this particular one? Anything you can help me with?
I am not sure how I can help you identify errors when you know some of them are there. :w00t: I can help but I am not a sql compiler. I don't mean to sound snarky but honestly the quickest way to find syntax type errors is through SSMS. You are going to have to spend some time making your script somewhat realistic. I can' do much to help debug with the current status.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2012 at 12:27 pm
morepainot (8/1/2012)
Lynn Pettis (8/1/2012)
morepainot (8/1/2012)
Okay, understood. This code is really long but Im going to post it with all the errors, mostly fixed. This included the procs usp_PreStageValidation and usp_PostStageValidation
ALTER PROCEDURE usp_PreStageValidation @ApplicationName VARCHAR(100),@SQL_str1 NVARCHAR(max)
,@SQL_str2 NVARCHAR(max)
,@SQL_str3 NVARCHAR(max)
,@SQL_str4 NVARCHAR(max)
,@SQL_str5 NVARCHAR(max)
AS
BEGIN
DECLARE @cat NVARCHAR(128)
,@tbl NVARCHAR(128)
,@col NVARCHAR(128)
,@msg NVARCHAR(128)
,@RecCount INT
,@ndx INT;
SET NOCOUNT ON;
CREATE TABLE #temp_table (
cat NVARCHAR(128)
,tbl NVARCHAR(128)
,col NVARCHAR(128)
);
--Fill Temporary Table with records of 1st Query
INSERT INTO #temp_table (
cat
,tbl
)
EXEC sp_executeSQL @SQL_str1
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,NULL
,N'This Stage table does not exist in this SOURCE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #1
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanStartEnd'
) --and this table is not existed in it
BEGIN
CREATE TABLE tbPlanStartEnd
(
--->Syd: code to be prepared to create the table 'tbPlanStartEnd' with required columns
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanStartEnd'
,NULL
,'This Table is Created Newly'
);
END;
--------------
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanGroup'
) --and this table is not existed in it
BEGIN
CREATE TABLE tbPlanGroup
(
--->Syd: code to be prepared to create the table 'tbPlanGroup' with required columns
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanGroup'
,NULL
,'This Table is Created Newly'
);
END
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @cat
AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbPlanGroup'
AND COLUMN_NAME = 'grp_name'
) --and this column is not existed in it
BEGIN
--->Syd: code to be prepared to create the column 'grp_name' with required data type
--- also populate this table with required records <<<<<<<<<<<<<<<<<<<<
INSERT INTO syd_LogTable
VALUES (
@cat
,'tbPlanGroup'
,NULL
,'This Table is added with column grp_name'
);
END;
END;
--Fill Temporary Table with records of 2nd Query
INSERT INTO #temp_table (
cat
,tbl
)
EXEC sp_executeSQL @SQL_str2
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,NULL
,N'This Stage table is not existed in this STAGE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #2
--Fill Temporary Table with records of 3rd Query
INSERT INTO #temp_table (
cat
,tbl
,col
)
EXEC sp_executeSQL @SQL_str3
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,col
,N'This Stage Column is not existed in this Table of this SOURCE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #3
--Fill Temporary Table with records of 4th Query
INSERT INTO #temp_table (
cat
,tbl
,col
)
EXEC sp_executeSQL @SQL_str4
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,col
,N'This Stage Column is not existed in this Table of this STAGE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #4
--Fill Temporary Table with records of 5th Query
INSERT INTO #temp_table (
cat
,tbl
,col
)
EXEC sp_executeSQL @SQL_str5
,N'@ApplicationName VARCHAR(100)'
,@ApplicationName = @ApplicationName;
INSERT INTO syd_LogTable
SELECT Cat
,Tbl
,col
,N'The Data Type of this Stage Column of this Table is NOT same as in this SOURCE database'
FROM #temp_table;
TRUNCATE TABLE #temp_table;
--------------------------------------------------------------------------end of #5
----end of usp_PreStageValidation procedure
-- --<<<<< Procedures which return Dynamic SQL for FIVE Queries
-- -- list stage Tables not in the source database
GO
ALTER PROCEDURE syd_StagedTablesNotInSource @ApplicationName VARCHAR(100)
,@SourceDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@SourceDatabaseName,TableName
FROM
(
select
ct.TableName
from
dbo.[Application] a
INNER JOIN dbo.CustomTable ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName
from
dbo.[Application] a
INNER JOIN dbo.StandardTable st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageTables
LEFT JOIN
(
SELECT
Table_Name
FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.tables
) CD
ON StageTables.tableName=CD.Table_name
WHERE CD.Table_Name IS NULL'
--list stage Tables not in the Stage database
GO
ALTER PROCEDURE syd_StagedTablesNotInStage @ApplicationName VARCHAR(100)
,@StageDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@StageDatabaseName,TableName
FROM
(
select
ct.TableName
from
dbo.[Application] a
INNER JOIN dbo.CustomTable ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName
from
dbo.[Application] a
INNER JOIN dbo.StandardTable st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageTables
LEFT JOIN
(
SELECT
Table_Name
FROM ' + @StageDatabaseName + '.INFORMATION_SCHEMA.tables
) CD
ON StageTables.tableName=CD.Table_name
WHERE CD.Table_Name IS NULL'
--list stage columns not in the source database
GO
ALTER PROCEDURE syd_StagedColsNotInSource @ApplicationName VARCHAR(100)
,@SourceDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@SourceDatabaseName,TableName,ColumnName
FROM
(
select
ct.TableName,ct.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.CustomColumn ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.StandardColumn st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name
FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL'
--list stage columns not in the Stage database
GO
ALTER PROCEDURE syd_StagedColsNotInStage @ApplicationName VARCHAR(100)
,@StageDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT
AS
SET @SQL_str = '
SELECT
@StageDatabaseName,TableName, ColumnName
FROM
(
select
ct.TableName,ct.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.CustomColumn ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName
from
dbo.[Application] a
INNER JOIN dbo.StandardColumn st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name,Column_Name
FROM ' + @StageDatabaseName + '.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName=CD.Column_name
WHERE CD.Column_Name IS NULL'
--list stage columns whose data type is not same in the Source and Stage Database
GO
ALTER PROCEDURE syd_StagedCols_DT_Differ_SourceCols
@ApplicationName VARCHAR(100)
,@SourceDatabaseName VARCHAR(100)
,@SQL_str NVARCHAR(max) OUT AS
SET @SQL_str = '
SELECT
@SourceDatabaseName,TableName,ColumnName
FROM
(
select
ct.TableName, ct.ColumnName, ct.DataType
from
dbo.[Application] a
INNER JOIN dbo.CustomColumn ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,st.ColumnName, st.DataType
from
dbo.[Application] a
INNER JOIN dbo.StandardColumn st
ON a.ApplicationId=st.ApplicationId
AND a.ApplicationName=@ApplicationName
) StageColumns
LEFT JOIN
(
SELECT
Table_Name, Column_Name, Data_Type
FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.columns
) CD
ON StageColumns.ColumnName = CD.Column_name AND
WHERE CD.Column_Name IS NOT NULL AND
StageColumns.DataType <> CD.Data_Type'
GO
--Following code has extracted from "StagesColumns.txt"
--It gives only ONE ROW as ResultSet
--It contains SourceDatabaseName in the Variable @SourceDatabaseName and
-- StageDatabaseName in the Variable @StageDatabaseName
==========================================
--<<<<<<<<<<<<<<<<<<<<<< Extracted by Syd >>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- *******************************************************************
-- Finding names of Source and Stage Databases
-- *******************************************************************
use biCentral
GO
SET NOCOUNT ON
GO
--<<< ADDed by Syd >>>
-----------------------
-- LOG table
-----------------------
CREATE TABLE syd_LogTable (
L_database nvarchar(128),
L_table nvarchar(128),
L_column nvarchar(128),
L_message nvarchar(128)
);
GO
DECLARE
@ApplicationName VARCHAR(100)='CD'
, @ClientName VARCHAR(100)='ACHP'
, @ClientID INT
, @nMColum1 NVARCHAR(max) ---<<<< Syd: Added
, @nMColum2 NVARCHAR(max)
, @nMColum3 NVARCHAR(max)
, @nMColum4 NVARCHAR(max)
, @nMColum5 NVARCHAR(max) --- Syd: Added >>>>>>
, @SourceDatabaseName VARCHAR(100)
, @StageDatabaseName VARCHAR(100)
SELECT
@SourceDatabaseName=(SELECT DatabaseName FROM [BICentral].[dbo].DatabaseLocation WHERE DatabaseID=ca.SourceDatabaseID)
, @StageDatabaseName=(SELECT DatabaseName FROM [BICentral].[dbo].DatabaseLocation WHERE DatabaseID=ca.TargetDatabaseID)
FROM
[BICentral].[dbo].[Client] c
INNER JOIN [BICentral].[dbo].[ClientApplication] ca
ON c.ClientId=ca.ClientId
AND c.ClientName=@ClientName --->Syd: Extracting one set of Src and Tgt databases
INNER JOIN [BICentral].[dbo].[Application] a ---> for One Client "ACHP"
ON ca.ApplicationId=a.ApplicationId
AND a.[ApplicationName] =@ApplicationName ---> with one Application "CD"
--> Syd: Making SQL strings Ready
-----------------------------------
EXEC syd_StagedTablesNotInSource --->Syd: Check these Stored Procedures after the following
@ApplicationName, --- Validation Procedures
@SourceDatabaseName,
@SQL_str = @nMColumn1 OUT;
EXEC syd_StagedTablesNotInStage
@ApplicationName,
@StageDatabaseName,
@SQL_str = @nMColumn2 OUT;
EXEC syd_StagedColsNotInSource
@ApplicationName,
@SourceDatabaseName,
@SQL_str = @nMColumn3 OUT;
EXEC syd_StagedColsNotInStage
@ApplicationName,
@StageDatabaseName,
@SQL_str = @nMColumn4 OUT;
EXEC syd_StagedCols_DT_Differ_SourceCols
@ApplicationName,
@SourceDatabaseName,
@SQL_str = @nMColumn5 OUT;
--> Syd: Messages to Log Table
-------------------------------
EXEC usp_PreStageValidation --->Syd: Executing Final Procedure
@nMColumn1,
@nMColumn2,
@nMColumn3,
@nMColumn4,
@nMColumn5;
GO
--> Syd: Messages to SQL Console
---------------------------------
SELECT * FROM syd_LogTable;
GO
--> Syd: Creating final requested POST Validation Procedure
-------------------------------------------------------------
CREATE PROCEDURE usp_PostStageValidation
@StageDatabaseName VARCHAR(100),
@SourceDatabaseName VARCHAR(100)
AS
BEGIN
DECLARE
@STR nvarchar(1000)
, @cat nvarchar(128)
, @tbl nvarchar(128)
, @col nvarchar(128)
, @msg nvarchar(128)
, @RecCount int
, @ndx int;
CREATE TABLE #temp_table(
RowId int IDENTITY(1, 1), --IDENTITY column to identify each row in WHILE LOOP
cat nvarchar(128),
tbl nvarchar(128),
col nvarchar(128)
);
SET @STR = '
SELECT a.tablename,a.rowcnt
from
(
SELECT sc.name + ''.'' + ta.name TableName
,SUM(pa.rows) RowCnt
FROM ' +@StageDatabaseName +'.sys.tables ta
INNER JOIN ' +@StageDatabaseName +'.sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN ' +@StageDatabaseName +'.sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id < 2
GROUP BY sc.name,ta.name) as a
LEFT OUTER JOIN
(
SELECT sc.name + ''.'' + ta.name TableName
,SUM(pa.rows) RowCnt
FROM ' +@SourceDatabaseName +'.sys.tables ta
INNER JOIN ' +@SourceDatabaseName +'.sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN ' +@SourceDatabaseName +'.sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id < 2
GROUP BY sc.name,ta.name) as b
ON a.tablename = b.tablename
WHERE a.rowcnt <> b.rowcnt
';
--Fill Temporary Table with records of 1st Query
INSERT INTO #temp_table(cat, tbl)
EXEC sp_executeSQL @STR;
--After INSERT the system variable @@ROWCOUNT contains count of total records
--now Loop through each record of Temporary table
SET @RecCount = @@ROWCOUNT;
SET @ndx = 1;
WHILE @ndx <= @RecCount
BEGIN
SELECT @cat = @db1, @tbl = TableName
FROM #temp_table
WHERE RowId = @ndx; --Extract only one record based on Loop Index
@msg = 'This Stage table is not having same records as that of SOURCE table';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,NULL,@msg); --add an entry in LOG table
SET @ndx = @ndx + 1;
END;
DROP TABLE #temp_table;
END;
If you double click on the error message in the Messages tab, it will take you to where the error is (or hopefully close to it).
Ya I was doing that but some of the errors I had no idea how to approach. I fixed some of the syntax errors but I dont know what to do with the others.
• Do all staged tables exist in the source and in the stage database?
• Do all staged columns exist in the source and in the stage database?
• Is the data type of all staged columns the same in the source and stage database?
• Does the tbPlanStartEnd table exist in the source database, if not, create it.
• Is the tbPlanStartEnd table populated in the source database, if not, populate it.
Viewing 15 posts - 46 through 60 (of 137 total)
You must be logged in to reply to this topic. Login to reply