March 22, 2017 at 2:11 pm
I am trying to create a procedure for logging the errors.
In all of my proceudres i am making generic proc for logging the errors.
Here is the new procedure -
CREATE PROCEDURE [dbo].[Log]
@ProcedureName varchar(100),
@ProcessName varchar(50),
@ActualColumn varchar(100),
@Error varchar(50),
@ErrorLine int,
@Description varchar(1000)
AS
INSERT INTO dbo.ErTable
(
ProcedureName, --
ProcessName,
ActualColumn,
Error,
ErrorLine,
Description,
CreatedDate
)
VALUES
(
ERROR_PROCEDURE(),
ERROR_NUMBER(),
ERROR_LINE(),
ERROR_MESSAGE(),
getdate()
)
In exisitng procedure i am calling the above procedure as like this- basically these two paramters are commin from procedure Main.then how to pass this values to dbo.Log procedure? DO i need to keep it as OUT param there ?
Create procedure dbo.Main
Exec Log(@ProcessName,@TN + ' - ' + @WDATE)
March 22, 2017 at 2:59 pm
I'm not sure what you mean by 2 parameters, it looks like your dbo.Log procedure has 6 parameters. You'd probably want to call this in some kind of TRY-CATCH block, such as in the example here:
https://msdn.microsoft.com/en-us/library/ms175976.aspx
Be careful about transactions though, if you have a transaction in the calling stored proc, you may want to get the attributes of the error and pass them into this Log proc, making sure to roll back the transaction before you call Log proc so your INSERT INTO dbo.ErTable doesn't get rolled back along with the failed transaction.
March 22, 2017 at 3:22 pm
TY
Resolved the issue..and we are using try catch block in main procedure
Quick question on the Active Column.
In the main procedure I have declared variable called KValue
Data type for WDATE is DATETME and TN is varchar(11)
DECLARE @KValue varchar(100)
SET @KValue = @TN + ' - ' + Weekend: CAST(@WDATE AS VARCHAR(50) + ID:@GID)
Exec InsertLogError @ProcessName, @KValue
I am looking the data in this format - AJ8574773, Weekend: 03/24/2016, ID: 1838474
But in the Ertable i am getting as AJ8574773- March 24 2016 12:00AM
May I know how to cast it to get in this format - AJ8574773, Weekend: 03/24/2016, ID: 1838474)
March 22, 2017 at 3:24 pm
I don't understand the question here at all. But as posted your main procedure is going to fail because your log procedure requires 6 parameters and you have passed it 2. You are going to have to provide more details before anybody can offer much more than blind guesses.
_______________________________________________________________
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/
March 22, 2017 at 3:27 pm
mcfarlandparkway - Wednesday, March 22, 2017 3:22 PMTY
Resolved the issue..and we are using try catch block in main procedureQuick question on the Active Column.
In the main procedure I have declared variable called KValue
Data type for WDATE is DATETME and TN is varchar(11)DECLARE @KValue varchar(100)
SET @KValue = @TN + ' - ' + Weekend: CAST(@WDATE AS VARCHAR(50) + ID:@GID)
Exec InsertLogError @ProcessName, @KValue
I am looking the data in this format - AJ8574773, Weekend: 03/24/2016, ID: 1838474
But in the Ertable i am getting as AJ8574773- March 24 2016 12:00AMMay I know how to cast it to get in this format - AJ8574773, Weekend: 03/24/2016, ID: 1838474)
Look at CONVERT if you want your dates styled a certain way. https://msdn.microsoft.com/en-us/library/ms187928.aspx
_______________________________________________________________
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/
March 22, 2017 at 3:34 pm
Hello Sean,
I forgot to remove paramters in my log procedure after I removed it went successful..Now theissue for me is with date format
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply