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 running package with 'larger' amount of data

Dear,

I created a package getting data from files and database sources, doing some transformations, retrieving dimension id's and then inserting it into a fact table.

Running this package with a limited amount of data (about a couple of 100.000 records) does not result in any errors and everything goes fine.

Now running the same package (still in debug mode) with more data (about 2.000.000 rows) doesn't result in any errors as well, but it just stops running. In fact, it doesn't really stop, but it doesn't continue as well. If I've only been waiting for some minutes or hours, I could think it's still processing, but I waited for about a day and it still is 'processing' the same step.

Any ideas on how to dig further into this in order to find the problem? Or is this a known problem?

Thanks for your ideas,

Jievie

Look at the BuffersSpooled perf counter. If it's really up there, you're thrashing the disk.

Also, just look task manager under the performance tab. Look at Physical Memory available. Chances are, you're running up against memory limitations.

Depending on what your pipeline contains, you should be able to optimize out the bottlenecks as well as eliminate any applications running that may be competing for resources.

This white paper is helpful:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

K

|||

Dear Jievie,

Could it be possible that the package tries to update a row locked by an other application?

Succes,

Paul

|||

Dear KirkHaselden and Paul,

In the meanwhile, I did some checks you both proposed me to do and can tell you the following:

I can't imagine another application is locking any rows or tables, as my sources are some flat files and two tables that are only used by my SSIS package. I also checked the system views (for the tables), no locks are listed...|||

There are three things you're not telling us ... how much memory you have, what transformations you are performing, and how big your rows are.

You are indicating you are memory constrained, but we don't know how much is involved.

The transformations are important - some transformations such as sort, or lookup, or aggregate, can require a lot of memory. Others, such as data conversion are very lightweight.

