Copying Autotask UDF to SQL Server: itgenase094 Can not create table error

[cgm29]

We are using Invantive Data Hub 24.0.324-PROD+4923 to copy a Autotask UDF table to our on premises SQL Server, but get this error:

Error itgencun013:
error itgenase094:
The table can not be created.
Maintenance of the table ‘WH_CONTRACT_UDF’ failed.
Incorrect syntax near ‘-’.Incorrect syntax near ‘-’.

Call stack:

System.Data.SqlClient.SqlException
ValidationException
ValidationException
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.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, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Invantive.Data.AnsiSqlProviderBase.C.A(Connection)
   at Invantive.Data.AnsiSqlProviderBase.ExecuteWithRetry[T](GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, Connection connection, ConnectionContext context, Func`2 action, Int32 maxAttempts, Int64 sleepOnRetryMs, String originMethod, Int32 originLineNumber)
   at Invantive.Data.AnsiSqlProviderBase.PassthroughSqlAction(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, String actionSql, ParameterList parameters, String loggingTableName)
   at Invantive.Data.Providers.SqlServer.SqlServerProvider.EnsureTableExists(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, String nonStandardizedPartitionCode, TableSpecification tableSpecification, Int32 preferredSqlDdlPerBatch)

I think the first time the table is created we need to do it in some special way.

[forums]

Internal checks on System Messages revealed that the following column names are being used which might raise this error:

• UDF_-_Contract_End_Date_as_datetime (datetime)
• UDF_-_Contract_End_Date_as_string (datetime)
• Direct/Wholesale_visible_value (string)
• Direct/Wholesale_stored_value (string)
• Contract_Term_-_Months_as_numeric (decimal)`
• Contract_Term_-_Months_as_string (decimal)`

upon execution of:

create or replace table wh_contract_udf@sql
as
select *
from   wh_contract_udf@dwh

[forums]

The problem can not be reproduced on the most recent 24.0-release 24.0.646 using the following test query:

create or replace table Test@sql
as 
select sysdateutc [UDF_-_Contract_End_Date_as_datetime]
,      sysdateutc [UDF_-_Contract_End_Date_as_string]
,      'x' [Direct/Wholesale_visible_value]
,      'y' [Direct/Wholesale_stored_value]
,      1.0 [Contract_Term_-_Months_as_numeric]
,      1.0 [Contract_Term_-_Months_as_string]

It is recommended to log on to Data Hub interactively or the Invantive Query Tool, and then try to execute this create or replace table statement.

Does it raise an error on the version you have installed?

[forums]

This question was automatically closed after at least 1 week of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.