Problem saving/retrieving unicode characters NVARCHAR with unicode collation (java jdbc)

I'm connecting to a SQL Server 2005 database using the latest (beta) sql server driver (Microsoft SQL Server 2005 JDBC Driver 1.1 CTP June 2006) from within Java (Rational Application Developer).

The table in SQL Server database has collation Latin1_General_CI_AS and one of the columns is a NVARCHAR with collation Indic_General_90_CI_AS. This should be a Unicode only collation. However when storing for instance the following String:

€_￡_ùúü?ùúü?_???????￡????_???_??????_прстуф_????
... it is saved with ? for all unicode characters as follows (when looking in the database):
€_￡_ùúü?ùúü?_???????￡????_???_?_?_?

The above is not correct, since all unicode characters should still be visible. When inserting the same string directly into the sql server database (without using Java) the result is ok.

Also when trying to retrieve the results again it complains about the following error within Java:

Codepage 0 is not supported by the Java environment.

Hopefully somebody has an answer for this problem. When I alter the collation of the NVARCHAR column to be Latin1_General_CI_AS as well, the data can be stored and retrieved however then of course the unicode specific characters are lost and results into ? So in that case the output is as described above (ie €_￡_ùúü?ùúü?_???????￡????_???_?_?_?)

We would like to be able to persist and retrieve unicode characters in a SQL Server database using the correct JDBC Driver. We achieved this result already with an Oracle UTF8 database. But we need to be compliant with a SQL Server database as well. Please help.

Thanks in advance for your help.So far nobody replied on my question. unfortunately.
Please help me to sort out this problem.

Bottom line we just want to be able to save and retrieve Unicode characters in SQL server within our Java application. We want to do this by setting the NVARCHAR columns in all tables to a Unicode collation.

Please help.
Thanks.|||

Hugo,

Can you tell me whether you have sendStringParametersAsUnicode set to "false" in your connection properties?

Thank you,

--David Olix

JDBC Development

|||

Hi David,

Thanks for your reply. I can ensure that I didn't set the sendStringParametersAsUnicode to "false". This parameter is default set to "true" so I didn't change it in the connection properties.

I found out that the unicode Collation I used "Indic_General_90_CI_AS" had a Codepage 0 in my SQL Server 2005 installation. In fact all unicode only collations do have a codepage 0 if I check them all using the following command:

SELECT COLLATIONPROPERTY('Indic_General_90_CI_AS', 'CodePage') as CodePage

Is this perhaps related to the problem I have in Java? Because in there the exception explains about the fact that the codepage is 0.

I really hope you can help me to sort this out.

Thanks in advance for your help David.

Hugo

|||Hi David,

I found the solution. When updating a SQL Server 2005 NVARCHAR column, you have to use the following syntax:

UPDATE <table> SET <column> = N'<value>'

So for example:
UPDATE customer SET id = '€_???????￡????_???_??????_прстуф_????'

Has to be this for a SQL Server connection:

UPDATE customer SET id = N'€_???????￡????_???_??????_прстуф_????'

The prefix N before the value is required to make sure to save the string in Unicode format. I did not do this so far. Now I'm able to store and retrieve data into a SQL Server database and my collatin is now Latin1_General_CI_AS since this supports Unicode as well.

Hugo|||

hi hugo

this is gr8 thread

i have facing a problem

i want to insert indic values in a table in SQL server

the update statement u mentioned is useful

UPDATE <table> SET <column> = N'<value>'

but when i use INSERT INTO <table> VALUES(N'<value>')

can u tell me y doesnt this work

tahnx in advance.

Problem saving/retrieving unicode characters NVARCHAR with unicode collation (java jdbc)

I'm connecting to a SQL Server 2005 database using the latest (beta) sql server driver (Microsoft SQL Server 2005 JDBC Driver 1.1 CTP June 2006) from within Java (Rational Application Developer).

The table in SQL Server database has collation Latin1_General_CI_AS and one of the columns is a NVARCHAR with collation Indic_General_90_CI_AS. This should be a Unicode only collation. However when storing for instance the following String:

€_￡_ùúü?ùúü?_???????￡????_???_??????_прстуф_????
... it is saved with ? for all unicode characters as follows (when looking in the database):
€_￡_ùúü?ùúü?_???????￡????_???_?_?_?

The above is not correct, since all unicode characters should still be visible. When inserting the same string directly into the sql server database (without using Java) the result is ok.

Also when trying to retrieve the results again it complains about the following error within Java:

Codepage 0 is not supported by the Java environment.

Hopefully somebody has an answer for this problem. When I alter the collation of the NVARCHAR column to be Latin1_General_CI_AS as well, the data can be stored and retrieved however then of course the unicode specific characters are lost and results into ? So in that case the output is as described above (ie €_￡_ùúü?ùúü?_???????￡????_???_?_?_?)

We would like to be able to persist and retrieve unicode characters in a SQL Server database using the correct JDBC Driver. We achieved this result already with an Oracle UTF8 database. But we need to be compliant with a SQL Server database as well. Please help.

Thanks in advance for your help.
So far nobody replied on my question. unfortunately.
Please help me to sort out this problem.

Bottom line we just want to be able to save and retrieve Unicode characters in SQL server within our Java application. We want to do this by setting the NVARCHAR columns in all tables to a Unicode collation.

Please help.
Thanks.
|||

Hugo,

Can you tell me whether you have sendStringParametersAsUnicode set to "false" in your connection properties?

Thank you,

--David Olix

JDBC Development

|||

Hi David,

Thanks for your reply. I can ensure that I didn't set the sendStringParametersAsUnicode to "false". This parameter is default set to "true" so I didn't change it in the connection properties.

I found out that the unicode Collation I used "Indic_General_90_CI_AS" had a Codepage 0 in my SQL Server 2005 installation. In fact all unicode only collations do have a codepage 0 if I check them all using the following command:

SELECT COLLATIONPROPERTY('Indic_General_90_CI_AS', 'CodePage') as CodePage

Is this perhaps related to the problem I have in Java? Because in there the exception explains about the fact that the codepage is 0.

I really hope you can help me to sort this out.

Thanks in advance for your help David.

Hugo

|||Hi David,

I found the solution. When updating a SQL Server 2005 NVARCHAR column, you have to use the following syntax:

UPDATE <table> SET <column> = N'<value>'

So for example:
UPDATE customer SET id = '€_???????￡????_???_??????_прстуф_????'

Has to be this for a SQL Server connection:

UPDATE customer SET id = N'€_???????￡????_???_??????_прстуф_????'

The prefix N before the value is required to make sure to save the string in Unicode format. I did not do this so far. Now I'm able to store and retrieve data into a SQL Server database and my collatin is now Latin1_General_CI_AS since this supports Unicode as well.

Hugo
|||

hi hugo

this is gr8 thread

i have facing a problem

i want to insert indic values in a table in SQL server

the update statement u mentioned is useful

UPDATE <table> SET <column> = N'<value>'

but when i use INSERT INTO <table> VALUES(N'<value>')

can u tell me y doesnt this work

tahnx in advance.

Problem on insert using 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
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)='',
> @.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
>

Problem on insert using 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
GODo 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)='',
> @.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
>

Problem on insert using 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
GODo 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 reccuren
t,
> 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
>