You can see the size of your rows by double clicking on the paths between transformations and looking at the metadata tab there - it will tell you what data types you are using for each row, and from there you can start to get the size of the row. (Remember folks, "row" is not a useful metric - we've seen rows from a few bytes to several megabytes being passed through SSIS.)

Once you get us this additional information, we should be able to help more. And do read the performance whitepaper - it will give you many useful suggestions.

thanks

Donald

Problem Retrieving SCOPE_IDENTITY

A couple of Web applications in different SQL Server 2000 databases use SCOPE_IDENTITY to retrieve the key value of a record that was just inserted. It works--most of the time. However, from time to time the identity value is not retrieved. Evidence suggests that in these cases, a null value is being retrieved. This has forced me to come up with less-than-ideal workarounds for the missing identity value.

Does anyone have any idea why SCOPE_IDENTITY sometimes fails to retrieve the identity value and transmit it back to the Web page? Could a network issue cause the problem? Is there anything I can do other than rewrite the apps to use a different algorithm than using SCOPE_IDENTITY? Thanks.

I am not aware of any issues with SCOPE_IDENTITY(); this might be an application / connection issue and not a problem with SCOPE_IDENTITY(). I am certainly interested in the outcome of this. Can somebody please check me on this?|||

If you are using embedded SQL in your application it might be worth placing this logic into a stored procedure and calling that from your application. That should avoid any comms problems as the procedure will run or not run as a single call (and not have a problem between statements in the operation).

|||

Yes, the web app uses embedded SQL in classic ASP. The application was written in classic ASP and there has never been a good reason to rewrite it. The web app is the only application that performs DML on the table--there are no separate triggers or other ways into the table.

How could an embedded SQL statement in a single Web page cause scope problems? One Web page consulted during the research on this problem said this situation should be treated as a single scope.

I will probably try the stored procedure method. But I am curious as to why all sources practically demand that SCOPE_IDENTITY be used within a stored procedure when it is allowed to work in other situations.

Thanks for the input.

Problem retrieving data from a linked server to Oracle

Don't know if this is the correct section.
I set up a linked server to a Oracle 9.2 from SQL 2005.
How can I see the tables that can be accessed from SQL? in SQL 2000 I just clicked on the linked server to see tables and views, but in SQL 2005 Management Studio I cannot see them.

Then to see if the linked server is set up correctly I tryed some scripts:
I do a SELECT * FROM RFVO..POKER.TABLE1 and I get all the data

then I tryed the same on a different table
SELECT * FROM RFVO..POKER.BOLLE and get this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "RFVO" supplied inconsistent metadata for a column. The column "TB_CAMBIO" (compile-time ordinal 32) of object ""POKER"."BOLLE"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.

What does it mean? How can I solve it?
Do I have to test all the tables in the linked server to be sure I can access data or is there a smarter way?

TIA

The functionality to view remote tables/views for linked servers is not available in SQL2005 Management Studio. You can use the system stored procedure 'sp_tables_ex' with linked server name to retrieve remote tables/views information.

The error message from the select query is a provider error. The provider reports that the column "TB_CAMBIO" has Unicode string datatype (DBTYPE_WSTR or 130) when the query is compiled and float datatype (DBTYPE_R8 or 5) when the query is execute. You can workaround the provider error by executing a pass-through query using OPENQUERY or EXEC AT. The other alternative is to use the Oracle's OLE DB provider instead of MS ORA provider.

|||Thanks for your answer!
I cannot install Oracle OLE DB provider on that machine. by now
I tried linking Oracle server using OLE DB for ODBC driver and creating an ODBC using microsoft driver on the SQL 2005 machine but now I have a different error "Error converting data type DBTYPE_DBTIMESTAMP to datetime."

Any suggestion on how to set up a linked server to oracle will be geatly appreciated!
IgorB|||see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Try using Openquery

|||I installed the Oracle OLE DB Provider, and everything seem to work correctly!
I think that the linked server will work also with OLE DB for ODBC and I am tryuing to do a test for performance reasones.

The error Error converting data type DBTYPE_DBTIMESTAMP to datetime is a data error: in a datetime field there is a strange value. Corrected the wrong value the select I posted in the first message now works.|||

FHANLON wrote:

see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Not this issue: there are no TIMESTAMP field (the error message is misleading), only DATE fields in Oracle.

FHANLON wrote:

Try using Openquery

I don't want to use Openquery, because I should rewrite a lot of stored procedures...sql

Problem retrieving data from a linked server to Oracle

Don't know if this is the correct section.
I set up a linked server to a Oracle 9.2 from SQL 2005.
How can I see the tables that can be accessed from SQL? in SQL 2000 I just clicked on the linked server to see tables and views, but in SQL 2005 Management Studio I cannot see them.

Then to see if the linked server is set up correctly I tryed some scripts:
I do a SELECT * FROM RFVO..POKER.TABLE1 and I get all the data

then I tryed the same on a different table
SELECT * FROM RFVO..POKER.BOLLE and get this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "RFVO" supplied inconsistent metadata for a column. The column "TB_CAMBIO" (compile-time ordinal 32) of object ""POKER"."BOLLE"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.

What does it mean? How can I solve it?
Do I have to test all the tables in the linked server to be sure I can access data or is there a smarter way?

TIA

The functionality to view remote tables/views for linked servers is not available in SQL2005 Management Studio. You can use the system stored procedure 'sp_tables_ex' with linked server name to retrieve remote tables/views information.

The error message from the select query is a provider error. The provider reports that the column "TB_CAMBIO" has Unicode string datatype (DBTYPE_WSTR or 130) when the query is compiled and float datatype (DBTYPE_R8 or 5) when the query is execute. You can workaround the provider error by executing a pass-through query using OPENQUERY or EXEC AT. The other alternative is to use the Oracle's OLE DB provider instead of MS ORA provider.

|||Thanks for your answer!
I cannot install Oracle OLE DB provider on that machine. by now
I tried linking Oracle server using OLE DB for ODBC driver and creating an ODBC using microsoft driver on the SQL 2005 machine but now I have a different error "Error converting data type DBTYPE_DBTIMESTAMP to datetime."

Any suggestion on how to set up a linked server to oracle will be geatly appreciated!
IgorB|||see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Try using Openquery

|||I installed the Oracle OLE DB Provider, and everything seem to work correctly!
I think that the linked server will work also with OLE DB for ODBC and I am tryuing to do a test for performance reasones.

The error Error converting data type DBTYPE_DBTIMESTAMP to datetime is a data error: in a datetime field there is a strange value. Corrected the wrong value the select I posted in the first message now works.|||

FHANLON wrote:

see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Not this issue: there are no TIMESTAMP field (the error message is misleading), only DATE fields in Oracle.

FHANLON wrote:

Try using Openquery

I don't want to use Openquery, because I should rewrite a lot of stored procedures...

Problem retrieving data from a linked server to Oracle

Don't know if this is the correct section.
I set up a linked server to a Oracle 9.2 from SQL 2005.
How can I see the tables that can be accessed from SQL? in SQL 2000 I just clicked on the linked server to see tables and views, but in SQL 2005 Management Studio I cannot see them.

Then to see if the linked server is set up correctly I tryed some scripts:
I do a SELECT * FROM RFVO..POKER.TABLE1 and I get all the data

then I tryed the same on a different table
SELECT * FROM RFVO..POKER.BOLLE and get this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "RFVO" supplied inconsistent metadata for a column. The column "TB_CAMBIO" (compile-time ordinal 32) of object ""POKER"."BOLLE"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.

What does it mean? How can I solve it?
Do I have to test all the tables in the linked server to be sure I can access data or is there a smarter way?

TIA

The functionality to view remote tables/views for linked servers is not available in SQL2005 Management Studio. You can use the system stored procedure 'sp_tables_ex' with linked server name to retrieve remote tables/views information.

The error message from the select query is a provider error. The provider reports that the column "TB_CAMBIO" has Unicode string datatype (DBTYPE_WSTR or 130) when the query is compiled and float datatype (DBTYPE_R8 or 5) when the query is execute. You can workaround the provider error by executing a pass-through query using OPENQUERY or EXEC AT. The other alternative is to use the Oracle's OLE DB provider instead of MS ORA provider.

|||Thanks for your answer!
I cannot install Oracle OLE DB provider on that machine. by now
I tried linking Oracle server using OLE DB for ODBC driver and creating an ODBC using microsoft driver on the SQL 2005 machine but now I have a different error "Error converting data type DBTYPE_DBTIMESTAMP to datetime."

Any suggestion on how to set up a linked server to oracle will be geatly appreciated!
IgorB|||see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Try using Openquery

|||I installed the Oracle OLE DB Provider, and everything seem to work correctly!
I think that the linked server will work also with OLE DB for ODBC and I am tryuing to do a test for performance reasones.

The error Error converting data type DBTYPE_DBTIMESTAMP to datetime is a data error: in a datetime field there is a strange value. Corrected the wrong value the select I posted in the first message now works.|||

FHANLON wrote:

see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Not this issue: there are no TIMESTAMP field (the error message is misleading), only DATE fields in Oracle.

FHANLON wrote:

Try using Openquery

I don't want to use Openquery, because I should rewrite a lot of stored procedures...

problem retrieving correct max(date) row from this dataset

Hello,
I respectfully request if someone could help me modify the following query:
The goal is to retrieve a record with a curExpireDate of 12/31/05 where ther
e
is more than one row for a given curRecordID - note: in the following
dataset there is only one correct row that I want to retrieve. So for given
curRecordID there is more than one row for a curExpireDate >= 12/31/05. The
n
if curEntryDate for a curExpireDate of 12/31/05 is greater than the
curEntryDate for curExpireDate of 12/31/06 (for the same curRecordID) then I
want to retrieve that row.
The following subdataset (the actual dataset contains thousands of rows
where I need to do the same thing) contains one row where Max(curExpireDate)
= 12/31/05. I don't want that row. There are 3 more curRecordID's with
curExpireDate of 12/31/05 and 12/31/06. For the rows with curRecordID =
18537 this ID contains a row for curExpireDate = 12/31/05 where
Max(curEntryDate) is greater than the curEntryDate for curExpireDate of
12/31/06 for ID = 18537. None of the otherID's has this condition. I need
to retrieve ID 18537 where curEntryDate = 1/27/06 and curExpireDate =
12/31/05.
Here is the dataset and the query I have been experimenting with:
create table #temp4(curRecordID int,
curEntryDate datetime, curExpireDate datetime)
insert Into #temp4
select 12783, '2005-04-07', '2005-12-31' Union
select 12783, '2004-01-12', '2005-12-31' Union
select 12783, '2006-02-03', '2006-12-31' Union
select 12783, '2005-01-11', '2006-12-31' Union
select 29714, '2005-06-29', '2005-12-31' Union
select 29714, '2005-02-02', '2006-12-31' Union
select 29714, '2005-12-01', '2006-12-31' Union
select 18537, '2004-12-10', '2005-12-31' Union
select 18537, '2006-01-27', '2005-12-31' Union --<--yes want this row
select 18537, '2006-01-10', '2006-12-31' Union
select 38537, '2004-12-10', '2005-12-31' --<--don't want this row
SELECT * FROM #temp4 as X
WHERE
curRecordID NOT IN --the list of recid from beginning
(SELECT curRecordID FROM #temp4 GROUP BY curRecordID
HAVING MAX(curExpireDate)='12/31/05')
AND
curExpireDate = '12/31/05' --order by curRecordID
AND curEntryDate >
(select top 1 curEntryDate from #temp4 as Y
where X.curRecordID = Y.curRecordID
and Y.curExpireDate =
(select max(curExpireDate) from #temp4 as Z
where X.curRecordID = Z.curRecordID))
This is the current resultset - which is not the desired resultset
curRecID curEntryDate curExpireDate
12783 2005-04-07 00:00:00.000 2005-12-31 00:00:00.000 --X
18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000 -- +
29714 2005-06-29 00:00:00.000 2005-12-31 00:00:00.000 --X
There is no way for me to isolate my desired row from this resultset
The desired resultset is this:
curRecID curEntryDate curExpireDate
18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000
Any help appreciated,
Thanks,
RichI think I found my problem. I changed
(select top 1 curEntryDate from #temp4 as Y...
to
(select Max(curEntryDate) from #temp4 as Y
This is now giving me the desired result in my test case. Hopefully, will
do the same for the actual data.
"Rich" wrote:

> Hello,
> I respectfully request if someone could help me modify the following query
:
> The goal is to retrieve a record with a curExpireDate of 12/31/05 where th
ere
> is more than one row for a given curRecordID - note: in the following
> dataset there is only one correct row that I want to retrieve. So for giv
en
> curRecordID there is more than one row for a curExpireDate >= 12/31/05. T
hen
> if curEntryDate for a curExpireDate of 12/31/05 is greater than the
> curEntryDate for curExpireDate of 12/31/06 (for the same curRecordID) then
I
> want to retrieve that row.
> The following subdataset (the actual dataset contains thousands of rows
> where I need to do the same thing) contains one row where Max(curExpireDat
e)
> = 12/31/05. I don't want that row. There are 3 more curRecordID's with
> curExpireDate of 12/31/05 and 12/31/06. For the rows with curRecordID =
> 18537 this ID contains a row for curExpireDate = 12/31/05 where
> Max(curEntryDate) is greater than the curEntryDate for curExpireDate of
> 12/31/06 for ID = 18537. None of the otherID's has this condition. I nee
d
> to retrieve ID 18537 where curEntryDate = 1/27/06 and curExpireDate =
> 12/31/05.
> Here is the dataset and the query I have been experimenting with:
> create table #temp4(curRecordID int,
> curEntryDate datetime, curExpireDate datetime)
> insert Into #temp4
> select 12783, '2005-04-07', '2005-12-31' Union
> select 12783, '2004-01-12', '2005-12-31' Union
> select 12783, '2006-02-03', '2006-12-31' Union
> select 12783, '2005-01-11', '2006-12-31' Union
> select 29714, '2005-06-29', '2005-12-31' Union
> select 29714, '2005-02-02', '2006-12-31' Union
> select 29714, '2005-12-01', '2006-12-31' Union
> select 18537, '2004-12-10', '2005-12-31' Union
> select 18537, '2006-01-27', '2005-12-31' Union --<--yes want this row
> select 18537, '2006-01-10', '2006-12-31' Union
> select 38537, '2004-12-10', '2005-12-31' --<--don't want this row
> SELECT * FROM #temp4 as X
> WHERE
> curRecordID NOT IN --the list of recid from beginning
> (SELECT curRecordID FROM #temp4 GROUP BY curRecordID
> HAVING MAX(curExpireDate)='12/31/05')
> AND
> curExpireDate = '12/31/05' --order by curRecordID
> AND curEntryDate >
> (select top 1 curEntryDate from #temp4 as Y
> where X.curRecordID = Y.curRecordID
> and Y.curExpireDate =
> (select max(curExpireDate) from #temp4 as Z
> where X.curRecordID = Z.curRecordID))
>
> This is the current resultset - which is not the desired resultset
> curRecID curEntryDate curExpireDa
te
> 12783 2005-04-07 00:00:00.000 2005-12-31 00:00:00.000 --X
> 18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000 -- +
> 29714 2005-06-29 00:00:00.000 2005-12-31 00:00:00.000 --X
> There is no way for me to isolate my desired row from this resultset
> The desired resultset is this:
> curRecID curEntryDate curExpireDa
te
> 18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000
> Any help appreciated,
> Thanks,
> Rich

Problem Retrieving @@Identity when inside a transaction.

I'm using transactions with my SqlConnection ( sqlConnection.BeginTransaction() ).
I do an Insert into my User table, and then subsequently use thegenerated identity ( via @.@.identity ) to make an insert into anothertable. The foreign key relationship is dependant on the generatedidentity. For some reason with transactions, I can't retrieve theinsert identity (it always returns as 1). However, I need theinserts to be under a transaction so that one cannot succeed while theother fails. Does anyone know a way around this problem?
So heres a simplefied explanation of what I'm trying to do:
Begin Transaction
Insert into User Table
Retrieve Inserted Identity (userID) via @.@.Identity
Insert into UserContact table, using userID as the foreign key.
Commit Transaction.
Because of the transaction, userID is 1, therefore an insert cannot bemade into the UserContact table because of the foreign key constraint.
I need this to be a transaction in case one or the other fails. Any ideas??


Try using Scope_Identity() instead of @.@.IDENTITY and see if that solvesyour problem. Scope_Identity will give you the last identityvalue generated in your current scope.
|||I tried using Scope_Identity() and I'm getting the exact same problem.
|||I can tell you that I just tried out your scenario and it worked withno problem for me. I tried it both wrapped in a transaction andnot. So there is something particular about your code that iscausing the problem. Could you post it here?
|||Fixed it. Had nothing to do with transactions. Stupid error on my part.
It seems as though I forgot to encapsulate my sql insert procedure in aBegin / End block, so that when I performed an insert it returned 1instead of the @.@.Identity which was on the next line.
Thanks for trying to help! Much appreciated =D
|||I'm glad you got it sorted. Be sure to use Scope_Identity()instead of @.@.IDENTITY. If at some point in the future, e.g., atrigger is added to your table which inserts a record into some table,@.@.IDENTITY will contain the Identity value of that inserted record, andthat's not what you want.