Defining the Scope of 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.

Default Values in Queries

Hello, I'm trying not to have NULL values in columns.
So I defined a default value ('n.d.') on a column named DDT.
How can i retrieve the rows with the default value in DDT column ?
I mean:
select OrderID, DateOfOrder
from
OrderTable
where
CustomerID=@.CustID
and
DDT is [DDT Column DEFAULT]
instead of :
select OrderID, DateOfOrder
from
OrderTable
where
CustomerID=@.CustID
and
DDT is NULL (or DDT='n.d.')
How does the query change if i define a Default Value DF_NULL char(4) = 'n.d
.'
and assign the defalut value of DDT column to DF_NULL ?
Thank you for help.
MicheleMichele wrote:
> Hello, I'm trying not to have NULL values in columns.
So, have you set the column to "NOT NULL"? That's the best way to prevent
NULLS from being stored in the column ...

> So I defined a default value ('n.d.') on a column named DDT.
> How can i retrieve the rows with the default value in DDT column ?
> I mean:
> select OrderID, DateOfOrder
> from
> OrderTable
> where
> CustomerID=@.CustID
> and
> DDT is [DDT Column DEFAULT]
> instead of :
> select OrderID, DateOfOrder
> from
> OrderTable
> where
> CustomerID=@.CustID
> and
> DDT is NULL (or DDT='n.d.')
> How does the query change if i define a Default Value DF_NULL char(4)
> = 'n.d.' and assign the defalut value of DDT column to DF_NULL ?
> Thank you for help.
> Michele
If you set the DDT column to "NOT NULL", then it will never contain NULL.
Are you saying that you won't know at runtime what the default value for DDT
is? Why wouldn't you just use:
WHERE DDT='n.d.'
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> So, have you set the column to "NOT NULL"? That's the best way to prevent
> NULLS from being stored in the column ...
Yes the column DDT is set to NOT NULL.

> Are you saying that you won't know at runtime what the default value for D
DT
> is?
Yes.

>Why wouldn't you just use:
> WHERE DDT='n.d.'
Because for some NOT NULL columns the default is 'n.d.' for others is
'<unknown>' for others '000000', so I'd like to treat the default value of
the column as a parameter for the query (if possible), like a ... where DDT
is NULL (in case of DDT column NULL, but this is not the case)
Thank's.|||Michele wrote:
> Yes the column DDT is set to NOT NULL.
>
> Yes.
>
> Because for some NOT NULL columns the default is 'n.d.' for others is
> '<unknown>' for others '000000', so I'd like to treat the default
> value of the column as a parameter for the query (if possible), like
> a ... where DDT is NULL (in case of DDT column NULL, but this is not
> the case)
>
I've never attempted to do this (I always know what the default values are
in my columns ... <g,d&r> )
I suppose you could create a scaler udf that uses the sp_columns procedure,
or queries the INFORMATION_SCHEMA.Columns table, to retrieve the column's
default value.
WHERE DDT=fColDefault(DDT)
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.