INSERT INTO EXEC eating ERROR in SQL Server 2000

Question

INSERT INTO EXEC eating ERROR in SQL Server 2000

Greg Larsen

SSC-Insane

Points: 20961
More actions
February 26, 2004 at 10:36 am

#161511

When the following code is run on my SQL SERVER 2000 machine the error message returned from xp_getfiledetails is not displayed. But when I run this code from a SQL SERVER 7.0 machine the error is displayed. Any one know why the differenence? Did someone thing change that would effect error handling in SQL Server 2000? Is there a server setting, or connection setting to make the error reappear in SQL Server 2000? Just in case you are wondering here is the error message that gets displayed:
xp_getfiledetails() returned error 2, 'The system cannot find the file specified.'
------------------------------------
set nocount on
Create table #size
(Alternate_Name char(20),
Size char(20),
Creation_Date char(20),
Creation_Time char(20),
Last_Written_Date char(20),
Last_Written_Time char(20),
Last_Accessed_Date char(20),
Last_Accessed_Time char(20),
Attributes char(20)
)
Insert Into #size EXEC Master..xp_getfiledetails 'c:\bogus_file.txt'
drop table #size
------------------------------------
Also just to let you know when I issue
EXEC Master..xp_getfiledetails 'c:\bogus_file.txt'
By itself I do get an error in SQL SERVER 2000.
The phrase "do not use undocumented XPs" comes to mind!!!!
Gregory A. Larsen, MVP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736544 More actions · Answer 1

Just confirmed this on my SQL2K, SP3a machine. Not sure why the error isn't displayed, but I think your phrase might ring true here.

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

Steve - Thank you for confirming that you get similar results.

If anyone is interested I did code around this problem by capturing the return code for the XP. Here is the code:

set nocount on

Create table #size

(Alternate_Name char(20),

Size char(20),

Creation_Date char(20),

Creation_Time char(20),

Last_Written_Date char(20),

Last_Written_Time char(20),

Last_Accessed_Date char(20),

Last_Accessed_Time char(20),

Attributes char(20)

)

declare @errcode int

Insert Into #size EXEC @errcode=Master..xp_getfiledetails 'c:\bogus_file.txt'

If @errcode = 1

raiserror('xp_getfiledetails got an error processing request. Most likely file c:\bogus_file.txt does not exist.' ,16,1)

drop table #size

Gregory A. Larsen, MVP

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 3

You are both right, but if you use a variable to catch the status you can confirm the error.

Try;

DECLARE @status AS INT

Insert Into #size EXEC @status = Master..xp_getfiledetails 'c:\bogus_file.txt'

PRINT @status

If the file exists, it will save the result in you temp table and return a status of 0. But if it doesn't exists it won't return any result, but a status on 1.