Serverless SQL Pool - Loop to iterate over a temporary table or cursor in a stored procedure

Question

I am migrating a procedure from SQL Dedicated to Serverless SQL Pool.

The procedure queries an existing Delta table and iterate over the rows.
I tried two ways inside the proc, but both failed with same message:

Started executing query at Line 16
Msg 15816, Level 16, State 2, Line 1
The query references an object that is not supported in distributed processing mode.
Total execution time: 00:00:00.839

These are the ways I tried, first using the OPENROWSET, then direct reference to the view.
In both cases the procedure is created with no errors, but when execution, I see the error above. I appreciate any help on how to iterate over a table inside a PROC in Serverless SQL Pool.

    CREATE TABLE #temp_table
    WITH
    ( DISTRIBUTION = ROUND_ROBIN)
    AS
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence,
    FIELD1, FIELD2, FIELD3...
    FROM OPENROWSET(
        BULK '<delta-table path',
        FORMAT = 'DELTA',
        DATA_SOURCE = '<ds-name>'
    ) AS source

    CREATE TABLE #temp_table
    WITH
    ( DISTRIBUTION = ROUND_ROBIN)
    AS
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence,
    FIELD1, FIELD2, FIELD3...
    FROM [<schema>].[<view-name>]

Answer 1

@Renato de Melo

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Ask: I am migrating a procedure from SQL Dedicated to Serverless SQL Pool.

The procedure queries an existing Delta table and iterate over the rows. I tried two ways inside the proc, but both failed with same message:

Started executing query at Line 16
Msg 15816, Level 16, State 2, Line 1
The query references an object that is not supported in distributed processing mode.
Total execution time: 00:00:00.839

These are the ways I tried, first using the OPENROWSET, then direct reference to the view. In both cases the procedure is created with no errors, but when execution, I see the error above. I appreciate any help on how to iterate over a table inside a PROC in Serverless SQL Pool.

    CREATE TABLE #temp_table
    WITH
    ( DISTRIBUTION = ROUND_ROBIN)
    AS
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence,
    FIELD1, FIELD2, FIELD3...
    FROM OPENROWSET(
        BULK '<delta-table path',
        FORMAT = 'DELTA',
        DATA_SOURCE = '<ds-name>'
    ) AS source

    CREATE TABLE #temp_table
    WITH
    ( DISTRIBUTION = ROUND_ROBIN)
    AS
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence,
    FIELD1, FIELD2, FIELD3...
    FROM [<schema>].[<view-name>]

**Solution:**Wrapping the query into a SP worked. Thank you!

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Wrapping the query into a SP worked. Thank you!