topic Capture fabric warehouse SP Name into a table in Data Warehouse

Capture fabric warehouse SP Name into a table

AJAJ — Mon, 23 Mar 2026 15:53:10 GMT

Hi there,

Inside the SP...

SELECT TOP 1 @ProcName =
OBJECT_SCHEMA_NAME(s.objectid) + '.' + OBJECT_NAME(s.objectid)
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.session_id = @@SPID;

insert into logtable (@ProcName, @starttime, @endtime)

when i select * from logtable, procname is empty since it was run via fabric pipeline.

Re: Capture fabric warehouse SP Name into a table

deborshi_nag — Mon, 23 Mar 2026 20:13:33 GMT

Hello AJAJ

I have used a slightly changed DMV and tested it by directly calling the procedure as well as via a pipeline, and both worked.

/*=========================================================== Fabric Warehouse-friendly logging table + stored procedure - VARCHAR columns (no NVARCHAR persisted) - IDENTITY without seed/increment - No GO (pipeline-safe) - No ERROR_LINE() (not supported) - ProcName resolved via DMVs (since @@PROCID not supported) ===========================================================*/ -- Create logging table if it doesn't exist IF OBJECT_ID('dbo.ProcExecutionLog', 'U') IS NULL BEGIN CREATE TABLE dbo.ProcExecutionLog ( LogId bigint IDENTITY NOT NULL, ExecutionId uniqueidentifier NOT NULL, ProcName varchar(256) NULL, Invoker varchar(200) NULL, StartTimeUtc datetime2(3) NOT NULL, EndTimeUtc datetime2(3) NULL, DurationMs bigint NULL, Status varchar(20) NOT NULL, -- STARTED | SUCCEEDED | FAILED ErrorNumber int NULL, ErrorSeverity int NULL, ErrorState int NULL, ErrorProcedure varchar(256) NULL, ErrorMessage varchar(4000) NULL ); END; -- Create/alter procedure in same batch EXEC(' CREATE OR ALTER PROCEDURE dbo.usp_DoWork_WithLogging @Invoker varchar(200) = NULL AS BEGIN SET NOCOUNT ON; DECLARE @ExecutionId uniqueidentifier = NEWID(); DECLARE @ProcName varchar(256) = NULL; DECLARE @StartTimeUtc datetime2(3) = SYSUTCDATETIME(); DECLARE @EndTimeUtc datetime2(3) = NULL; -- declare ONCE DECLARE @DurationMs bigint = NULL; -- Resolve current module name using DMVs (Fabric workaround) -- If this returns NULL in some pipeline contexts, consider passing ProcName as a parameter instead. SELECT TOP (1) @ProcName = s.name + ''.'' + p.name FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t INNER JOIN sys.procedures p ON p.object_id = t.objectid INNER JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE r.session_id = @@SPID; -- Insert STARTED row INSERT INTO dbo.ProcExecutionLog ( ExecutionId, ProcName, Invoker, StartTimeUtc, Status ) VALUES ( @ExecutionId, @ProcName, @Invoker, @StartTimeUtc, ''STARTED'' ); BEGIN TRY ------------------------------------------------------- -- DO WORK HERE (replace with your real logic) ------------------------------------------------------- DECLARE @Dummy int = 1; SET @Dummy = @Dummy + 1; ------------------------------------------------------- SET @EndTimeUtc = SYSUTCDATETIME(); SET @DurationMs = CAST(DATEDIFF(MILLISECOND, @StartTimeUtc, @EndTimeUtc) AS bigint); UPDATE dbo.ProcExecutionLog SET EndTimeUtc = @EndTimeUtc, DurationMs = @DurationMs, Status = ''SUCCEEDED'' WHERE ExecutionId = @ExecutionId; END TRY BEGIN CATCH SET @EndTimeUtc = SYSUTCDATETIME(); SET @DurationMs = CAST(DATEDIFF(MILLISECOND, @StartTimeUtc, @EndTimeUtc) AS bigint); UPDATE dbo.ProcExecutionLog SET EndTimeUtc = @EndTimeUtc, DurationMs = @DurationMs, Status = ''FAILED'', ErrorNumber = ERROR_NUMBER(), ErrorSeverity = ERROR_SEVERITY(), ErrorState = ERROR_STATE(), ErrorProcedure = CAST(ERROR_PROCEDURE() AS varchar(256)), ErrorMessage = CAST(ERROR_MESSAGE() AS varchar(4000)) WHERE ExecutionId = @ExecutionId; THROW; END CATCH END ');

You can invoke the stored procedure in a Fabric pipeline passing the pipeline id @pipeline().RunId and it will capture that in the log table.

Re: Capture fabric warehouse SP Name into a table

v-hashadapu — Tue, 24 Mar 2026 10:09:45 GMT

Hi AJAJ , Thank you for reaching out to the Microsoft Community Forum.

