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

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.



"Erland Sommarskog" wrote:

Rich (Rich@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
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
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).

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.

--
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: Many Installations of MSSQLSERVER.
    ... > target workstations by putting the workstations into an OU and/or ... > If these people's accounts are in the local Administrators or Power Users ... > you can install MSDE on workstation, or if you install the SQL server CD, ...
    (microsoft.public.win2000.windows_update)
  • Re: Many Installations of MSSQLSERVER.
    ... > target workstations by putting the workstations into an OU and/or ... > If these people's accounts are in the local Administrators or Power Users ... > you can install MSDE on workstation, or if you install the SQL server CD, ...
    (microsoft.public.windows.group_policy)
  • Re: Many Installations of MSSQLSERVER.
    ... > target workstations by putting the workstations into an OU and/or ... > If these people's accounts are in the local Administrators or Power Users ... > you can install MSDE on workstation, or if you install the SQL server CD, ...
    (microsoft.public.sqlserver.security)
  • Re: Many Installations of MSSQLSERVER.
    ... > target workstations by putting the workstations into an OU and/or ... > If these people's accounts are in the local Administrators or Power Users ... > you can install MSDE on workstation, or if you install the SQL server CD, ...
    (microsoft.public.win2000.security)
  • Re: MSAccess can run SP on some workstations but not others - why?
    ... The Access program can invoke several other stored procedures. ... workstations, but the error message on the other workstations says: ... The connection is fine. ... How is the connection to SQL Server set up in Access? ...
    (microsoft.public.sqlserver.security)