June 3, 2009 at 12:36 pm
I am missing something in the following:
F EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].sprUpdateReOpenedTicket') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sprUpdateReOpenedTicket
GO
CREATE PROCEDURE [dbo].sprUpdateReOpenedTicket
(
@ticketID int = null,
@ticketType nvarchar(50) = null,
@ticketStatus nvarchar(50) = null,
@ticketSeverity nvarchar(50) = null,
@shortDescription nvarchar(1000) = null,
@externalTicket nvarchar(1000) = null,
@reasonMenu nvarchar(50) = null,
@problemNote ntext = null,
@solutionNote ntext = null,
@actionNote ntext = null,
@followupNote ntext = null,
@companiesFlag bit = null,
@agentCreated nvarchar(50) = null,
@dateCreated datetime = null,
@dateLastModified datetime = null,
@agentClosed nvarchar(50) = null,
@dateClosed datetime = null,
@agentFollowup nvarchar(50) = null,
@followupBy datetime = null,
@deletedFlag bit = null,
@ticketTypeOld nvarchar(50) = null,
@assignedTo varchar(50) = null,
@requestType varchar(50) = null,
@serviceImpact varchar(50) = null,
@dateTimeAssigned datetime = null,
@priority varchar(50) = null,
@dateAssigned datetime = null,
@trackIt varchar(50) = null,
@contactMethod varchar(50) = null,
@locationSIMs varchar(50) = null,
@userid nvarchar(50) = null,
@ticketApplicationType nvarchar(50) = null,
@accountID int = null,
@resultCode INT OUTPUT,
@resultMessage NVARCHAR(200) OUTPUT
)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
UPDATE
[dbo].[tbl_tickets]
SET
[TicketType] = @ticketType,
[TicketStatus] = @ticketStatus,
[TicketSeverity] = @ticketSeverity,
[ShortDescription] = @shortDescription,
[ExternalTicket] = @externalTicket,
[ReasonMenu] = @reasonMenu,
[ProblemNote] = @problemNote,
[SolutionNote] = @solutionNote,
[ActionNote] = @actionNote,
[FollowupNote] = @followupNote,
[CompaniesFlag] = @companiesFlag,
[AgentCreated] = @agentCreated,
[DateCreated] = @dateCreated,
[DateLastModified] = @dateLastModified,
[AgentClosed] = @agentClosed,
[DateClosed] = @dateClosed,
[AgentFollowup] = @agentFollowup,
[FollowupBy] = @followupBy,
[DeletedFlag] = @deletedFlag,
[TicketTypeOld] = @ticketTypeOld,
[AssignedTo] = @assignedTo,
[RequestType] = @requestType,
[ServiceImpact] = @serviceImpact,
[DateTimeAssigned] = @dateTimeAssigned,
[Priority] = @priority,
[DateAssigned] = @dateAssigned,
[TrackIt] = @trackIt,
[ContactMethod] = @contactMethod,
[LocationSIMs] = @locationSIMs,
[UserID] = @userid,
[TicketApplicationType] = @ticketApplicationType,
[AccountID] = @accountID
WHERE
[TicketID] = @ticketID
SET @resultCode = 0
SET @resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
if (@@ROWCOUNT>0)
begin
INSERT INTO [dbo].[tbl_ticket_history]
([TicketID]
,[Note]
,[DateCreated]
,[AgentCreated]
,[POCIDCreated]
,[CustomerVisible]
,[UpdateAlertRequired]
,[UpdateAlertSent]
,[DateUpdateAlertSent])
VALUES
( @ticketID
,Cast(@actionNote as nvarchar(max))
,GETDATE()
,@agentCreated
,null
,0
,null
,null
,null)
end
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 1 ROLLBACK
SET @ticketID = 0
SET @resultCode = 1
SET @resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
+ ERROR_NUMBER() + '. ' + char(13) + char(10)
+ ERROR_MESSAGE() + '. ' + char(13) + char(10)
+ ERROR_LINE() + '. ' + char(13) + char(10)
+ ERROR_PROCEDURE() + '. ' + char(13) + char(10)
+ ERROR_STATE() + '. ' + char(13) + char(10)
END CATCH
COMMIT TRAN
END
GO
I appreciate any help.
June 3, 2009 at 1:16 pm
I have no idea what you are missing - or, what kind of error you are getting, or what problem you are trying to solve.
It would probably help if you would read the article I link to in my signature.
Just looking at your code, the first thing that I notice is that you are trying to perform an update, and if it fails it should drop to the catch block. However, you have the insert statement inside the try block which will never get called if the ticket ID doesn't exist.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 3, 2009 at 1:20 pm
I get an error of ticketID 0?
June 3, 2009 at 1:22 pm
mathieu_cupryk (6/3/2009)
I get an error of ticketID 0?
Sorry - no idea what that means.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 3, 2009 at 1:23 pm
can u just copy paste the error message u r getting while execting above?
June 3, 2009 at 1:24 pm
{"Conversion failed when converting the nvarchar value 'ticketID 0' to data type int."}
June 3, 2009 at 1:34 pm
If you want to get help with your questions here, you really need to read the article I link to in my signature and post the create statements, sample data and expected results that you are looking for.
The error that you are getting tells me that most likely the definition of the ticketID column is nvarchar - and you have defined the variable as an int. The int variable cannot be converted to an nvarchar value.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 3, 2009 at 1:42 pm
mathieu_cupryk (6/3/2009)
{"Conversion failed when converting the nvarchar value 'ticketID 0' to data type int."}
In the following statement
SET @resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
+ ERROR_NUMBER() + '. ' + char(13) + char(10)
+ ERROR_MESSAGE() + '. ' + char(13) + char(10)
+ ERROR_LINE() + '. ' + char(13) + char(10)
+ ERROR_PROCEDURE() + '. ' + char(13) + char(10)
+ ERROR_STATE() + '. ' + char(13) + char(10)
Error_number, error_line and error_state all return numeric values and hence need an explicit cast to varchar like you've done with the @ticketID variable. Why are you using nvarchar?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 3, 2009 at 1:43 pm
I am still confused.
June 3, 2009 at 1:45 pm
Probably the code fails in following block:
SET @resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
+ ERROR_NUMBER() + '. ' + char(13) + char(10)
+ ERROR_MESSAGE() + '. ' + char(13) + char(10)
+ ERROR_LINE() + '. ' + char(13) + char(10)
+ ERROR_PROCEDURE() + '. ' + char(13) + char(10)
+ ERROR_STATE() + '. ' + char(13) + char(10)
SQL Server tries to convert the value "ticketID 0" implicitly to an integer because of the concatenation of ERROR_NUMBER() [which is an integer]
So, try this instead:
SET @resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
+ CAST(ERROR_NUMBER() AS NVARCHAR(200)) + '. ' + char(13) + char(10)
+ ERROR_MESSAGE() + '. ' + char(13) + char(10)
+ CAST(ERROR_LINE() AS NVARCHAR(200)) + '. ' + char(13) + char(10)
+ ERROR_PROCEDURE() + '. ' + char(13) + char(10)
+ CAST(ERROR_STATE() AS NVARCHAR(200)) + '. ' + char(13) + char(10)
Edit: Gail was a bit faster...
June 3, 2009 at 2:02 pm
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
June 3, 2009 at 2:14 pm
mathieu_cupryk (6/3/2009)
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
Is there a question here? Again, please review the article I link to and think about what you need to post to get a better answer.
This error - I am assuming - is caused because of where you have the begin transaction and commit transaction statements.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 3, 2009 at 2:15 pm
This is what I am getting:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
June 3, 2009 at 2:15 pm
As the error says, you're asking SQL to commit a transaction that's not commitable (because the catch block was invoked). Move the commit tran into the TRY block and add a ROLLBACK TRANSACTION to the end of the catch block.
And please, please, please read the article that people are telling you to read. I'm guessing because you're not posting enough information to be certain.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 3, 2009 at 2:46 pm
I read the article. I am making sure other people will read it.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply