Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NiceSituation
Starting Member
22 Posts |
 Posted - 2007-11-09 : 13:46:04
|
| Hello everyone. I created a job which will execute a couple of SQL statements and the results of each statement will output to a file. What I would like to know is: 1) Is the output file a text file? I checked a file created by a job and though it seemed to be a flat text file, there were some weird characters here and there.2) If the output file is indeed a flat file, is there any way to give it a specific format? I'm thinking about the formatting that you can give these files when you use the "Export Data" task (First row has column names, character used as a delimiter, etc.)I am using SQL Enterprise Manager, by the way. Any suggestions?May today be good, and tomorrow better |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-11-09 : 14:10:06
|
| You can use BCP to export the results to a text file and use the format file to delimit it properly. Have a look into books online for BCP utility.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
NiceSituation
Starting Member
22 Posts |
Posted - 2007-11-09 : 14:35:28
|
| Thanks for the info. However, I do have a problem I forgot to mention. The database I am accessing is a remote database to which I have no telnet access (which I believe, but correct me if I am wrong, that is the only way of using BCP remotely) because the server is configured to refuse telnet connections, I was only given access Enterprise Manager. Any thoughts?May today be good, and tomorrow better |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-10 : 00:29:03
|
| BCP doesn't use telnet, it has options for server name and so. Find all options with 'bcp /?' in dos prompt. |
|
|
|
NiceSituation
Starting Member
22 Posts |
Posted - 2007-11-12 : 15:37:31
|
| I see. And can you use a batch file to run the different commands automatically? I'm thinking about creating a scheduled task on windows which runs a batch file containing bcp commands in order to extract information from a frequently updated database and store the information on different files. Is this possible using a scheduled task and a batch file, or is there any way to do it?May today be good, and tomorrow better |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-12 : 23:05:01
|
| Yes you can put multiple commands in batch file then run the batch as sql job.. |
|
|
|
NiceSituation
Starting Member
22 Posts |
Posted - 2007-11-13 : 15:03:17
|
| Thanks, then bcp will fit my needs. I tried a bcp test command a whie ago and it seemed to work correctly, so I will be doing the batch file soon. One problem though, after writing the command, the system asked me for file storage type, prefix length and field terminator for each field. Would I have to put those parameters into the batch file after the command, or is there any way to define them inside the command (I know that field delimiter is define with -t, but for some reason I was asked for this value for each field, and I am not sure about the prefix length and storage type) or even somehow keep the thing from asking me about these values (maybe taking some default values or taking them from a file elsewhere, although in this case, correct me if I am wrong, I would have to write a format file for each table used in the operation)?May today be good, and tomorrow better |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-13 : 23:01:08
|
| Yes you need format file for it. |
|
|
|
|
|
|
|
|
Job output files