Access Client does not work with INSERT and SELECT in same stored procedure

Access Client does not work with INSERT and SELECT in same stored procedure

Post by Tore Gylve » Fri, 07 Feb 2003 03:51:13



I have an Access project as client to SQL Server 2000. Client and server run
on the same development machine with Windows 2000 Professional, latest
servicepack.
SQL server 2000 has latest service pack 3 installed, Office 2000 (Access)
also has latest service pack 3. I am using Windows login to the SQL Server.

The Access client will not accept Insert and Select to the same table in one
stored procedure. The stored procedure returns nothing when called from the
Client,  but it returns a number of records if called locally from the SQL
Query Analyzer, or if I run the debug tool of the Query Analyzer. Here is my
procedure:

Create                                       PROCEDURE
TMSP_CustomerListSearchTest
AS
BEGIN

INSERT  dbo.TM_Export (OrgName) VALUES ('testorg')
Select dbo.TM_Export.orgname from dbo.TM_Export

END

Nothing is returned to the client when the client calls the stored
procedure. If I remove the INSERT line,   all records are returned to the
client. What could be wrong?

I have a number of stored procedures that works perfectly with the client,
however without the SELECT - INSERT case. It does not seem like my problem
is a login/security problem.

Regards

Tore Gylver

 
 
 

Access Client does not work with INSERT and SELECT in same stored procedure

Post by Mary Chipma » Fri, 07 Feb 2003 08:23:45


Your problem is that the Access client does not support multiple
result sets. Put SET NOCOUNT ON as the first statement in the sproc
and try again. What it got back was the done in proc message from the
insert statement ("n rows affected") as the first result set, so it
couldn't deal with the second SELECT.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446


>I have an Access project as client to SQL Server 2000. Client and server run
>on the same development machine with Windows 2000 Professional, latest
>servicepack.
>SQL server 2000 has latest service pack 3 installed, Office 2000 (Access)
>also has latest service pack 3. I am using Windows login to the SQL Server.

>The Access client will not accept Insert and Select to the same table in one
>stored procedure. The stored procedure returns nothing when called from the
>Client,  but it returns a number of records if called locally from the SQL
>Query Analyzer, or if I run the debug tool of the Query Analyzer. Here is my
>procedure:

>Create                                       PROCEDURE
>TMSP_CustomerListSearchTest
>AS
>BEGIN

>INSERT  dbo.TM_Export (OrgName) VALUES ('testorg')
>Select dbo.TM_Export.orgname from dbo.TM_Export

>END

>Nothing is returned to the client when the client calls the stored
>procedure. If I remove the INSERT line,   all records are returned to the
>client. What could be wrong?

>I have a number of stored procedures that works perfectly with the client,
>however without the SELECT - INSERT case. It does not seem like my problem
>is a login/security problem.

>Regards

>Tore Gylver


 
 
 

Access Client does not work with INSERT and SELECT in same stored procedure

Post by Tore Gylve » Fri, 07 Feb 2003 10:05:41


It seems to work perfectly, I am happy.Thank You.

Tore


> Your problem is that the Access client does not support multiple
> result sets. Put SET NOCOUNT ON as the first statement in the sproc
> and try again. What it got back was the done in proc message from the
> insert statement ("n rows affected") as the first result set, so it
> couldn't deal with the second SELECT.

> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446


> >I have an Access project as client to SQL Server 2000. Client and server
run
> >on the same development machine with Windows 2000 Professional, latest
> >servicepack.
> >SQL server 2000 has latest service pack 3 installed, Office 2000 (Access)
> >also has latest service pack 3. I am using Windows login to the SQL
Server.

> >The Access client will not accept Insert and Select to the same table in
one
> >stored procedure. The stored procedure returns nothing when called from
the
> >Client,  but it returns a number of records if called locally from the
SQL
> >Query Analyzer, or if I run the debug tool of the Query Analyzer. Here is
my
> >procedure:

> >Create                                       PROCEDURE
> >TMSP_CustomerListSearchTest
> >AS
> >BEGIN

> >INSERT  dbo.TM_Export (OrgName) VALUES ('testorg')
> >Select dbo.TM_Export.orgname from dbo.TM_Export

> >END

> >Nothing is returned to the client when the client calls the stored
> >procedure. If I remove the INSERT line,   all records are returned to the
> >client. What could be wrong?

> >I have a number of stored procedures that works perfectly with the
client,
> >however without the SELECT - INSERT case. It does not seem like my
problem
> >is a login/security problem.

> >Regards

> >Tore Gylver

 
 
 

1. insert does not work when triggerd Stored Procedure fails

Post the code and table dml.  Also, what errors are you getting.  Doesn't
sound too hopeful though.  Most any error that occurs while in a trigger
will cause the transaction to rollback and the batch to end.

--
----------------------------------------------------------------------------
-----------

Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)


insert to a stored procedure , the stored procedure then  would update a 2nd
table. I've noticed that if the stored procedure fails  the insert on the
origianal table fails as well. Is there any way around this?

2. Informix and LD_LIBRARY_PATH

3. V$ select in stored procedure not working

4. Alter ALTER TABLE statement ...

5. doing SELECT after INSERT OR UPDATES in stored proc causes proble ms

6. Btrieve 6.15 with C++ Builder

7. preserving case when doing selects/compares in stored procedure

8. Exporting Memo field data to a text file

9. Session sometimes hangs when doing select in stored procedure

10. Trigger doing UNLOAD to file OR calling Store Procedure doing the UNLOAD to file

11. Referencing the timestamp from my last insert statement on the client-side, @@DBTS does not work

12. Select distinct being ignored in stored procedure in db a, working correctly in db b

13. Executing a Stored Procedure in a Stored Procedure and selecting on the result