CODEWITHSUNDEEP
t-sqlazure-functionsazure-synapseopenrowset
Anubhav
Anubhav

Reputation: 365

I am getting an error Incorrect syntax near 'OPENROWSET'. while using openrowset function on dedicated SQL Pool

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]

Upvotes: -1

Views: 2906

Answers (3)

Himanshu Kumar Sinha
Himanshu Kumar Sinha

Reputation: 1806

I will suggest you to let Synapse generate the code for you and then you can compare and know what is not different . For this you will hav to add the container as a linked service and then just point to the folder/file and Synapse Studio will do the rest .

enter image description here

Upvotes: 1

Joel Cochran
Joel Cochran

Reputation: 7758

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.

Upvotes: 0

GregGalloway
GregGalloway

Reputation: 11625

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

Upvotes: 4

Related Questions