SCOPE_IDENTITY is not a recognized function name

Richard12345 · **Joined:** Sat Jun 09, 2007 9:40 am **Posts:** 11

I've been trying to learn this for over 2 weeks and it's a bit hard. I can see the rewards but am getting close to throwing in the towel and going back to plain ADO.Net Sorry for the rant! I think I"m a good programmer, been doing it for about 35 years, dot net since 1999

Pointing me to the manual section or an example would be cool if you can't figure this out.

I'm trying to insert a plain record with an auto-incremented indentity column. Here are the details:

Here is what looks like the error:

Why does it throw in the select SCOPE_IDENTITY() function. I"m runn MS SQL Server version 6.5

Code:

INSERT INTO cash (Account_Number, Access_Code, Allow_Edits, Transaction_Amount, Transaction_Date, Transaction_Type, USD_Transaction_Amount, UserId) VALUES (?, ?, ?, ?, ?, ?, ?, ?); select SCOPE_IDENTITY() 

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">

  <class name="XYZ.Accounting.CashTransaction.Core.Domain.Cash, CashTransaction" table="cash" lazy="false">
    <id name="Id" type="Int32" unsaved-value="null">
      <column name="Identifier" length="4" sql-type="int" not-null="true" index="PK_Cash" unique="true"/>
      <generator class="native" />
    </id>

    <property name="AccountNumber" column="Account_Number" length ="18" />
    <property name="AccessCode" column="Access_Code" length ="4" />
    <property name="AllowEdits" column="Allow_Edits" length ="1" />
    <property name="TransactionAmount" column="Transaction_Amount" length ="8" />
    <property name="TransactionDate" column="Transaction_Date" length ="8" />
    <property name="TransactionType" column="Transaction_Type" length ="2" />
    <property name="USDTransactionAmount" column="USD_Transaction_Amount" length ="8" />
    <property name="UserId" column="UserId" length ="30" />

    <!--
-->    
    
  </class>

</hibernate-mapping>

Code:

using System;
using System.Collections.Generic;
using System.Text;

namespace XYZ.Accounting.CashTransaction.Core.Domain
{
    /// <summary>
    /// Cash object for NHibernate mapped table 'Cash'.
    /// </summary>
    [Serializable]
    public class Cash
    {
      #region Member Variables

        public int      id;

        public int      accessCode;
        public string   accountNumber;
        public decimal  transAmount;
        public DateTime transDate;
        public string   transType;
        public decimal  uSDTransactionAmount;
        public string   userId;
        
        public virtual int Id
        {
            get { return id; }
            set { id = value; }
        }

        public virtual Int32 AccessCode
        {
            get { return accessCode; }
            set { accessCode = value; }
        }

        public virtual string AccountNumber
        {
            get { return accountNumber; }
            set { accountNumber = value; }
        }

        public virtual decimal TransactionAmount
        {
            get { return transAmount; }
            set { transAmount = value; }
        }

        public virtual DateTime TransactionDate
        {
            get { return transDate; }
            set { transDate = value; }
        }

        public virtual string TransactionType
        {
            get { return transType; }
            set { transType = value; }
        }

        public virtual decimal USDTransactionAmount
        {
            get { return uSDTransactionAmount; }
            set { uSDTransactionAmount = value; }
        }
        
        public virtual string UserId
        {
            get { return userId; }
            set { userId = value; }
        }

        public Cash()
        {
        }

        #endregion

        public override bool Equals(object obj)
        {
            return base.Equals(obj);
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }
    }
}

Code:

            ITransaction transaction = session.BeginTransaction();
            Cash c2 = new Cash();

            c2.AccountNumber = "46108119885";
            c2.AccessCode = 0;
            c2.AllowEdits = Convert.ToChar("N");
            c2.TransactionAmount = 123.45M;
            c2.TransactionDate = Convert.ToDateTime("5/1/2007");
            c2.TransactionType = "PU";
            c2.USDTransactionAmount = 555.22M;
            
            session.Save(c2);

Code:

2007-06-20 21:30:03,198 [10] NHibernate.Transaction.AdoTransaction.Begin(:0) - begin

2007-06-20 21:30:03,198 [10] NHibernate.Connection.DriverConnectionProvider.GetConnection(:0) - Obtaining IDbConnection from Driver

