Check file existence by command prompt in sql server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Check file existence by command prompt in sql...

Check file existence by command prompt in sql server

Rate Topic

Display Mode

Topic Options

Author

Message

Bhuvnesh

Posted Wednesday, November 18, 2009 5:07 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #820721

Nabha

Posted Wednesday, November 18, 2009 6:07 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 02, 2011 3:36 AM
Points: 579, Visits: 1,803

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.

---------------------------------------------------------------------------------

Post #820757

Lynn Pettis

Posted Wednesday, November 18, 2009 6:28 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:17 PM
Points: 21,832, Visits: 27,855

Check out the following thread, it may help.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #820766

Bhuvnesh

Posted Wednesday, November 18, 2009 6:35 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #820771

Nabha

Posted Wednesday, November 18, 2009 6:52 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 02, 2011 3:36 AM
Points: 579, Visits: 1,803

What if the file is already there? That command is for opening the file, no?

---------------------------------------------------------------------------------

Post #820785

Bhuvnesh

Posted Wednesday, November 18, 2009 9:42 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

my requirement is only to check the existence of file

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #821348

« Prev Topic | Next Topic »

Permissions