Executing xp_cmdshell directory from stored procedure

Question

Executing xp_cmdshell directory from stored procedure

ed.nettestad

SSC-Addicted

Points: 480
More actions
November 5, 2007 at 11:42 am

#183598

I want to test for the existance of an ASCII text file in a stored procedure. If the file exists I am going to terminate the process until the existing file is processed by another application.
This is on SQL 2005
I have set up a test and it works fine when I run it "standalone" from SQL Query Analyzer, but when I incorporate it into my stored procedure it fails.
I've included the script that works (A), the script that does NOT work (B)and the output from "B" as (C) below.
Thanks for any help, Ed
(A)
DECLARE @result int, @fileerror varchar(1000), @homeofficeadm varchar(100),@IAEFile nvarchar(100),@dir varchar(200)
set @IAEFile = 'TO_AE_RO.txt'
set @dir = 'dir \\shpfile01\public\Attendance_System\From_HH\'+@IAEFile
print @dir
EXEC @result = xp_cmdshell @dir
IF (@result = 0)
goto fileexists
ELSE
PRINT 'File Does Not Exist'
goto contproc
fileexists: print 'File Exists'
set @fileerror = 'The ASCII file P:\Attendance_System\From_HH\'+@IAEFile+' exists. This means the Attendance Enterprise import did not run.'
set @homeofficeadm = 'enettestad@shpseniorliving.com'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail',
@recipients= @homeofficeadm,
@subject = 'Carevoyant Time Clock Error',
@body = @fileerror,
@importance = 'High'
goto endproc
contproc:
declare @cmd1 varchar(1000), @cmd2 varchar(1000)
set @cmd1 = 'bcp "select AE_Badge,AE_Station,AE_date,AE_time,AE_Transaction_Type,'
set @cmd1 = @cmd1+'AE_Location,AE_Department,AE_Cost_Center,AE_Position '
set @cmd1 = @cmd1+'from regency.dbo.hhinfosystimeclock2" '
set @cmd1 = @cmd1+' queryout \\shpfile01\public\Attendance_System\From_HH\TO_AE_RO.txt '
set @cmd1 = @cmd1+'-c -t , -T '
execute xp_cmdshell @cmd1
endproc:
(B)
alter procedure usp_HH_Infosys_Timeclock
(@Idb nvarchar(30),
@IAEFile nvarchar(30),
@Station nvarchar(1),
@Location nvarchar(1),
@HHlocaluser nvarchar(30),
@HHhomeofficeadm nvarchar(30))
/*
@Idb = Infosys data base
@IAEFile= Infosys ASCII export file name to Attendance Enterprise
@Station= Attendance Enterprise (AE) Station Code
@Location= AE Location Code
@HHlocaluser= HH site user group
@HHhomeofficeadm= HH home office administrator (IT)
*/
as
declare @cmd0 nvarchar(4000),@cmd1 nvarchar(4000), @cmd2 nvarchar(4000), @cmd3 nvarchar(4000), @cmd4 nvarchar(4000), @cmd5 nvarchar(4000), @cmd6 nvarchar(4000)
Begin Transaction
Set @cmd0 = '
declare @homeofficeadm nvarchar(100),@IAETextFile nvarchar(100),@result int, @fileerror varchar(1000),@dir varchar(300)
set @homeofficeadm = '''+@HHhomeofficeadm+'@SHPSeniorLiving.com''
set @IAETextFile = ''\\shpfile01\public\A-Test\'+@IAEFile+'''
set @dir = ''dir ''+@IAETextfile
print @dir
EXEC @result = xp_cmdshell @dir
print @result
IF (@result = 0)
goto fileexists
ELSE
PRINT ''File Does Not Exist''
goto contproc
fileexists: print ''File Exists''
set @fileerror = ''The ASCII file P:\Attendance_System\From_HH\''+@IAETextFile+'' exists. This means the Attendance Enterprise import did not run.''
exec msdb.dbo.sp_send_dbmail
@profile_name = ''SQL Mail'',
@recipients= @homeofficeadm,
@subject = ''Carevoyant Time Clock Error'',
@body = @fileerror,
@importance = ''High''
contproc:
'
(C)
dir \\shpfile01\public\A-Test\To-AE-RO.txt
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Volume in drive \\shpfile01\public is Data
Volume Serial Number is 5018-90EF
NULL
Directory of \\shpfile01\public\A-Test
NULL
File Not Found
NULL
(7 row(s) affected)
(7 row(s) affected)
1
File Does Not Exist

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

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

The most common cause for this type of failure is that the SQL Server service account, which runs the xp when it runs from a proc, doesn't have the proper permissions to run the command or access the file directory.

And then again, I might be wrong ...
David Webb