Bizarre INSERT behavior - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Bizarre INSERT behavior

Author

Topic

ron2112
Starting Member

44 Posts

Posted - 2008-01-18 : 09:40:41

We witnessed some very strange behavior from one of our SPs yesterday. In brief, the SP contains two INSERT statements. The first INSERT statement only posted half the values it was supposed to, the second worked fine. No errors were reported.

The SP's parameters, and the values we passed to them, are as follows:

@RFQRecNum int, =1
@TaskRecNum int, =2
@Quantity int, =4
@Size nvarchar(20), ='1/2'
@Description nvarchar(50), ='BOGUS-1'
@Unit float, =33.33
@UM nvarchar(2) ='FT'

The INSERT statements (in the order they appear in the SP) are as follows:

INSERT INTO TRC_RFQDetails
( RFQ_Rec_Num,
Size,
Description,
UM,
Quantity,
Unit,
AddedManually,
TaskRecNum)
VALUES
( @RFQRecNum,
@Size,
@Description,
@UM,
@Quantity,
@Unit,
'true',
@TaskRecNum)

INSERT INTO JBM_TasksMaterial
( TaskRecNum,
Budget,
Inst,
Size,
Description,
Unit,
UM,
Amount,
Stock_Number,
Item_Rec_Num,
UPC,
Manufacturer,
CatalogNumber,
CEData)
VALUES
( @TaskRecNum,
0,
0,
@Size,
@Description,
@Unit,
@UM,
0,
NULL,
NULL,
NULL,
NULL,
NULL,
'false')

The resulting records looked like this:

TRC_RFQDetails
RFQ_Rec_Num = 1
Size = '1/2'
Description = 'BOGUS-1'
UM = 'FT'
Quantity = 0 <<<INCORRECT (field has a default of 0)
Unit = NULL <<<INCORRECT
AddedManually = 'false' <<<INCORRECT (this is field's default)
TaskRecNum = NULL <<<INCORRECT

JBM_TasksMaterial
TaskRecNum = 1 <<<CORRECT!
Budget = 0
Inst = 0
Size = '1/2'
Description = 'BOGUS-1'
Unit = 33.33 <<<CORRECT!
UM = 'FT'
Amount = 0
Stock_Number = NULL
Item_Rec_Num = NULL
UPC = NULL
Manufacturer = NULL
CatalogNumber = NULL
CEData = 'false'

As you can see, the first INSERT only posted half the values to the record, and stopped there. The second INSERT had no problems, and there was no error message. We restored a backup of the DB and ran the SP again with the same values, and got the same results. Then we ran the SP a second time with nearly identical values and it worked fine. Then we restored the backup again and ran the SP with the original set of values, and it worked fine. We haven't been able to reproduce it since.

We've gone over this ad nauseum, and we're just not seeing how this is possible. But it's scary. Has anyone seen this before, or maybe have some explanation as to how it might have happened?

Thanks
Ron Moses

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-18 : 11:50:43

A few possibilities
The SP you are calling isn't what you think it is
There is a trigger on table
The row didn;t come from the SP - maybe the row was already there and the first insert errored on the insert (do you check for errors?).
The row was updated after the insert.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources