quote:

---

Originally posted by spirit1

could you create a CLR sproc to do this? it is prefered way to do custom disk read/write activity in sql server 2005.

---

SET NOCOUNT ON

DECLARE @ErrorNumber INT
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ErrorProcedure NVARCHAR(126)
DECLARE @ErrorLine INT
DECLARE @ErrorMessage NVARCHAR(2048)

--For OLE Automation.
DECLARE @ObjectReturn INT
DECLARE @ObjectToken INT
DECLARE @ErrorSource VARCHAR(255)
DECLARE @ErrorDesc VARCHAR(255)
DECLARE @FileHandle INT
DECLARE @ObjectTargetFileName VARCHAR(1000)
DECLARE @Buffer VARCHAR(4000)
DECLARE @BufferPos INT
DECLARE @BufferSize INT; SET @BufferSize = 4000

DECLARE @ScheduleData XML

/* Get the schedule data */
--custom code went here to return XML datatype into @ScheduleData - so assume @ScheduleData is valid XML.

BEGIN TRY
    /* Create a file system object. */
    EXEC @ObjectReturn = sp_OACreate 'Scripting.FileSystemObject', @ObjectToken OUTPUT
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('Create Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Open the file. */
    SET @ObjectTargetFileName = '\\SERVERNAME\SHARENAME\SYSMAINT.XML'
    EXEC @ObjectReturn = sp_OAMethod @ObjectToken, 'OpenTextFile', @FileHandle OUTPUT, @FileName=@ObjectTargetFileName, @IOMode=2, @Create=1
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('OpenTextFile Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Write the file */
    /* Since the writing of the file gets truncated at 4000 characters, buffer the writes. */
    SET @BufferPos = 0
    WHILE (@BufferPos < LEN(CAST(@ScheduleData AS NVARCHAR(MAX))))
        BEGIN
            SET @Buffer = SUBSTRING(CAST(@ScheduleData AS NVARCHAR(MAX)), @BufferPos + 1, @BufferSize)
            SET @BufferPos = @BufferPos + @BufferSize

            EXEC @ObjectReturn = sp_OAMethod @FileHandle, 'Write', NULL, @Text=@Buffer
            IF (@ObjectReturn <> 0)
                BEGIN
                    EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
                    RAISERROR('Write Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
                END
        END

    /* Close the file. */
    EXEC @ObjectReturn = sp_OAMethod @FileHandle, 'Close'
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('Close Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Destroy the text stream object. */
    EXEC @ObjectReturn = sp_OADestroy @FileHandle
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('TextStream Destroy Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Destroy the file system object. */
    EXEC @ObjectReturn = sp_OADestroy @ObjectToken
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('FileSystemObject Destroy Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END
END TRY
BEGIN CATCH
    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorProcedure = ERROR_PROCEDURE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorMessage = ERROR_MESSAGE();
    RAISERROR('Procedure ''%s'' failed on line number ''%u'' with message ''%s'' - (error number: ''%u'', severity: ''%u'', state: ''%u'').', 15, 1, @ErrorProcedure, @ErrorLine, @ErrorMessage, @ErrorNumber, @ErrorSeverity, @ErrorState)
END CATCH

DECLARE @ErrorNumber INT
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ErrorProcedure NVARCHAR(126)
DECLARE @ErrorLine INT
DECLARE @ErrorMessage NVARCHAR(2048)

--For OLE Automation.
DECLARE @ObjectReturn INT
DECLARE @ObjectToken INT
DECLARE @ErrorSource VARCHAR(255)
DECLARE @ErrorDesc VARCHAR(255)
DECLARE @FileHandle INT
DECLARE @ObjectTargetFileName VARCHAR(1000)
DECLARE @Buffer VARBINARY(4000)

DECLARE @BufferPos INT
DECLARE @BufferSize INT; SET @BufferSize = 4000

DECLARE @Image VARBINARY(MAX)

--custom code went here to retrieve an IMAGE column into variable @Image from an IMAGE datatype in a table.
--SELECT @Image = assume valid query is here.

BEGIN TRY
    /* Create a file system object. */
    EXEC @ObjectReturn = sp_OACreate 'Scripting.FileSystemObject', @ObjectToken OUTPUT
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('Create Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Open the file. */
    SET @ObjectTargetFileName = '\\SQLPRODVIRTUAL\SQLUPLOAD\TEMP\' + 'BEN01.JPG'
    EXEC @ObjectReturn = sp_OAMethod @ObjectToken, 'OpenTextFile', @FileHandle OUTPUT, @FileName=@ObjectTargetFileName, @IOMode=2, @Create=1
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('OpenTextFile Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    SET @BufferPos = 0
    WHILE (@BufferPos < DATALENGTH(@Image))
        BEGIN
            SET @Buffer = SUBSTRING(@Image, @BufferPos + 1, @BufferSize)
            SET @BufferPos = @BufferPos + @BufferSize

            EXEC @ObjectReturn = sp_OAMethod @FileHandle, 'Write', NULL, @Text=@Buffer
            IF (@ObjectReturn <> 0)
                BEGIN
                    EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
                    RAISERROR('Write Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
                END
        END

    /* Close the file. */
    EXEC @ObjectReturn = sp_OAMethod @FileHandle, 'Close'
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('Close Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Destroy the text stream object. */
    EXEC @ObjectReturn = sp_OADestroy @FileHandle
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('TextStream Destroy Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Destroy the file system object. */
    EXEC @ObjectReturn = sp_OADestroy @ObjectToken
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('FileSystemObject Destroy Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END
END TRY
BEGIN CATCH
    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorProcedure = ERROR_PROCEDURE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorMessage = ERROR_MESSAGE();
    RAISERROR('Procedure ''%s'' failed on line number ''%u'' with message ''%s'' - (error number: ''%u'', severity: ''%u'', state: ''%u'').', 15, 1, @ErrorProcedure, @ErrorLine, @ErrorMessage, @ErrorNumber, @ErrorSeverity, @ErrorState)
END CATCH

Function BinaryToString(Binary) 
 Dim RS, LBinary 
 Const adLongVarChar = 201 
 Set RS = Server.CreateObject("ADODB.Recordset") 
 LBinary = LenB(Binary) 


 If LBinary>0 Then 
  RS.Fields.Append "mBinary", adLongVarChar, LBinary 
  RS.Open 
  RS.AddNew 
  RS("mBinary").AppendChunk Binary 
  RS.Update 
  BinaryToString = RS("mBinary") 
 Else 
  BinaryToString = "" 
 End If 

 RS.Close 
 Set RS = Nothing 
End Function 

quote:

---

Originally posted by spirit1

so don't let them have permissions to create assemblies.

---

EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, <variable defined as VARBINARY(MAX)>
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, <location on disk>, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken

DECLARE @SQLIMG VARCHAR(MAX),
	@IMG_PATH VARBINARY(MAX),
	@TIMESTAMP VARCHAR(MAX),
	@ObjectToken INT

DECLARE IMGPATH CURSOR FAST_FORWARD FOR 
		SELECT csl_CompanyLogo from mlm_CSCompanySettingsLocalizations
		
OPEN IMGPATH 

FETCH NEXT FROM IMGPATH INTO @IMG_PATH 

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @TIMESTAMP = 'd:\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.bmp'

		PRINT @TIMESTAMP
		PRINT @SQLIMG

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

		FETCH NEXT FROM IMGPATH INTO @IMG_PATH 
	END 

CLOSE IMGPATH
DEALLOCATE IMGPATH