2 Questions about BCP

[Schaeffrcc]

I have a SP that updates 3 tables in SQL with data from approx 7 others. This works great and I have a .Bat file that runs BCP to export them out to a ~ delimited .CSV File. This also seems to work good but I have a problem.

The Dates in SQL look like MM/DD/YYYY or MM/DD/YYYY HH:MM:SS PM but when I run the Following DCP command I get dates in the format YYYY-MM-DD HH:MM:SSS and my import utility does not like that date format at all, it wants MM/DD/YYYY. I have read up on the CONVERT Command but do not know how to use it. I also do not get the idea of Format files but this seems like a way to solve this issue.

bcp "Database.dbo.Table" out "c:\sweeper\CaseFee.csv" -c -q -t~ -Server -T

My second Question is hopefully easy but can I run the BCP Statement as part of my SP and if so can someone give me an idea of the syntax to do it.

Thanks for your Help,

Thomas.

 

[NoCoolHandle]

Ok...

second q first..
BCP is a command line utilitiy.. therfore to run it in SQL from a proc you need to use XP_CmdShell.. I word of caution, xp_cmdshell exists in the master db, therfore you need to execute it as master.dbo.xp_cmdshell 'bcp etc.etc.etc.

1st question..
Yes internalily SQL uses yyyymmdd
to do the format you will need to use a sql statement..
e.g. Select [blue]convert(varchar(30),YourDateField,1) as datefield[/blue], nextField,NextField .. From Table

Have you thought about using a DTS job instead?

These can also be scheduled or executed using xp_cmdshell but are much easier to set up.

HTH

Rob