Re: MSAccess can run SP on some workstations but not others - why?

the test sp looks like this

Create Procedure Test_SP
Select * from tbl1
Go


tbl1 contains 10 rows of data.

The Access program can invoke several other stored procedures. This
procedure is the recordsource for a report. It runs fine on a few
workstations, but the error message on the other workstations says:

the Test_SP for the recordsource of this form or report cannot be found.

Then in the database window (In Access) for stored procedures I can see
Test_SP, but it is followed by a semicolon and a 1 on this particular
workstation. ON other workstations where the SP can run -- there is no
semicolon or number 1. When I try to run the SP from the database window of
the trouble workstation -- I get the same message: The Test_SP cannot be
found. But I am looking right at it (in an Access ADP).

It must be a configuration issue/security issue with either the account or
workstation. The connection is fine. In Access in the Connection window
there is a test button to test the connection. That works fine. What could
this problem be?



"Erland Sommarskog" wrote:

Rich (Rich@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I have a test SP on sql server 2000 running on Win2003 server.
Workstations are all XP SP2. On some workstations, MSAccess can run the
test sp, but Access does not recognize the same SP on other
workstations. I tried logging into another workstation using my
account - which is Network Administrator and Sql Server Administrator,
and I could not run the test SP from Access on this one workstation.
The SP is set to public. I can run the SP on my workstation and on a
few others, but not on all of the workstations.
Is there some security thing I need to set on the server for each
workstation? Or is it an account thing? or do I need to configure
something on the local workstations?

What "does not recognize the same SP" mean? Do you get an error message?
In such case, could you post this error message?

How is the connection to SQL Server set up in Access?

How does the code in Access that calls the procedure look like?

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

.