question

RohitKulkarni-3496 avatar image
0 Votes"
RohitKulkarni-3496 asked ·

Blob Storage multiplle files

Hello Team,

In a container there are 5 files are present.With the same column name.But the order of the column name are different among all the 5 files.

FolderName :

File1,
File2,
File3,
File4,
File5

When i run query in MS Sql server i am getting the error :
Msg 15813, Level 16, State 1, Line 1
Error handling external file: 'Max errors count reached.'. File/External table name: 'https://storagename.blob.core.windows.net/staging-zone/FolderName/Filename*.csv'.

Please advise how i can solve this issue


Regards
RK

sql-server-generalazure-blob-storage
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In which environment are you running this? You say SQL Server, but the format of OPENROWSET you use does not seem familiar to me. It looks as if you could be on Synapse.

Could you post the output of "SELECT @@version" so that we know where you are?

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered ·

Hi @RohitKulkarni-3496,

Have you created a database scoped credential created for a shared access signature and created an external data source pointing to an Azure storage account before accessing data in a CSV file?
Please refer to bulk access to data in Azure Blob storage and Configure PolyBase to access external data in Azure Blob Storage which might help.
In addition, it seems there is something wrong with the OPENROWSET statement in your query. Please refer to OPENROWSET Syntax for more details.

Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RohitKulkarni-3496 avatar image
0 Votes"
RohitKulkarni-3496 answered ·

Just for example I am copying the query which i used in this format:

SELECT FROM
OPENROWSET(BULK 'https://storagename.blob.core.windows.net/staging-zone/FolderName/Filename
.csv'
, FORMAT = 'csv' , Parser_Version= '2.0' , FIELDTERMINATOR = '|' , FIELDQUOTE='"' , ESCAPECHAR = '\') WITH
(
fieldName1 nvarchar(50)
fieldName2 nvarchar(20)
fieldName3 tinyint,
fieldName4 nvarchar(50),
fieldName5 int
) as r

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RohitKulkarni-3496 avatar image
0 Votes"
RohitKulkarni-3496 answered ·

I wanted to write the same like :

SELECT FROM
OPENROWSET(BULK 'https://storagename.blob.core.windows.net/staging-zone/FolderName/Filename
.csv'
, FORMAT = 'csv' , Parser_Version= '2.0' , FIELDTERMINATOR = '|' , FIELDQUOTE='"' , ESCAPECHAR = '\') with
select * from tableName
) as r

But not able to find the correct syntax.Please advise.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RohitKulkarni-3496 avatar image
0 Votes"
RohitKulkarni-3496 answered ·

The solution is got fixed after changing the data types for few of the columns.

Thanks

RK

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.