Interesting I tried this which is what I think you were suggesting... 

#!perl

use Win32::ODBC;
my $db=new Win32::ODBC('DB_SERVER');

my $sql="INSERT INTO T_Test (C_Data) VALUES ('data');";    #2
$db->Sql($sql);

sleep(20);

$sql="SELECT \@\@IDENTITY AS C_ID;";
$db->Sql($sql);
$db->FetchRow();
print $db->Data("C_ID")."\n";

[download]

It runs the insert, pauses for 20 seconds, gets the last identity value and outputs to the screen.

During the 20 second pause you can rerun the same script but without the pause in that version and both versions pull the correct identity value. This can be verified by watching the database through query analyzer. I guess it mean the the @@IDENTITY value must be unique to each session which I didn't realise.... and which of course sorts my main issue so ta for that. =D

Would still be nice to know why the INSERT-SELECT fails when the SELECT-SELECT works while using MoreResults() if anyone knows though.

In reply to Re^4: Win32::ODBC and MoreResults() by Lyndley
in thread Win32::ODBC and MoreResults() by Lyndley

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.