July 24, 2012 at 4:56 am
we are queirying an stored procedure multiple times same time,from our application. In this case, few processes executing successfully and few getting failed with error "50000 error executing the stored procedure" and if we run thesame process again its getting executed sucessfully.
Does the MySQL cannot handle multiple threads same time? can some one help us in this case.
July 24, 2012 at 7:38 am
Can you confirm if you mean Microsoft SQL Server or MySQL? This group is about Microsoft SQL server, but you refer to mysql in the question.
If it is SQl Server then 50000 is an error code reserverd for general "user defined" error messages, so something in the stored proc is raisong that error. To determine why we would need the source code (at least) of that stored procedure.
As for multiple clients/threads then yes it works fine, but it could be something in your stored procedure is not allowing for that.
Mike
July 25, 2012 at 12:54 am
Hi Mike,
Thank you for your reply... Its a typo mistake in hurry.. we are using microsoft SQL only. Below is the stored procedure we are using in our application. I have replcaed few names(only company related names ,not any key words) with"xxxx" for security reason ..
Could you guide us resolving this issue..
Thank you .
USE [Dev]
GO
/****** Object: StoredProcedure [dbo].[FileUpdate] Script Date: 07/25/2012 00:31:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FileUpdate]
(
@key uniqueidentifier,
@FileID Varchar(80)
)
AS
set quoted_identifier OFF
DECLARE @CurrentDate DATETIME
DECLARE @BpUser varchar(20)
DECLARE @InvoiceFlowKey uniqueidentifier
DECLARE @PedigreeKey uniqueidentifier
DECLARE @ClockKey uniqueidentifier
DECLARE @ClockStatusCd varchar(20)
DECLARE @ClockEventKey uniqueidentifier
DECLARE @RetCode INTEGER
DECLARE @ReturnMessage VARCHAR(max)
SET @CurrentDate = getdate()
SET @BpUser = 'BP'
set @RetCode = 0
SET @ReturnMessage = ''
DECLARE @Debug INTEGER
SET @Debug = 0
/*
Environment
*/
IF @Debug = 1
BEGIN
SET NOCOUNT OFF
END
ELSE
BEGIN
SET NOCOUNT ON
END
IF @Debug = 1
PRINT ' Begin FileUpdate '
/*
1. Get the invoiceFlowKey for the given batch
*/
SELECT
@InvoiceFlowKey = invoiceFlowKey
FROM
tbBatch
WHERE
batchKey = @BatchKey
/*
2. Get pedigree key for the batch.
*/
SELECT
@PedigreeKey = pedigreeKey
FROM
tbBatch
WHERE
batchKey = @BatchKey
/*
3. Use the pedigree key to get the clock key for the pedigree.
*/
SELECT
@ClockKey = clockKey
FROM
tbPedigree
WHERE
pedigreeKey = @PedigreeKey
/*
4. Get the last clock event.
*/
SELECT TOP 1
@ClockStatusCd = clockStatusCd
FROM
tbClockEvent
WHERE
clockKey = @ClockKey
ORDER BY
clockEventEffectiveStartTS desc
IF @ClockStatusCd is null
SET @ClockStatusCd = ''
IF @Debug = 1
BEGIN
PRINT '@InvoiceFlowKey = ' + CAST(@InvoiceFlowKey AS varchar(40))
PRINT '@PedigreeKey = ' + CAST( ISNULL(@PedigreeKey, '00000000-0000-0000-0000-000000000000') AS varchar(40))
PRINT '@ClockKey = ' + CAST( ISNULL(@ClockKey, '00000000-0000-0000-0000-000000000000') AS varchar(40))
PRINT '@ClockStatusCd = ' + @ClockStatusCd
END
BEGIN TRANSACTION;
BEGIN TRY
UPDATE
tbBatch
SET
batchStatusCd = 'Ready',
batchTypeCd = 'Routed',
rowAlteredTS = @CurrentDate,
rowAlteredID = @BpUser
WHERE
batchKey = @BatchKey
UPDATE
tbBatchFile
SET
fileReferenceID = @FileID
WHERE
batchKey = @BatchKey
IF @debug = 1
select * from tbBatchFile where batchKey = @BatchKey
-- If the last clock event was a Hold, create a restart event.
IF @ClockStatusCd = 'Hold'
BEGIN
SET @ClockEventKey = Newid()
INSERT INTO tbClockEvent
(
clockEventKey,
clockStatusCd,
clockKey,
clockEventCmmt,
clockEventEffectiveStartTS,
clockEventInitialStartTS,
clockEventLastChangedByID,
rowCreatedTS,
rowCreatedID,
rowAlteredTS,
rowAlteredID
)
VALUES
(
@ClockEventKey,
'Restart',
@ClockKey,
'', -- default to a blank comment
Getdate(),
Getdate(),
'xxxxxxxxxSystem',
Getdate(),
@BpUser,
Getdate(),
@BpUser
)
END
-- If there is no last clock event, create a start event.
IF @ClockStatusCd = ''
BEGIN
SET @ClockEventKey = Newid()
INSERT INTO tbClockEvent
(
clockEventKey,
clockStatusCd,
clockKey,
clockEventCmmt,
clockEventEffectiveStartTS,
clockEventInitialStartTS,
clockEventLastChangedByID,
rowCreatedTS,
rowCreatedID,
rowAlteredTS,
rowAlteredID
)
VALUES
(
@ClockEventKey,
'Start',
@ClockKey,
'', -- default to a blank comment
Getdate(),
Getdate(),
'xxxxxxxxxSystem',
Getdate(),
@BpUser,
Getdate(),
@BpUser
)
END
/*
7. Find all users associatated WHERE ith the invoice flow and send an e-mail.
*/
SELECT
inv.invoiceFlowKey,
ifc.tradingPartnerContactKey,
tradingPartnerRltMemberKey,
tpc.contactKey,
contactTypeCd,
tradingPartnerContactActiveInd
INTO
#temp_Contacts
FROM
tbInvoiceFlow inv
JOIN
tbInvoiceFlowContact ifc
ON
inv.invoiceFlowKey = ifc.invoiceFlowKey
JOIN
tbTradingPartnerContact tpc
ON
ifc.tradingPartnerContactKey = tpc.tradingPartnerContactKey
JOIN
tbContact con
ON
tpc.contactKey = con.contactKey
WHERE
contactTypeCd = 'User'
AND
--tradingPartnerContactActiveInd = 'Y'
--AND
inv.invoiceFlowKey = @InvoiceFlowKey
IF @Debug = 1
BEGIN
print 'data from #temp_Contacts...'
select * from #temp_Contacts
END
--Get the sending trading partner name
Declare @SendPartnerName nvarchar(250)
select @SendPartnerName=tp.tradingPartnerNm from tbTradingPartner tp
join tbBatch b
on b.tradingPartnerID = tp.tradingPartnerID
where batchKey = @BatchKey
DECLARE @EmailBody VARCHAR(MAX)
set @EmailBody = dbo.EmailBodySelect( 'RI',1, dbo.fcnBatchFileListSelect(@BatchKey), @InvoiceFlowKey,@SendPartnerName)
-- get invoice name using invoiceFlowKey
Declare @FlowName varchar(500)
select @FlowName = InvoiceFlowDesc from tbInvoiceFlow where InvoiceFlowKey = @InvoiceFlowKey
INSERT INTO tbInboxMessage
(
inboxMessageKey,
emailStatusCd,
inboxKey,
messageStatusCd,
messageTypeCd,
messageBodyTxt,
messageFromTxt,
messageForwardedInd,
messageRepliedToInd,
messageToTxt,
messageSentTS,
messageSubjectTxt,
rowCreatedTS,
rowCreatedID,
rowAlteredTS,
rowAlteredID
)
SELECT
newid() as inboxMessageKey,
'Pending',
inboxKey,
'Active',
'System',
@EmailBody,
'xxxxxxxxxx.com',
'N',
'N',
CAST(con.contactKey AS varchar(50)),
Getdate(),
' alert: Files sent from '+@SendPartnerName+' for Flow '+@FlowName,
Getdate(),
@BpUser,
Getdate(),
@BpUser
FROM
#temp_Contacts con
JOIN
tbInbox ibx
ON
con.contactKey = ibx.contactKey
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
EXECUTE ErrorHandler
RETURN 55555
END CATCH;
SELECT
@RetCode AS ReturnCode,
@ReturnMessage AS ReturnMessage
July 25, 2012 at 1:06 am
50000 is a user-defined error. It's raised by using RAISERROR.
Assuming EXECUTE ErrorHandler has that in, something has failed somewhere in that catch block Suggest you add some more debugging or use the ERROR_MESSAGE and ERROR_LINE functions in the catch to return meaningful errors
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
July 25, 2012 at 2:44 am
Hi Gail Shaw,
yes we are using ERROR_MESSAGE function in the Error handler.It has function RAISERROR(@errmsg,@severity,@state).
and the parameter @errmsg getting value from ERROR_MESSAGE function itself which still returning 50000 error. Please advise if we Can look in some where (any logs) to find the correct error message.. and I am not sure why this ending with errors for few threads and executing correctly for remaining and if we rerun the failed ones individually , they are getting executed successfully.
---Mohan
July 25, 2012 at 7:49 am
If the custom error handling isn't returning the right info, then you need to debug and fix the custom error handling.
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
July 25, 2012 at 9:36 am
The custom Error Handler has following things,
intialized @errmsg varchar(max)
assigned ERROR_MESSAGE() value to @errmsg and returned the errmsg value.
can we include any more functionalities to get the actual error message...
---Mohan
July 25, 2012 at 9:37 am
The custom Error Handler has following things,
intialized @errmsg varchar(max)
assigned ERROR_MESSAGE() value to @errmsg and returned the errmsg valuethrough RAISEERROR .
can we include any more functionalities to get the actual error message...Please advise.
---Mohan
July 25, 2012 at 10:28 am
Can you post the code for the error handling procedure? Also, what is (are) the exact error message(s) that are being returned from this procedure?
July 25, 2012 at 11:29 pm
Hi,
Please find the error handler stored proc below and adivse if we can include any more fuctionality to catch the actuall error. Thank you.
USE [Devl]
GO
/****** Object: StoredProcedure [dbo].[ErrorHandler] Script Date: 07/25/2012 03:33:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ErrorHandler]
AS
/*
-----------------------------------------------
Name: ErrorHandler
Description: This procedure is a standard error handler sp used in several
stored proces where stored proc calls are nested in other stored procs.
Version: 1.0
History: 10-13-10 initial creation (xxxxxx)
Test(s):
EXECUTE ErrorHandler
------------------------------------------------
*/
DECLARE @errmsg nvarchar(2048),
@severity tinyint,
@state tinyint,
@errno int,
@proc sysname,
@lineno int
SELECT
@errmsg = error_message(),
@severity = error_severity(),
@state = error_state(),
@errno = error_number(),
@proc = error_procedure(),
@lineno = error_line()
IF @errmsg NOT LIKE '***%'
BEGIN
SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') +
', ' + ltrim(str(@lineno)) + '. Errno ' +
ltrim(str(@errno)) + ': ' + @errmsg
RAISERROR(@errmsg, @severity, @state)
END
ELSE
RAISERROR(@errmsg, @severity, @state)
July 27, 2012 at 12:34 am
Hi,
As I am not aware of SQL issues in depth , We are stuck in the middle with this problem even we completed our application related development/testing... and have postponed our Go Live dateto monday.
Kindly some one help us in this case to resolve this 50000 issue .
We welome any suggesstions .... Thank you very much for your help for all these days..
Regards,
Mohan.
July 27, 2012 at 12:56 am
You're getting 50000 because you raise the error with raiserror. There's no way with the code there you'll get any error number other than 50000
What's the error message that you see?
Put some debugging print statements into your error handler and see if you can narrow things down that way.
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
July 27, 2012 at 1:48 am
Hi
I have removed RAISEERROR function and returned the actuall error(Select @errmsg as errmsg).. then I have tried executing 5 processes at the same time,
Now the process didnot failed at the sql statement where it fails usually, all the 5 processes executed successfully... after completing the process , it again submits the success results to the data base through another stored procedures.. and all the stored procedures in the 5 processes failed at the later stage and returned the below error message.
ErrorDescription="Update failed. The record to be updated could either not be found or it was already deleted or updated by another transaction." ErrorRelatedMoreInfo="The Statement that was being executed is [update /*YANTRA*/ XXXXXX(table name) set CONSUMER_MSGID = 4536,LOCKID=4,MODIFYTS = {ts '2012-07-27 00:24:01'} WHERE LOCKID =3 AND DELIVERY_KEY='20120727002356228714']">
Regards,
Mohan
July 27, 2012 at 5:01 am
That is not a SQL Server error. Looks like that's thrown by your front end app.
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
July 27, 2012 at 6:30 am
Hi Gail Shaw,
Yes it is the error from my application. once I have removed the functionality to handle the exceptions(RAISEERROR) and ended the error handler, I started receiving these errors from my application data base.
I assume to be happening now is, ignored the exception raised at stored procedure and that affected some thing else in application data base and thus seeing these errors.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply