SQL Server Management Studio reading image on c:\

Question

I am using SQL Server Management Studio 17. I am connecting to an Azure database. I have an insert query where I want to insert an image file .png into the database. I cannot figure out how to allow the management studio to read the file on my local hard drive. The error I get is: 

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\img\imagefile.png" could not be opened. Operating system error code (null).

I've tried several things - adding NETWORK SERVICE and a SQLServer2005.... account to the permissions of the folder. This did not work.

How can I load an image into an Azure SQL Server table?

Thanks,

Answer 1

Hi Brian,

What is the statement you use to insert the .png file into the Azure database. If you use the BULK INSERT, you should note that the Azure SQL Database only supports reading from Azure Blob Storage. You have to upload a file to an Azure Blob Store and then, from there, you can use BULK INSERT or OPENROWSET to open the file.

Please refer to following posts:
BULK INSERT (Transact-SQL)

How to insert image file, stored in Azure blob storage, into an Azure SQL table

Azure bulk import csv file into Azure SQL Server from Azure file service location

As an alternative to import and export data in SQL Azure database, maybe you can consider Import and Export Wizard(Azure data sources currently only Azure Blob Storage.) or BCP utility.

Best Regards,
Cris

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

to read the file on my local hard drive. 

SQL Azure can never access your local drive, independent of the used tool or the file type. You have to encode the image as hex value, you have to copy the image or you have to program an app/PowerShell script to insert the image into the table.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

Hi

Insert into SFMC_HTML_Images (HTML_Image) 
Select BulkColumn FROM OPENROWSET( 
BULK '4017033.jpg', 
DATA_SOURCE = 'MyAzureBlobStorage', SINGLE_BLOB) AS ImageFile; 
Thanks and regards

Answer 4

Hi friend,

Is there any update on this case? Was your issue resolved? 

If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

In addition, if you have another questions, please feel free to ask.
Thanks for your contribution.

Best regards,
Cris

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

I will give this a try - using Azure Blob Storage. Sorry, didn't reply sooner, but am not getting any notifications in email that I had any replies.

Answer 6

Hi Brian,

Thanks for your reply.

Looking forward to your update.

Best Regards,
cris

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 7

I followed the below steps and I'm still getting the same error in SQL Management Studio. 

1. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'a password';

2. I generated a shared access signature in the Azure portal for the storage account I'm trying to access.

3.  Ran:

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '**********wdlacupx&se=2020-08-06T05:45:19Z&st=2020-08-05T21:45:19Z&**********'

I used both the secret as copied and as copied deleting the leading ? per the MS documentation.

4. Ran

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

5. Ran

INSERT INTO db_datawriter.mwhmockvtsreps VALUES ('23412345','Nathan Summers','Nathan','Summers','VTS','800-555-1234','Mon-Fri: 8:00 AM - 5:00 PM, CT',(SELECT * FROM OPENROWSET(BULK 'nathansummers.png', DATA_SOURCE = 'MyAzureBlobStorage', SINGLE_BLOB) as T1));

Get the following error:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "nathansummers.png" could not be opened. Operating system error code 5(Access is denied.)

Both the SQL Server and the storage are in a VNET - same VNET, though.

Answer 8

Hi Brian,

Please check below posts if help:

Azure Blob Cannot Bulk Load

Examples of bulk access to data in Azure Blob storage

And  I suggest you open a thread in Azure SQL database forum, People there will help you more effectively.

Best Regards,
Cris

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.