--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;
*/
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