Re: SQL 2000 Stored Procedure Problem

Richard (rich@xxxxxxxxxxxxxxxx) writes:
******** 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!

No, that just means that the first recordset you get is a rowcount from
the INSERT. Use .NextRecordset to proceed to the next. Or issue SET
NOCOUNT ON in your procedure.

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

So on the old server where things work, you connect to one server, and
then issue a call stored procedure on a second server through the first
server? But on the server you are having problems with, the IP address
is the same as the server you connect to, so that you have a loopback?

This could explain why it works on the first server: the rowcount is
consumed by the server you connect to, and does not reach your client.
Whereas in the second case SQL Server is smart enough to shortcut,
and there is not really any linked server, so it is a direct call to
the procedure.

--
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: effect of trigger in Access?
    ... Even if you use SQL Server, you want to avoid triggers as much as ... >> and also have retained table level events for more ...
    (microsoft.public.access.externaldata)
  • 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: 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: Best replication architecture?
    ... Looking for a SQL Server replication book? ... So if it is subscribing to Publisher 1, ...
    (microsoft.public.sqlserver.replication)