MSAccess can run SP on some workstations but not others

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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rich - 20 Mar 2008 23:23 GMT

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?

Thanks,
Rich

Reply to this Message

Erland Sommarskog - 20 Mar 2008 23:58 GMT

> 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
[quoted text clipped - 7 lines]
> 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?

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

Reply to this Message

Rich - 21 Mar 2008 00:30 GMT

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?

> > 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
[quoted text clipped - 14 lines]
>
> How does the code in Access that calls the procedure look like?

Reply to this Message

Erland Sommarskog - 21 Mar 2008 10:56 GMT

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

That seems to be an error message from Access.

> 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
[quoted text clipped - 3 lines]
> Test_SP cannot be found. But I am looking right at it (in an Access
> ADP).

If you try it Query Analyzer, you will find that

EXEC Test_SP
EXEC Test_SP;1

yield the same result. The ;1 is part of a quite obscure feature in
SQL Server: versioned stored procedures. That is, you can create Test_SP;2,
Test_SP;3 etc. I think the only point with it is that you can drop
them all in one bang with "DROP PROCEDURE Test_SP".

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

This seems to be a problem on the Access side, and I don't know Access,
so I cannot really say. But I can't see that security has anything to
do with it. Configuration in Access? Maybe. But my gut feeling is that is
a bug in Access, and my guess is that the troublesome workstations have
a different version of Access than the others. That, or of some
component that Access uses to get the information.

Signature

Reply to this Message

Rich - 21 Mar 2008 16:08 GMT

thank you for the explanation about TestSP;1... That makes sense now.

And I agree that it is probably a bug with Access about how it deals with
connections/procedures...

I am in the process of migrating everything to a .Net platform anyway. In
the meantime, the place where I am at has some MS Access apps in place that I
have to support. Oh well, I will have to keep experimenting.

> > The Access program can invoke several other stored procedures. This
> > procedure is the recordsource for a report. It runs fine on a few
[quoted text clipped - 33 lines]
> a different version of Access than the others. That, or of some
> component that Access uses to get the information.

Reply to this Message

Rich - 21 Mar 2008 18:59 GMT

I just discovered that the workstations with the problems are using the
deployed version of Access and do not have the Access application installed.
The fix is to install the full Access application on these machines.

> thank you for the explanation about TestSP;1... That makes sense now.
>
[quoted text clipped - 42 lines]
> > a different version of Access than the others. That, or of some
> > component that Access uses to get the information.

Reply to this Message