Re: SQL 2000 Stored Procedure Problem

Richard (rich@xxxxxxxxxxxxxxxx) writes:
I made some mods to the failing SProc and zeroed in on INSERT statement.
I used a few Blocks of code (from your tutorial) in the SProc to force
errors to see what happened. The "Force Error" block works as expected
when it is before the "INSERT" - but when it's after the INSERT the app
does not get the error message and the recordset fails to OPEN. If I
move the "-- BLOCK: Trap Error" Just After the INSERT - No error
message is returned to the app.

It is, but you have to look for it.

You can find it directly on objConn.Errors, or it will be raised when you
call .NextRecordset. And if you run with SET NOCOUNT ON (see also below),
it should appear directly. When NOCOUNT is off, the INSERT statement
generates a "rows affected" message, and the error is queued behind that
message.

But it is certainly quite confusing when ADO returns an error. The good
news is that if you are heading for SqlClient, is that ADO .Net is a lot
cleaner. Particularly if you use the connection property
..FireInfoMessageEventsOnUserErrors.



There are a few things that are not clear to me. You talk about running
on different servers. Do you always run the client application on the
same machine? Or you always run it on the SQL Server machine?

You stored-procedure calls appears to include a linked server. Is this
linked server always the one and the same? Or is the SQL Server you
connect to always the same, and then you call different linked servers?



Permit me a couple of more comments.

'---- The Test SProc:
SET NOCOUNT ON
GO

Whether you have SET NOCOUNT ON when you create the procedure matters
little. This setting is not saved with the procedure, but the run-time
setting applies. You have better use for this statement inside the
procedure.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

Any particular reason that you have these in the non-standard position?
Or are you just victim of the crap known as Enterprise Manager? Keep in
mind that there are features in SQL Server that are not available when
these settings are OFF, so there is all reason to have them ON. (These
two settings *are* saved with the procedure, so this is where it matters.)

Private Sub cmdDevtest_Click(Index As Integer)
'-- Test some simple SPROCs
On Error GoTo Handler

Dim sSql As String
Dim objRsDetails As ADODB.Recordset
Dim objConn As ADODB.Connection
Dim tracer As String

Set objConn = New ADODB.Connection

You don't set the cursor location. I strongly recommend using client-
side cursors.

objConn.Open CONNECTION_STRING, "", ""
Set objRsDetails = New ADODB.Recordset

If TextSpName.Text <> "" Then
sSql = " exec " & IpDatabaseOwner & TextSpName.Text
Else
If Index = 0 Then
sSql = " exec " & IpDatabaseOwner & "spTestSprocSimple "
Else
sSql = " exec " & IpDatabaseOwner & "spTestTempTable "
End If
End If

Rather than using adCmdText, use adCmdStoredProcedure. Then the stored
procedure is called trough RPC which is more effective. That also forces
you to use the parameters collection when you have parameters. And
that is a good thing. Never embed user values into the SQL string
directly.



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Item Cannot be Found in Collection
    ... The "SET NOCOUNT ON" suggestion is not relevant for Access databases. ... setting that can only be performed in SQL Server. ... > I tried Set NOCOUNT = ON and I got a syntax error. ...
    (microsoft.public.scripting.vbscript)
  • Re: SQL 2000 Stored Procedure Problem
    ... I added SET NOCOUNT ON as the first line in spTestTempTable - same ... SELECT 'Test Temp Table' ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: Bcp and temp tables
    ... Here is the proc: ... bcp "SET FMTONLY OFF EXEC pubs.dbo.Kristoffer" queryout ... without SET NOCOUNT ON with the same results. ... > Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Newbie question; Why is my procedure slow when a parameter is used?
    ... since your WHERE clause refers to columns in the unpreserved ... Columnist, SQL Server Professional ... Alter Procedure prInvDet ... set nocount on /* set nocount on */ ...
    (microsoft.public.sqlserver.programming)
  • transaction error??
    ... Theere are two sql server in different city,I connect then with VPN, then I ... BEGIN DISTRIBUTED TRANSACTION ... SET NOCOUNT OFF ...
    (microsoft.public.sqlserver.programming)