hii want to run stored procedure in synapse but ima getting an error ,can anyone help me

Question

query:

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://msinstorageaccount145.dfs.core.windows.net/gold/SalesLT/'+@ViewName+'/'',FORMAT = ''DELTA'') AS [result]'

EXEC (@statement)

END
GO

error i got is this
Error

Stored procedure1

{ "errorCode": "2402", "message": "Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 156. Error Message: Incorrect syntax near the keyword 'SELECT'.", "failureType": "UserError", "target": "Stored procedure1", "details": [] }
can anyone help please

Answer

I am assuming you may need only the Azure SQL Database or Azure Synapse Analytics tag here, I updated your query like the following :

USE gold_db;
GO

-- Optionally, drop the existing procedure if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'CreateSQLServerlessView_gold')
    DROP PROCEDURE CreateSQLServerlessView_gold;
GO

CREATE PROCEDURE CreateSQLServerlessView_gold @ViewName nvarchar(100)
AS
BEGIN
    DECLARE @statement NVARCHAR(MAX);
    SET @statement = N'CREATE VIEW ' + QUOTENAME(@ViewName) + N' AS 
        SELECT * FROM OPENROWSET(
            BULK ''https://msinstorageaccount145.dfs.core.windows.net/gold/SalesLT/' + @ViewName + '/'',
            FORMAT = ''CSV''
        ) AS result;';
    EXEC sp_executesql @statement;
END;
GO

Answer

Hi naveena kankipati
The code you provided looks correct and should create a SQL Serverless view that reads data from a Delta Lake table stored in Azure Data Lake Storage Gen2. The error message you received earlier might have been caused by a temporary issue or a typo in the SQL statement. If you encounter the error again, please double-check the syntax of the SQL statement and make sure that all the required parameters are provided correctly. If you have any further questions or issues, please let me know.

Share via

hii want to run stored procedure in synapse but ima getting an error ,can anyone help me

2 answers

Your answer