Capture fabric warehouse SP Name into a table

[AJAJ]

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.

[v-hashadapu]

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.

[v-hashadapu]

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.

[AJAJ]

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.

[v-hashadapu]

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.

[AJAJ]

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.

[v-hashadapu · Accepted solution]

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;

[deborshi_nag]

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. 

[deborshi_nag]

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;

[v-hashadapu]

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.

[deborshi_nag]

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.