xp_write_file strange behavior export blob fails on call works on select

[Former Member]

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

[Former Member]

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

[Former Member]

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;

[former_SQLA_member1694875]

I would try doing so:

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