xp_fileexist workd in Query window but doesnt in stored procedure

5 pts.
Tags:
SQL error messages
SQL Query
SQL Server
Stored Procedures
Hello, I am running on SQL server on my local PC . I am singed onto the PC using a network user. This probelm happens if the SQL server service runs as either Local system or network service. In the server management studio if I go to a query window and enter Declare @result int exec xp_fileexist 'C:PlayWarrenty.txt' , @result output PRINT @result it works fine and finds the file. If I move it into a stored procedure something like. ALTER PROCEDURE [dbo].[FileExistsCheck] ( @file text ) AS DECLARE @fe int EXEC master.dbo.xp_fileexist @file , @fe OUTPUT RETURN @fe and execute it like this from an SQL Query window DECLARE @RC int EXECUTE @RC = [dbo].[FileExistsCheck] 'C:PlayWarrenty.txt' I get Msg 22027, Level 15, State 1, Line 0 Usage: EXECUTE xp_fileexist <filename> [, <file_exists INT> OUTPUT] The 'FileExistsCheck' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. Any Ideas?? thanks Martin
ASKED: June 26, 2008  1:59 PM
UPDATED: September 7, 2010  7:16 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

The local account which the SQL Server is running under probably doesn’t have access to the file you are looking at. Change the SQL Server to run under a domain account, or local user defined user which has rights to the file.

The problem is that the parameter is declared as TEXT which isn’t valid for xp_fileexist. Same with varchar(max). Make it varchar(2000) and it will work.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following