Forum

Hello,

I'm getting some errors from a database script when installing a module. The script creates some tables and should insert some data into those tables just created.

The script works perfectly when run from SQL Server Management Studio but when installing the module gives the following error: "SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'CUST_CONTENT_COLORS' when IDENTITY_INSERT is set to OFF"

Full error details below.

The offending lines of code are as follows:
SET IDENTITY_INSERT [dbo].[CUST_CONTENT_COLORS] ON 
GO
INSERT [dbo].[CUST_CONTENT_COLORS] ([ColorID], [ColorName], [ColorValue], [ColorNumber]) VALUES (1, N'Dark Blue', N'37389A', 1)
GO

As you can see, I'm setting IDENTITY_INSERT on but the script thinks it's off. The script does create the tables successfully before attempting the insert. I generated the scripts using the 'generate scripts' facility in SQL Server Management Studio, I should also mention I'm using DNN 8.0.4.

I've googled this but can't find anything that would explain why this would fail in the DNN environment.

Best Regards,

Steve

SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'CUST_CONTENT_COLORS' when IDENTITY_INSERT is set to OFF. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script) ClientConnectionId:ff5503e5-0585-4b0a-a06a-ecc8f8df765f Error Number:544,State:1,Class:16 INSERT [dbo].[CUST_CONTENT_COLORS] ([ColorID], [ColorName], [ColorValue], [ColorNumber]) VALUES (1, N'Dark Blue', N'37389A', 1) 

Hi Aderson,

That's a brilliant solution, I would never have thought of that.

And I will try it out tomorrow (it's late where I am :-)) and post back here.

The only thing is that I can easily generate the scripts using SQL Server management studio. I have a lot of records to insert so it will be a lot of work plus I'll have to redo it each time I regenerate the scripts.

But worst case, at least having a way to do it that works would be great.

Thank you for the Host/SQL  tip as well.

Steve

That is a creative approach Steve.

Cheers,

Aderson