This is because your query relies on DMVs to infer the current stored procedure. In pipeline runs, the execution is often wrapped or submitted as a batch, so sys.dm_exec_requests and sys.dm_exec_sql_text don’t reliably return your procedure’s objectid. So, @ProcName comes back NULL, making your log table shows it as empty.

You can’t fix this using DMVs in Fabric. I suggest you stop deriving the procedure name at runtime and instead set it explicitly, either hardcode it inside the procedure or pass it as a parameter from the pipeline. This avoids dependency on execution context, which is exactly what’s causing the issue.

Re: Capture fabric warehouse SP Name into a table

AJAJ — Wed, 25 Mar 2026 14:25:31 GMT

Thanks deborshi_nag. Unfortunately you slightly modified code still returns null via pipeline run.

My pipeline dynamically triggers SPs from ETLConfig tables. So SP name is already a parameter to pipeline.
v-hashadapu you understood the issue exactly that when run via pipeline its not working. I didnt want to hardcode.

there are 2 ways we run sp, via pipeline or for debugging/development via ssms.

im using a simple if else conditon if parmeter via pipeline is not null, use that, else use query.
approach i took.

ALSO ANY THOUGHTS ON HOW TO TRACK IF A TABLE IS USED BY A SP OR VIEW? In fabric warehouse. SQL server has view dependency when right clicked on a table. There are also dependency scripts which point out if a table is being used by a view or sp. Do by any chance know of script if a table is used by view or sp.

Re: Capture fabric warehouse SP Name into a table

deborshi_nag — Wed, 25 Mar 2026 19:24:04 GMT

Hello AJAJ

See if this works:

DECLARE @SchemaName varchar(128) = 'dbo';

DECLARE @TableName varchar(128) = 'ProcExecutionLog';

SELECT

OBJECT_SCHEMA_NAME(d.referencing_id) AS referencing_schema,

OBJECT_NAME(d.referencing_id) AS referencing_object,

o.type_desc AS referencing_type,

d.referenced_schema_name,

d.referenced_entity_name

FROM sys.sql_expression_dependencies d

JOIN sys.objects o

ON d.referencing_id = o.object_id

WHERE d.referenced_schema_name = @SchemaName

AND d.referenced_entity_name = @TableName

AND o.type IN ('V','P') -- V = View, P = Stored Procedure

ORDER BY referencing_type, referencing_schema, referencing_object;

Re: Capture fabric warehouse SP Name into a table

deborshi_nag — Wed, 25 Mar 2026 19:28:12 GMT

Hello AJAJ

In regards to your original query, why don't you write a stored procedure that will accept a parameter, check that the value supplied matches with a stored procedure name, and if so, insert a line in the log table and run the stored procedure.

I can supply you with the code if that helps. I have tried this approach, it works both when calling it directly, as well as via a pipeline activity.

Re: Capture fabric warehouse SP Name into a table

v-hashadapu — Thu, 26 Mar 2026 06:46:35 GMT

Hi AJAJ , Thank you for reaching out to the Microsoft Community Forum.

Your IF/ELSE approach is exactly the right way to handle this. In Fabric, DMV based detection isn’t reliable in pipeline runs, so using the pipeline parameter when available and falling back to DMVs for SSMS/debug scenarios is the correct and practical pattern.

For your second question, sys.sql_expression_dependencies is the right way to track which procedures or views use a table in Fabric, but it only captures static SQL. If you’re using dynamic SQL or config-driven execution, some dependencies won’t show up there. In practice, you’ll need to combine it with a search on sys.sql_modules to catch anything missed, since Fabric doesn’t have a fully complete dependency view like SQL Server.

Re: Capture fabric warehouse SP Name into a table

v-hashadapu — Mon, 30 Mar 2026 05:06:47 GMT

Hi AJAJ , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

Re: Capture fabric warehouse SP Name into a table

AJAJ — Mon, 30 Mar 2026 16:17:45 GMT

thanks v-hashadapu . Yes most of SQLs are static. Do you by any chance could share the code based on the your suggestion to use sys.sql_expression_dependencies? Object is what fabric warehouse table is used by any sp or any view.

Re: Capture fabric warehouse SP Name into a table

v-hashadapu — Wed, 01 Apr 2026 05:01:35 GMT

Hi AJAJ , Thank you for reaching out to the Microsoft Community Forum.

Please try this:
DECLARE @SchemaName sysname = 'dbo';
DECLARE @TableName sysname = 'YourTableName';
SELECT
s.name AS referencing_schema,
o.name AS referencing_object,
o.type_desc AS referencing_type
FROM sys.sql_expression_dependencies d
JOIN sys.objects o
ON d.referencing_id = o.object_id
JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE d.referenced_schema_name = @SchemaName
AND d.referenced_entity_name = @TableName
AND o.type IN ('P','V') -- P = Stored Procedure, V = View
ORDER BY o.type_desc, s.name, o.name;

Re: Capture fabric warehouse SP Name into a table

v-hashadapu — Mon, 06 Apr 2026 05:47:54 GMT

Hi AJAJ , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.