The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

Can someone point out the error or my ways. I'm using xp_write_file to export BLOBs to TIFF images. When i run the Select query it works great, only does the first 110 rows, and will create more images if i page down...not to desirable for 170,000 images. So, I try to substitute CALL for SELECT and i get a syntax error near line 1 ',' Here is my select statement, why won't call work?

SELECT xp_write_file('c:inetpubwwwrootimagesimagesc02' || ImageID || '' || ImageDetailID || '.tiff', Image), CAST(ImageID AS VARCHAR) || '' || CAST(ImageDetailID AS VARCHAR) || '.tiff' FROM c04.imagedetail

asked 02 Sep '11, 19:04

jdilla5's gravatar image

jdilla5
31234
accept rate: 0%

Comment Text Removed

You could try using a for loop to output all of your files. A brief example would be:

BEGIN
DECLARE count INTEGER;

SET count = 0;

FOR acur AS acur CURSOR FOR
SELECT xp_write_file('d:\\cases\\cust_' || id || '.file',surname || ' ' || givenname) 
  AS my_ret FROM customers
DO
  SET count = count + 1;
END FOR;

MESSAGE 'Exported ' || count || ' records!' TO CLIENT;

END;
permanent link

answered 02 Sep '11, 19:29

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

Take out the extra fields referenced in your select, and limit it to JUST the xp_write_file procedure:

SELECT 
    xp_write_file('c:\inetpub\wwwroot\images\imagesc02\' || ImageID || ImageDetailID || '.tiff', Image) 
FROM 
    c04.imagedetail
permanent link

answered 02 Sep '11, 21:22

Calvin%20Allen's gravatar image

Calvin Allen
1.5k232638
accept rate: 25%

I would try doing so:

select ... into #tmp;
select * from #tmp;
permanent link

answered 12 Sep '11, 01:18

Arthoor's gravatar image

Arthoor
1.1k264056
accept rate: 0%

edited 12 Sep '11, 01:24

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15

question asked: 02 Sep '11, 19:04

question was seen: 1,104 times

last updated: 12 Sep '11, 01:24