Question Re: xp_write_file return values in Technology Q&A

xp_write_file return values

former_SQLA_member1694875 — Tue, 09 Aug 2011 01:25:41 GMT

Hello again, Can anybody tell me what the return values of system function xp_write_file() are (in SA12)? Documentation only says that "It returns 0 if successful, and non-zero if it fails." So what are those non-zero values and their meanings?

Re: xp_write_file return values

MarkCulp — Tue, 09 Aug 2011 08:51:05 GMT

The only non-zero values that are returned from the xp_write_file() function are the values -1 and +1. The value "1" indicates that the write failed. (Sorry, no additional information at this time is available).

Just in case the return value ever changes in the future I would suggest that you write your code as:

declare @rv int;
set @rv = xp_write_file( ... );
if @rv != 0 then
    -- handle error condition
else
    -- write succeeded.
end if;

Re: xp_write_file return values

Former Member — Tue, 09 Aug 2011 09:07:21 GMT

I don't believe the lack of documentation is intentional. Here's a short summary:

if you do not have permission to execute xp_write_file(), the procedure returns -1 and an SQL error is generated (SQLCODE -121).
If there is a problem opening the file (an error was returned from the OS OPEN() call) then the procedure returns +1.