Access pass-through query Doesn't work

I am having trouble getting a pass-through query in Microsoft Access 2002 to
work on one of my computers. Here's the setup:
Computer 1: Windows XP Professional, MDAC 2.8, MS Access 2002
Computer 2: Windows 2000 Server, MDAC 2.6, MS Access 2002, Running SQL Serve
r 2000
My Pass Through Query invokes a stored procedure in SQL Server 2000 on Compu
ter 2 and returns the number of records found in the query. The pass through
query uses an ODBC Connection to get to SQL Server 2000.
When I use the Query in computer 2 (same one with the SQL Server) the correc
t answer is returned. When I use the same query in computer 1, the answer r
eturned is always 0 (zero), which is incorrect.
I can use the same query through the Enterprise Manager on SQL Server 2000 a
nd it also returns the correct answer.
I have upgraded the MDAC version from 2.5RTM to 2.8 on Computer 1, but it ma
de no difference. When I use the same ODBC Connection on Computer 1 that I
used with other linked tables on the same Microsoft Access database on compu
ter 1 (linked to SQL Server
on computer 2), I am able to successfully view, modify or delete the data on
the SQL server, so it shouldn't be a permission problem.
Does anyone have any suggestions as to how I might resolve this problem?
Thanks for any helpful suggestions.
dm at herald21.com (for real email address, remove spaces and replace at wit
h at symbol)Try running profiler when you execute the query from the PC
where it's not working - that may give you some more
information on what's going on.
Also, did you use SET NOCOUNT ON in the stored procedure? If
not, try adding that to the stored procedure.
If this is the only stored procedure or PT query that you
are having the problems with, you may want to post the ddl
for the stored procedure as it could help in understanding
what the issues might be.
-Sue
On Wed, 17 Mar 2004 10:01:10 -0800, "Dick Marcum"
<anonymous@.discussions.microsoft.com> wrote:

>I am having trouble getting a pass-through query in Microsoft Access 2002 t
o work on one of my computers. Here's the setup:
>Computer 1: Windows XP Professional, MDAC 2.8, MS Access 2002
>Computer 2: Windows 2000 Server, MDAC 2.6, MS Access 2002, Running SQL Serv
er 2000
>My Pass Through Query invokes a stored procedure in SQL Server 2000 on Comp
uter 2 and returns the number of records found in the query. The pass throug
h query uses an ODBC Connection to get to SQL Server 2000.
>When I use the Query in computer 2 (same one with the SQL Server) the corre
ct answer is returned. When I use the same query in computer 1, the answer
returned is always 0 (zero), which is incorrect.
>I can use the same query through the Enterprise Manager on SQL Server 2000
and it also returns the correct answer.
>I have upgraded the MDAC version from 2.5RTM to 2.8 on Computer 1, but it made no d
ifference. When I use the same ODBC Connection on Computer 1 that I used with other
linked tables on the same Microsoft Access database on computer 1 (linked to SQL Se
rve
r on computer 2), I am able to successfully view, modify or delete the data on the SQL serv
er, so it shouldn't be a permission problem.
>Does anyone have any suggestions as to how I might resolve this problem?
>Thanks for any helpful suggestions.
>dm at herald21.com (for real email address, remove spaces and replace at with at sy
mbol)|||Thanks Sue for steering me in the right direction. The Profiler did indeed
point out the error of my ways. It turned out the pass-through query was no
t passing through a required leading 0 on one of the varchar parameters. I
have corrected that and it
works correctly now. Thanks again
Dick