topic Error retrieving data from a data source with stored procedure in Pipelines

Error retrieving data from a data source with stored procedure

JānisB — Tue, 18 Mar 2025 06:14:53 GMT

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?

Re: Error retrieving data from a data source with stored procedure

lbendlin — Mon, 17 Mar 2025 23:23:19 GMT

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

Re: Error retrieving data from a data source with stored procedure

JānisB — Tue, 18 Mar 2025 06:09:19 GMT

'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

Re: Error retrieving data from a data source with stored procedure

lbendlin — Tue, 18 Mar 2025 10:45:55 GMT

Try running this with a SP in the default schema.

Re: Error retrieving data from a data source with stored procedure

JānisB — Wed, 19 Mar 2025 07:05:32 GMT

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

Re: Error retrieving data from a data source with stored procedure

v-menakakota — Wed, 14 May 2025 07:30:00 GMT

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.

Re: Error retrieving data from a data source with stored procedure

JānisB — Wed, 14 May 2025 07:46:37 GMT

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

The solution is using table variables instead of temporary tables.