| Author |
Topic |
|
NiceSituation
Starting Member
22 Posts |
 Posted - 2007-11-13 : 18:07:09
|
| Hi all, I am trying to use BCP to periodically extract data from a number of views, store the data from each view in separate text files. For this, I am thinking of creating a batch file containing the BCP commands and a windows scheduled task or SQL Server Job (I don't know which one will work though) which executes the batch file.Right now my main problem is that I thought the BCP commands were working, but none of them is creating a file. This is the command I am using:bcp pubs.user.viewX outD:\interface\IMAGEtextfile.txt -c -r \n -t ; -SDATABASE -Ulogin -Ppasswhere D:\interface\IMAGE is the path on the server where I want to store the files.the only things changing are the viewX (each view), and the name of the text file for the output. I removed the space between "out" and the destination path because if not I would get an "unable to open host data file" error. Although a text file should be created at the destination path, none is created. I tried using quotes, double quotes, etc, but to no avail. Any suggestions?Also, every time I run this command, I get prompted to enter the field storage value, prefix-length and field terminator for each field. In my batch job, is there a way to avoid these steps while at the same time specifying a field and row delimiter (those are the ones I am most interested in, I want a semicolon as a field delimiter and a newline as row delimiter). Any thoughts about this problem?May today be good, and tomorrow better |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-13 : 18:16:28
|
| The space needs to exist between out and the path. So since you are getting an error there, you've got a path issue.bcp runs in the context of the client and not the database server (unlike BULK INSERT and xp_cmdshell). So wherever this bcp command is running is where this path needs to exist. So does D:\interface\ exist on the machine where this command is running?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
NiceSituation
Starting Member
22 Posts |
Posted - 2007-11-13 : 18:23:47
|
| Oh I see....thanks. For some reason I thought that BCP ran from the server side. So that is the problem. Besides logging to the remote server via telnet (I do not have physical access to it) and using the BCP commands there, is there another way to do what I am trying to do?May today be good, and tomorrow better |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-13 : 18:30:52
|
| You could schedule your commands to run in a SQL job or via Windows Scheduler on the database server. Those would then run from the server's perspective.You could also provide a UNC path to the server in your comamnd if you've got access to it:bcp pubs.user.viewX out \\server1\d$\interface\IMAGEtextfile.txt -c -r\n -t; -SDATABASE -Ulogin -PpassTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
NiceSituation
Starting Member
22 Posts |
Posted - 2007-11-13 : 18:40:52
|
| Sorry for my ignorance, but what does the "d$" do? And is it correct to have IMAGE and textfile.txt together or is there an "\" missing?May today be good, and tomorrow better |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-13 : 18:46:48
|
| d$ is an admin share for the D drive. The only way to connect to remote directories on Windows boxes is via shares. Please consult a Windows OS book/article on shares for more information.I have no idea what you want for the filename. I just assumed you wanted to name it IMAGEtextfile.txt. Is IMAGE instead a directory?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
NiceSituation
Starting Member
22 Posts |
Posted - 2007-11-13 : 18:56:49
|
| Ooops my mistake, it is supposed to be D:\interface\IMAGE\textfile.txt but I missed the "\", hhmm...I better be more careful with that...One final question (hopefully!). I just created one of those files (on my computer, not yet on the server), and I wanted to have the column names written on the first line of the file, separated with the same character delimiter I am using for all the rows. Is this possible in the bcp commands?May today be good, and tomorrow better |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-13 : 19:01:45
|
| It is possible, however it can be tricky. You'd have to make your view do the work as bcp can't do it for you. I wouldn't recommend it. If you really require it, I'd suggest using DTS instead of bcp.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
NiceSituation
Starting Member
22 Posts |
Posted - 2007-11-14 : 12:59:20
|
| I understand. The column names are not necessary, though desirable, so I do not really need to get them.Well, thank you very much for your help!May today be good, and tomorrow better |
|
|
|
NiceSituation
Starting Member
22 Posts |
Posted - 2007-11-14 : 17:58:01
|
| hhhmm....I am getting the same "unable to open BCP host data file" error. What I did was put the bcp commands in a SQL job (this time I am working in the server), where each step is defined as an Operating System Command (CmdExec), and have output files at "D:\interface\IMAGE\". When I look at "textfile.txt" (one of the output files) I see the error message. I changed "D:\" for "d$\" but got the same error message. Any thoughts?May today be good, and tomorrow betterEDIT: Never Mind, I found what the problem was. It seems that even when working in the server itself, the command must be"bcp Database.User.Tablename out \\SERVER\d$\Path\File -c -r\n -t; -Ulogin -Ppassword" and not"bcp Database.User.Tablename out D:\Path\File -c -r\n -t; -Ulogin -Ppassword" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-14 : 19:37:58
|
| That should not be the case. Something is wrong with your config if you require a UNC path for a local path.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
BCP command not working properly