How to fix error code 202 when using stored procedure in Azure Synaspe

Question

Hello all,

I need some assistance here.

I created a pipeline to run a stored produce inside a for each activity. I am getting the errors shown in the first code box below. I have checked the stored procedure code and it works when ran by itself. Each time I run this pipeline the syntax error moves to another table name.

The goal is to use the For Each activity with the store procedure to create a view for each table in the data lake.

Operation on target For each table name failed: Activity failed because an inner activity failed; Inner activity name: Stored procedure1, Error: Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 102. Error Message: Incorrect syntax near 'VIEWSalesOrderDetail'.

stored procedure code:

USE gold_db
GO
CREATE OR ALTER PROC CreateSQLServerlessView_gold @ViewName nvarchar (100)
AS
BEGIN

DECLARE @statement varchar(max)

    SET @statement = N'CREATE OR ALTER VIEW' + @ViewName + ' AS
        SELECT *
        FROM
            OPENROWSET(
                BULK ''https://ebgenlake2.dfs.core.windows.net/gold/SalesLT/' + @ViewName + '/'',
                FORMAT = ''DELTA''
        ) AS [result]
    '
EXEC (@statement)

END
GO

Answer 1

I think the error you're encountering is related to a syntax error in the SQL statement that is dynamically constructed within the stored procedure.

There is a space issue between the CREATE OR ALTER VIEW and the @ViewName.

Try to update your query like below :

USE gold_db
GO
CREATE OR ALTER PROC CreateSQLServerlessView_gold @ViewName nvarchar (100)
AS
BEGIN
DECLARE @statement varchar(max)
    SET @statement = N'CREATE OR ALTER VIEW ' + @ViewName + ' AS
        SELECT *
        FROM
            OPENROWSET(
                BULK ''https://ebgenlake2.dfs.core.windows.net/gold/SalesLT/' + @ViewName + '/'',
                FORMAT = ''DELTA''
        ) AS [result]
    '
EXEC (@statement)
END
GO