xp_fileexist workd in Query window but doesnt in stored procedure
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
Software/Hardware used:
Software/Hardware used:
ASKED:
June 26, 2008 1:59 PM
UPDATED:
September 7, 2010 7:16 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.