Cursor for blob to file system ole adodb.stream

CREATE 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)) DECLARE @ImageData VARBINARY(max); DECLARE @FileName VARCHAR(max); DECLARE @Launch AS VARCHAR(1000) DECLARE WorkStationCursor CURSOR FAST_FORWARD FOR SELECT AttachmentFile --the blob ,AttachFileName --the file system name 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; 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 ------------------------------------------ --You may have to enable the ability to do this: Use master Go EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO EXEC sp_configure 'show advanced options', 0; GO RECONFIGURE;

Cursor between dates (format them nicely in output)

CREATE TABLE #tmpI ( WeekEnding DATETIME ,iCounts INT ) DECLARE @workstation DATETIME DECLARE @workstationEnd DATETIME DECLARE WorkStationCursor CURSOR FAST_FORWARD FOR SELECT DISTINCT convert(NVARCHAR, DATEADD(ww, DATEDIFF(ww, 0, convert(NVARCHAR, dtClicked, 101)), 0), 101) AS dtClicked ,convert(NVARCHAR, DATEADD(ww, DATEDIFF(ww, 6, convert(NVARCHAR, dtClicked, 101)), 0), 101) AS dtClickedEnd FROM [intranet].[dbo].[_ClickTracker] WHERE dtClicked >= '03-01-2020' AND RID = 16 ORDER BY dtClicked DESC OPEN WorkStationCursor FETCH NEXT FROM WorkStationCursor INTO @workstation ,@workstationEnd WHILE @@FETCH_STATUS = 0 BEGIN PRINT cast(@workstationEnd AS NVARCHAR(20)) + ',' + cast(@workstation AS NVARCHAR(20)) INSERT INTO #tmpI SELECT @workstation ,COUNT(dtClicked) FROM [intranet].[dbo].[_ClickTracker] WHERE dtClicked BETWEEN @workstationEnd AND @workstation --group by dtClicked FETCH NEXT FROM WorkStationCursor INTO @workstation ,@workstationEnd END CLOSE WorkStationCursor DEALLOCATE WorkStationCursor SELECT convert(NVARCHAR(20), WeekEnding, 101) AS WeekEnding ,iCounts FROM #tmpI ORDER BY cast(WeekEnding AS DATE) DESC DROP TABLE #tmpI --DROP TABLE @tempTable