SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Export image datatype to disk
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/25/2008 :  11:02:56  Show Profile  Reply with Quote
I have a table with an IMAGE datatype. Without using xp_cmdshell and bcp, is it possible to get this image to disk? We do have the OLE procedures enabled so I can use those to create in instance of the Scripting.FileSystemObject and write to disk but how do I convert the image datatype to something I can store on disk and it actually be a readable format? If it helps, we only store this particular field in JPEG format in the database.

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 04/25/2008 :  11:09:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If the data is stored as IMAGE, it means it is stored binary.
Make a binary filestream with Scripting.FileSystemObject .



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/25/2008 :  12:00:52  Show Profile  Reply with Quote
Here lies my issue... can you use the Scripting.FileSystemObject to write a binary file using T-SQL? I know in VB / C#, etc you can by using a byte array but how would I emulate that in T-SQL? Perhaps I can use an ADODB.Stream object instead...
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 04/25/2008 :  14:06:37  Show Profile  Visit spirit1's Homepage  Reply with Quote
could you create a CLR sproc to do this? it is prefered way to do custom disk read/write activity in sql server 2005.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/25/2008 :  14:27:38  Show Profile  Reply with Quote
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.


I might be able to but I won't :). In my situation, I do not want to open the avenue of developers coding CLR on my databases for obvious reasons. Here is a method I use for standard text files:
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


Now when I tailor this for an image datatype, the write always fails. The error message is very vague unfortunately but I have to assume it's due to the file being binary. Here is a similar script I have at this point for binary data (note the line in red that failes):
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


The error I get back is
Msg 50000, Level 15, State 1, Line 105
Procedure '(null)' failed on line number '69' with message 'Write Error (return: '2148139013', source: '(null)', description: '(null)')' - (error number: '50000', severity: '15', state: '1').
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 04/25/2008 :  14:41:01  Show Profile  Visit spirit1's Homepage  Reply with Quote
i can't believe you prefer sp_OA* to CLR...

Write can only write string. so you'll have to convert your binary to string.
usually this is done with ADODB.RecordSet object:

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 


so... i still can't convince you to use clr for this? it's just a few lines of code

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Edited by - spirit1 on 04/25/2008 14:41:17
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/25/2008 :  14:48:29  Show Profile  Reply with Quote
As far as CLR is concerned, I'd love to honestly but I'm in a situation where if I did, some developer would start writing code on my database and cause it to fail... guaranteed :). Not maliciously or anything but we do from time to time struggle with quality control of the developers overseas (we outsource 1/2 our shop).

Anyway, I did change one line and the writes started working:
EXEC @ObjectReturn = sp_OAMethod @ObjectToken, 'OpenTextFile', @FileHandle OUTPUT, @FileName=@ObjectTargetFileName, @IOMode=2, @Create=1, @Format=-1

However the file still isn't a valid JPG... most likely due to not being converted properly. I was really hoping there was some casting I could do in T-SQL to accomplish this (like SUBSTRING since it was reworked to work with VARBINARY(MAX) datatype).

Anyway, I would like to actually not even use the sp_OA procs so if you have any other suggestions I definitely would welcome them. I need to run but I'm going to try a ASCII(CHAR(n)) on each character later... LOL.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 04/25/2008 :  15:01:02  Show Profile  Visit spirit1's Homepage  Reply with Quote
so don't let them have permissions to create assemblies.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/28/2008 :  12:03:38  Show Profile  Reply with Quote
quote:
Originally posted by spirit1

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


I just might go this route TBH. Especially since I'm pretty fluent in C# as well. I suspect I can make this work in our current security model as well (we have a daily job that strips and resets permissions across all of our databases). As long as I just keep the permissions for creating CLR to a few system IDs I should be good to go. Correct me if I'm wrong but this is already part of the sysadmin role so I really will not need to make any security changes with the exception of executing. Which I can do since we have standard roles that I can just assign this proc to (methinks).
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 04/28/2008 :  16:36:42  Show Profile  Reply with Quote
Hey spirit (and anyone else that responded)... I ended up taking a shortcut and still using the sp_OA procs. In summary I just utilized the ADODB.Stream object something like this:

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

I would love to go the CLR route but right now I do not have the luxary to fit it into our environment and this works simply put.

