July 29, 2012 at 11:21 am
Wow, thanks a lot Jeff!I know you advised me against looping from the beginning but after I talked to one of their senior developers he said looping was "the way to go". When it came to demo my code they HATED it.
Just call it a "hunch" based on experience. I pretty much figured that if they hated cursors, they'd also hate While Loops no matter what they said about how to replace them.
Be advised that some folks use the term "cursor" to include any form of looping/RBAR whether it's actually a cursor, a While Loop, and even some forms of code that look set based but are not such as a Triangular Join and certain types of recursive CTEs (rCTE). You can find out more about both in the following articles.
http://www.sqlservercentral.com/articles/T-SQL/61539/
http://www.sqlservercentral.com/articles/T-SQL/74118/
Just to emphasze... not all rCTEs are bad. The ones that use single row counting (like those in the article I provided a link to) should probably be avoided at all costs. There are just too many other better ways to do such a thing.
[edit] Shifting gears, I'm still concerned that your proc executes code passed as parameters. It smacks of an outside RBAR process and leaves you wide open for massive SQL Injection attacks by hackers. If you'd provide more pertinent information about the process that calls the proc, someone may be able to help you there, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2012 at 8:29 am
Jeff Moden (7/29/2012)
Wow, thanks a lot Jeff!I know you advised me against looping from the beginning but after I talked to one of their senior developers he said looping was "the way to go". When it came to demo my code they HATED it.
Just call it a "hunch" based on experience. I pretty much figured that if they hated cursors, they'd also hate While Loops no matter what they said about how to replace them.
Be advised that some folks use the term "cursor" to include any form of looping/RBAR whether it's actually a cursor, a While Loop, and even some forms of code that look set based but are not such as a Triangular Join and certain types of recursive CTEs (rCTE). You can find out more about both in the following articles.
http://www.sqlservercentral.com/articles/T-SQL/61539/
http://www.sqlservercentral.com/articles/T-SQL/74118/
Just to emphasze... not all rCTEs are bad. The ones that use single row counting (like those in the article I provided a link to) should probably be avoided at all costs. There are just too many other better ways to do such a thing.
[edit] Shifting gears, I'm still concerned that your proc executes code passed as parameters. It smacks of an outside RBAR process and leaves you wide open for massive SQL Injection attacks by hackers. If you'd provide more pertinent information about the process that calls the proc, someone may be able to help you there, as well.
Alright Jeff, thanks! Im here back at the office and I am trying to change the loops into the code you have given me. I cant seem to understand how to do that. My SQL skills are still developing, Im fairly new to all of this. My actual role here is supposed to be for data integration, creating SSIS packages. I understand this code is important to the integration process but I have written this 2 times already and neither has been right. I know this is asking a lot but can you help me with the rest of code? I will send you any details you need. Along with PreStage_Validation, I also have to creat PostStage_Validation. I will greatly appreciate the help you can provide me. Thanks again.
July 30, 2012 at 8:46 am
Alright Jeff, thanks! Im here back at the office and I am trying to change the loops into the code you have given me. I cant seem to understand how to do that. My SQL skills are still developing, Im fairly new to all of this. My actual role here is supposed to be for data integration, creating SSIS packages. I understand this code is important to the integration process but I have written this 2 times already and neither has been right. I know this is asking a lot but can you help me with the rest of code? I will send you any details you need. Along with PreStage_Validation, I also have to creat PostStage_Validation. I will greatly appreciate the help you can provide me. Thanks again.
Ok the irony is too much for me now. You were contracted by this company to come in and fix their code. It is readily apparent you in way over your head and you are now asking for people online to provide you the solutions (at no cost) for the work that you are going to get paid for. I can't speak for the others but I am not here to do your work for you. The one that Jeff already graciously created for you should be enough of an example for you to rework the others.
I am happy to help with specific questions if they come up.
_______________________________________________________________
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/
July 30, 2012 at 8:51 am
Sean Lange (7/30/2012)
Alright Jeff, thanks! Im here back at the office and I am trying to change the loops into the code you have given me. I cant seem to understand how to do that. My SQL skills are still developing, Im fairly new to all of this. My actual role here is supposed to be for data integration, creating SSIS packages. I understand this code is important to the integration process but I have written this 2 times already and neither has been right. I know this is asking a lot but can you help me with the rest of code? I will send you any details you need. Along with PreStage_Validation, I also have to creat PostStage_Validation. I will greatly appreciate the help you can provide me. Thanks again.
Ok the irony is too much for me now. You were contracted by this company to come in and fix their code. It is readily apparent you in way over your head and you are now asking for people online to provide you the solutions (at no cost) for the work that you are going to get paid for. I can't speak for the others but I am not here to do your work for you. The one that Jeff already graciously created for you should be enough of an example for you to rework the others.
I am happy to help with specific questions if they come up.
Ok Sean, I understand the irony behind all of this but yes I am in over my head. I have re-worked this code 2 times already but as you all know they werent happy, for reasons known to them, and need me to do it all over again. I am using Jeffs example but like I said before I am not in an expert or have as much as experience as you and Jeff, respectively. I have no problem with compensation. If someone provides me the help I need then they deserve some sort of compensation. Thanks, I will see what I can do with I was given but if either of you guys would be able to help a little extra that would be greatly appreciated.
July 31, 2012 at 8:01 am
morepainot (7/30/2012)
Im here back at the office......{snip}...
Ok Sean, I understand the irony behind all of this but yes I am in over my head. I have re-worked this code 2 times already but as you all know they werent happy, for reasons known to them, and need me to do it all over again. I am using Jeffs example but like I said before I am not in an expert or have as much as experience as you and Jeff, respectively. I have no problem with compensation. If someone provides me the help I need then they deserve some sort of compensation. Thanks, I will see what I can do with I was given but if either of you guys would be able to help a little extra that would be greatly appreciated.
First, kudos on understanding your limits. A whole lot of people just won't admit to being in over their head.
You mention "back at the office". Are you working on a team of developers for this type of thing? If so, can't they help?
You might also want to post the requirements that you PM'd me. I could certainly be wrong but they seem a whole lot different than what you're doing. Of course, I don't know what the SQL is that you're passing in the parameters (remember that I asked you for an example?).
Shifting gears, I would love to help you on this but I couldn't do justice to your project simply because my day job has me quite a bit overcommitted as it is.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2012 at 8:55 am
Jeff Moden (7/31/2012)
morepainot (7/30/2012)
Im here back at the office......{snip}...
Ok Sean, I understand the irony behind all of this but yes I am in over my head. I have re-worked this code 2 times already but as you all know they werent happy, for reasons known to them, and need me to do it all over again. I am using Jeffs example but like I said before I am not in an expert or have as much as experience as you and Jeff, respectively. I have no problem with compensation. If someone provides me the help I need then they deserve some sort of compensation. Thanks, I will see what I can do with I was given but if either of you guys would be able to help a little extra that would be greatly appreciated.
First, kudos on understanding your limits. A whole lot of people just won't admit to being in over their head.
You mention "back at the office". Are you working on a team of developers for this type of thing? If so, can't they help?
You might also want to post the requirements that you PM'd me. I could certainly be wrong but they seem a whole lot different than what you're doing. Of course, I don't know what the SQL is that you're passing in the parameters (remember that I asked you for an example?).
Shifting gears, I would love to help you on this but I couldn't do justice to your project simply because my day job has me quite a bit overcommitted as it is.
We're at the end of a deployement so all the developers on my team are busy with that. This task I was given is supposed to be my project to work on till I get some "real" work to do. Thank you for your help, I understand that you have a life outside of these forums and Im thankful for you taking time out to help. I sent you a code but I realized how wrong it was. I tried a different approach;
use biCentral
GO
SET NOCOUNT ON
GO
DECLARE
@ApplicationName VARCHAR(100)='CD'
, @ClientName VARCHAR(100)='ACHP'
, @ClientID INT
, @nsql NVARCHAR(max)
, @nsq2 NVARCHAR (max)
, @nsq3 NVARCHAR (max)
, @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
INNER JOIN [BICentral].[dbo].[Application] a
ON ca.ApplicationId=a.ApplicationId
-- *******************************************************************
-- list stage tables not in the source database
-- *******************************************************************
SET @nsql = '
SELECT
TableName+'' is missing from the '+@SourceDatabaseName+' SOURCE database''
FROM
(
select
ct.TableName
from
dbo.[Application] a
INNER JOIN dbo.CustomTables ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName
from
dbo.[Application] a
INNER JOIN dbo.StandardTables 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'
--PRINT @nsql
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
-- *******************************************************************
-- list stage tables not in the Stage database
-- *******************************************************************
SET @nsql = '
SELECT
TableName+'' is missing from the '+@StageDatabaseName+' STAGE database''
FROM
(
select
ct.TableName
from
dbo.[Application] a
INNER JOIN dbo.CustomTables ct
ON a.ApplicationId=ct.ApplicationId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName
from
dbo.[Application] a
INNER JOIN dbo.StandardTables 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'
--PRINT @nsql
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
-- *******************************************************************
-- list stage columns not in the source database
-- *******************************************************************
SET @nsq2 = '
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'
--PRINT @nsq2
EXEC sp_executeSQL @nsq2, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
-- *******************************************************************
-- list stage columns not in the Stage database
-- *******************************************************************
SET @nsq2 = '
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'
--PRINT @nsq2
EXEC sp_executeSQL @nsq2, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
-- *******************************************************************
-- list stage columns whose data type is not same in the Source and Stage Database
-- *******************************************************************
SET @nsq3 = '
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'
--PRINT @nsq3
EXEC sp_executeSQL @nsq3, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
August 1, 2012 at 7:36 am
I updated this query withouth using loops. Its the same stored procedure from before, just reworked. It would be great if you guys could give me some input, thanks.
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Syd: Following procedures are created First
--> Syd: Creating final requested Procedure
--------------------------------------------
CREATE 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
--->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
--->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');
ELSE
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; --end of usp_PreStageValidation procedure
--<<<<< Procedures which return Dynamic SQL for FIVE Queries --<<<<<<<<<<<<<<<<<<<<<< Added by Syd >>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- list stage Tables not in the source database
****************************************************CREATE 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'
--<<<<<<<<<<<<<<<<<<<<<< Added by Syd ****************************************************-- list stage Tables not in the Stage database
****************************************************CREATE 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'
====================================================
--<<<<<<<<<<<<<<<<<<<<<< Corrected by Syd ****************************************************-- list stage columns not in the source database
****************************************************CREATE 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'
--<<<<<<<<<<<<<<<<<<<<<< Corrected by Syd ****************************************************-- list stage columns not in the Stage database
****************************************************CREATE 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'
============================================================================================
--<<<<<<<<<<<<<<<<<<<<<< Added by Syd ****************************************************-- list stage columns whose data type is not same in the Source and Stage Database ****************************************************CREATE 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'
August 1, 2012 at 8:06 am
I updated this query withouth using loops. Its the same stored procedure from before, just reworked. It would be great if you guys could give me some input, thanks.
Well we can't really test anything but this looks a LOT better. How is the performance?
There are a couple things that I would reject if this were on my system. The first is the sql injection vulnerability. You receive sql as a parameter and then execute it.
The other is that this proc is too tightly coupled with whatever calls it. You make the assumption that whatever calls it is going to have a specific number of columns in a certain order in the dynamic sql it passes. I say that because you create a temp table and then insert into it using the sql string passed in. I don't know the process or anything like so I can't speak to the details but that seems pretty optimistic that it will always work.
Given the dynamic nature of this sproc I would also require there to be some level of error handling (this may be ok because it is handled in the process that calls this).
What you have is a MASSIVE improvement over the cursor/looping you had originally. Assuming this produces the desired results I think you have something you can take back to code review 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 8:14 am
Sean Lange (8/1/2012)
What you have is a MASSIVE improvement over the cursor/looping you had originally. Assuming this produces the desired results I think you have something you can take back to code review again.
Alright! Thats really great to hear. But the performance, I dont know. Im trying to execute this script but I have errors that I cant seem to figure out. From looking at the code, could you tell me what you think the errors may be? I have a few syntax errors and the rest are scalar vaiable errors. How should I approach those? Also, if you see the code, theres some notes that require me to create a table. Funny thing is that I know I have to create that table but I dont how what columns to add. I know that sounds really dumb, but remember I am really new to all of this. Thanks for your input so far!
August 1, 2012 at 8:32 am
morepainot (8/1/2012)
From looking at the code, could you tell me what you think the errors may be? I have a few syntax errors and the rest are scalar vaiable errors. How should I approach those? Also, if you see the code, theres some notes that require me to create a table. Funny thing is that I know I have to create that table but I dont how what columns to add. I know that sounds really dumb, but remember I am really new to all of this. Thanks for your input so far!
Well that just isn't really very feasible. We don't have any of these tables and the compiler is far better at telling you what is wrong than my eyes. It does appear that you have at least one BEGIN without an END. You don't have a GO between procs.
The code as you posted it is a mess and pretty much not readable. When posting code blocks you should use the IFCode shortcuts (over on the left when posting). they will make your really long code posts legible.
I passed your code through a free online formatter at http://poorsql.com
Here is what that looks like now (notice the nice code window).
CREATE 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
--->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
--->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'
);ELSE
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;--end of usp_PreStageValidation procedure
--<<<<< Procedures which return Dynamic SQL for FIVE Queries --<<<<<<<<<<<<<<<<<<<<<< Added by Syd >>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- list stage Tables not in the source database
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
CREATE 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'
--<<<<<<<<<<<<<<<<<<<<<< Added by Syd ****************************************************-- list stage Tables not in the Stage database
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
CREATE 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' = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--<<<<<<<<<<<<<<<<<<<<<< Corrected by Syd ****************************************************-- list stage columns not in the source database
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
CREATE 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'
--<<<<<<<<<<<<<<<<<<<<<< Corrected by Syd ****************************************************-- list stage columns not in the Stage database
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
CREATE 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' = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--<<<<<<<<<<<<<<<<<<<<<< Added by Syd ****************************************************-- list stage columns whose data type is not same in the Source and Stage Database ****************************************************CREATE 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'
This is a bit easier on the eyes but as you said there are still a bunch of errors in here.
_______________________________________________________________
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:00 am
I add GO before every proc? Its giving me an incorrect syntax error right about it where the comment lines are.
GO
CREATE 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'
August 1, 2012 at 9:06 am
morepainot (8/1/2012)
I add GO before every proc? Its giving me an incorrect syntax error right about it where the comment lines are.
GOCREATE 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'
This compiles just fine for me. I suspect you mean you are getting errors when running the whole thing? There are errors in the formatted version I posted. Some of them are because you have comment lines without -- at the beginning etc.
_______________________________________________________________
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:11 am
Ok, I took care of those errors. Now the only errors I am getting are
"Msg 2714, Level 16, State 3, Procedure usp_PreStageValidation, Line 185
There is already an object named 'usp_PreStageValidation' in the database."
Im guessing thats because I am running the code?
August 1, 2012 at 9:16 am
Took the code from above, added GO batch separators between the CREATE PROCEDURE statements.
Also, again, when posting code please use the IFCode [ code="sql" ]/[ /code ] shortcuts (WITHOUT the spaces inside the square brackets) around your code blocks.
CREATE 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
--->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
--->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'
);ELSE
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;--end of usp_PreStageValidation procedure
--<<<<< Procedures which return Dynamic SQL for FIVE Queries --<<<<<<<<<<<<<<<<<<<<<< Added by Syd >>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- list stage Tables not in the source database
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
GO
CREATE 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'
--<<<<<<<<<<<<<<<<<<<<<< Added by Syd ****************************************************-- list stage Tables not in the Stage database
--* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
GO
CREATE 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' = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--<<<<<<<<<<<<<<<<<<<<<< Corrected by Syd ****************************************************-- list stage columns not in the source database
--* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
GO
CREATE 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'
--<<<<<<<<<<<<<<<<<<<<<< Corrected by Syd ****************************************************-- list stage columns not in the Stage database
--* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
GO
CREATE 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' = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--<<<<<<<<<<<<<<<<<<<<<< Added by Syd ****************************************************
-- list stage columns whose data type is not same in the Source and Stage Database ****************************************************
GO
CREATE 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
August 1, 2012 at 9:18 am
morepainot (8/1/2012)
Ok, I took care of those errors. Now the only errors I am getting are"Msg 2714, Level 16, State 3, Procedure usp_PreStageValidation, Line 185
There is already an object named 'usp_PreStageValidation' in the database."
Im guessing thats because I am running the code?
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.
Viewing 15 posts - 31 through 45 (of 137 total)
You must be logged in to reply to this topic. Login to reply