Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

output image from tsql MS 2000

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)
SELECT BulkColumn

now for the writing out bit i have tried the below but i'm unable to open and preview my picture??

trial 1

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 ""

trail 2
exec master..xp_cmdshell 'bcp "select col1 from mydb.dbo.tImageTest where id = 1" queryout "C:\outpic.jpg" -T -n -S myserver '

trial 3

Declare @ObjectToken int
,@SOURCEPATH varbinary(8000)
,@DESTPATH varchar(8000)

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?
1 Solution
Ryan McCauleyDatabase and Reporting ManagerCommented:
Your last example looks like it should work, but is your image actually smaller than 8000 bytes? If it's larger, then using that variable would truncate the file and you'd be unable to view it. If you're storing the image in the SQL database using VARBINARY(8000) and the images you're trying to store are larger than about 7.5KB, you've got a problem.

In SQL 2000, you can use the IMAGE data type for storage, which has a maximum length of 2GB (despite the name, it's not just for images - it's the large binary type, the equivilent of TEXT but for binary rather than string data).

This may not be an option for you, but I'd strongly encourage you to use a new version of SQL server, which makes interacting with types like this much, much easier - you can use VARBINARY(MAX), which has some efficiency improvements over IMAGE, and the BCP method works well in 2005+ (it should work in 2000 as well, though maybe it's the field size problem you're seeing here, not bad data).
LBGUC04Author Commented:
I actually managed to get this working from sql server 2005 yesterday. This wasnt the ideal version i wanted to use at this time but as it works i am going to have to migrate my work accross sooner rather than later. I also managed to work out how to convert text to jpg using c# which i will convert into a web service.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now