Error retrieving data from a data source with stored procedure

[JānisB]

Hi,

Pipeline > Copy data activity > Source SQL Server > Use query = Stored procedure

(on-premises connection)

Procedure: [PBI].[sp_Rergher_HI_AZIDV_OPK_Ipaasg] (if it matters)

Procedure is made by third party

Preview data works correctly!

Error appears during pipeline Run process:

Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Invalid object name '#AAADB_N_P'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Invalid object name '#AAADB_N_P'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=208,Class=16,ErrorCode=-2146232060,State=0,Errors=[{Class=16,Number=208,State=0,Message=Invalid object name '#AAADB_N_P'.,},],'

I guess that #AAADB_N_P is a temporary table for the procedure on the server side.

What is a problem and how to solve it?

[JānisB · Accepted solution]

The problem is using temporary tables for the procedure. This problem is not solved.

The solution is using table variables instead of temporary tables.

[lbendlin]

Rename your stored procedure to something less fancy, like 'AAADB-N-P'

[JānisB]

'AAADB_N_P' is not the name of a procedure.

I guess that #AAADB_N_P is the name of a temporary table for the procedure.

Procedure: [PBI].[sp_Rergher_HI_AZIDV_OPK_Ipaasg]

Procedure is made by a third party

[lbendlin]

Try running this with a SP in the default schema.

[JānisB]

Using [dbo].[sp_Rergher_HI_AZIDV_OPK_Ipaasg] the same error.

[v-menakakota]

Hi @JānisB ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.