Re: SQL 2000 Stored Procedure Problem

Not sure how you don't have tempdb and have temp table -see this link

http://msdn2.microsoft.com/en-us/library/ms190768.aspx


"Richard" <rich@xxxxxxxxxxxxxxxx> wrote in message
news:eZo9lIBoHHA.4428@xxxxxxxxxxxxxxxxxxxxxxx
Thanks Alex,

Brand new server with "terra bytes" of storage - there is no Temp DB just
a Temp Table. No errors in the log.
Thanks for grabbing at a few straws with me.


"AlexS" <salexru2000NO@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:enEoVgAoHHA.716@xxxxxxxxxxxxxxxxxxxxxxx
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: version & instances
    ... Hitchhiker’s Guide to Visual Studio and SQL Server ... SQL 2000 SP3a for Antivirus app ... Consider that MSDE as a product is somewhat dated ...
    (microsoft.public.sqlserver.msde)
  • Re: Temp files in Stored Procedures
    ... you do need to use SELECT INTO, try to schedule it when your SQL Server is ... server to its knees. ... >> The ideal method is to avoid using temp tables within stored procedures. ...
    (microsoft.public.sqlserver.programming)
  • 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)