When fetching @@IDENTITY column while inside doing inserts inside select/fetch loop, I am getting Nulls until the last portion of data passed from MSSQL and client (in Prefetch routine).
Really weird. I have not authored OleDB and I am a little bit lost in all those Ole interfaces, but I see nothing particulary wrong there anyway. Other selects work just as expected, just this one if wrong.
I have found a working workaround using IDENT_CURRENT('tablename'). Of course, it requires the name of table to be stored, so we have to look into statement executed if it is insert and what is after 'into'. Anyway, seems to fix the issue....
BTW, this is really so weird that I would like somebody else to check and try.
AFAIK, the critical moment is when 'outer' select loop has more rows than can be prefetched in single step. In that case, @@IDENTITY (and SCOPE_IDENTITY) does not work until the last prefetch is performed.
That OTOH means that unless you do inserts in large fetch loop, the bug is hidden.
Of course, still possible something is wrong in OleDB.
I have interesting followup to this bug - I believe I now know WHY and it is indeed unbelievable:
While working on ODBC, I have noticed strange problem - MSSQL server is in effect able to sustain only single SQL command with result-set (at least in default mode, there is something new called "MARS" to avoid this problem...)
This is debilitating condition make SQL apps development next to impossible (think exactly about the loop here - this is what MSSQL cannot do easily). That is why OLDDB introduces concept of "hidden connections".
To put it straight, when it detects there is some result set pending, it opens another connection to database. That means a new session that knows nothing about previous identity insert.
Now if result set is small, it is (in our testcase) fetched in one go and no more connections need to be created. However, if it is large, OLEDB creates another connection with new session.
What implication this unbelievable thing has to transaction safety or prepared statments I do not dare to predict...
In any case, I am now going to fix my ODBC U++ connector so that it will work with single ODBC statement only and fetch if required. I guess it is the only safe option...