Re: SQL 2000 Stored Procedure Problem

Do you have enough space available for tempdb?
Any errors in server log - when insert is eaten up?


"Richard" <rich@xxxxxxxxxxxxxxxx> wrote in message
news:%23Vyi0N%23nHHA.1476@xxxxxxxxxxxxxxxxxxxxxxx
Again, Many thanks Erland !

Please see my in line comments/answers to your questions. They are
indicated by ********


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns993CD9F37C96FYazorman@xxxxxxxxxxxx
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.

******** The error trap in the VB app is raised on any reference to the
recordset (Operation is not allowed when object is closed).
objRsDetails.MoveFirst triggers it. "If objRsDetails.EOF" triggers it.

That's why I looked at objRsDetails.State after the Open - it = 0
(Closed) - so the open fails! I also just now tested objConn.Errors.Count
and it = 0. So I'm really perplexed now since it appears I cannot even
"look for it".


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.

******** Yes - There is a web site attached to this project that I already
converted 100% .Net / ADO.Net - and yes it's much cleaner all over.


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?


******** Server1 (older) does use linked servers since that's where the
web site resides and it has to read from either Server - it works fine.
Ultimately Server1 is going away - and the linked server will no longer be
needed. But this problem app does not use it - it is just a Reports App
and the server that has the app will always be the same server that has
their SQL Server and Database. The full "IP.DBName.[dbo]" is simply used
to make it easier to manage since the IP and DB are in a Setup Table that
depending on the customer the IP "may" be different and the DBName will
always be different.

This Reports app is run over a VPN - and there is another huge front end
VB6 app that is the heart of all the data entry - it still has 100% SQL
pass through (also scheduled for .NET conversion) and it works fine on
both servers. Lucky the Reports app is the less critical one.



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.)

******** Did not know there was a standard order. Must be Enterprise
Manager - I used its template.

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.

******** AH good notice ! The actual code DOES have the client-cursor - I
just removed it today from this
test routine to see if it had any affect - none ! Same results. I'm
grasping at straws at this point.


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.


*****************************
**** I agree - I am simply trying to get the VB6 app trimmed down (Stored
Procedures etc) so the conversion to .NET will be easier - where I will
implement the ADO.NET correctly with the SProc methods.

*****************************
!!! One last thing: I just changed the SProc to INSERT to one of the
Existing data tables in the DB - Not a temp one !! It WORKS ! So it's not
a permissions on the INSERT command itself - just when INSERT to a Temp
Table !
Baffling !!


--
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: Admin Server 2003 myself? Or find someone to host?
    ... to do yourself -- configuration of SQL for your app and setup of your .Net ... where the server is hosted for an ASP.Net app as a good Internet provider ... These providers all geographically far away from my city ... and share an individual SQL Server instance ...
    (microsoft.public.windows.server.general)
  • RE: SBS 2003 Unable to connect to database STS_Config
    ... Uninstall the SQL server from the SBS 2k3 server from add/remove programs ... Uninstall Microsoft SQL Server Desktop Engine (SHAREPOINT) ... If AV software install any extra IIS virtual directory, ...
    (microsoft.public.windows.server.sbs)
  • RE: migrating from wmsde to sql server
    ... Click Start, point to All Programs\Microsoft SQL Server, and then click ... then click New SQL Server Registration. ... Microsoft CSS Online Newsgroup Support ... This newsgroup only focuses on SBS technical issues. ...
    (microsoft.public.windows.server.sbs)
  • Re: SQL 2000 Stored Procedure Problem
    ... message is returned to the app. ... And if you run with SET NOCOUNT ON, ... Or you always run it on the SQL Server machine? ...
    (microsoft.public.sqlserver.security)
  • RE: SBS 2003 Unable to connect to database STS_Config
    ... Uninstall the SQL server from the SBS 2k3 server from add/remove programs ... Uninstall Microsoft SQL Server Desktop Engine (SHAREPOINT) ... If AV software install any extra IIS virtual directory, ...
    (microsoft.public.windows.server.sbs)