SQL Export blob to Network Share

--EXEC sp_xp_cmdshell_proxy_account 'sqlPatientFeedbackHistory', 'L3tsg3t1tD0n3';

ALTER PROCEDURE sp_GetDocuments (@FeedbackID INT)

AS

--https://www.mssqltips.com/sqlservertip/4963/simple-image-import-and-export-using-tsql-for-sql-server/

EXEC XP_CMDSHELL 'net use y: \\it-lemmermann\test'

    ,no_output

--EXEC XP_CMDSHELL 'Dir y:'

IF OBJECT_ID(N'dbo.tmp', N'U') IS NOT NULL

    DROP TABLE dbo.tmp

CREATE TABLE dbo.tmp (

    sFileName VARCHAR(MAX)

    ,[Description] VARCHAR(MAX)

    )

DECLARE @ImageData VARBINARY(max);

DECLARE @FileName VARCHAR(max);

DECLARE @Launch AS VARCHAR(1000)

DECLARE WorkStationCursor CURSOR FAST_FORWARD

FOR

SELECT AttachmentFile

    ,AttachFileName

    ,[Description]

FROM [PatientFeedback_Historical].[dbo].[FMProAttachmentList] al

JOIN [PatientFeedback_Historical].[dbo].[FMProAttachment] a ON al.attachId = a.attachId

WHERE FEEDBACKID = @FeedbackID

ORDER BY al.attachid DESC

OPEN WorkStationCursor

FETCH NEXT

FROM WorkStationCursor

INTO @ImageData

    ,@FileName

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @FileName = '\\it-lemmermann\test\' + @FileName

    DECLARE @Obj INT

    BEGIN TRY

        EXEC sp_OACreate 'ADODB.Stream'

            ,@Obj OUTPUT;

        EXEC sp_OASetProperty @Obj

            ,'Type'

            ,1;

        EXEC sp_OAMethod @Obj

            ,'Open';

        EXEC sp_OAMethod @Obj

            ,'Write'

            ,NULL

            ,@ImageData;

        EXEC sp_OAMethod @Obj

            ,'SaveToFile'

            ,NULL

            ,@FileName

            ,2;

        EXEC sp_OAMethod @Obj

            ,'Close';

        EXEC sp_OADestroy @Obj;

        -- SET @Launch=@Launch+'"' + @FileName + '"'

        INSERT INTO dbo.tmp (sFileName)

        VALUES (@FileName)

    END TRY

    BEGIN CATCH

        EXEC sp_OADestroy @Obj;

        SET @Launch = '"' + 'Error' + '"'

    END CATCH

    FETCH NEXT

    FROM WorkStationCursor

    INTO @ImageData

        ,@FileName

END

CLOSE WorkStationCursor

DEALLOCATE WorkStationCursor

--EXEC XP_CMDSHELL @Launch

EXEC XP_CMDSHELL 'net use y: /delete'

    ,no_output

--select @Launch

SELECT *

FROM dbo.tmp

DROP TABLE dbo.tmp

    /*

CREATE TABLE dbo.tmp (

    unit NVARCHAR(max)

    ,totclosed INT

    )

--Can we name the output file ptflowclsedbed.csv

INSERT INTO dbo.tmp

EXEC CrouseCustom_ClosedBedCounts

DECLARE @sql VARCHAR(8000)

SELECT @sql = 'bcp "select * from dbo.tmp" queryout h:\out.csv -c -t, -T -d APF_OLTP -S' + 'CRH16VTPTFLOWDB' --@@servername

EXEC master..xp_cmdshell @sql

DROP TABLE dbo.tmp

--S:\FTP_Data\Cloverleaf\datatodss\test

SELECT @@servername

*/

    /*

Use master

Go

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

--ALTER SERVER ROLE [bulkadmin] ADD MEMBER [Enter here the Login Name that will execute the Import]

GO

EXEC sp_configure 'show advanced options', 0;

GO

RECONFIGURE;

*/


Grants

credential = ##xp_cmdshell_proxy_account## to windows account

GRANT ALTER ON SCHEMA::dbo TO sqlPatientFeedbackHistory;

use master

grant exec opn sp_cmdshell to sqlPatientFeedbackHistory

grant exec on dbo.sp_OASetProperty to sqlPatientFeedbackHistory

grant exec on dbo.sp_OACreate to sqlPatientFeedbackHistory

grant exec on dbo.sp_OAMethod to sqlPatientFeedbackHistory

grant exec on dbo.sp_OADestroy to sqlPatientFeedbackHistory