I have a (very simple and standard) UPDATE statement which works fine either
directly in Query Analyser, or executed as a stored procedure in Query
Analyser.

UPDATE A
SET
A.field1 = B.col1
, A.field2 = B.col2
FROM
tblA AS A INNER JOIN tblB AS B
ON A.pk1 = B.pk1 AND A.pk2 = B.pk2

Problem is when i execute the same stored proc via microsoft ADP (by
double-clicking on the sproc name or using the Run option), it says "query
ran successfully but did not return records" AND does NOT update the records
when i inspect the tables directly.

Before anyone even says "syntax of MS-Access is different than SQLServer
T-SQL", i remember that with ADP everything happens on the server and one is
actually passing thru to T-SQL.

What's going on here?

Which version of ADP/Access are you using and what's exactly the full code
of the SP? Also, take a look with the SQL-Server Profiler, maybe this will
put some light on it.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"prideaux" <(E-Mail Removed)> wrote in message
news:33E2CD0A-CF58-4E18-8D27-(E-Mail Removed)...
>I have a (very simple and standard) UPDATE statement which works fine
>either
> directly in Query Analyser, or executed as a stored procedure in Query
> Analyser.
>
> UPDATE A
> SET
> A.field1 = B.col1
> , A.field2 = B.col2
> FROM
> tblA AS A INNER JOIN tblB AS B
> ON A.pk1 = B.pk1 AND A.pk2 = B.pk2
>
> Problem is when i execute the same stored proc via microsoft ADP (by
> double-clicking on the sproc name or using the Run option), it says "query
> ran successfully but did not return records" AND does NOT update the
> records
> when i inspect the tables directly.
>
> Before anyone even says "syntax of MS-Access is different than SQLServer
> T-SQL", i remember that with ADP everything happens on the server and one
> is
> actually passing thru to T-SQL.
>
> What's going on here?

Ok found my own answer.

Tools / Options / Advanced / Client-Server Settings / Default max records
set at 10,000. Change this to 0 for unlimited.

My table had 100,000+ rows and whatever set of 10,000 it was updating was
difficult to find ( among a sea of 90,000+ un-updated ).

"prideaux" wrote:

> I have a (very simple and standard) UPDATE statement which works fine either
> directly in Query Analyser, or executed as a stored procedure in Query
> Analyser.
>
> UPDATE A
> SET
> A.field1 = B.col1
> , A.field2 = B.col2
> FROM
> tblA AS A INNER JOIN tblB AS B
> ON A.pk1 = B.pk1 AND A.pk2 = B.pk2
>
> Problem is when i execute the same stored proc via microsoft ADP (by
> double-clicking on the sproc name or using the Run option), it says "query
> ran successfully but did not return records" AND does NOT update the records
> when i inspect the tables directly.
>
> Before anyone even says "syntax of MS-Access is different than SQLServer
> T-SQL", i remember that with ADP everything happens on the server and one is
> actually passing thru to T-SQL.
>
> What's going on here?

Don't use alias name for the updated table. This is an SQL issue.
Better use Query designer in SQL server Managment Studio.

UPDATE tblA
SET field1=B.Col1 , field2=B.col2
FROM tblA inner join tblB as B ON tblA.Pk1= B.pk1


"Sylvain Lafontaine" wrote:

> Which version of ADP/Access are you using and what's exactly the full code
> of the SP? Also, take a look with the SQL-Server Profiler, maybe this will
> put some light on it.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "prideaux" <(E-Mail Removed)> wrote in message
> news:33E2CD0A-CF58-4E18-8D27-(E-Mail Removed)...
> >I have a (very simple and standard) UPDATE statement which works fine
> >either
> > directly in Query Analyser, or executed as a stored procedure in Query
> > Analyser.
> >
> > UPDATE A
> > SET
> > A.field1 = B.col1
> > , A.field2 = B.col2
> > FROM
> > tblA AS A INNER JOIN tblB AS B
> > ON A.pk1 = B.pk1 AND A.pk2 = B.pk2
> >
> > Problem is when i execute the same stored proc via microsoft ADP (by
> > double-clicking on the sproc name or using the Run option), it says "query
> > ran successfully but did not return records" AND does NOT update the
> > records
> > when i inspect the tables directly.
> >
> > Before anyone even says "syntax of MS-Access is different than SQLServer
> > T-SQL", i remember that with ADP everything happens on the server and one
> > is
> > actually passing thru to T-SQL.
> >
> > What's going on here?
>
>
>