I will however put a task on my list to upgrade our approach (we've been using this for literally YEARS).

Edited by - tfountain on 04/28/2008 16:38:23
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 04/28/2008 :  16:43:25  Show Profile  Visit spirit1's Homepage  Reply with Quote
nice!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

pprajapati
Starting Member

2 Posts

Posted - 01/12/2009 :  01:49:16  Show Profile  Reply with Quote
i have tried with single image with following command , anf get ..sucess
DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL,0xFFD8FFE000104A46494600010101006000600000FFDB004300080606070605080707070909080A0C140D0C0B0B0C1912130F141D1A1F1E1D1A1C1C20242E2720222C231C1C2837292C30313434341F27393D38323C2E333432FFDB0043010909090C0B0C180D0D1832211C213232323232323232323232323232323232323232323232323232323232323232323232323232323232323232323232323232FFC0001108009300C003012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00F7FA28A2800A28A0D00646B5ACD9E816D1DDDFB98ED9E6589A5C709BB3827DB3DFB66B4A374954323065619041C823B1AE13E2DCCABE1358187FAD93A7D01FF1AE17E0CF8C35A6D67FE11B9209AF74C0AC566033F63239C13FDD3D31D8918EF408F7AA29BBBA678A5CFB503168A4CD31E458D0BB90AA3A92700500494564E95AED8EB573791D8C9E725A388E4957EE97C64807BE38FCEB5A800A28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A43CD2E6A379022E5B8A37137632F59D0F4CD6E144D56D5278A324AABB103F1C1E7E95512EF4DD12D3ECFA658C3042BD1228C469F9015A13092EF214F15C77885DECF721356A36DCC9CEFB0BAA78DEF6DF3E4B429EDB73FCEB9B6F8AFAC5ACB978ED6741D5594AFEA2B9AD4EEDD98F35CBDEC8C4934DA434D9EE3E1EF8B3A16AF325A5F16D32EDCE144E7F76C7D9FA7E75C97C5D93C53A7C46EA391E5D1E4FF0096D0E40873D9C761D81E873DB35E3B7ADB95BA7D2BD07E16FC48974CB98BC39AFC9F69D22E4F950BCDF37904F014E7AA1F43D2A4B3D47E0F581B3F87967330FDE5E3BDC9FA1385FD003F8D77F556D2DE0B4B68ADEDA258A08902468830AAA3A003D3D2AD549485A28A2800A28A2800A28A2800A28A2800A28A2800A28A280133C534B80093C629DD4579CF8CAC7C69668F71A15FCD7D6C397B5E1664FF00748C6F1EDD68427B1DADA6AF657F7D79676F2EF9ECCA89D7046D2C323F4ACFD53530246556E14915C2FC2596F4E8FE23D66FD2459A7B9C319410C5913073919E32055CB9BD2E4FCD5A4519CD9D1C1AD8451C8E2B8CF156ABF68B82734B3DD98D7EF5729AB5D9772734DB25233AF65DD9AC2B871C8357A79B20E4D645C12ED815172D2332E86F7E3A55431F0548E0F6AD378BD6AAC8983D2819F4EFC2AD7DFC45E06B4927937DD5A936D313D495E84FD548AEE6BC4BE005D36CD76D09F94345281EE43027F20BF957B6D4B2905145140C28A28A0028A28A0028A28A0028A28A0028A28A000D215F7A5A28032F5A8CFF00615F88C7CDE431000F419AF2017DBB043706BDC1D43A1561907820D7CF5AEC6FE1DF10DD69B704AA236E89B1C321FBA7F2E3EA0D54591245ABFBF1BB19ED5CDDFDD073D6A4BCBA471B964073DF358CF2177EB4D89682B317E299E4D4F1A8A9582AAE4D21999247B413D3EB504763757F23C5676B35C48AA5D96242C428E49E3A62ACCCFE649850E79030832C7271851DD8E781D4FD335EDFAEA68BF0E3E1A5CC7A74456EAF61F2236947EFA691C632DDFE5049C7418C7140EC73DF0060FDFEBD71DB6C099C7FBE7FA57B8579AFC14D19B4DF038BB740AD7F31997DD000AA7F1DA4FE35E9552C6828A28A0614514500145145001451450014514500145145001451450015E7DF13FC18FE26D205E58479D52D14F96A3832A7529F5CF23F1F5AF41A4233401F2058D86A178F2A5B59DC4EF0FF00AD58A32CC98E0E40E473C74A9C0685CA4A8C8E3AABAED3F91FEB5EE7E2DF87D35FEA5FF090F862ECE9BAE2F2C549549CE3BE38CFB9041F4EF5C16ABE3AF11E9979041E2BF0B69B757B6FFEA27BBB6DAC0FAAB0CA9FF80E29DC9B77391538EB55EE673B4F5C115A3AFF008B755F14DD4736A33A08E2C98ADE14D91A67A9F73EE49AC776CA1C914C96765E05F184BA5E9D3D85A68BA699E326E64BEBB94F247DD3B71924678C11593A70D6FE2BF8E618350B979510E66741B52DE1079D83B6781EA73C935C5497A239E48D243B5BEFED3D87AFB5743F0F7C4B1F857C73697B753BC562EAF1DCB2E486420E381D70429FCA868699F57DADBC5696D15B408B1C312048D17A2803007E9562B96F0A78EFC3FE30130D2AECBCB072F14A851C2E786C1EA3DEBA9073525A168A28A0028A28A0028A28A0028A28A0028A28A0028A28A0028A28A0028A28A0069E9CD792FC6FF10AD8F87ED74582545BABF93738C02CB1AFA64704B6067D8D7A9DD5C2DBC259B19CE003DCD7CCFA95C8F1C7C70B08FCE77864BA48F0790B1C7F31007A100FE74D225BE87211CB3AFDE556FA714F92569176F96467DEB6752D38D96AD796CE815A299D718EDB8E3F4A804031D2A89397B9B421CC983C765EA6BA6BC934CB4F09D9594304736A1227993CCC39048F9554F6007E64531E24504BAF1EA05567B588FCC49C7B7423B50328E9FAC5E7876FA2D434EB9782F9788D978E0F5CFB7B77E2BE9EF863E3393C67E16FB5DD2A26A16F2186E420C027008603B6411C7A835F2CBDB99AE1E46FB99C007DABE97F833E1E4D1BC109785834FA93FDA1CA9C80A3851F90CFE349A1A67A45145152505145140051451400514514005145140051451400514514005068A43D280384F88FAD1D27C3B7D70AFB596230C7FEF3704FE033F9D78D7C10B07D47E268BD6048B3B5966627D5B083FF004327F0AEB3E2F5D5C5DC62D51599036F603D7FFD55B1F02BC3A34DF0D5C6BB29066D4DF11E3B448481F9B6EFD2ADEC671DEE735E36B059EEA7BB840F39246DC07F10CD711E6F1D6BB5D6EF835C5C1CF576CFBF27FC6B84BD6549894FBADFA50C445792EE4F6FA66BD5FC1DE00D3FC57F09ADC48AB6FA84934CF0DD851B94EF6001C755E3A578F5C3E5083E95F4B7C24DADF0C747C6305643FF00911A93291E1DAA7C32F1868F3BA3E9135DC609C4D6789030F5C0E47E38AF78F869A3DE685E05B0B1D406CB9F9E46889E630EC582FE00D75BB01EBCFD40A7018A572921D451452185145140051451400514514005145140051451400514514005238CA914B41A00F25BF9EDF5696E609954CD16720F7A93E1CEAD67A6E85ACE9525E46B7105C4B3C303B80DE5B229F941FF006B77E75C8F896F5B43F1E48240444D7261909E06D63D7F506B93F144517F6DDADCA30F3E2B84CB0EB82403F85599EC4BACDE157605B93D6B99925691B24F15B7E2288ACA1B9C74A67863C31A9F8BE5BF874936E65B28848C923905F39C05E3AF14D891872E592BE83F815A9A5DF8164B12D992C6E9D08F456F9C7EA5BF2AF9E8316046D21870C31D0F715EA3F01354FB2F8A752D2DDB0B796DE6A027AB237FF12C7F2A96544FA1A8A4CE696A4B0A28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A46E94B41A00F1EF8D1E1D12D826B31C67CB51E55D3019DA07DC73FCBF2AF13BF9655F20A4F14C59D773AB77CD7D8F2451CD1B472A2BA30C15619047B8AC47F04785E47DEFE1FD30B6739FB2A673F9534C968F06F14D88F2D594727A56A7C067F27C67ABDB9E3CDB00E0671F76403FF0066A7F8A154168978DAC56B23E1EEA4BE1FF893A74927CB0DD96B263E9BF1B7FF001E0B54F6263A30D4BC2E9078E7C456FB3114576EE831DA4C38FD1AA9784A71E1EF88BA4DD676442E444E4F657F94FF003AF50F1ADB25B78B6EA72003756F13E7D71B97FA0AF2BD660DD33C89C1EA08EC68E83EA7D543AD158DE14D54EB9E15D37526FF00593C0A64FF007C70DFA835B55058514514005145140051451400514514005145140051451400514514005145140051451401E03E3050BE21D4540C05B87C0F4F9ABCF75A91E1B88DE362AC8C8EA47660E3068A2AFA19F53D9BE29FCBAA696EBC33DBBEE3EB8C579B4FCC0D45142067B2FC2291DFC051066242CF305F61BC9FEA6BBDED45152CB5B0B451452185145140051451400514514005145140051451401FFFD9
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'd:\20090112115849813.bmp', 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken


and it is sucessfuly excuted and i mage was generated.

But i am getting one most problem...

when i use cursor loop for multiple images i am getting conversatrion error of varbinary to varchar..
like ..

DECLARE @SQLIMG VARCHAR(MAX)
DECLARE @IMG_PATH varbinary(MAX)
DECLARE @TIMESTAMP VARCHAR(MAX)
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 = replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','')
SET @SQLIMG = '
DECLARE @ObjectToken INT
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, ''d:\'+@TIMESTAMP+'.bmp'', 2
EXEC sp_OAMethod @ObjectToken, ''Close''
EXEC sp_OADestroy @ObjectToken
'
PRINT (@SQLIMG)
EXEC(@SQLIMG)
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
END
CLOSE IMGPATH
DEALLOCATE IMGPATH


Pls try with table with contain images..and pls help me to find the solution..

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 01/12/2009 :  03:21:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Something like this?
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



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 02/10/2009 :  16:50:40  Show Profile  Reply with Quote
I completely missed this but Peso is right. The root cause is you attempted to do some string manipulation inline with a procedure call. Can't do that in T-SQL. Peso's solution is correct in that he's formatting the variable before the call and uses that variable in the call.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 02/10/2009 :  17:14:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Thank you.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

jcuttitta
Starting Member

1 Posts

Posted - 02/23/2009 :  14:22:30  Show Profile  Reply with Quote
Peso, Thanks so much for the sample code.
Go to Top of Page

Ailey
Starting Member

1 Posts

Posted - 11/26/2010 :  17:52:24  Show Profile  Reply with Quote
How could I modify Peso's code so the file name (of the image to be save) could come from a column in that same table.

so in this case say I had something like this

SELECT csl_CompanyLogo, DesiredFileName from mlm_CSCompanySettingsLocalizations

thank you for your assistance.
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/26/2010 :  18:07:38  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
Wow..resurrected this one.

You can concatenate the two fields together such that the @IMGPATH in his sample would contain the desired file name as well.

To modify your code..

Select csl_CompanyLogo + '/' + DesiredFileName from mim_CSCCompanySettingsLocalizations

If the you need to reference one column for the file itself, and another which would have a different name, you would declare another variable and populate the desired file name in the code . Presuming the desired filename would ALSO have the path, this is the idea..


DECLARE @SQLIMG VARCHAR(MAX),
	@IMG_PATH VARBINARY(MAX),
	@TIMESTAMP VARCHAR(MAX),
	@ObjectToken INT,
	
	@FILENAME varbinary(MAX)---new variable here

DECLARE IMGPATH CURSOR FAST_FORWARD FOR 
		SELECT csl_CompanyLogo 
		      ,DesiredFileName ---column selected here
		from mlm_CSCompanySettingsLocalizations
		
OPEN IMGPATH 

FETCH NEXT FROM IMGPATH INTO @IMG_PATH ,@FILENAME ---new column in cursor with new variable

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, @FILENAME --new variable here
		EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
		EXEC sp_OAMethod @ObjectToken, 'Close'
		EXEC sp_OADestroy @ObjectToken

		FETCH NEXT FROM IMGPATH INTO @IMG_PATH ,@FILENAME --new variable here also
	END 

CLOSE IMGPATH
DEALLOCATE IMGPATH






Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000