Coping data from [Warehouse_Live].[sys].[tables]

[Byzza]

Hi Brains Trust,

I'm trying to get a list of all the warehouse tables and put them in a table that I can do some reporting on, but I keep running into the error 

The query references an object that is not supported in distributed processing mode.
Msg 15816, Level 16, State 3, Code line: 28

My latest version of the code is

CREATE TABLE #TempSysTables (
    name VARCHAR(128)  COLLATE Latin1_General_100_BIN2_UTF8 NULL,
    create_date DATETIME2(6)  NULL,
    modify_date DATETIME2(6)  NULL
)

INSERT INTO #TempSysTables 
SELECT
    CAST(T1.name AS VARCHAR(128))      AS name,
    CAST(T1.create_date AS DATETIME2(6)) AS create_date,
    CAST(T1.modify_date AS DATETIME2(6)) AS modify_date
FROM [Warehouse_Live].[sys].[tables] T1
WHERE type = 'U'
--ORDER BY name

SELECT * FROM #TempSysTables Order by create_date

--SELECT * 
--FROM [Warehouse_Live].[dbo].[WarehouseLiveTables] T0
 --FULL JOIN #TempSysTables T1 ON T0.Name = T1.name 
--WHERE 1=1
    -- and type = 'U'
    --AND T0.Name is NULL
--ORDER BY name

INSERT INTO [Warehouse_Live].[dbo].[WarehouseLiveTables] (Name)
SELECT name 
FROM #TempSysTables T1
  LEFT JOIN [Warehouse_Live].[dbo].[WarehouseLiveTables] T0 ON T0.Name = T1.name 

any help would be greatly Appreciated

[spaceman127]

Hi @Byzza ,

I can confirm what @AndyDDC  says.

The only workaround is to use a pipeline.

Best regards

[AndyDDC · Accepted solution]

Hi @Byzza this is a know issue (System Views in Microsoft Fabric – Query references an object that is not supported in distributed p...)

In the meantime you could use a pipeline to extract any sys data you need, load into tables in the warehouse and do what you need to do.

------------------------------------------------------------

If this reply has helped, please consider giving kudos

and marking the reply as the answer to help other

------------------------------------------------------------

[Byzza]

OMG, thank you. you have saved me so much time trying various work arounds in notebooks. 

This work fine for what I need.