Solved

History Table not deploying anymore 'SCD Type I Hash Key' Error

  • 3 March 2023
  • 8 replies
  • 114 views

Userlevel 2

After attempting to add a new column to my history table, I cannot get the table deployed and executed anymore. The new column is currently not in the table, but I also can't get it to get back to "old" historical table back without the new column. Currently have tried the following things, but so far without result:

  • Set the project version back to an earlier version where the change wasn’t made yet;
  • Upgrarde the PROD SQL DB Size and deploy again.

It does not get beyond the Valid Table structure when deploying the historical table. When previewing I also get the following error messages:

The table you are trying to preview has not been deployed yet. Deploy first.

Invalid column name 'SCD Type I Hash Key'. 

 

 

The table you are trying to preview has not been deployed yet. Deploy first.

 

Invalid column name 'SCD Type I Hash Key'.

 

Details:

 

SQL Server: 'c5d03ed4f0ca.tr30953.westeurope1-a.worker.database.windows.net,11052'
SQL Procedure: ''
SQL Line Number: 1
SQL Error Number: 207

 

Invalid column name 'SCD Type I Hash Key'.
Module: .Net SqlClient Data Provider
System.Data.SqlClient.SqlException
   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.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   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.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at TimeXtender.DataManager.PreViewTable_ST_DW_Command.<>c__DisplayClass12_1.<dialog_TableChange>b__0()
   at TimeXtender.DataManager.ConnectingThread.ExecuteConnectingThread(Object dummy)

 

Invalid column name 'SCD Type I Hash Key'.
Module: timeXtender
TimeXtender.DataManager.ExceptionWrapperException
   at TimeXtender.DataManager.ConnectingThread.HandleError()
   at TimeXtender.DataManager.ConnectingThread.Execute(String title, Int32 progressSteps, List`1 actions)
   at TimeXtender.DataManager.ConnectingThread.ExecuteFastAction(String title, Action action, IWin32Window parentForm, CancelBehaviors cancelBehavior, ErrorBehaviors errorBehavior, String callerName)
   at TimeXtender.DataManager.PreViewTable_ST_DW_Command.dialog_TableChange(Object sender, EventArgs e)

 

Time: 2023-03-02 17:23:03
UTC: 2023-03-02 17:23:03
Title:  TimeXtender 20.10.35.64
Application: 20.10.35.64
Repository: 20.10.35.64 (in Azure)
SQL Server: Microsoft SQL Azure (RTM) - 12.0.2000.8 
    Jan 12 2023 05:25:39 
    Copyright (C) 2022 Microsoft Corporation

 

 

icon

Best answer by Christian Hauggaard 3 March 2023, 11:15

View original

8 replies

Userlevel 6
Badge +5

You mention that you upgraded the PROD SQL DB Size. Two questions regarding this:

  1. Was the SQL DB running out of space, is this why you scaled it up?
  2. So this column addition to the history table was made directly in the PROD environment? Or was it first done in a TEST/DEV environment, and if so are you experiencing the same error there?

If you are experiencing the same error in TEST/DEV, perhaps try a full deploy (disable differential) to see if that will work.

 

Userlevel 2
  1. When I tried to execute other packages I saw the SQL PROD DB had ran out of space while deploying the history table in the last hour. When I restarted to PROD VM the DB Size went back to normal. Hence I tried to scale up and deploy again, but with no result.
  2. I first tried to add the column in DEV and it went all smoothly in seconds. When I tried the same in PROD, it kept deploying for 1 hour +, after which I killed the process.

I tried to clone the table structure and duplicate the process in PROD and that all goes fine. The Valid history table is still accessible, so i tried to insert the SQL history table in another table for Backup.

However, my orginal history table still won’t deploy. 

Userlevel 6
Badge +5

As you have made a backup, please try to deploy again and ensure that differential deployment is disabled

 

Userlevel 2

It keeps stuck on this step:

 

Userlevel 6
Badge +5

@KCMT did it fail with the same error message or is it still running? 

Userlevel 2

The deployment of the table keeps on running forever if I dont kill the process. The longest i let it run was like 1h15m yesterday evening I think.

In test it is minutes/seconds work.

Userlevel 6
Badge +5

@KCMT I will open a support ticket so we can investigate further.

Userlevel 2

@Christian Hauggaard The deployment has succesfully finished in the mean time with the disable of the differential deployment. So this did the trick after all. Thanks a lot!

Reply