2007-06-20 21:30:05,089 [10] NHibernate.Impl.SessionImpl.DoSave(:0) - saving [XYZ.Accounting.CashTransaction.Core.Domain.Cash#<null>]

2007-06-20 21:30:05,104 [10] NHibernate.Impl.SessionImpl.ExecuteInserts(:0) - executing insertions

2007-06-20 21:30:05,120 [10] NHibernate.Persister.Entity.AbstractEntityPersister.Insert(:0) - Inserting entity: XYZ.Accounting.CashTransaction.Core.Domain.Cash (native id)

2007-06-20 21:30:05,136 [10] NHibernate.Impl.BatcherImpl.LogOpenPreparedCommand(:0) - Opened new IDbCommand, open IDbCommands: 1

2007-06-20 21:30:05,136 [10] NHibernate.Impl.BatcherImpl.Generate(:0) - Building an IDbCommand object for the SqlString: INSERT INTO cash (Account_Number, Access_Code, Allow_Edits, Transaction_Amount, Transaction_Date, Transaction_Type, USD_Transaction_Amount, UserId) VALUES (?, ?, ?, ?, ?, ?, ?, ?); select SCOPE_IDENTITY()

2007-06-20 21:30:05,136 [10] NHibernate.Persister.Entity.AbstractEntityPersister.Dehydrate(:0) - Dehydrating entity: [XYZ.Accounting.CashTransaction.Core.Domain.Cash#<null>]

2007-06-20 21:30:05,136 [10] NHibernate.Type.NullableType.NullSafeSet(:0) - binding '46108119885' to parameter: 0

2007-06-20 21:30:05,136 [10] NHibernate.Type.NullableType.NullSafeSet(:0) - binding '0' to parameter: 1

2007-06-20 21:30:05,136 [10] NHibernate.Type.NullableType.NullSafeSet(:0) - binding 'N' to parameter: 2

2007-06-20 21:30:05,151 [10] NHibernate.Type.NullableType.NullSafeSet(:0) - binding '123.45' to parameter: 14

2007-06-20 21:30:05,151 [10] NHibernate.Type.NullableType.NullSafeSet(:0) - binding '5/1/2007' to parameter: 15

2007-06-20 21:30:05,151 [10] NHibernate.Type.NullableType.NullSafeSet(:0) - binding 'PU' to parameter: 16

2007-06-20 21:30:05,151 [10] NHibernate.Type.NullableType.NullSafeSet(:0) - binding '555.22' to parameter: 17

2007-06-20 21:30:05,151 [10] NHibernate.Type.NullableType.NullSafeSet(:0) - binding null to parameter: 18

2007-06-20 21:30:05,167 [10] NHibernate.Impl.BatcherImpl.LogCommand(:0) - INSERT INTO cash (Account_Number, Access_Code, Allow_Edits, Transaction_Amount, Transaction_Date, Transaction_Type, USD_Transaction_Amount, UserId) VALUES (@p0, @p1, @p2, @p14, @p15, @p16, @p17, @p18); select SCOPE_IDENTITY(); @p0 = '46108119885', @p1 = '0', @p2 = 'N', @p3 = '', @p14 = '123.45', @p15 = '5/1/2007 12:00:00 AM', @p16 = 'PU', @p17 = '555.22', @p18 = ''

2007-06-20 21:30:05,198 [10] NHibernate.Impl.BatcherImpl.LogClosePreparedCommand(:0) - Closed IDbCommand, open IDbCommands: 0

2007-06-20 21:30:05,214 [10] NHibernate.Util.ADOExceptionReporter.LogExceptions(:0) - could not insert: [XYZ.Accounting.CashTransaction.Core.Domain.Cash] [INSERT INTO cash (Account_Number, Access_Code, Allow_Edits, Transaction_Amount, Transaction_Date, Transaction_Type, USD_Transaction_Amount, UserId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]

System.Data.SqlClient.SqlException: 'SCOPE_IDENTITY' is not a recognized function name.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlCommandInfo sql, Object obj, ISessionImplementor session)
2007-06-20 21:30:05,214 [10] NHibernate.Util.ADOExceptionReporter.LogExceptions(:0) - System.Data.SqlClient.SqlException: 'SCOPE_IDENTITY' is not a recognized function name.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlCommandInfo sql, Object obj, ISessionImplementor session)

2007-06-20 21:30:05,214 [10] NHibernate.Util.ADOExceptionReporter.LogExceptions(:0) - 'SCOPE_IDENTITY' is not a recognized function name.

Code:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Cash]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Cash]
GO

CREATE TABLE [dbo].[Cash] (
   [Management_Code] [varchar] (2) NOT NULL ,
   [Account_Number] [varchar] (18) NOT NULL ,
   [Transaction_Amount] [money] NOT NULL ,
   [Currency] [varchar] (3) NOT NULL ,
   [Bank_Transaction_Identifier] [int] NOT NULL ,
   [Debit_Or_Credit] [char] (1) NULL ,
   [Allow_Edits] [char] (1) NOT NULL ,
   [Status] [char] (1) NOT NULL ,
   [UserId] [varchar] (30) NOT NULL ,
   [Parent_id] [int] NULL ,
   [Accounting_Month] [datetime] NULL ,
   [Computation_date] [datetime] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Cash] WITH NOCHECK ADD 
   CONSTRAINT [PK_Cash] PRIMARY KEY  CLUSTERED 
   (
      [Identifier],
      [Bank_Transaction_Identifier]
   ) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Cash] ADD 
   CONSTRAINT [DF__CASH__Bank_Trans__11BF94B6] DEFAULT (1) FOR [Bank_Transaction_Identifier],
   CONSTRAINT [DF__CASH__Allow_Edit__12B3B8EF] DEFAULT ('N') FOR [Allow_Edits],
   CONSTRAINT [DF__CASH__Status__13A7DD28] DEFAULT ('O') FOR [Status],
   CONSTRAINT [DF__CASH__UserId__149C0161] DEFAULT ('XX') FOR [UserId],
   CONSTRAINT [DF_Cash_Parent_id] DEFAULT (1) FOR [Parent_id]
