output image from tsql MS 2000
Posted on 2012-09-11
I have been trying to load and export an image. The long term goal is to dynamically create my own image in sql server and output the image to embed into a html email. The reason for the image is that blackberrys dont display html tables well and wrap all the columns (I have looked into many possibilities on how to rewrite the html)
Anyway so i firstly want to be able to load and then write a jpg using sql 2000 and then i can take the next step of looking at the binary to edit it to include my text in the image.
I have googled loads and apparently loading the image is quite easy and i have done this myself using the below
INSERT dbo.tImageTest (col1)
FROM OPENROWSET (BULK 'C:\MyPic.jpg', SINGLE_BLOB) AS X
now for the writing out bit i have tried the below but i'm unable to open and preview my picture??
exec master..xp_cmdshell 'bcp "select col1 from mydb.dbo.tImageTest where id = 1" queryout "C:\outpic.jpg" -T -fC:\\imageformat.fmt -S myserver '
with format file
1 SQLIMAGE 0 0 "" 1 Image ""
exec master..xp_cmdshell 'bcp "select col1 from mydb.dbo.tImageTest where id = 1" queryout "C:\outpic.jpg" -T -n -S myserver '
Declare @ObjectToken int
select @SOURCEPATH = col1 from mydb.dbo.tImageTest where id = 2
select @DESTPATH = 'C:\outpic.jpg'
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @SOURCEPATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @DESTPATH, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
None of these have worked as i cant preview the picture and the file size is much smaller that the original
Can you help?