@@Identity after insert in SP 1

[SprintFlunky]

I have a stored procedure passing in the variables I want inserted. The key is a generated Sequence number that I want returned if the insert was successful. Normal INSERT INTO... Values (@..., @,,,); . Then I issue a Set @newRec = Select @@Identity. The @newRec is defined as a passed variable OUTPUT along with all of the passed input variables for the insert.

Here is the problem - I get the new record number returned from the SP, however, the row was not inserted into the table. If I remove the Select statement, the insert works just fine.

It there some trick to passing input and output variables to astored procedure?

Thanks for the help,

Flunky

 

[cmmrfrds]

Are you saying because you do this in the store procedure
@newRec = Select @@Identity
the record does not get inserted?

How are you verifying that it did not get inserted?
Need more information on the flow in the SP.
Also, how you are looking at the identity value in the APP.

 

[SprintFlunky]

I do not have the code with me but it is something like this:

Using MSDE 2000 with an Access ADP project to interface to the tables and stored procedures. All program access is through VB .net SQLAdapters.

TableA has 3 columns, 1 is the auto sequence field
In the store procedure:

Create InsertRec
@var1 varchar(15)
@var2 varchar(15)
@newSeq int OUTPUT
AS
Insert into TableA values (@var1, var2);
@newSeq = Select @@Identity
Return

The Program creates SQLParameter fields for all 3 passed fields. The @newSeq is further defined as OUTPUT

When I call the SP with reference to the @@Identity, I get the new Identity number back in @newSeq, however, when I go into Access to look at the table, the row was not inserted.

When I remove all references to @newSql, the row is inserted correctly.

Any Ideas?

 

[cmmrfrds]

I don't know how the net adapter works, but I am familiar with the ADO connection with the OLEDB provider for sql server. Sql server typically generates informational messages that are sent back to ADO in the form of a recordset. You may need to read these before the output variable becomes available in the app. You could set up a loop to read all recordsets sent back to the app, but this is probably unnecessary if you suppress the informational messages.
In the SP make sure NOCOUNT = ON which will suppress the informational stuff associated with the insert.
Just a thought - not sure.

Another thought, if you have triggers on the table the @@identity value may be wrong. Best to use SCOPE_IDENTITY so that you get the identity that is on your table instead of some identity of an underlying triggered table.

 

[SprintFlunky]

I think it has something to do with the combination of input and output variables so I do a little more research on MSDN. An no triggers yet - I'm still learning so all my SQL is real simple Selects, updates, Inserts, and deletes. This is the first insert with a generated key.

I tried scope_identity, however, Access ADP does not like command that for some reason. I am not sure if all the SQL-T commands are available in the MSDE database.

I'll take a look at the NOCOUNT = ON and thanks for the input.

 

[cmmrfrds]

What size/data type is in the Access app?

An Access integer is 2 bytes while an sql server int is 4 bytes - big difference. In Access make sure it is at least a Long integer.

Your use of the input/output parameters is quite standard. The SET NOCOUNT = ON may fix it, I would try that first.

 

[SprintFlunky]

Good point about the integer size - forgot about that one - and I'll past back about the SET NOCOUNT = ON once I get a chance to test it.

Thanks again - have a STAR!

 

[cmmrfrds]

This link has an example.

http://www.aspfaq.com/show.asp?id=2174

Instead of using an output variable this example sends the identity back as a recordset with 1 record 1 field, which should be easy to deal with. Alternative if you still have trouble with the output variable.