SQL Server Performance Forum – Threads Archive

Problem on insert threw stored procedure

Here is my problem on SQL Server SP3a.
I have a table that reach more than 2 millions rows. My primary key is an identity column. From a Java program I’m calling a strored procedure to insert a new row in that table and to get back the id of the new row using the scope_identity() function. I log the id returned by the first stored procedure and to ensure the line has been added I call a second stored procedure to look if my line exists in the table. That request returns a line and when I call a request from SQL Server enterprise manager on my id I get no line in my table. I really don’t understand what can be my problem because it’s not reccurent, most of the time it’s work fine. It seems that the first transaction is sometimes rolled back by the systems. If someone has an idea… Stored procedure 1 to INSERT:
=============================
CREATE procedure SetIndFpsLogWeb
@F_WobNum varchar(40)=” ,
@codeuser varchar(30)=” ,
@datedebutetat varchar(20)=”,
@datefinetat varchar(20)=”,
@datereception varchar(20)=”,
@datecreationdossier varchar(20)=”,
@numeroenregistrement varchar(16)=”,
@activites varchar(20)=” ,
@produit varchar(4)=” ,
@do varchar(4)=” ,
@idclient varchar(12)=”,
@etatdossier varchar(2)=”,
@mediaentree varchar(4)=”,
@mediasortie varchar(4)=”,
@datefindossier varchar(20)=”,
@segmentclient varchar(12)=”,
@actions varchar(50)=” ,
@etatexportdata varchar(1)=”,
@numeroenregistrementpli varchar(20)=” ,
@taches varchar(50)=” ,
@languecontactentrant varchar(4)=” ,
@languecommunication varchar(4)=”,
@pays varchar(4)=” ,
@datefinprevisionnelle varchar(20)=” ,
@datelimitetraitement varchar(20)=”
AS
begin transaction IF (@numeroenregistrement <> ” AND @numeroenregistrementpli =” )
BEGIN
SET @numeroenregistrementpli = @numeroenregistrement
END IF (@actions<> ” )
BEGIN
if( SUBSTRING(@actions, 1,1)=’;’)
BEGIN
SET @actions = SUBSTRING(@actions, 2, LEN(@actions))
END
END IF (@taches<> ” )
BEGIN
if( SUBSTRING(@taches, 1,1)=’;’)
BEGIN
SET @taches = SUBSTRING(@taches, 2, LEN(@taches))
END
END IF (@activites<> ” )
BEGIN
if( SUBSTRING(@activites, 1,1)=’;’)
BEGIN
SET @activites= SUBSTRING(@activites, 2, LEN(@activites))
END
END INSERT INTO ind_fps (
F_WobNum,
codeuser,
datedebutetat,
datefinetat,
datereception,
datecreationdossier,
numeroenregistrement,
activites,
produit,
do,
idclient,
etatdossier,
mediaentree,
mediasortie,
datefindossier,
segmentclient,
actions,
etatexportdata,
numeroenregistrementpli,
taches,
languecontactentrant,
languecommunication,
pays,
datefinprevisionnelle,
datelimitetraitement)
VALUES (
@F_WobNum,
@codeuser,
@datedebutetat,
@datefinetat,
@datereception,
@datecreationdossier,
@numeroenregistrement,
@activites,
@produit,
@do,
@idclient,
@etatdossier,
@mediaentree,
@mediasortie,
@datefindossier,
@segmentclient,
@actions,
@etatexportdata,
@numeroenregistrementpli,
@taches,
@languecontactentrant,
@languecommunication,
@pays,
@datefinprevisionnelle,
@datelimitetraitement) declare @return varchar(500)
if @@error <> 0
begin
set @return = ‘ERROR : ‘ + cast(@@error as varchar)
rollback tran
end
else
begin
set @return = scope_identity()
commit tran
end select @return
GO Stored procedure 2 to GET:
========================== CREATE procedure dbo.GetIndFpsInfosById
@indfps_id varchar(20) = ”
as
begin transaction
SELECT *
FROM [ind_fps]
WHERE id = @indfps_id
commit transaction
GO

This line
declare @return varchar(500)
will set @@error variable to 0 and you can’t then check it’s real value. WBR, Vlad A. Scherbinin

See if this helps:
http://www.sommarskog.se/error-handling-II.html
http://www.sommarskog.se/error-handling-I.html —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>