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 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 processing cube

I am getting the error:

Warning 2 Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: B@. - key@. of Dimension: Brcledger from Database: Misys, Cube: Misys 1, Measure Group: Brcledger, Partition: Brcledger, Record: 24163. 0 0

I have read that this is caused by a referential integrity problem however, the fact table is the dimension table, and the joint primary key is definitely unique. There doesn't seem to be anything special about record number 24163 So why can i not process my cube?

Martin

Just incase it is any help.. the other error message which comes up I have pasted here:

Warning 1 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_brcledger, Column: B_x0040_, Value: 0; Table: dbo_brcledger, Column: key_x0040_, Value: 474142463032485130310000000C. 0 0

SELECT *
FROM brcledger
WHERE (key@. = '474142463032485130310000000C')

Returns 1 result

|||

You have a situation when Analysis Server cannot find a key in the dimension while processing partition.

I would guess you might have problems with dimension process that lead to partition processing errors.

Try re-process your Brcledger dimension change processing options to report and stop on every error.

After you fix dimension problems you should be able to process your partitions without a problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I have the same problem but it only occurs when i define my dimension as a time dimension. ]

Also my fact table is a view which gets it's surrgate keys by joining to the dimension tables in its definiton. This might be connected but the cube processes fine with the same data source, fact table, dimensions etc as long as i dont define the dimension as a time dimension.

Also the value of the attribute keys it cant find is always zero '0'. There are the same number of errors in the processing as there are nulls in the key column of the fact table. So i assume the nulls are being converted to 0 and then the referential integrity goes.....

Please help,

thanks,

Problem on cross-tab

I want to insert Espacial field (Record number) in cross-tab report.
How is it possible?
It works nice on standered report but in cross-tab i can't do that.
eliasHello
Mr. Ello
u should write it details
so that i can----

write abt ur code|||hi siraj,

U know in standered report if u select the espacial field(Record Number)
and place it in details section then u can find the record number in the
report.

I want to do same thing in cross-tab report.

However I can't still solve ur question(2 see the total in individual page)

I did the solution which helped u to do that.(But if i place formula-3 in
report footer I can't see anything in individual page but only in last page)

If I place it in page footer then the same problem u written arises..

so help me......

ur ello|||Dear Ello
to solve my problem u can follw last help of Madhi

For ur problem i think u can use standard report & in standard report u can insert cross-tab where record count cn b used as u wish

i hv tested it :if u cn for ur problem u should inform me

bye

siraj

Problem inserting decimal value into SQL Server 2000

In my VS 2005 windows control I am inserting a record into a table using a proc.

One of the fields "Accuracy" should look like this 66.4, but when I isnert it from the proc itlooks like 66.0. If I bypass my proc and use an inser statement from SQL quey analyzer it look like it should 66.4.

What am I doing wrong in my proc...

CREATE PROCEDURE [dbo].[insMyLameProc]

@.PlayerName nvarchar(255),
@.Score int,
@.Rounds int,
@.Accuracy decimal,
@.CorrectPicks int,
@.IncorrectPicks int

AS

--Insert the new game score
--===============================================================================================
insert into wmTurnTileScores
(PlayerName, Score, Rounds, Accuracy, CorrectPicks, IncorrectPicks)

values (@.PlayerName, @.Score, @.Rounds, @.Accuracy, @.CorrectPicks, @.IncorrectPicks)
--===============================================================================================
GO

@.Accuracy decimal(18,2)

--The 2 gives it 2 decimal places.

|||

rpack79:

@.Accuracy decimal(18,2)

--The 2 gives it 2 decimal places.

This is right, but as a tip: don't use the 18 if you are sure you will not need it. (faster query + save space).

Good luck.

|||

Yup, that worked, thanks