Azure SQL database - Right approach to get content of a file in store procedure

Question

** Scenario:**

SQL stored procedure takes fileName as input.
Read the content of the file and insert it into a table.

For local SQL server, above scenario has been achieved by following stored procedure.

-- @_filePath would be input to the store procedure
DECLARE @_filePath NVARCHAR(MAX) =  'C:\SQLContent\test1.sql'

CREATE TABLE tmpTable (tmpIndex INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED, sqlContent NVARCHAR(MAX) NOT NULL)

-- Read content of the file @_filePath
DECLARE @_insertQuery NVARCHAR(MAX) = 'insert tmpTable (sqlContent) select * from OPENROWSET(bulk ''' + @_filePath + ''', SINGLE_CLOB) as a'
EXEC sp_ExecuteSql @_insertQuery


Select * from tmpTable

Problem:

The actual problem is with Azure SQL server, a path provided in above-mentioned stored procedure won't be accessible to SQL server.

C:\SQLContent\test1.sql

Error Message with SQL Azure:

Cannot bulk load because the file "C:\SQLContent\test1.sql" could not be opened. Operating system error code (null).

Possible Solutions:

Upload file test1.sql to Azure blob and in the stored procedure read file content from the blob as follows:

select *
from OPENROWSET
 (BULK 'temp1.sql', DATA_SOURCE = 'MyAzureStorage', SINGLE_CLOB) as data

Reference - [error executing OPENROWSET (BULK) / Azure SQL Database

Instead of passing @_filePath as an argument to the stored procedure, pass file content as an argument to the stored procedure.

Ask

Out of two possible solutions to the problem, which one is the best one and why?

Also, Suggest if there is any other better approach.

Alberto Morillo Alberto Morillo · Accepted Answer · 2018-09-20T14:35:28

You can use OPENROWSET or you can use BULK INSERT also. I don't know any advantage or disadvantage that can make one better than the other. However, if the storage account is public you need as a minimum to create an external data source:

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
 WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://myazureblobstorage.blob.core.windows.net');

BULK INSERT Product
FROM 'data/product.dat'
WITH ( DATA_SOURCE = 'MyAzureBlobStorageAccount');

If the storage account is not public you need to create a database scoped credential additionally.

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'sv=2015-12-11&ss=b&srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z&spr=https&sig=copyFromAzurePortal';

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
 WITH ( TYPE = BLOB_STORAGE,
        LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
        CREDENTIAL= MyAzureBlobStorageCredential);

BULK INSERT Product
FROM 'data/product.dat'
WITH ( DATA_SOURCE = 'MyAzureBlobStorageAccount');

Hope this helps.

Azure SQL database - Right approach to get content of a file in store procedure

1 Answers