Very Computer

>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

> 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