I was attempting to use BCP today via xp_cmdshell. I have never done anything with BCP before, so it was very enlightening. However, I ran across a problem that maybe someone could help explain to me a little more.
I am using the "queryout" option, and when I run it, the error I get is that you "can't skip fields except for on inserts" or something like that.
The reason I was trying to use bcp is the ability to dynamically generate a filename, i.e. filename = 04182004 (the date). Because in the file name argument, I can use a variable. Make sense?
Since I apparently can't ignore fields, I am thinking of taking all of the information I need daily out, and into a seperate table, then I can use the xp_cmdshell to run a bcp that creates a file with the date as a filename, and I won't be ignoring any fields because I have just put the information I need in the new table. Am I making sense? Does this sound like an appropriate thing to do?
Basically I have sales data that I have to export to a file. The file has to be named as the date with no extension like: 04192004 . The query is not a problem, as I've been doing this manually through DTS for some time. However, I need to automate this process. I am not aware of a way to script the filename in DTS. So, I've discovered BCP, and the ability to use xp_cmdshell to execute an BCP with a variable filename (@filename = getdate()-1) etc. .. So I end up with something like: