SQL Server Cursors – Write Out Images to a Mapped Drive

cursorssql server

I am using a cursor to write out images stored in a database to text.

Code snippet here:

DECLARE @SOURCEPATH VARBINARY(MAX),
        @DESTPATH VARCHAR(MAX),
        @ext varchar(MAX),
        @ObjectToken INT,
        @image_ID Varchar(255)

DECLARE IMGPATH CURSOR FAST_FORWARD FOR
    SELECT <my data>
    FROM <my table>

OPEN IMGPATH

FETCH NEXT FROM IMGPATH INTO @SOURCEPATH, @image_ID

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DESTPATH = '<my path>'+ @image_ID  

    EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
    EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    EXEC sp_OAMethod @ObjectToken, 'Open'
    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @SOURCEPATH
    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @DESTPATH, 2
    EXEC sp_OAMethod @ObjectToken, 'Close'
    EXEC sp_OADestroy @ObjectToken

    FETCH NEXT FROM IMGPATH INTO @SOURCEPATH, @image_ID
END

CLOSE IMGPATH
DEALLOCATE IMGPATH

I am able to write to a local drive without a problem, but I cannot write to the network share that I'd like to write to. I've followed the steps here,

https://www.mssqltips.com/sqlservertip/3499/make-network-path-visible-for-sql-server-backup-and-restore-in-ssms/

in an effort to make the drive "visible" to SQL Server, but I still am not having any luck writing the images to this mapped drive.

Is this functionality supported, should I go down the route of checking permissions with our network guys, or is this not something that is possible?

Best Answer

SQL Server can usually see a UNC path like \\server\share\subfolder

But only if the service running SQL Server has rights to that share. For example, if you're running a scheduled job, the job runs as the user for the SQL Agent Service. If you're running code directly in SSMS or a stored procedure called from your program, then the SQL Engine Service must have rights to that shared folder.

If that user is a local user only, then it won't have rights to the shared folder. SQL Server engine and/or agent must be running as a domain user to access the volume.

For example, we run our backups to a \\server\DBs\SQL\<server name> shared drive. The SQL Agent Service account has to have full rights to that shared folder for the backups to succeed. There's no mapped drive involved in these backups.

Related Question