I'm losing my mind at times it seems. I have a pretty simple update
statement with a subquery. The subquery always returns 1 row but the table
never gets updated.

update tPhNums
set intDeptId =
(select intDeptId
from tDepts
where tDepts.idDeptId=tPhNums.idPhNumId);

"int" fields are integers, "id" fields are GUIDs.

The last few times I executed it, I got "ElevateDB Error #9999 Access
violation at address 00403EAA in module 'edbsrvr.exe'. Read of address
00000004". I'm using b10 C/S.

If I pick a valid GUID from tPhNums, and use that specifically, it does not
update tPhNums either.
select idDeptId
from tDepts
where tDepts.idDeptId='{D39C7B41-F6EA-4695-BD53-A33C7664886B}'  <<< this
always returns 1 row

Yet, the following update does not update tPhNums either.
update tPhNums
set intDeptId =
(select intDeptId
from tDepts
where tDepts.idDeptId='{D39C7B41-F6EA-4695-BD53-A33C7664886B}' );

I'm going to shut everything down and keep trying if the AV clears up.

Does anyone see something that I'm having a brain hiccup over? I'm on hour
#9 and going nowere. I don't want to build a cursor and iterate through
the rows because that's overkill I think.

Thanks!!


jr

I disconnected all connections from the server cleanly, and restarted the
ElevateDB server. And it partly works. I am concerned why it always failed,
created AVs until I had to disconnect and restart the server though...

This now works:
> select idDeptId
> from tDepts
> where tDepts.idDeptId='{D39C7B41-F6EA-4695-BD53-A33C7664886B}'

It is updating tPhNums but setting the intPhNumId field to null... not what
I'm expecting because  (and why I think I'm partly brain dead at times!).
Should this syntax update tPhNums to the value in tDepts only on the rows
that have matching idDeptId field values? (this is my intent):
> update tPhNums
> set intDeptId =
> (select intDeptId
> from tDepts
> where tDepts.idDeptId=tPhNums.idPhNumId);

This is the syntax I'm more familiar with, and I already know I can't use it
with ElevateDB:
update tPhNums
set intDeptId=tDepts.intDeptid
from tPhNums inner join tDepts on tPhNums.idDeptid = tDepts.idDeptid

I appreciate any suggestions/feedback.


jr

James,

<< The last few times I executed it, I got "ElevateDB Error #9999 Access
violation at address 00403EAA in module 'edbsrvr.exe'. Read of address
00000004". I'm using b10 C/S. >>

Could you send me the database catalog and tables that you're using ?  I'll
need to run that query to see if I can reproduce the AV.

<< If I pick a valid GUID from tPhNums, and use that specifically, it does
not update tPhNums either. >>

Same situation - I'll have to check it out with your data to see what the
issue is.

--
Tim Young
Elevate Software
www.elevatesoft.com

James,

<< It is updating tPhNums but setting the intPhNumId field to null... not
what I'm expecting because  (and why I think I'm partly brain dead at
times!).  Should this syntax update tPhNums to the value in tDepts only on
the rows that have matching idDeptId field values? (this is my intent): >>

Yes, that should be the case.

--
Tim Young
Elevate Software
www.elevatesoft.com

> Could you send me the database catalog and tables that you're using ?
> I'll
> need to run that query to see if I can reproduce the AV.

I can't reproduce it often enough to make it worthwhile pursuing.

>
> << If I pick a valid GUID from tPhNums, and use that specifically, it does
> not update tPhNums either. >>
>
> Same situation - I'll have to check it out with your data to see what the
> issue is.

I initially thought it was my problem, and while building the test db I'm
convinced it is a "me issue". I'll work thru it and see where I went wrong.
No need to pester you with it.

Thanks though.


jr

James,

<< I initially thought it was my problem, and while building the test db I'm
convinced it is a "me issue". I'll work thru it and see where I went wrong.
No need to pester you with it. >>

Well, if you need me to look at anything at all, just let me know.

--
Tim Young
Elevate Software
www.elevatesoft.com