Access ADP,Server 2000, inconsistent behavior with output paramete

Access ADP,Server 2000, inconsistent behavior with output paramete

Post by bWFsY29sb » Fri, 31 Mar 2006 13:27:01


I am getting inconsistent responses from SQL Server 2000 and do not know why.
I populate the controls on an unbound form by executing a command object that
calls a sproc that returns a recordset.

On SQL Server 2000 the srpoc returns the recordset used to fill the form's
controls plus an output parameter indicating a record was received. The
output parameter is the value of @@ROWCOUNT. If the value of the output
parameter = 0, the user gets an appropriate message concerning the failure to
retrieve the data.

The server on my desktop returns both the recordset for populating the form
and the value of @@ROWCOUNT from the database. No problem.

However, when I connect to an identical dataase on a server in Chicago using
a VPN, all I get back is the recordset. The value of the output parameter is
always zero, telling me no record was selected.

However, the record was selected as the data populates the form.

I can easily branch to the needed message using "If rst.BOF and rst.EOF
then..." but I'd like to know why the output parameter does not get returned
or is always zero when I use the VPN to connect to the database.

If I use a command object to execute a sproc that does not return a
recordset, I do get back any desired output parameters. This inconsistent
behavior only appears when the executed command object returns a recordset.

Is there a setting on the Chicago server causing the problem?

--
malcolm
 
 
 

Access ADP,Server 2000, inconsistent behavior with output paramete

Post by Sylvain La » Fri, 31 Mar 2006 16:05:14

How are you sure that these two databases are really identical?

One possibility would be that the server in Chicago has a different order
for fields inside a table. This could cause some trouble with ADP is you are
using things like Select * instead of Select field1, field2, ...

When you change the connection, the Tables and the Views/SP/Functions
windows should be refreshed but we never know what may have happened. One
way to be sure would be to use the Refresh command for *BOTH* the Tables and
the Views/SP/Functions windows and/or to recompile the ADP file.

Another possibility would be a permission problem or a logon account that is
not mapped to the same role.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://www.yqcomputer.com/




>I am getting inconsistent responses from SQL Server 2000 and do not kno>
>wh>.
> I populate the controls on an unbound form by executing a command objec>
> th>t
> calls a sproc that returns a recordse>. >>
> On SQL Server 2000 the srpoc returns the recordset used to fill the form>s
> controls plus an output parameter indicating a record was received. T>e
> output parameter is the value of @@ROWCOUNT. If the value of the outp>t
> parameter = 0, the user gets an appropriate message concerning the failur>
> >o
> retrieve the dat>. >>
> The server on my desktop returns both the recordset for populating th>
> fo>m
> and the value of @@ROWCOUNT from the database. No proble>. >>
> However, when I connect to an identical dataase on a server in Chicag>
> usi>g
> a VPN, all I get back is the recordset. The value of the output paramete>
> >s
> always zero, telling me no record was selecte>. >>
> However, the record was selected as the data populates the for>. >>
> I can easily branch to the needed message using "If rst.BOF and rst.E>F
> then..." but I'd like to know why the output parameter does not ge>
> return>d
> or is always zero when I use the VPN to connect to the databas>. >>
> If I use a command object to execute a sproc that does not return>a
> recordset, I do get back any desired output parameters. This inconsiste>t
> behavior only appears when the executed command object returns >
> recordse>. >>
> Is there a setting on the Chicago server causing the proble>? >>
> ->
> malcolm