RAISERROR used to define custom error message . this error message can be printed from stored procedure when validation failed
RAISEERROR ('invalid ProductID' 5001, 2)
RAISERROR Syntax in a SQL Server Stored Procedure
Bobby P
221
Reputation points
I read about using the RAISERROR hack when using a SQL Server Stored Procedure that goes across a Linked Server.
Just wondering what the Syntax is for RAISERROR within a SQL Server Stored Procedured.
Thanks for your review and am hopeful for a reply.
Thanks!
5 answers
Sort by: Most helpful
-
LiHong-MSFT 9,996 Reputation points
2022-01-18T06:47:18.067+00:00 Hi,@Bobby P
RAISERROR is used to throw an exception or error.
For example,you can add ERROR CHECKS in stored procedure,like this:IF (@type_id IS NULL) BEGIN RAISERROR ('Parameter ''role_type_id'' can not be null.' , 16, 1) WITH NOWAIT RETURN 1 ENDPlease refer to this document for more details.
Best regards,
LiHong
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Bobby P 221 Reputation points2022-01-22T17:23:53.933+00:00 I am specifically referring to this link...
https://dba.stackexchange.com/questions/236584/linked-server-returned-message-query-timeout-expired
-
Ronen Ariely 14,146 Reputation points MVP2022-01-22T17:33:55.48+00:00 Hi @Bobby P ,
Your question was
what the Syntax is for RAISERROR within a SQL Server Stored Procedured.and you got the answer. How this is relevant to check the link? Please clarify what is missing in the answers which you already got
Sign in to comment
Bobby P 221 Reputation points2022-01-22T17:30:20.873+00:00 Can anyone help me out here? This Job will run successfully for 3-4 weeks at a time and then hits this time-out issue out of nowhere it seems. It's driving me CRAZY!
Any insight at all?!?!?
Sign in to answer-
Ronen Ariely 14,146 Reputation points MVP
2022-01-22T17:36:40.067+00:00 How this is related to your original question?
For new question please close this thread and open a new thread for the new question and remember to add all the information about the question, starting with how to preproduce it if you know or exactly what you do and what the result. Add any full error message and if can help than screenshot as well
Sign in to comment
That's an interesting approach, that I have not thought of. And a quick test indicates that it works (on SQL 2019, which the poster had not tested.)
But what is really your question? There is an example with RAISERROR right in the StackOverflow post, so why ask here rather than copy and paste?
OK, I understand that your real problem is that you are experiencing a remote query timeout, and I seem to recall that you had a thread about that. I was able to find the thread, and I see that I suggested you to collect query plans, but you never came back with any information.
From that thread, I cannot really determine if this RAISERROR hack is applicable here.
That hack is only applicable under fairly specific circumstances. First of all, you must be calling a stored procedure on the remote server. If you are running a query with four-part notation or OPENQUERY, there is no place where you can inject this RAISERROR.
Next, you must have possibility to edit this procedure. That is, if this is an SP which is part of Dynamics/CRM, it is unlikely that you can change it.
And finally, there must be some place where you can inject the RAISERROR. If the procedure runs a loop or several long-running statements, you can splice it in somewhere. But if the procedure get stuck on the same query for ten minutes, you are out of luck.
By the way, this was your previous thread: https://learn.microsoft.com/en-us/answers/questions/698108/sql-server-job-error-query-timeout-expiredbut-work.html.