I have the same problem with copying data from Autotask to SQL Server:
create or replace table api_opportunities@sql
as
select *
from opportunities_itemsuserdefinedfields@at t
pivot ( max(t.value)
for t.name in
( xxx, yyy, zzz )
)
Error is:
itgenase094
The table can not be created.
Maintenance of the table ‘API_OPPORTUNITIES’ failed.
There is already an object named ‘API_OPPORTUNITIES_pk’ in the database.
Could not create constraint or index. See previous errors.
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)
First tried workaround was drop the table using drop table api_opportunities@sql
, but that did not solve the issue.
It only occurs on this SQL-statement. Other create or replace
statements are working fine. We are using Invantive Data Hub 24.0.324-PROD+4922.
The table has the following fields:
id (int64, True), advancedField1 (double, True), advancedField2 (double, True), advancedField3 (double, True), advancedField4 (double, True), advancedField5 (double, True), amount (double, True), assessmentScore (double, True), barriers (string, True), closedDate (datetime, True), companyID (int32, True), contactID (int32, True), cost (double, True), createDate (datetime, True), creatorResourceID (int32, True), description (string, True), helpNeeded (string, True), impersonatorCreatorResourceID (int32, True), lastActivity (datetime, True), leadSource (int32, True), lossReason (int32, True), lossReasonDetail (string, True), lostDate (datetime, True), market (string, True), monthlyCost (double, True), monthlyRevenue (double, True), nextStep (string, True), onetimeCost (double, True), onetimeRevenue (double, True), opportunityCategoryID (int32, True), organizationalLevelAssociationID (int32, True), ownerResourceID (int32, True), primaryCompetitor (int32, True), probability (int32, True), productID (int32, True), projectedCloseDate (datetime, True), promisedFulfillmentDate (datetime, True), promotionName (string, True), quarterlyCost (double, True), quarterlyRevenue (double, True), rating (int32, True), relationshipAssessmentScore (double, True), revenueSpread (int32, True), revenueSpreadUnit (string, True), salesOrderID (int32, True), salesProcessPercentComplete (double, True), semiannualCost (double, True), semiannualRevenue (double, True), stage (int32, True), startDate (datetime, True), status (int32, True), technicalAssessmentScore (double, True), throughDate (datetime, True), title (string, True), totalAmountMonths (int32, True), useQuoteTotals (boolean, True), winReason (int32, True), winReasonDetail (string, True), yearlyCost (double, True), yearlyRevenue (double, True)
That is weird, since the new columns from the UDFs (see pivot
) are missing here.
Second workaround tried was to remove the table directly from SQL Server Management Studio. And then re-run the job.
That did the job.
Looking back, there was another table previously also named api_opportunities
with the column list above, without the UDFs.
It seems as if the previously created table was not dropped / removed correctly and replaced by one including the new columns.
Workaround seems to be to drop the table causing problems from SQL Server Management Studio.