xp_write_file return values

[#1]

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?

[Former Member · Accepted solution]

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.

[MarkCulp]

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;