How to tell SQL Server Not Sending Message If Error

[kim1]

Hi guys,

Is there a way to tell SQL Server not sending an error message if there is an Error?

Thanks
Kim

 

[dcrussel]

Kim,
What type of errors are you trying to suppress? Are you talking in an ASP environment? Are you trying to prevent users from seeing errors?

-dc

 

[kim1]

Hi DC,


I want to prevent the caller from seeing the error if there is an error.

example.

ADO calls a SP that have 4 inserts statement

the 3e failed, I want the 4e inserts to execute and ADO not knowing that there is an error.

Is is possible.

Thank you so much.
Kim

 

[tlbroadbent]

Basically, no. Insert failures are usually due to duplicate values. This raises a fatal SQL error. You cannot supress fatal erros. You can prevent the error by adding a WHERE clause to the INSERT statement. You'll want to check on the Primary Key or Unique contraint column(s).

Example: Insert row into to Table1 From Table2 if the Key Value doesn't already exist in Table1.

Insert table1 (KeyCol, ColB, ColC)
Select colA, ColD, ColR
From table2
Where Not Exists
(Select * From Table2
Where KeyCol=Table2.ColA) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[kim1]

Hi Terry,

Is there any way we can prevent the CALLER (it could be a ADODB.Command or from ASP page) FROM SEEING THE ERROR if there is an error generated from SQL Server?

thanks Kim

 

[tlbroadbent]

I don't think you can prevent the communication between SQL Server and the client. SQL Server has to tell the client the status of the requested operation.

However, I am not a developer. I haven't used ADO very much. You should ask your question in one of the following forums. I'm sure you'll get more client side help.

forum333 - ASP
forum194 - ActiveX
forum222 - VB 5 & 6 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

Is there any way we can prevent the CALLER (it could be a ADODB.Command or from ASP page) FROM SEEING THE ERROR if there is an error generated from SQL Server?

I was thinking about this earlier, but was reluctant to answer because I didn't feel I understood the question. But, below is a (abbreviated) example of something I do all the time in ASP. You can see that I am testing the number of errors on the connection object conn; if I see errors, I do one thing; no errors I do another. (A duplicate PK on an Insert is an example of something that would generate an error on the conn object.)

So, I suppose we can say I am controlling how/what the client sees after an error, and perhaps this illustrates what kim1 is asking. (I'm still not sure. If you don't find this helps at all, please, please ignore)



[tt]
------------------------------
<%
objCmd.CommandText = &quot;dbo.sprInsertHeader&quot;
objCmd.CommandType = adCmdStoredProc
On Error Resume Next
objCmd.Execute

totalErrors = conn.Errors.Count

If totalErrors <> 0 Then%>
<!-- #INCLUDE FILE=&quot;fatalerror.asp&quot; -->
<%Else
theRowCount = objCmd.Parameters(&quot;RC&quot.Value
If theRowCount = 1 Then
Response.redirect(gotoUrl & theTrans)
Else
Response.redirect(&quot;weird.asp&quot
End If
End If%>
-------------------------
[/tt]