SQL Statement "OUTPUT" is not supported.

Pfisterbi — Sat, 03 Jun 2023 06:58:26 GMT

Hi
In our DWH (an ASQL DB), we use a SP at the start of the ADF Pipeline to write some metadata about the Pipeline into an Audit log (table in DB). The following code is an excerpt of the code:

insert into logging.adf_run_log (master_pl_run_id, parent_pl_run_id, pl_run_id, execution_object, source_object, layer, status, start_time) OUTPUT inserted.pl_run_id select ISNULL(@master_pl_run_id,cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) , ISNULL(@parent_pl_run_id,cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) , ISNULL(@pl_run_id,NEWID()) , @execution_object, @source_object, @layer, 'RUNNING', getdate()

While the code works smoothly in the ASQL DB, we get an error message in Fabric.
"OUTPUT is not supported.
Msg 15868, level 16, state 11, code line 35
Each SQL statement is executed as an independent session. The session context is not maintained across SQL statements."

The provided link for the infos about the error does not work: https://go.microsoft.com/fwlink/?linkid=2230216.

Is this a bug?

If not, how can we write logging / audit infos about the Pipeline into a Fabric DWH?
We need to know, when a Pipeline runs starts, ends and the number of rows written, read etc.
Thank you.

Re: SQL Statement "OUTPUT" is not supported.

HimanshuS-msft — Thu, 08 Jun 2023 05:28:21 GMT

Hello Pfisterbi
I am assuming that the code which you shared is from the SP . I will suggest you to please comment the below line and try again .

insert into logging.adf_run_log (master_pl_run_id, parent_pl_run_id, pl_run_id, execution_object, source_object, layer, status, start_time)

-- OUTPUT inserted.pl_run_id

select ISNULL(@master_pl_run_id,cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) , ISNULL(@parent_pl_run_id,cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) , ISNULL(@pl_run_id,NEWID()) , @execution_object, @source_object, @layer, 'RUNNING', getdate()

Please do let us know how it goes .

Thanks
Himanshu

topic SQL Statement "OUTPUT" is not supported. in Data Warehouse

SQL Statement "OUTPUT" is not supported.

Re: SQL Statement "OUTPUT" is not supported.