Any way to check if a log file (.txt) already exists before BCPing a file?

I want to have one log file for each task ran within a job. There are several tasks for each job that may be handled by different people so I would like to have a running log file on the network that can be checked by supervisors. I know how to creat the log file:

master..xp_cmdshell bcp "SELECT * FROM ##logFile" queryout "c:\log.txt"

Problem is next time a task is run, the original text file will get written over by the new one. I came up with a solution for that by using bcp to export a temporary log file, then append that file to my existing log file, then kill the temporary file and that works fine:

master..xp_cmdshell copy log_temp.txt + log.txt

The problem is the second command obviously fails if the main log file does not exist.

Ideally I would like to check to see if the log file exists, if not create it, if it does exist, append to it.

Having a master log table stored in SQL has been suggested and shot down. They want one text file for job ran.

Any help would be greatly appreciated.

Thanks.

Tim

Write VBScript code (for example) and run this in a SQLAgent job. Don't use xp_cmdshell for these sort of things. It is less flexible and a security vulnerability. It takes few lines of code in VBScript to check for file. Or you can write a CMD script and run it in a SQLAgent job.

Any SQL wizard can help? Reformat the input file and transfer into SQL server

I am trying to transfer 200 txt files into SQL server by using query analyzer.
The command is 'Bulk insert [tableName] from 'path\filename.txt'
However, I need to read and modifiy the txt file.
I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily.

Thank you for the help in advance!

Here is the raw data layout, which is comma delimited.
BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990
Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005
Fq D Fq D Fq D Fq D
Date R P M E D Date R P M E D Date R P M E D Date R P M E D
1/1/90 1 2 3 4 5 1/1/90 2 3 4 5 6 1/1/90 3 4 5 6 7 1/1/90 4 5 6 7 8
2 3 4 5 6 1 2 3 4 5 3 4 5 6 7 6 7 8 9 1
1/1/05 ..... 1/1/05 ... 1/1/05 .... 1/1/05 ....

This is the desired output after load into the table, which is tacking each repeating block on top of each other.
Date R P M E D
1/1/90 1 2 3 4 5
2 3 4 5 6
1/1/05 .....
1/1/90 2 3 4 5 6
2 3 4 5 6
1/1/05 .....
1/1/90 3 4 5 6 7
3 4 5 6 7
1/1/05 .....
1/1/90 4 5 6 7 8
6 7 8 9 1
1/1/05 ....."I am trying to transfer 200 txt files into SQL server by using query analyzer."
--DTS might be more appropriate.

"I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily."
--Faith is a powerful thing.

"Here is the raw data layout, which is comma delimited."
--What you posted is not comma delimited.

"This is the desired output after load into the table, which is tacking each repeating block on top of each other."
--You are going to need to load this data into a staging table and normalize it before loading into your production tables. The process will be complex, involving several passes through the data.

If at all possible, try to get your source data in a better format. Practically any other format would be preferable to what you posted.|||Blindman,
Thank you for your reply.
You are right... I forgot to put "," in my sample file layout.
I am using another source provider to request time series in excel. This is the most efficient way I can utilize excel ability (256 columns and over 65,000 rows). That's why the raw data layout looks wired. However, I have to stick to it.

I was thinking to load these files into a table to normalize but I am not sure if I know SQL well enough to say this is the best solution. I think I got the answer from you.

What is staging db. I assume it is one of defualt DB in in enterprise manager, however, I did not see it. Or this is the name you gave?

Thank you again for the help.
Shiparsons|||Not "Staging DB". "Staging TABLE."

A staging table is basically an table that has the same structure as your input data, with additional columns added as needed to keep track of records as they are being processed. I always add an "Imported" column that defaults to getdate(), and an ImportErrors column that I populate as necessary during processing.

Your staging table should have no Primary Keys or constraints (unless you add a surrogate PKey for processing...), so that your import process never fails because the data does not match what is expected.

Once the data is in the staging table you cleans it and make sure it satisfies all the business rules required by your production tables. Then you make as many passes through the staging table as necessary to update the various production tables it feeds, starting with the top-level tables.|||Thank you for the explanation.
What datatype I should use when I create my staging table? I assume this is nonconstraints type since my raw data contains text, datetime, and float.

Thank you,
Qing|||You should try to match the datatype to the type of the data being entered, though some people just make all staging table columns varchar by default. I don't do this, as a rule, but you may have no other choice since your import file is actually a mix of different layouts. String fields are the only column types that will accept any input type.|||Blindman,
Thank you for the help.

I will try.

shiparsons