How to save images in SQL Server from backend
OPENROWSET
Includes all connection information that is required to access remote data from an OLE DB data source. This method is
an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data
by using OLE DB. For more frequent references to OLE DB data sources, use linked servers instead.
The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.
OPENROWSET also supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset.
Using OPENROWSET to bulk insert file data into a varbinary(max) column
The following example creates a small table for demonstration purposes, and inserts file data from a file named Text1.txt located in the C: root directory into a varbinary(max) column.
USE AdventureWorks2008R2;
GO
CREATE TABLE myTable(FileName nvarchar(60),
FileType nvarchar(60), Document varbinary(max));
GO
INSERT INTO myTable(FileName, FileType, Document)
SELECT 'Text1.txt' AS FileName,
'.txt' AS FileType,
* FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
GO
When used with the BULK provider keyword you can name a data file to read as one of three types of objects:
- SINGLE_BLOB, which reads a file as varbinary(max)
- SINGLE_CLOB, which reads a file as varchar(max)
- SINGLE_NCLOB, which reads a file as nvarchar(max)
OPENROWSET returns a single column, named BulkColumn, as its result. Here’s an example that reads a text file:
SELECT BulkColumn
FROM OPENROWSET (BULK 'c:\temp\mytxtfile.txt', SINGLE_CLOB) MyFile
The correlation name, in this case MyFile, is required by OPENROWSET.
There are additional requirements when reading single files that must also be observed as mentioned below.
Access control is always a concern. The operating system level file operations to read the file are executed with the privileges of the account that the SQL Server data engine is using. Therefore, only files accessible to that account may be read. This includes network drives or UNC paths, which are permitted if the account has the privileges. If you want to read network files, run SQL Server as a domain user.
The BULK provider won’t convert between Unicode and plain ASCII files. It must be told which type of encoding is used in the file. If you don’t the result is error 4806 as seen here:
SELECT BulkColumn
FROM OPENROWSET (BULK 'c:\temp\SampleUnicode.txt', SINGLE_CLOB) MyFile
Unicode files must be read with the SINGLE_NCLOB option shown here:
SELECT BulkColumn
FROM OPENROWSET (BULK 'c:\temp\SampleUnicode.txt', SINGLE_NCLOB) MyFile
Similarly, files with non text structures, such as Word documents are not converted. They must be converted by some other mechanism before being read or they can be read as binary files with the SINGLE_BLOB option.
Bulk Importing from a Remote Data File
To use
BULK INSERT or INSERT...SELECT * FROM OPENROWSET(BULK...)
to bulk import data from another computer, the data file must be shared between the two computers. To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form, \\Servername\Sharename\Path\Filename. Additionally, the account used to access the data file must have the permissions that are required for reading the file on the remote disk.
SELECT BulkColumn
FROM OPENROWSET (BULK ‘\\Servername\Sharename\SampleUnicode.txt', SINGLE_NCLOB) MyFile