SQL Code that is submitted to server changes when run on different clients

[Dale]

I have an application that works fine when I run it on my own client,
but when it is run on a different client the SQL code that is generated
is different.... Let me explain...

I have an access ADP that in one of the reports, it uses a User-defined
function(UDF) as its recordsource. By watching it run using SQL
Profiler, I can see that when I run it on my PC, the SQL code that is
sent to the SQL Server is correct, in that it selects all of the
columns I was expecting it to. When I run it on another machine, the
SQL that is generated is..... "Select 1 from
"dbo"."udf_Author_Mailing"() and not all the columns.

I dont think that the issue is a security thing, as i have tested this
on a machine with a person logged on as an SA, and DBO of the database
I am using, with the same results.

I had them try running the UDF directly, and they are able to run the
UDF and return data correctly. But when they run the report, all it
does is return the word - ERROR#.

If this is not clear, I will try to explain differently if needed.

Dale

 

[Philipp Stiefel]

I have an application that works fine when I run it on my own
client, but when it is run on a different client the SQL code
that is generated is different.... Let me explain...

I have an access ADP that in one of the reports, it uses a
User-defined function(UDF) as its recordsource. By watching it
run using SQL Profiler, I can see that when I run it on my PC,
the SQL code that is sent to the SQL Server is correct, in that
it selects all of the columns I was expecting it to. When I run
it on another machine, the SQL that is generated is.....
"Select 1 from "dbo"."udf_Author_Mailing"() and not all the
columns.

Have you verified that the same version of MDAC is installed
on both clients?

Did you try to use "SELECT * FROM dbo.yourUDF()" as Recordsource
for the report?

cheers
Phil

 

[Dale]

Yes, both clients are running the same MDAC version.... I dont see any
broken references..... I am stumped. I have taken the same UDF and
created a quick report, and It works on the other client. The only
difference that I can see is that in the other reports, I use the
"TRIM" command to get rid of spaces, when I concatenate address fields
together, for formatting. Where does the "TRIM"Command reside ?? Is
that in an external reference of some kind ??

 

[Philipp Stiefel]

Yes, both clients are running the same MDAC version.... I dont see any
broken references..... I am stumped. I have taken the same UDF and
created a quick report, and It works on the other client. The only
difference that I can see is that in the other reports, I use the
"TRIM" command to get rid of spaces, when I concatenate address fields
together, for formatting. Where does the "TRIM"Command reside ?? Is
that in an external reference of some kind ??

The Trim-Function resides in the VBA-Library (VBE6.DLL). While
there may be problems with references and TRIM
(see http://www.mvps.org/access/bugs/bugs0001.htm) I don't think
that this is the reason for this problem.

Nevertheless you may as well remove the spaces in you UDF on the
server (use LTrim + RTrim there) and get rid of the client-side
TRIM.

What about my suggestion to use "SELECT * FROM dbo.yourUDF()"?

Cheers
Phil