August 8, 2012 at 8:27 pm
Jeff Moden (8/8/2012)
morepainot (8/8/2012)
I showed one of the senior guys the code and they said the same thing you said.??? :blink:I thought you said you didn't have anyone who could help.
No...I said they were busy with their own tasks so they werent to always help me. I didnt want to lean on them too much for help. I asked a few things and figured out the rest myself. And witht the help of you guys too.
August 9, 2012 at 7:14 am
morepainot (8/8/2012)
Lynn Pettis (8/8/2012)
Turns out the space may not matter (tested here at home), but the parens violated the ALTER TABLE syntax.Regarding the space, it may really come down to being consistent when writing your queries. Personally, I prefer not seeing them there as it just looks "wrong."
Ok, thanks, ill try it out at the office. I showed one of the senior guys the code and they said the same thing you said. I will format the code once I figure out this syntax mess. I have to go through the entire code to format it and comment the print commands. I still have a good amount to do, thankfully its just cleaning up.
Hey, Good Morning. I took the parenthesis out and the code works. I compiled the code all the way through, tested it and it works just fine now. Thank you for showing me that. I really appreciate it.
August 10, 2012 at 7:41 am
I had my code review...it didnt go so well. The revisions I need to make are;
If StandardColumns has no list of columns for a table, then all the source columns must be in the stage database table.
In WHERE clause JOIN always fully qualify Schema, table & column
Table must be flagges as "active" otherwise do not include in staging validation.
If anyone can help me out with those, that would be great. Here is the code;
ALTER PROCEDURE [dbo].[usp_PreStageValidation]
(
@ClientName VARCHAR(100)
,@ApplicationName VARCHAR(100)
)
/* ====================================================================================================
** Date Created: 2012-08-01
** Created By:
** Purpose: To list the potential staging problems before the staging process begins
and where possible, automatically correct potential problems.
** Modify_dateModified_byWork item idDescription
** -------------------------------------------------------------------------------------------------
**
======================================================================================================*/
AS
SET NOCOUNT ON --this prevents record counts from being printed to the console
SET XACT_ABORT ON --if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back
BEGIN TRY
DECLARE
@ClientID INT
,@ApplicationID INT
,@nsql NVARCHAR(max)
,@SourceDatabaseName VARCHAR(100)
,@StageDatabaseName VARCHAR(100)
,@MessageText VARCHAR(1000)
,@OverallStartDateTime DATETIME = GETDATE()
,@StartDateTime DATETIME = GETDATE()
,@ElapsedMilisecond BIGINT = 0
,@ErrorCount INT=0
, @RowCount INT
DECLARE @Messages TABLE -- used to collect messages along the way
(
MessageId INT IDENTITY(1,1)
,MessageText VARCHAR(1000)
,isError BIT DEFAULT(1)
,ElapsedMilisecond BIGINT DEFAULT(0)
)
SELECT
@ClientId=ClientId
FROM dbo.Client
WHERE ClientName=@ClientName
SELECT
@ApplicationId=a.ApplicationId
,@SourceDatabaseName=(SELECT DatabaseName FROM dbo.DatabaseLocation WHERE DatabaseID=ca.SourceDatabaseID)
,@StageDatabaseName=(SELECT DatabaseName FROM dbo.DatabaseLocation WHERE DatabaseID=ca.TargetDatabaseID)
FROM
dbo.Client c
INNER JOIN dbo.ClientApplication ca
ON c.ClientId=ca.ClientId
AND c.ClientName=@ClientName
INNER JOIN dbo.Application a
ON ca.ApplicationId=a.ApplicationId
AND a.ApplicationName =@ApplicationName
IF @ClientID IS NULL
BEGIN
SET @MessageText=OBJECT_NAME(@@procid)+': Client Name: '+@ClientName+' is invalid'
INSERT INTO @Messages
(MessageText)
VALUES
(@MessageText)
RAISERROR
( @MessageText
,11 -- severity higher than 10 will exit the TRY block imediately, to prevent other false errors from being reported
,1
)
END
IF @ApplicationId IS NULL
BEGIN
SET @MessageText=OBJECT_NAME(@@procid)+' Application Name: '+@ApplicationName+' is invalid for Client Name: '+@ClientName
INSERT INTO @Messages
(MessageText)
VALUES
(@MessageText)
RAISERROR
( @MessageText
,11
,1
)
END
SET @MessageText='START: '+OBJECT_NAME(@@procid)+': for Client: '+ @ClientName + ' Application: ' +@ApplicationName
SET @ElapsedMilisecond = DATEDIFF(MS,@StartDateTime,GETDATE())
INSERT INTO @Messages
(MessageText,isError)
VALUES
(@MessageText,0)
-- *******************************************************************
-- Does the SOURCE database exist?
-- *******************************************************************
-- if the SOURCE database is not defined
IF @SourceDatabaseName IS NULL
INSERT INTO @Messages
(MessageText)
VALUES
('No SOURCE database is defone for ClientName:'+ @ClientName + ' ApplicationName: ' +@ApplicationName)
-- if the SOURCE database does not exist
IF NOT EXISTS (SELECT Name FROM SYS.DATABASES WHERE Name = @SourceDatabaseName )
BEGIN
SET @MessageText= 'SOURCE database: '+@SourceDatabaseName+' does not exist'
INSERT INTO @Messages
(MessageText)
VALUES
(@MessageText)
RAISERROR( @MessageText,11,1 )
END
-- *******************************************************************
-- Does the STAGE database exist?
-- *******************************************************************
-- if the STAGE database is not defined
IF @StageDatabaseName IS NULL
INSERT INTO @Messages
(MessageText)
VALUES
('No STAGE database is defone for ClientName:'+ @ClientName + ' ApplicationName: ' +@ApplicationName)
-- if the STAGE database does not exist
IF NOT EXISTS (SELECT Name FROM SYS.DATABASES WHERE Name = @StageDatabaseName )
BEGIN
SET @MessageText= 'STAGE database: '+@StageDatabaseName+' does not exist'
INSERT INTO @Messages
(MessageText)
VALUES
(@MessageText)
RAISERROR( @MessageText,11,1 )
END
-- *******************************************************************
-- Do all staged tables exist in the source and in the stage database?
-- *******************************************************************
-- 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
) StageTableList
LEFT JOIN
(
SELECT
Table_name
FROM '+@SourceDatabaseName+'.INFORMATION_SCHEMA.tables
) SOURCE
ON StageTableList.tableName=SOURCE.Table_name
WHERE SOURCE.Table_name IS NULL'
--print @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
--print 'Completed Step: 1'
-- 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
) StageTableList
LEFT JOIN
(
SELECT
Table_name
FROM '+@StageDatabaseName+'.INFORMATION_SCHEMA.tables
) STAGE
ON StageTableList.tableName=STAGE.Table_name
WHERE STAGE.Table_name IS NULL'
--print @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
--print 'Completed Step:2'
-- ********************************************************************
-- Do all staged columns exist in the source and in the stage database?
--*********************************************************************
-- list stage columns not in the STAGE database
SET @nsql = '
SELECT
ColumnName + '' is missing from the ' + @StageDatabaseName + ' STAGE database''
FROM
(
select
ct.TableName,ctc.ColumnName
from
dbo.Application a
INNER JOIN dbo.CustomTables ct
ON a.ApplicationId=ct.ApplicationId
INNER JOIN dbo.Client c
ON ct.ClientId=c.ClientId
INNER JOIN dbo.CustomTableColumns ctc
ON ct.CustomTableId=ctc.CustomTableId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,stc.ColumnName
from
dbo.Application a
INNER JOIN dbo.StandardTables st
ON a.ApplicationId=st.ApplicationId
INNER JOIN dbo.StandardTableColumns stc
ON st.StandardTableID=stc.StandardTableID
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 @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
--print 'Completed Step:3'
-- list stage columns not in the source database--
SET @nsql = '
SELECT
ColumnName + '' is missing from the ' + @SourceDatabaseName + ' SOURCE database''
FROM
(
select
ct.TableName,ctc.ColumnName
from
dbo.Application a
INNER JOIN dbo.CustomTables ct
ON a.ApplicationId=ct.ApplicationId
INNER JOIN dbo.Client c
ON ct.ClientId=c.ClientId
INNER JOIN dbo.CustomTableColumns ctc
ON ct.CustomTableId=ctc.CustomTableId
AND a.ApplicationName=@ApplicationName
UNION
select
st.TableName,stc.ColumnName
from
dbo.Application a
INNER JOIN dbo.StandardTables st
ON a.ApplicationId=st.ApplicationId
INNER JOIN dbo.StandardTableColumns stc
ON st.StandardTableID=stc.StandardTableID
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 @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
--print 'Completed step:4'
-- *******************************************************************
--Is the data type of all staged columns the same in the source and stage database?
-- *******************************************************************
SET @nsql = '
SELECT
''Data_Type of Column''+ ColumnName + ''Of the Table'' + TableName + ''is not same as its SOURCE''
FROM
(
SELECT
ct.TableName,ctc.ColumnName
FROM
dbo.Application a
JOIN CustomTables ct
on a.ApplicationID = ct.ApplicationID
AND a.ApplicationName=@ApplicationName
INNER JOIN dbo.CustomTableColumns ctc
ON ct.CustomTableID = ctc.CustomTableID
UNION
SELECT
st.TableName,stc.ColumnName
FROM
dbo.Application a
JOIN StandardTables st
on a.ApplicationID = st.ApplicationID
AND a.ApplicationName=@ApplicationName
AND st.ActiveInd = '' ''
INNER JOIN dbo.StandardTableColumns stc
ON st.StandardTableID = stc.StandardTableID
) StageColumns
JOIN CD_Stage_NPHP.INFORMATION_SCHEMA.columns inf_col
on StageColumns.TableName = inf_col.TABLE_NAME
AND StageColumns.ColumnName = inf_col.COLUMN_NAME
WHERE inf_col.Column_Name IS NULL ' --AND
-- ----StageColumns.DataType <> inf_col.Data_Type'
--print @nsql
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
--print 'completed step:5'
-- *******************************************************************
-- Does the tbPlanStartEnd table exist in the source database
--also in ELSE part
-- *******************************************************************
-- Is the tbPlanStartEnd table populated in the source database
SET @nSQL = '
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = '+ @SourceDatabaseName + ' AND
TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanStartEnd'' )
INSERT INTO @Messages
(MessageText)
VALUES
(''the table tbPlanStartEnd does not exist in Source Database'')
ELSE
BEGIN
SELECT @RowCount = COUNT(*) FROM tbPlanStartEnd;
IF @RowCount = 0
INSERT INTO @Messages
(MessageText)
VALUES
(''the table tbPlanStartEnd is not Populated with data in Source Database'')
END
';
EXEC sp_executeSQL @nSQL;
--print 'completed step:6'
-- *******************************************************************
-- Does the tbPlanGroup table exist in the source database
--also in ELSE part
-- *******************************************************************
-- Is the grp_name column in the tbPlanGroup table in the source database
--also in 2nd ELSE part
-- *******************************************************************
-- Is the tbPlanGroup table populated in the source database
SET @nSQL = '
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG =' + @SourceDatabaseName + ' AND
TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanGroup'' )
INSERT INTO @Messages
(MessageText)
VALUES
(''the table tbPlanGroup does not exist in Source Database'')
ELSE
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = ' + @SourceDatabaseName + ' AND
TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanGroup'' AND
COLUMN_NAME = ''grp_name'' )
INSERT INTO @Messages
(MessageText)
VALUES
(''the column grp_name of the table tbPlanGroup of Source Database does not exist'')
ELSE
BEGIN
SELECT @RowCount = COUNT(*) FROM tbPlanGroup;
IF @RowCount = 0
INSERT INTO @Messages
(MessageText)
VALUES
(''the table tbPlanGroup is not Populated with data in Source Database'')
END
';
EXEC sp_executeSQL @nSQL;
print @nSQL
-------------------------------------------
--Checking emptyness of table tbEnrollment
-------------------------------------------
SET @nSQL = '
SELECT @RowCount = COUNT(*) FROM ' + @SourceDatabaseName + 'dbo.tbEnrollment;
IF @RowCount = 0
INSERT INTO @Messages
(MessageText)
VALUES
(''the table tbEnrollment is not Populated with data in Source Database'')
';
EXEC sp_executeSQL @nSQL;
print @nSQL
--print 'completed step:7'
-- *******************************************************************
-- automatically handle problems if you can
-- *******************************************************************
-- Does the tbPlanStartEnd table exist in the source database, if not, CREATE it.
-- *******************************************************************
SET @nSQL = '
IF NOT EXISTS (SELECT * FROM ' + @SourceDatabaseName + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ''Dbo'' AND
TABLE_NAME = ''tbPlanStartEnd'') '+
'CREATE TABLE ' + @SourceDatabaseName + '.dbo.tbPlanStartEnd
(
TABLE_CATALOG NVARCHAR(128),
TABLE_SCHEMA NVARCHAR(128),
TABLE_NAME NVARCHAR (128),
COLUMN_NAME NVARCHAR(128),
ORDINAL_POSITION INT,
COLUMN_DEFAULT NVARCHAR(MAX),
IS_NULLABLE VARCHAR(3),
DATA_TYPE NVARCHAR(128),
CHARACTER_MAXIMUM_LENGTH INT,
CHARACTER_OCTET_LENGTH INT,
NUMERIC_PRECISION TINYINT,
RADIX_NUMERIC_SCALE SMALLINT,
DATETIME_PRECISION SMALLINT,
CHARACTER_SET_CATALOG NVARCHAR(128),
CHARACTER_SET_SCHEMA NVARCHAR(128),
CHARACTER_SET_NAME NVARCHAR(128),
COLLATION_CATALOG NVARCHAR(128),
COLLATION_SCHEMA NVARCHAR(128),
COLLATION_NAME NVARCHAR(128),
DOMAIN_CATALOG NVARCHAR(128),
DOMAIN_SCHEMA NVARCHAR(128),
DOMAIN_NAME NVARCHAR(128),
);
';
EXEC sp_executeSQL @nSQL;
--PRINT @nSQL
--print 'completed step:8'
-- *******************************************************************
-- Does the tbPlanGroup table exist in the source database, if not, create it.
SET @nSQL = '
IF NOT EXISTS (SELECT * FROM '+ @SourceDatabaseName + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''Dbo'' AND
TABLE_NAME = ''tbPlanGroup'' AND
COLUMN_NAME = ''Grp_name'') '+
'CREATE TABLE ' + @SourceDatabaseName + '.dbo.tbPlanGroup
(
TABLE_CATALOG NVARCHAR(128),
TABLE_SCHEMA NVARCHAR(128),
TABLE_NAME NVARCHAR (128),
COLUMN_NAME NVARCHAR(128),
ORDINAL_POSITION INT,
COLUMN_DEFAULT NVARCHAR(MAX),
IS_NULLABLE VARCHAR(3),
DATA_TYPE NVARCHAR(128),
CHARACTER_MAXIMUM_LENGTH INT,
CHARACTER_OCTET_LENGTH INT,
NUMERIC_PRECISION TINYINT,
RADIX_NUMERIC_SCALE SMALLINT,
DATETIME_PRECISION SMALLINT,
CHARACTER_SET_CATALOG NVARCHAR(128),
CHARACTER_SET_SCHEMA NVARCHAR(128),
CHARACTER_SET_NAME NVARCHAR(128),
COLLATION_CATALOG NVARCHAR(128),
COLLATION_SCHEMA NVARCHAR(128),
COLLATION_NAME NVARCHAR(128),
DOMAIN_CATALOG NVARCHAR(128),
DOMAIN_SCHEMA NVARCHAR(128),
DOMAIN_NAME NVARCHAR(128),
);
';
EXEC sp_executeSQL @nSQL;
--PRINT @nSQL
--print 'completed step:9'
-- *******************************************************************
-- Is the grp_name column in the tbPlanGroup table in the source database, if not, add it.
SET @nSQL = '
IF NOT EXISTS (SELECT * FROM '+ @SourceDatabaseName + ' .INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo'' AND
TABLE_NAME = ''tbPlanGroup'' AND
COLUMN_NAME = ''Grp_name'') '+
' ALTER TABLE ' + @SourceDatabaseName + '.dbo.tbPlanGroup
(
ADD Group_Name VARCHAR(128)
);
';
--PRINT @nSQL
EXEC sp_executeSQL @nSQL;
--PRINT 'completed step: 10'
-- Record the elapsed time of the procedure
-- *******************************************************************
SET @MessageText='END: '+OBJECT_NAME(@@procid)+': for Client: '+ @ClientName + ' Application: ' +@ApplicationName
SET @ElapsedMilisecond = DATEDIFF(MS,@OverallStartDateTime, GETDATE())
INSERT INTO @Messages
(MessageText, isError, ElapsedMilisecond)
VALUES
(@MessageText,0, @ElapsedMilisecond)
--print 'completed step:11'
-- *******************************************************************
-- Store the messages in the LoadLog table
-- *******************************************************************
INSERT INTO dbo.LoadLog
(
ClientID
,ApplicationID
,TableName
,[Message]
,IsError
,LoadLogDate
,ElapasedMilisecond
)
SELECT
@ClientID
,@ApplicationID
,NULL --@TableName
,MessageText
,IsError
,GETDATE()
,ElapsedMilisecond
FROM @Messages
-- *******************************************************************
-- If there were any errors RAISERROR
-- *******************************************************************
SELECT @ErrorCount=COUNT(*) FROM @Messages WHERE isError=1
IF @ErrorCount>0
BEGIN
SET @MessageText='There were '+CAST(@ErrorCount as VARCHAR)+' errors in the pre-stage validation'
RAISERROR
(
@MessageText
,11 -- severity higher than 10 will exit the TRY block imediately
,1
);
END
--print 'completed step:12'
END TRY
BEGIN CATCH
DECLARE
@ErrorMessage NVARCHAR(4000)
,@ErrorSeverity INT
,@ErrorState INT
,@BriefMsg NVARCHAR (800)
SELECT
@ErrorMessage = ERROR_MESSAGE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@BriefMsg = CAST (ERROR_MESSAGE() AS NVARCHAR (500))
SET @MessageText='ABORTED: '+OBJECT_NAME(@@procid)+': '+ @BriefMsg
EXEC dbo.usp_LoadLog_Insert @Message=@MessageText, @IsError=1
RAISERROR
(
@ErrorMessage
,@ErrorSeverity
,@ErrorState
)
END CATCH
--print 'completed: final step'
Viewing 3 posts - 136 through 137 (of 137 total)
You must be logged in to reply to this topic. Login to reply