Using: LLBL Gen Pro ver 2.0.0.0 27-may-2007 .Net 3.0 SQL Server 2005

I'm having a problem using the adapter's SaveEntity method. When the adapter does an insert the Primary Key is not returning the SCOPE_IDENTITY(). I've run a SQL Server Profile and the code it generates works just fine and returns the proper id, LLBL however seems to be ignoring it and the Primary Key remains at 0. For updates when I call SaveEntity I am getting an ORMConcurrencyException. I looked at the trace and it appears that the transaction is getting aborted before it actually does the update. I've run profiler on that as well and the generated t-sql outside of the transaction works just fine. I'm attaching a script for my database, a sample project and the LLBL trace information.

The solution for the project can be found in the ConsoleTestApp folder.

Thanks.

Hi IObject (cool hehe ). Your project works just fine for me. The only difference is that I'm using .Net 2.0. Could you test at .Net 2.0 only to dismiss that possibility?

Thanks for looking I tried it on a different machine running .net 2.0 and still had the same problems. Where to next?

Can't reproduce it, it works perfect for me.

Would you please post your database version? (SELECT @@Version)

My "SELECT @@Version" returns: _Mine is: Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 6.0 (Build 6000: ) _

I can't repro it either, with .net 3.0 installed.

@@Version: Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Well, that doesn't sound very good for me that it can't be reproduced... All the computers I've tried at work still produce the same errors. I'm guessing that it is boiling down to the SQL Server then? Thats the only common denominator, but I can't think of anything that has changed in the last 3-4 weeks which is when we started to notice this problem...

Try to check if the field is still an identity field in the DB you're connecting to. (that's important, perhaps you're connecting to a different catalog etc.)

Also ORMConcurrencyException may be thrown if you are doing an UPDATE not an INSERT. Would you please post the complete exception text and stack trace?

