Hi,
We're trying to use the SqlBulkCopy .Net API to insert into a temp table in Azure Synapse and receive the error "Could not find stored procedure 'tempdb.pdw.sp_pdw_resolve_schema_name'". The SqlBulkCopy works with concrete tables, but not with temp tables that are created during the session that the SqlBulkCopy uses. Note that in regular Sql Server instances, this works just fine:
Full Stack Trace:
System.InvalidOperationException: Cannot access destination table '[#test]'. ---> System.Data.SqlClient.SqlException: Could not find stored procedure 'tempdb.pdw.sp_pdw_resolve_schema_name'.
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.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
at System.Data.SqlClient.SqlBulkCopy.CreateAndExecuteInitialQueryAsync(BulkCopySimpleResultSet& result)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
at CompAnalytics.Execution.Modules.BasicSqlExecutor.<CreateTempTables>d__46.MoveNext() in C:\projects\companalytics\Product\CompAnalytics.Extension.Sql\BasicSqlExecutor.cs:line 233
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at CompAnalytics.Execution.Modules.CoreQueryExecutor.<ExecuteQueryAsyncWithTimeout>d__4.MoveNext() in C:\projects\companalytics\Product\CompAnalytics.Extension.Sql\CoreQueryExecutor.cs:line 32
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at CompAnalytics.Execution.Modules.BasicSqlExecutor.<ExecuteQueryAsync>d__44.MoveNext() in C:\projects\companalytics\Product\CompAnalytics.Extension.Sql\BasicSqlExecutor.cs:line 161
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at CompAnalytics.Execution.Modules.SqlQueryExecutor.<ExecuteAsync>d__8.MoveNext() in C:\projects\companalytics\Product\CompAnalytics.Extension.Sql\SqlQueryExecutor.cs:line 42
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at CompAnalytics.Execution.AsyncModuleExecutor.Execute(IExecutionContext context) in C:\agent\_work\7\s\Product\CompAnalytics.Execution\AsyncModuleExecutor.cs:line 22
at CompAnalytics.Execution.ExecutionContext.ExecuteOrRestoreModule(ModuleExecutor executor, Module module, Int32 currentRetryCount) in C:\agent\_work\7\s\Product\CompAnalytics.Execution\ExecutionContext.cs:line 1146
at CompAnalytics.Execution.ExecutionContext.ExecuteModuleWithRetry(ModuleExecutor executor, Module module) in C:\agent\_work\7\s\Product\CompAnalytics.Execution\ExecutionContext.cs:line 1129
at CompAnalytics.Execution.ExecutionContext.ExecuteModule(ModuleExecutor executor, Module module, ExecutionCallback postExecutionCallback) in C:\agent\_work\7\s\Product\CompAnalytics.Execution\ExecutionContext.cs:line 965
