I am getting an error Incorrect syntax near 'OPENROWSET'. while using openrowset function-CodePudding

First I added a credential:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Mumbai@1234';
GO
CREATE DATABASE SCOPED CREDENTIAL credanubhav -- this name must match the container path, start with https and must not contain a trailing forward slash.
    WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it.
    , SECRET = '?{REDACTED}' -- this is the shared access signature token
GO

Then I Created an External Data Source:

    create external DATA SOURCE anubhav
    WITH(
        LOCATION = 'abfss://[email protected]/Folder1/',
CREDENTIAL=credanubhav
    )

(In my CSV file there is 3 columns name id, name and age) when I write openrowset function to see my data I am getting an error:

SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'blob.csv',
        DATA_SOURCE= 'anubhav',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0'
    ) AS [result]

CodePudding user response：

Currently OPENROWSET only works in a Synapse Serverless SQL pool, not a Synapse Dedicated SQL Pool. Instead you can use the CREATE EXTERNAL TABLE or the COPY INTO statements.

For example try:


CREATE EXTERNAL FILE FORMAT ff_CSV
 WITH (FORMAT_TYPE = DELIMITEDTEXT,
 FORMAT_OPTIONS(
 FIELD_TERMINATOR = ',',
 STRING_DELIMITER = '"',
 FIRST_ROW = 2,
 USE_TYPE_DEFAULT = True,
 Encoding = 'UTF8'
 )
 )


CREATE EXTERNAL TABLE [dbo].[SampleExternal]
( [id] [nvarchar](200) NULL,
  [name] nvarchar(200) NULL,
  [age] nvarchar(200) NULL )
WITH
(
    LOCATION='blob.csv' ,
    DATA_SOURCE = anubhav ,
    FILE_FORMAT = ff_CSV ,
    REJECT_TYPE = VALUE ,
    REJECT_VALUE = 0
);

SELECT TOP 100 * from dbo.SampleExternal

CodePudding user response：

CREATE EXTERNAL DATA SOURCE and OPENROWSET use the https format, not abfss.

Sample: https://<storage_account>.dfs.core.windows.net//subfolders

NOTE: This is NOT the answer - Dedicated SQL Pool does not support OPENROWSET.