GO

 CREATE  INDEX [Cash_AcctNum_TDate_IX] ON [dbo].[Cash]([Account_Number], [Transaction_Date]) ON [PRIMARY]
GO

 CREATE  INDEX [Cash_MgmtCd_FundCd_TDate_IX] ON [dbo].[Cash]([Management_Code], [Fund_Code], [Transaction_Date]) ON [PRIMARY]
GO

 CREATE  INDEX [Cash_TDate_TType_IX] ON [dbo].[Cash]([Transaction_Date], [Transaction_Type]) ON [PRIMARY]
GO

/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_155_1] ON [dbo].[Cash] ([Management_Code]) ')
GO

/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_155_10] ON [dbo].[Cash] ([Effective_Date]) ')
GO

/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_155_5] ON [dbo].[Cash] ([Transaction_Type]) ')
GO

/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_155_10_1] ON [dbo].[Cash] ([Effective_Date], [Management_Code]) ')
GO

/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_155_10_1_5_8] ON [dbo].[Cash] ([Effective_Date], [Management_Code], [Transaction_Type], [USD_Transaction_Amount]) ')
GO

Richard12345 · **Joined:** Sat Jun 09, 2007 9:40 am **Posts:** 11

I changed <generator class="native" /> to <generator class="increment" />

Now I get

System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'Cash' when IDENTITY_INSERT is set to OFF.

Is there a way to do this without changing the settings in the db?

karlchu · **Posted:** Thu Jun 21, 2007 1:14 am

I believe the minimum SQL Server version supported is 7. See here:
http://www.hibernate.org/hib_docs/nhibe ... l-dialects
I don't know much about SQL Server 6.5; it looks to me SCOPE_IDENTITY function does not exist in 6.5 based on the exception message.

As to your second post, an "increment" generator basically does a "SELECT MAX(ID)", add one, then it will explicitly insert that ID value. Since the database column is auto increment, IDENTITY_INSERT has to be "on" to do that successfully. Have a look here to learn more about the differences between the different generators:
http://www.hibernate.org/hib_docs/nhibe ... -generator

Richard12345 · **Joined:** Sat Jun 09, 2007 9:40 am **Posts:** 11

Thank you for your reply. You basically confirmed what I suspected, so that saves me a lot of time in pursuing this angle further.

I had looked at the sql 6.5 help and it did not have SCOPE_IDENTITY but sql 2000 did.

I had read that chapter on generator and tried every possble option. I'm not sure if I'll be able to turn IDENTITY_INSERT due to non-technical issues, ie: it's a big production database, lots of users, etc. but will certainly explore that option. I'll definetly test it on our development server.

However, the seemingly good news is that I discovered the session.Connection.CreateCommand() I'm hoping with that I can do the same ExecuteNonQueries that I'm familiar with. With this strategy I can use nH for the mapping, non-managed entities (transformer beans thing), and possibly the .Save .Update and .Flush which are things I've got mostly working. As I become more versant in nH I can start using more of it's features. This will give me time to get the system deliverable and still use all the best practices of nH I had a feeling it would be good for something :)

mabraham · **Joined:** Mon Mar 20, 2006 10:49 pm **Posts:** 59

A point and a question.

Point: I can confirm that SQL Server 6.5 does not support SCOPE_IDENTITY.

Question: If the Identifier column of table Cash was not an IDENTITY column, most of your problems would go away. If you are able to modify the schema to make Identifier a non-identity column, I would urge you to do so.

Another point: You should definitely not try to work around the problem by using SET IDENTITY_INSERT ON. This should only be used for one off operations.

Yet another point: Note that the increment generator is not cluster aware, so will not work if you are updating the DB from multiple sites.

Richard12345 · **Joined:** Sat Jun 09, 2007 9:40 am **Posts:** 11

I might explore SET IDENTITY_INSERT ON on a one off basis. In the same session would I exec the SET IDENTITY_INSERT ON and then do the insert?

As far as removing the identity, I agree it would make life simpler to us technicians. However banks are a bit picky about cash. I guess its a business domain , legacy, old school type of thing.

What would you suggest for an immutable table record that loads records from different feeds plus GUI's? An application controlled counter?

mabraham · **Joined:** Mon Mar 20, 2006 10:49 pm **Posts:** 59

You can implement a SQL 6.5 dialect by subclassing the SQL 2000 dialect and overriding the method that gets the select identity string (it's called something like GetSelectIdentityString).

You can look at the Sybase dialect for a model, since it still uses SELECT @@IDENTITY.