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.3k355266
accept rate: 11%

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:

×18

question asked: 02 Sep '11, 19:04

question was seen: 2,742 times

last updated: 12 Sep '11, 01:24