January 8, 2009 at 10:36 pm
How to export image in to folder from image field of table.
i want to do it only from SQL script.
Is there any way?
January 9, 2009 at 6:36 am
You can run an xp_cmdshell with textcopy.exe
January 9, 2009 at 9:57 pm
Nicholas Cain (1/9/2009)
You can run an xp_cmdshell with textcopy.exe
i want to create image from that.......Can u explain in brief with example...
January 10, 2009 at 5:32 pm
I didn't realise that textcopy doesn't come with 2005, that's what happens when you get stuck in 2000 for many years.
You can pull textcopy off 2000 media if you have it, run it in dos to check the syntax and just call that through the xp_cmdshell.
There's a much more elegant solution using CLR that's worth looking at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101754
January 11, 2009 at 11:46 pm
I have found command to export image by SQL command
:
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..
January 12, 2009 at 5:52 am
EXEC sp_OAMethod @ObjectToken, ''Write'', NULL, '+@IMG_PATH+'
Just curious, have you tried removing the quotes and just having (not sure this will work)
EXEC sp_OAMethod @ObjectToken, ''Write'', NULL, @IMG_PATH
January 12, 2009 at 7:05 am
good paresh good
Raj Acharya
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply