August 1, 2012 at 12:33 pm
Oh man looks like Im going to have trouble with this review tomorrow. I have a developer who is remotely looking at ny code but hes the type the guy who knows where youre making the mistake but wont help you or even give you suggestions. I have about 4 hours left till the office closes to finish up this code. Is it possible?
August 1, 2012 at 12:33 pm
•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.
Those would be a great place to start with for comments. It seems there are a few steps missing though. So far all that has happened is to populate a single table. Or is this entire process for a single table?
From the code you posted I don't think you are doing all those steps?
_______________________________________________________________
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:35 pm
morepainot (8/1/2012)
•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.
This is very high level and a good start, but it doesn't help me understand what the procedure is doing and why.
What are the inputs to the procedure? Looking at the code it appears to be SQL code that needs to be executed. What is this code?
How do you identify the source and stage databases? Is this the information passed in by the SQL code that you have to execute?
What is tbPlanStartEnd and what type of data does it contain?
August 1, 2012 at 12:36 pm
Sean Lange (8/1/2012)
•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.
Those would be a great place to start with for comments. It seems there are a few steps missing though. So far all that has happened is to populate a single table. Or is this entire process for a single table?
From the code you posted I don't think you are doing all those steps?
Yeah, the code is supposed to do all of that. In the results tab I do see a table being formed but without any data. I dont have 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. part covered but I think everything else is being done from the code.
August 1, 2012 at 12:37 pm
morepainot (8/1/2012)
Oh man looks like Im going to have trouble with this review tomorrow. I have a developer who is remotely looking at ny code but hes the type the guy who knows where youre making the mistake but wont help you or even give you suggestions. I have about 4 hours left till the office closes to finish up this code. Is it possible?
Not from what I have seen. Don't mean to sound negative but I am not really sure you understand what this process is supposed to do. If you don't know what it is supposed to do there is no chance you have of getting the code right. And there is even less chance of somebody else helping you get it there. Don't give up the ship though. You may have a better understanding than you are able to convey online.
You have a lot of cleanup to do 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 12:39 pm
Sean Lange (8/1/2012)
morepainot (8/1/2012)
Oh man looks like Im going to have trouble with this review tomorrow. I have a developer who is remotely looking at ny code but hes the type the guy who knows where youre making the mistake but wont help you or even give you suggestions. I have about 4 hours left till the office closes to finish up this code. Is it possible?Not from what I have seen. Don't mean to sound negative but I am not really sure you understand what this process is supposed to do. If you don't know what it is supposed to do there is no chance you have of getting the code right. And there is even less chance of somebody else helping you get it there. Don't give up the ship though. You may have a better understanding than you are able to convey online.
You have a lot of cleanup to do in here.
I probably cant convey it online. This is the EXACT email I recieved from the director who wanted the code from me. These are the exact details.
Purpose:
To list the potential staging problems before the staging process begins and where possible, automatically correct potential problems.
List of list the potential staging problems
•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.
List of list the potential staging problems to automatically correct when found
•Does the tbPlanGroup table exist in the source database, if not, create it.
•Is the grp_name column in the tbPlanGroup table in the source database, if not, add it.
•Is the tbPlanGroup table populated in the source database, if not, populate it.
Ideas:
The BICentral.dbo.usp_CopyApplicationData procedure is used to stage data from a source database to a stage database. It does this by reading meta data in the following tables:
•StandardTableColumns
•StandardTableLocation
•StandardTables
•CustomTableColumns
•CustomTables
The source and stage database are listed in the dbo.DatabaseLocation table.
We don’t need to hard code the validation checks because we can use the meta data to dynamically check for potential problems. In addition, we can use SQL Server metadata in INFORMATION_SCHEMA.tables and INFORMATION_SCHEMA.columns
Create a new BICentral procedure called usp_PreStageValidation.
•For each of the potential problems, execute a SQL that lists tables that do not meet the condition specified. With the list of tables, with corresponding message to the log table and print the messages to the SQL console.
•For each of the potential problems that can be automatically corrected, make the correction but also insert a message to the log table and print the messages to the SQL console.
If unhandled errors are found, raise an error and tell the user to check the log table or printed messages for details.
August 1, 2012 at 1:00 pm
morepainot (8/1/2012)
Sean Lange (8/1/2012)
morepainot (8/1/2012)
Oh man looks like Im going to have trouble with this review tomorrow. I have a developer who is remotely looking at ny code but hes the type the guy who knows where youre making the mistake but wont help you or even give you suggestions. I have about 4 hours left till the office closes to finish up this code. Is it possible?Not from what I have seen. Don't mean to sound negative but I am not really sure you understand what this process is supposed to do. If you don't know what it is supposed to do there is no chance you have of getting the code right. And there is even less chance of somebody else helping you get it there. Don't give up the ship though. You may have a better understanding than you are able to convey online.
You have a lot of cleanup to do in here.
I probably cant convey it online. This is the EXACT email I recieved from the director who wanted the code from me. These are the exact details.
Purpose:
To list the potential staging problems before the staging process begins and where possible, automatically correct potential problems.
List of list the potential staging problems
•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.
List of list the potential staging problems to automatically correct when found
•Does the tbPlanGroup table exist in the source database, if not, create it.
•Is the grp_name column in the tbPlanGroup table in the source database, if not, add it.
•Is the tbPlanGroup table populated in the source database, if not, populate it.
Ideas:
The BICentral.dbo.usp_CopyApplicationData procedure is used to stage data from a source database to a stage database. It does this by reading meta data in the following tables:
•StandardTableColumns
•StandardTableLocation
•StandardTables
•CustomTableColumns
•CustomTables
The source and stage database are listed in the dbo.DatabaseLocation table.
We don’t need to hard code the validation checks because we can use the meta data to dynamically check for potential problems. In addition, we can use SQL Server metadata in INFORMATION_SCHEMA.tables and INFORMATION_SCHEMA.columns
Create a new BICentral procedure called usp_PreStageValidation.
•For each of the potential problems, execute a SQL that lists tables that do not meet the condition specified. With the list of tables, with corresponding message to the log table and print the messages to the SQL console.
•For each of the potential problems that can be automatically corrected, make the correction but also insert a message to the log table and print the messages to the SQL console.
If unhandled errors are found, raise an error and tell the user to check the log table or printed messages for details.
WOW no offense but you are nowhere even close with this process. You are not looking in any of the tables used in the COPY proc.
Your inserts to the log tables have no where clauses. You reuse the same temp table, fill it with hard coded values for every row returned in executed sql string, then immediately truncate it.
You are NOT handling any errors.
Are you familiar with flowcharts? I would recommend making one for this. The process as I see it in your code and the process described in the above email are totally different.
I have a meeting that will take me the rest of the day. Sorry I can't help anymore today.
_______________________________________________________________
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 1:54 pm
Here is some code for you to review. Ask yourself questions about the code, then try to answer them yourself by seeing what is happening.
I am using dynamic sql as I need to pull information from the INFORMATION_SCHEMA views from the respective databases. Personally, I would use the system views n the dynamic sql but the email you showed us seems to indicate that you need to use the INFORMATION_SCHEMA views. From what I can see, tese should provide you with the data you need.
Once you understand the code I have written, you should be able to start identifying what steps (if not the code) you need to compare columns between tables in the source and destination (stage) databases.
declare @DBTables table (
TABLE_CATALOG nvarchar(128) null,
TABLE_SCHEMA sysname null,
TABLE_NAME sysname not null,
TABLE_TYPE varchar(10)
);
declare @SQLCmd nvarchar(max),
@SourceDB sysname = N'sourceDB',
@DestinationDB sysname = N'StageDB';
/*
You could probably populate the Source and Destination like this:
select
@SourceDB = SourceDB, -- Not sure of column names in dbo.DatabaseLocation table
@DestinationDB = DestinationDB -- Not sure of column names in dbo.DatabaseLocation table
from
BICentral.dbo.DatabaseLocation
where
SomeColumn = SomeValue; -- Again, not sure what would go here
SomeValue should also be validated some how to ensure that it isn't subject to SQL Injection attacks
*/
set @SQLCmd =
'use ' + @SourceDB + ';
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from information_schema.tables where TABLE_TYPE = ''BASE TABLE'';
';
insert into @DBTables
exec (@SQLCmd);
set @SQLCmd =
'use ' + @DestinationDB + ';
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from information_schema.tables where TABLE_TYPE = ''BASE TABLE'';
';
insert into @DBTables
exec (@SQLCmd);
with
MissingStageTables (
TableName
) as (
-- Tables in @SourseDB not in @DestinationDB
select TABLE_SCHEMA + '.' + TABLE_NAME from @DBTables where TABLE_CATALOG = @SourceDB
except
select TABLE_SCHEMA + '.' + TABLE_NAME from @DBTables where TABLE_CATALOG = @DestinationDB
),
MissingSourceTables (
TableName
) as (
-- Tables in @DestinationDB not in @SourceDB
select TABLE_SCHEMA + '.' + TABLE_NAME from @DBTables where TABLE_CATALOG = @DestinationDB
except
select TABLE_SCHEMA + '.' + TABLE_NAME from @DBTables where TABLE_CATALOG = @SourceDB
)
select SourceDatabase, TableName, ErrorMessage
into #MissingTables
from (
select
@SourceDB as SourceDatabase,
TableName,
'Table missing from database: ' + @DestinationDB as ErrorMessage
from
MissingStageTables
union all
select
@DestinationDB,
TableName,
'Table missing from database: ' + @SourceDB as ErrorMessage
from
MissingSourceTables) dt
select * from #MissingTables;
-- Tables in @SourceDB and @DestinationDB
select TABLE_SCHEMA, TABLE_NAME from @DBTables where TABLE_CATALOG = @SourceDB
intersect
select TABLE_SCHEMA, TABLE_NAME from @DBTables where TABLE_CATALOG = @DestinationDB;
go
drop table #MissingTables;
go
August 1, 2012 at 1:57 pm
Also, this code is not what you should necessarily use directly. It should probably be extended and expanded to make use of your logging tables, etc. It is simply a starting point from which you can build.
August 1, 2012 at 2:07 pm
Lynn Pettis (8/1/2012)
Also, this code is not what you should necessarily use directly. It should probably be extended and expanded to make use of your logging tables, etc. It is simply a starting point from which you can build.
I have completely rebuilt what I had earlier. I have this incomplete but its where im starting from.
USE [BICentral]
GO
/****** Object: StoredProcedure [dbo].[usp_PreStageValidation_AC] Script Date: 08/01/2012 14:45:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-- TEST usp_PreStageValidation_AC using this SQL
DECLARE
@StartDate DATETIME = GETDATE()
EXEC dbo.usp_PreStageValidation_AC
@ClientName ='ACHP'
,@ApplicationName ='CD'
EXEC usp_LoadLog_Select @StartDate
*/
ALTER PROCEDURE [dbo].[usp_PreStageValidation_AC]
(
@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
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
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)
)
BEGIN TRY
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 -- severity higher than 10 will exit the TRY block imediately, to prevent other false errors from being reported
,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 )
INSERT INTO @Messages
(MessageText)
VALUES
('SOURCE database: '+@SourceDatabaseName+' does not exist')
-- *******************************************************************
-- 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 )
INSERT INTO @Messages
(MessageText)
VALUES
('STAGE database: '+@StageDatabaseName+' does not exist')
-- *******************************************************************
-- 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'
INSERT INTO @Messages
(MessageText)
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
) StageTableList
LEFT JOIN
(
SELECT
Table_name
FROM '+@StageDatabaseName+'.INFORMATION_SCHEMA.tables
) STAGE
ON StageTableList.tableName=STAGE.Table_name
WHERE STAGE.Table_name IS NULL'
INSERT INTO @Messages
(MessageText)
EXEC sp_executeSQL @nsql, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName
-- *******************************************************************
-- 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
-- *******************************************************************
-- *******************************************************************
-- Is the tbPlanStartEnd table populated in the source database
-- *******************************************************************
-- *******************************************************************
-- Does the tbPlanGroup table exist in the source database
-- *******************************************************************
-- *******************************************************************
-- Is the grp_name column in the tbPlanGroup table in the source database
-- *******************************************************************
-- *******************************************************************
-- Is the tbPlanGroup table populated in the source database
-- *******************************************************************
-- *******************************************************************
-- *******************************************************************
-- automatically handle problems if you can
-- *******************************************************************
-- *******************************************************************
IF @ApplicationName='CD'
BEGIN
SET @ApplicationName='CD' -- remove ths line of code after you add code below
-- *******************************************************************
-- 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.
-- *******************************************************************
-- *******************************************************************
-- Does the tbPlanGroup table exist in the source database, if not, create it.
-- *******************************************************************
-- *******************************************************************
-- Is the grp_name column in the tbPlanGroup table in the source database, if not, add it.
-- *******************************************************************
-- *******************************************************************
-- Is the tbPlanGroup table populated in the source database, if not, populate it.
-- *******************************************************************
END
-- *******************************************************************
-- 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)
-- *******************************************************************
-- 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
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
August 1, 2012 at 2:41 pm
Looking really good. You are on the right track for what you need to accomplish. I like the code you are writing, it is well structured, easy to read. It is much easier to follow what you are trying to accomplish.
August 1, 2012 at 2:45 pm
One question, do you need to report the error messages recorded if you enter the CATCH block?
August 1, 2012 at 3:08 pm
Lynn Pettis (8/1/2012)
Looking really good. You are on the right track for what you need to accomplish. I like the code you are writing, it is well structured, easy to read. It is much easier to follow what you are trying to accomplish.
+1
_______________________________________________________________
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 3:20 pm
Lynn Pettis (8/1/2012)
Looking really good. You are on the right track for what you need to accomplish. I like the code you are writing, it is well structured, easy to read. It is much easier to follow what you are trying to accomplish.
Thanks. Can you help me with the code that I still need to write? In the SQL if you scroll down to the comment blocks it tells you what type of code I need to write. Im not sure how to approach that. Whats the best way to finish the code?
August 1, 2012 at 3:26 pm
morepainot (8/1/2012)
Lynn Pettis (8/1/2012)
Looking really good. You are on the right track for what you need to accomplish. I like the code you are writing, it is well structured, easy to read. It is much easier to follow what you are trying to accomplish.Thanks. Can you help me with the code that I still need to write? In the SQL if you scroll down to the comment blocks it tells you what type of code I need to write. Im not sure how to approach that. Whats the best way to finish the code?
Not really. We don't have the necessary information readily available to write your code. Bottom line, we can't see from here what you see there.
You have it broken down well. Start small and work your way up. If something stumps you ask a question but be sure to give all the information needed for someone to be able to help. That means posting DDL (CREATE TABLE) statements, sample data (INSERT INTO statements), and expected results (should be visually oriented, not just a description, could even use a table (dbo.ExpectedResults) and "sample data" in the format it should be returned.
Viewing 15 posts - 61 through 75 (of 137 total)
You must be logged in to reply to this topic. Login to reply