November 18, 2009 at 5:07 am
hi
my requirement is i need to check any txt file on drive
----------------------------------------------------------
for example , if a file "output.txt" exists at "c:\"
Query :
declare @command varchar(100)
SET @command = 'dir c:\output.txt'
EXEC [master].[dbo].[xp_cmdshell] @command
Output is below on resulkt pane
Volume in drive C has no label.
Volume Serial Number is 5CE8-8857
NULL
Directory of c:\
NULL
File Not Found
NULL
----------------------------------------------------------
but this way i cant update status in controller sql table.
as i need only one value (lets say 1\0 ) instead of output getting above
please help
Bhuvnesh
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 18, 2009 at 6:07 am
one way you can do it is using CLR procedure/function. Check the file existence using .net code (vb/c#) and return 1/0 based on ur check there.
---------------------------------------------------------------------------------
November 18, 2009 at 6:35 am
Thanks to all you put their efforts , i got the resolution of it
below :
declare @nRetVal int
SET @nRetVal = ''
SET @filename = @share_unc + '\' + @filename
EXEC @nRetVal = master.dbo.xp_cmdshell @filename ,no_output
IF @nRetVal <> 0
exec CVII_PROD.[dbo].[up_cvii_DataDump_SETStatus] @QueueID, 63 -- Xcopy File Error
PRINT @filename
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 18, 2009 at 6:52 am
What if the file is already there? That command is for opening the file, no?
---------------------------------------------------------------------------------
November 18, 2009 at 9:42 pm
my requirement is only to check the existence of file
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply