Member Avatar for Tank50

Hi Guys

I have an application that developed using ASP.net.There is cancel option,user can select number of item code and cancel it,but there is isssue.The issue is once user select more than 200 item code to cancel then it generate the error messsage.Error message is

"Time Expird or server is not respond"

Above message is not exact message but above I mention the meaning of the error message.
After that using sql profiler ,I trace the exception while cancel the item code.In trace I got message like "Error: 8114, Severity: 16, State: 5".Please tell me what is meaning of the Erorr and how to over come this issue.


Thanks
Tank50

  • 2 Contributors
  • 3 Replies
  • 679 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by buddylee17

Dani AI

Generated

Short answer: Msg 8114 is a SQL Server conversion error — the engine failed to convert a character value to a numeric type (varchar/nvarchar -> numeric/decimal/float). That is the root of the 8114 message you saw in Profiler. (support.microsoft.com)

Why it happens only when you pick many items: SQL Server uses data-type precedence and will implicitly convert the lower-precedence side to the higher-precedence type for an expression (CASE, UNION, COALESCE, ORDER BY, etc.). If one branch returns a non-numeric string (currency symbol, commas, empty string, or other text), the server will try to convert it to numeric and fail. That explains why a small set might work (only numeric branches executed) but a larger set exercises a branch with non-numeric data. (learn.microsoft.com)

Practical next steps (diagnose fast):

  • Follow and capture the exact failing SQL and parameter values (Profiler / Extended Events — capture TextData and parameter columns). Re-run that exact statement in SSMS to reproduce.

  • Find the offending values in the table using TRY_CAST / TRY_CONVERT instead of ISNUMERIC (they return NULL for non-convertible rows, so you can locate the bad rows). Example:

    -- find rows that cannot convert to numeric
SELECT PK, col_with_strings
FROM YourTable
WHERE TRY_CONVERT(DECIMAL(18,4), col_with_strings) IS NULL
  AND col_with_strings IS NOT NULL;

    Using TRY_* is safer than ISNUMERIC which has known quirks. (learn.microsoft.com)

Remedies:

  • Fix or sanitize the bad data (remove '$', commas, empty strings) before converting.
  • Make expressions unambiguous: explicitly CAST branches so a CASE always returns the same type.
  • If the app builds large IN(...) lists to cancel items, switch to a table-valued parameter or batch the work — TVPs avoid long concatenated strings and are more reliable and performant. Also add server-side logging (TRY/CATCH logging ERROR_MESSAGE/ERROR_LINE) to capture future failures. (learn.microsoft.com)

If the Profiler capture is available, paste the TextData and parameter values here (or reproduce the statement in SSMS) — that will let others point to the exact conversion expression to fix.

Recommended Answers

All 3 Replies

Member Avatar for buddylee17 
--This will tell you about error 8114
SELECT description FROM master..sysmessages
WHERE error = 8114 AND severity = 16 AND msglangid = 1033

Try wrapping your code in try catch to get more details.

Example:

BEGIN TRY   
   --This will cause an error
   DECLARE @number int
   SET @number = 'ABC'
END TRY

BEGIN CATCH
   --Capture the error info
   SELECT ERROR_NUMBER() AS ErrorNumber
	  ,ERROR_SEVERITY() AS ErrorSeverity
	  ,ERROR_STATE() AS ErrorState
	  ,ERROR_PROCEDURE() AS ErrorProcedure
	  ,ERROR_LINE() AS ErrorLine
	  ,ERROR_MESSAGE() AS ErrorMessage;	    		 
END CATCH
Member Avatar for Tank50

Hi

Thanks for reply,but I find that error from sql server profiler.and the cancel process works fine for few item code like 25 item codes.Once number of item codes increase it generate the error message .why it works for few items?I dont have query that error message generate from application use in our company.


Thanks

Member Avatar for buddylee17

Can you retrieve the query that is erroring from profiler?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.