INSERT INTO only appears to work

[john0532]

When using the T-SQL debugger the sp appears to be working, returning the new record number from a SELECT statement. However 98% of the time the Enterprise Manager shows no new records in the table. It's the fact that it works occasionally that has me stumped. The Profiler says the sp is doing 300+ reads and 0 writes.

SQL Server 2000

here's the sp:

CREATE PROCEDURE [dbo].[spTestCopyJob]

AS
declare @newrecnum int
declare @oldrecnum int
declare @jobid int
declare @asite varchar(50)
declare @model varchar(50)
declare @shop varchar(50)
declare @cc varchar(50)
declare @position varchar(50)
declare @job varchar(50)
declare @RetVal int
declare @counter int
declare @LML datetime

set @jobid = 34631
set @asite = 'EVERETT'
set @model = '767'
set @shop = 'TW330'
set @cc = '201'
set @position = '06'
set @job = '12e312r_copy1'

select @LML = Lastmodifiedlayout from jobs where [job id] = @jobid

BEGIN TRANSACTION T1
insert into [testspcopyjob] (site, model, shop, cc, position, job, lastmodifiedlayout)
values(@asite, @model, @shop, @cc, @position, @job, @LML)
COMMIT TRANSACTION T1

--insert into jobs (site, model, shop, cc, position, job, Lastmodifiedlayout)
select site, model, shop, cc, position, @job, lastmodifiedlayout
from [testspcopyjob]
where [job id] = @jobid
select @newrecnum=[job id]
from [testspcopyjob]
where site = @asite and model = @model and shop = @shop and cc = @cc and position = @position and job = @job

select @counter = count(*)
from [testspcopyjob]
where site = @asite and model = @model and shop = @shop and cc = @cc and position = @position and job = @job
print 'counter='
print @counter

set @RetVal = @newrecnum
GO

 

[mkal]

You are not testing to see if the insert fails but still you commit the transaction, what happens if for some reason the insert fails.

Try something like this.

Declare @err int
Begin Tran T1
insert into [testspcopyjob] (site, model, shop, cc, position, job, lastmodifiedlayout)
values(@asite, @model, @shop, @cc, @position, @job, @LML)
Select @err = @@error
If @err <> 0
Begin
Rollback Tran T1
Return @err
End
else
Begin
Commit Tran T1
End

 

[john0532]

It doesn't recognize it as failing to do the insert. @@error is still 0 so that doesn't catch it.

This select statement from the sp grabs the new record number successfully:
select @newrecnum=[job id]
from [testspcopyjob]
where site = @asite and model = @model and shop = @shop and cc = @cc and position = @position and job = @job

This tells me how many were created, 0 if insert failed.
select @counter = count(*)
from [testspcopyjob]
where site = @asite and model = @model and shop = @shop and cc = @cc and position = @position and job = @job

 

[Mdavis123]

John,

The hypens are a typeo right???
--insert into jobs (site, model, shop, cc, position, job, Lastmodifiedlayout)
This would fail all the time but explain all the reads and no writes.

Otherwise I don't see anything that would cause what your talking about.

MikeD

 

[SonOfEmidec1100]

The two hyphens make that line a comment no error would returned and as MikeD says no writes to Jobs table.