ADP Update Query does NOT update !

[prideaux]

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?

 

[Sylvain Lafontaine]

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]

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 ).

 

[mustafa Turkoglu]

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