Monday, March 12, 2012

Problem with EXEC xp_cmdshell in UDF's

Hi All

Some what of a noob to UDF's and the EXEC statement...
I am confused on the following code....

CREATE FUNCTION DBO.DoesFileExist(@.FileLocation NVARCHAR(500))
RETURNS INT AS
BEGIN
DECLARE @.return AS INT
DECLARE @.strCmd AS NVARCHAR(1050)
SET @.strCmd = 'IF EXIST "' + @.FileLocation + '" ECHO 0
ELSE ECHO 1'
EXEC @.return = XP_CMDSHELL @.strCmd
RETURN @.return
END

GO

DECLARE @.FileLocation AS NVARCHAR(500)
SET @.FileLocation = 'C:\to\log.txt'

DECLARE @.return AS INT
DECLARE @.strCmd AS NVARCHAR(1050)
SET @.strCmd = 'IF EXIST "' + @.FileLocation + '" ECHO 0
ELSE ECHO 1'
EXEC @.return = XP_CMDSHELL @.strCmd

SELECT @.return AS Exp1

SELECT DBO.DoesFileExist(@.FileLocation) AS Exp2


RETURNS:
Exp1 = 0
Exp2 = 1

WHY IS THAT? Shouldn't Exp2 also be equal to 0? It's running the same function...

Any help would be much appreciated
Thanks
Mike
IF EXIST c:\MyFile.txt (ECHO 0) ELSE (ECHO 1)

No comments:

Post a Comment