MS SQL Server - Problem on insert using stored procedure

Author

Problem on insert using stored procedure

edemasi

2005-07-15, 9:23 am

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)='',
@numeroenregistremen
t 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)='',
@numeroenregistremen
tpli varchar(20)='' ,
@taches varchar(50)='' ,
@languecontactentran
t varchar(4)='' ,
@languecommunication
varchar(4)='',
@pays varchar(4)='' ,
@datefinprevisionnel
le varchar(20)='' ,
@datelimitetraitemen
t varchar(20)=''
AS

begin transaction

IF (@numeroenregistreme
nt <> '' AND @numeroenregistremen
tpli ='' )
BEGIN
SET @numeroenregistremen
tpli = @numeroenregistremen
t
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,
numeroenregistrement
pli,
taches,
languecontactentrant
,
languecommunication,

pays,
datefinprevisionnell
e,
datelimitetraitement
)
VALUES (
@F_WobNum,
@codeuser,
@datedebutetat,
@datefinetat,
@datereception,
@datecreationdossier
,
@numeroenregistremen
t,
@activites,
@produit,
@do,
@idclient,
@etatdossier,
@mediaentree,
@mediasortie,
@datefindossier,
@segmentclient,
@actions,
@etatexportdata,
@numeroenregistremen
tpli,
@taches,
@languecontactentran
t,
@languecommunication
,
@pays,
@datefinprevisionnel
le,
@datelimitetraitemen
t)

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

Alejandro Mesa

2005-07-15, 11:23 am

Do not use a transaction in the second sp and use "set nocount on" in both
sps, as the first statement.

AMB

"edemasi" wrote:

> 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)='',
> @datecreationdossie
r varchar(20)='',
> @numeroenregistreme
nt 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)='',
> @numeroenregistreme
ntpli varchar(20)='' ,
> @taches varchar(50)='' ,
> @languecontactentra
nt varchar(4)='' ,
> @languecommunicatio
n varchar(4)='',
> @pays varchar(4)='' ,
> @datefinprevisionne
lle varchar(20)='' ,
> @datelimitetraiteme
nt varchar(20)=''
> AS
>
>
> begin transaction
>
> IF (@numeroenregistreme
nt <> '' AND @numeroenregistremen
tpli ='' )
> BEGIN
> SET @numeroenregistremen
tpli = @numeroenregistremen
t
> 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,
> datecreationdossie
r,
> numeroenregistreme
nt,
> activites,
> produit,
> do,
> idclient,
> etatdossier,
> mediaentree,
> mediasortie,
> datefindossier,
> segmentclient,
> actions,
> etatexportdata,
> numeroenregistreme
ntpli,
> taches,
> languecontactentra
nt,
> languecommunicatio
n,
> pays,
> datefinprevisionne
lle,
> datelimitetraiteme
nt)
> VALUES (
> @F_WobNum,
> @codeuser,
> @datedebutetat,
> @datefinetat,
> @datereception,
> @datecreationdossi
er,
> @numeroenregistrem
ent,
> @activites,
> @produit,
> @do,
> @idclient,
> @etatdossier,
> @mediaentree,
> @mediasortie,
> @datefindossier,
> @segmentclient,
> @actions,
> @etatexportdata,
> @numeroenregistrem
entpli,
> @taches,
> @languecontactentr
ant,
> @languecommunicati
on,
> @pays,
> @datefinprevisionn
elle,
> @datelimitetraitem
ent)
>
> 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
>

Sponsored Links