Question

  Asked: Jun 26 2008   1:59 PM GMT
  Asked by: Kendo


xp_fileexist workd in Query window but doesnt in stored procedure


Stored procedures, SQL error messages, SQL Query, SQL Server

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:\Play\Warrenty.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:\Play\Warrenty.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

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window

Answer Wiki (Improve, edit or add to this answer)


 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0



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.
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Database, SQL Server and Development.

Looking for relevant Database Whitepapers? Visit the SearchOracle.com Research Library.


Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Mrdenny  |   Sep 9 2008  10:20AM GMT

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.