Here is the stack trace:

   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(List`1 queueToPersist, Boolean insertActions)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave)
   at ConsoleTestApp.Program.Main(String[] args) in C:\Users\matts\Desktop\llblissues\ConsoleTestApp\ConsoleTestApp\Program.cs:line 35

Here is the output from the trace:

Method Enter: DataAccessAdapterBase.SaveEntity(4)
Active Entity Description: 
    Entity: DataAccess.EntityClasses.TestTableEntity. ObjectID: d356be8c-8677-436e-9035-bc910f23e6f0
    PrimaryKey field: PrimaryId. Type: System.Int32. Value: <undefined value>
Method Enter: DataAccessAdapterBase.DetermineActionQueues(7)
Active Entity Description: 
    Entity: DataAccess.EntityClasses.TestTableEntity. ObjectID: d356be8c-8677-436e-9035-bc910f23e6f0
    PrimaryKey field: PrimaryId. Type: System.Int32. Value: <undefined value>
Entity added to insert queue: 
    Entity: DataAccess.EntityClasses.TestTableEntity. ObjectID: d356be8c-8677-436e-9035-bc910f23e6f0
    PrimaryKey field: PrimaryId. Type: System.Int32. Value: <undefined value>
Method Exit: DataAccessAdapterBase.DetermineActionQueues(7)
Method Enter: DataAccessAdapterBase.StartTransaction
:   Transaction name: RecursiveSave. Isolation level: ReadCommitted.
Method Enter: DataAccessAdapterBase.OpenConnection
: New connection created.
: Connection physically opened.
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.StartTransaction
Method Enter: DataAccessAdapterBase.PersistQueue
Persistence action info: Action: Insert. Queue length: 1
Current persisted entity info: 
    Entity: DataAccess.EntityClasses.TestTableEntity. ObjectID: d356be8c-8677-436e-9035-bc910f23e6f0
    PrimaryKey field: PrimaryId. Type: System.Int32. Value: <undefined value>
'ConsoleTestApp.vshost.exe' (Managed): Loaded 'C:\Users\matts\Desktop\llblissues\ConsoleTestApp\ConsoleTestApp\bin\Debug\SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll', No symbols loaded.
Method Enter: CreateInsertDQ
Method Enter: CreateSingleTargetInsertDQ
Generated Sql query: 
    Query: INSERT INTO [llbltestissues].[dbo].[TestTable] ([OtherTextField], [OtherInt])  VALUES (@OtherTextField, @OtherInt);SELECT @PrimaryId=SCOPE_IDENTITY()
    Parameter: @PrimaryId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>.
    Parameter: @OtherTextField : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "test text".
    Parameter: @OtherInt : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.


Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
Method Enter: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Executed Sql Query: 
    Query: INSERT INTO [llbltestissues].[dbo].[TestTable] ([OtherTextField], [OtherInt])  VALUES (@OtherTextField, @OtherInt);SELECT @PrimaryId=SCOPE_IDENTITY()
    Parameter: @PrimaryId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: 44.
    Parameter: @OtherTextField : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "test text".
    Parameter: @OtherInt : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.


Method Exit: DataAccessAdapterBase.ExecuteActionQuery
PersistQueue method result: queuePersisted result: False
Method Exit: DataAccessAdapterBase.PersistQueue
Method Enter: DataAccessAdapterBase.PersistQueue
Persistence action info: Action: Update. Queue length: 0
PersistQueue method result: queuePersisted result: True
Method Exit: DataAccessAdapterBase.PersistQueue
Method Enter: DataAccessAdapterBase.Commit
Method Enter: DataAccessAdapterBase.Reset
Method Exit: DataAccessAdapterBase.Reset
Method Exit: DataAccessAdapterBase.Commit
Method Enter: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.SaveEntity(4)
A first chance exception of type 'System.InvalidOperationException' occurred in System.dll
Method Enter: DataAccessAdapterBase.SaveEntity(4)
Active Entity Description: 
    Entity: DataAccess.EntityClasses.TestTableEntity. ObjectID: 36baa03e-3983-4f2f-9603-9b8943f77ef9
    PrimaryKey field: PrimaryId. Type: System.Int32. Value: 1
Method Enter: DataAccessAdapterBase.DetermineActionQueues(7)
Active Entity Description: 
    Entity: DataAccess.EntityClasses.TestTableEntity. ObjectID: 36baa03e-3983-4f2f-9603-9b8943f77ef9
    PrimaryKey field: PrimaryId. Type: System.Int32. Value: 1
Entity added to update queue: 
    Entity: DataAccess.EntityClasses.TestTableEntity. ObjectID: 36baa03e-3983-4f2f-9603-9b8943f77ef9
    PrimaryKey field: PrimaryId. Type: System.Int32. Value: 1
Method Exit: DataAccessAdapterBase.DetermineActionQueues(7)
Method Enter: DataAccessAdapterBase.StartTransaction
:   Transaction name: RecursiveSave. Isolation level: ReadCommitted.
Method Enter: DataAccessAdapterBase.OpenConnection
: New connection created.
: Connection physically opened.
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.StartTransaction
Method Enter: DataAccessAdapterBase.PersistQueue
Persistence action info: Action: Insert. Queue length: 0
PersistQueue method result: queuePersisted result: True
Method Exit: DataAccessAdapterBase.PersistQueue
Method Enter: DataAccessAdapterBase.PersistQueue
Persistence action info: Action: Update. Queue length: 1
Current persisted entity info: 
    Entity: DataAccess.EntityClasses.TestTableEntity. ObjectID: 36baa03e-3983-4f2f-9603-9b8943f77ef9
    PrimaryKey field: PrimaryId. Type: System.Int32. Value: 1
Method Enter: CreateUpdateDQ(4)
Method Enter: CreateSingleTargetUpdateDQ(4)
Generated Sql query: 
    Query: UPDATE [llbltestissues].[dbo].[TestTable] SET [OtherInt]=@OtherInt WHERE ( [llbltestissues].[dbo].[TestTable].[PrimaryId] = @PrimaryId1)
    Parameter: @OtherInt : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5.
    Parameter: @PrimaryId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.


Method Exit: CreateSingleTargetUpdateDQ(4)
Method Exit: CreateUpdateDQ(4)
Method Enter: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Executed Sql Query: 
    Query: UPDATE [llbltestissues].[dbo].[TestTable] SET [OtherInt]=@OtherInt WHERE ( [llbltestissues].[dbo].[TestTable].[PrimaryId] = @PrimaryId1)
    Parameter: @OtherInt : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5.
    Parameter: @PrimaryId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.


Method Exit: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: DataAccessAdapterBase.Rollback
Method Enter: DataAccessAdapterBase.Reset
Method Exit: DataAccessAdapterBase.Reset
Method Exit: DataAccessAdapterBase.Rollback
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMConcurrencyException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll
Method Enter: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.SaveEntity(4)

And finally the corresponding sql profiler info:

declare @p3 int
set @p3=46
exec sp_executesql N'INSERT INTO [llbltestissues].[dbo].[TestTable] ([OtherTextField], [OtherInt])  VALUES (@OtherTextField, @OtherInt);SELECT @PrimaryId=SCOPE_IDENTITY()',N'@PrimaryId int output,@OtherTextField nvarchar(50),@OtherInt int',@PrimaryId=@p3 output,@OtherTextField=N'test text',@OtherInt=1
select @p3

exec sp_executesql N'UPDATE [llbltestissues].[dbo].[TestTable] SET [OtherInt]=@OtherInt WHERE ( [llbltestissues].[dbo].[TestTable].[PrimaryId] = @PrimaryId1)',N'@OtherInt int,@PrimaryId1 int',@OtherInt=5,@PrimaryId1=1

The Repro code you have sent was:

            TestTableEntity tte = new TestTableEntity();
            tte.OtherInt = 1;
            tte.OtherTextField = "test text";
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.SaveEntity(tte);
            int retrievedId = tte.PrimaryId;
            Console.WriteLine(retrievedId);
            Console.ReadLine();

            TestTableEntity tte2 = new TestTableEntity(1); // always assumes the inserted row has a PK value = 1 which is only valid for the first insertion on the table
            tte2.IsNew = false;
            tte2.OtherInt = 5;
            DataAccessAdapter adapter2 = new DataAccessAdapter();
            adapter2.SaveEntity(tte2);

TestTableEntity tte2 = new TestTableEntity(1); // always assumes the inserted row has a PK value = 1 which is only valid for the first insertion on the table

This would pretty much gives a ORMConcurrencyException if there is no ROW with a PK = 1.

Would you please modify the Repro code to be as follows:

            TestTableEntity tte = new TestTableEntity();
            tte.OtherInt = 1;
            tte.OtherTextField = "test text";
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.SaveEntity(tte);
            int retrievedId = tte.PrimaryId;
            Console.WriteLine(retrievedId);
            Console.ReadLine();

            TestTableEntity tte2 = new TestTableEntity(retrievedId);
            tte2.IsNew = false;
            tte2.OtherInt = 5;
            DataAccessAdapter adapter2 = new DataAccessAdapter();
            adapter2.SaveEntity(tte2);

Or use adapter.UpdateEntitiesDirectly and check for the returned result.

Sorry for the late reply. I'm already outputting the ID that it is supposed to be returning which to me shows up as 0, which would make trying to retrieve that entity from the database fail. One other interesting thing, a coworker set up a sql express instance and then generated code against that and it worked fine. Anyone know if there is something with SQL Server 2005 that would cause these symptoms?

Would you please check the "DQE Compatibility mode" section in the manual: "Using the generated code -> Application configuration through .config files" Try to set it to sqlserver 2005.

I added that to the .config file with it set at 2 for sql server 2005. No change in behavior.

The ID is definitely read back: (stacktrace you posted reveals this: )

Executed Sql Query: Query: INSERT INTO [llbltestissues].[dbo].[TestTable] ([OtherTextField], [OtherInt]) VALUES (@OtherTextField, @OtherInt);SELECT @PrimaryId=SCOPE_IDENTITY() ** Parameter: @PrimaryId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: 44. **

I'm looking into the stacktrace with the code now, as PersistQueue returned false, which is odd.

(edit). I think I know what's wrong. ROWCOUNTING is shut off at your server. The thing is: the save is only valid if the # of affected rows is > 0. As the execution of the insert succeeds (see quoted trace above), the insert does take place. However, LLBLGen Pro gets 0 back as the # of affected rows.

This is often caused by the fact that a DBA has switched off row counting on the server level, e.g. all connections have default row counting switched off. Row counting has to be enabled, it signals client code if things went wrong or not.

Open SSMS, right click the server and select properties. Click connections at the left and scroll down to 'no count' in the connection options. This likely has the checkbox checked, uncheck it. Click OK. It might be you have to re-start the sqlserver service (so simply do net stop "SQL SERVER (SQLSERVER2005)" on the command line and then the same statement with net start... )

If this is set off at the server level, would llblgen be able to generate and insert a statement into the command turning it on?

Otis wrote:

The ID is definitely read back: (stacktrace you posted reveals this: )

Executed Sql Query: Query: INSERT INTO [llbltestissues].[dbo].[TestTable] ([OtherTextField], [OtherInt]) VALUES (@OtherTextField, @OtherInt);SELECT @PrimaryId=SCOPE_IDENTITY() ** Parameter: @PrimaryId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: 44. **

I'm looking into the stacktrace with the code now, as PersistQueue returned false, which is odd.

(edit). I think I know what's wrong. ROWCOUNTING is shut off at your server. The thing is: the save is only valid if the # of affected rows is > 0. As the execution of the insert succeeds (see quoted trace above), the insert does take place. However, LLBLGen Pro gets 0 back as the # of affected rows.

This is often caused by the fact that a DBA has switched off row counting on the server level, e.g. all connections have default row counting switched off. Row counting has to be enabled, it signals client code if things went wrong or not.

Open SSMS, right click the server and select properties. Click connections at the left and scroll down to 'no count' in the connection options. This likely has the checkbox checked, uncheck it. Click OK. It might be you have to re-start the sqlserver service (so simply do net stop "SQL SERVER (SQLSERVER2005)" on the command line and then the same statement with net start... )

That was it exactly! I found the setting and changed it back. Thanks for all your detective work! Its not something I would have thought to look for, or something that should have been changed.

arschr wrote:

If this is set off at the server level, would llblgen be able to generate and insert a statement into the command turning it on?

That would require for every statement to emit SET ROWCOUNT ON; into the query... though in general, it shouldn't be enabled on the server, as it's not really what you want: all client code will need rowcounting.

yes, I'm asking if it would be possible for llblgen to identify that rowcount is not on and if so emit those statements with each connect. Or baring that if there could be a config file option that would tell llblgen to emit the statement.

If llblgen requires this be on, it seems (to me) that it would be worth the effort to identify if it is on at the first connection, and throw an error if it's not. And if it's not on, and the dba won't turn it on, provide the developer with a way to use llblgen pro.

I have no idea if it's detectable, I think it is, but that then would require a roundtrip. Of course the DQE could always emit SET NOCOUNT OFF; into the delete/insert/update query... the documentation of SET NOCOUNT is a bit vague: if you set it on connection A, is it also set on connection B.. I don't think it is though. The docs also state it's mainly for procs, with multiple statements. Dyn. sql is typically one statement -> expect result. So if a proc needs this benefit, one should set nocount on in that proc.