Stored Proc on name of file

Question

Post reply

Stored Proc on name of file

kyle.doouss

SSCommitted

Points: 1513
More actions
December 9, 2011 at 2:05 am

#253272

I am creating stored procedures which down load files, rename files and import files. This is going to be running on a schedule.
The file the script is picking up may not always be there becuase they are files coming from the web. Therefore if the job runs and there is not a file there you get an error. Is there a way to only run if there is a file with the requested name.
If I have not been clear enough please ask and I will try to explain further

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

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

Pradyothana Shastry SSCertifiable Points: 5211 More actions · Answer 1

create one batch file which checks for the required files, then involve this if files exists.

- Pradyothana http://www.msqlserver.com

kyle.doouss SSCommitted Points: 1513 More actions · Answer 2

I am thinking of using the following. Could you show me what you would do to look at the required files.

USE HBSLIVE

GO

CREATE TABLE CSVTest

(ID INT,

FirstName VARCHAR(40),

LastName VARCHAR(40),

BirthDate SMALLDATETIME)

GO

BULK

INSERT CSVTest

FROM 'c:\csvtest.txt'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO

--Check the content of the table.

SELECT *

FROM CSVTest

GO

--Drop the table to clean up database.

SELECT *

FROM CSVTest

GO

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

I would use a TRY ... CATCH routine to check for the FILE EXISTS condition.

http://msdn.microsoft.com/en-us/library/ms175976.aspx

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.