Cannot Execute SP From Windows XP Machine

[MikeC]

I'm currently testing an Access XP project (ADP) on various PCs around our
office. This project connects to an MSDE 2000 back end using trusted
authentication. The application works fine except from *one* PC.

------------------------------------------------------
The error message is shown below:

"The recordsource 'sp_GetEmployeeInfo' specified on this form or report does
not exist.

You misspelled the name, or it was deleted or renamed in the current
database or it exists in a different database.

In the form or report's design view, display the property sheet by clicking
on the Properties button, and then set the RecordSource property to an
existing table or query."
------------------------------------------------------

The problem machine has the *Windows XP* operating system. The other
machines have Windows 2000 and are *not* experiencing any problems.

The *same* user (i.e. same permissions) can run the application from the
other machines without getting the above error, so my first guess is that
this problem is somehow related to Windows XP.

Upon further investigation, I discovered that this user cannot access any
other stored procedures from the Windows XP machine. The user has execute
permissions for all the stored procedures and, as mentioned above, this
problem occurs on only one machine. I also found that none of the database
objects are displaying in the database window for this user on this machine.
The database objects do display for the same user if he connects from a
Windows 2000 machine.

Can anyone tell me what I need to do to resolve this problem?

 

[Sylvain Lafontaine]

You first error is probably to use the prefix sp_ for your own stored
procedures. Rename any custom stored procedures beginning with sp_ or xp_
and check if your problem is still there after that.

S. L.

 

[Sylvain Lafontaine]

Just to be sure, it was not a joke: SQL-Server gives special treatment to
stored procedures beginning with sp_ and xp_; which often leads to this kind
of problems (as well to other problems).

S. L.

 

[MikeC]

Yes, I understand that. It was my oversight.

I have renamed the stored procedures and their references. Anything that
was named using, "sp_" is now using "stp_" instead. I granted execute
permissions on the renamed permissions just to be sure the user has
permission to execute them.

I re-tested the application and the same error displays, but with the new
stored procedure name. Other ideas?

 

[Sylvain Lafontaine]

Yes, here another one: did you take the precaution of specifying dbo. as
the owner of your stored procedures ("Record Source Qualifier" property) or
if you have left this field blank ?

I observed in the past that ADP doesn't always follow the usual rules and
will specify the name of the user account as the name of the owner to be
used instead of using dbo. if you leave this field blank; with the effect
that SQL-Server will search for UserName.stp_StoredProcedure instead of
searching for dbo.stp_StoredProcedure.

S. L.

 

[MikeC]

Yes, the "Record Source Qualifier" was set to "dbo" in all cases. I also
specified "dbo." inside the stored procedures. Below is an example. This
is the procedure for the specific error described in the below thread.
Other forms that reference stored procedures experienced the same type of
error, but this should be enough to give you an idea how the procedures are
written.
-------------------------------------------------------------
ALTER PROCEDURE stp_GetEmployeeInfo
AS
SET NOCOUNT ON
SELECT
e.EmployeeID
, e.LastName
, e.FirstName
, e.PhoneNo
, t.JobTitle
, e.UserAccount
, e.Email
, s.SectionName
, e.FESecRoleID
FROM
dbo.tblEmployee e, dbo.lutTitle t, dbo.lutSection s
WHERE
e.UserAccount = Right(System_User, Len(System_User) - CharIndex('\',
System_User))
AND t.TitleID = e.TitleID
AND s.SectionID = e.SectionID
RETURN
-------------------------------------------------------------

It is also worth emphasizing that these stored procedures work fine on all
the other machines that are running the Win2K O/S. Something appears to be
different about the WinXP machine. I'm wondering if there might be some
type of O/S or network configuration setting that could be causing the
problem.

We still cannot rule-out the possibility that something is wrong with the
stored procedures or the ADP, but I seem to be running out of things to
check. :-(

 

[Sylvain Lafontaine]

To be honest, I had numeros problems with ADP-2002 running on WinXP after
the installation of SP2; none of these like your but since then, I have
erased Office 2002 from my machine and now I'm running 2003 exclusively.

Before SP2, things were fine with ADP-2002. With 2003, no problem other
than the numerous bugs for ADP which are already known and which were
already there before SP2.

But in your case, I would make sure that all service packs has been applied,
including the latest version of MDAC. Does this problem occurs with only
one WinXP machine or with other WinXP machines as well? If you are using an
alias, make sure also that it has been correctly configured. We never know
were we can have made a little error by distraction. Check also the
references in the VBA window, refresh the tables and the queries windows
(F5) and recompile all the procedures.

Also, make sure that you are not directly using an ADP file coming from a
W2K machine to a WinXP machine without recompiling all the stuff.

Personally, I doesn't have any problem using ADP 2003 on WinXP for
connecting to a remote machine but I don't use a trusted connection.

S. L.

 

[MikeC]

I just decompiled/recompiled on the WinXP machine and re-tested the ADP
client. The problem did not go away.

However, I found another WinXP machine and installe/tested the ADP client.
It worked perfectly!!! Both of the WinXP machines are running Windows XP
SP1 and have Office 2002 SP3 installed. Both machines are running MDAC 2.7
SP1. Right now, I'm guessing that there is something wrong with the Office
2002 installation on the machine that is getting the errors.