output image from tsql MS 2000

Posted on 2012-09-11
Last Modified: 2012-09-13
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?
Question by:LBGUC04
    LVL 28

    Accepted Solution

    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).

    Author Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now