Trouble getting bigint identity value back from stored proc via ODBC

microsoft.public.sqlserver.programming

24 Aug 2006 9:52 PM

jscheller

Argh!

I'm running an ODBC 3.0 application against SQL Server 8.00.260 SP3 and
trying to get a 64 bit identity value back from a stored procedure I'm
calling via SQLExecDirect().

I have a table similar to the following...

CREATE TABLE [SomeTable] (
    [ID] [bigint] IDENTITY (1, 1) NOT NULL ,
    [ColX] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ColY] [int] NOT NULL ,
    [ColZ] [int] NOT NULL
) ON [PRIMARY]
GO

....and a stored procedure similar to below...

CREATE PROCEDURE procAddRow
    @X    varchar(50),
    @Y    int,
    @Z    int,
    @NewID    bigint    OUTPUT
AS
    INSERT INTO SomeTable (ColX,ColY,ColZ)
        VALUES (@X,@Y,@Z)
    Set @NewID = SCOPE_IDENTITY()
GO

....and I call it something like this...

__int64 foo = 999;

SQLBindParameter( m_Statement, 1, SQL_PARAM_OUTPUT, SQL_C_SBIGINT,
SQL_BIGINT, 0, 0, (void *) & foo, 0, 0L );

SQLExecDirect( m_Statement, "{call procAddRefRow('Text',123,321,?)}",
SQL_NTS );

....the stored procedure runs okay, but foo never changes. The row is
getting added fine at the database and there's no errors returned from
SQLExecDirect(). I've tried binding the parameter as SQL_CHAR, and it's
the same (nothing in the character buffer changes). I'm trapping for
errors and calling SQLGetDiagRec() if needed on all the calls leading
up to this, and nothing is reporting a failure.

Any insights greatly appreciated...

Thanks!

Jim

24 Aug 2006 9:55 PM

jscheller

That last line should actually read...

SQLExecDirect( m_Statement, "{call procAddRow('Text',123,321,?)}",
SQL_NTS );