March 19, 2012 at 12:31 pm
I am trying to create a format file for BCP. Here is my statement:
bcp [DataStore].[dbo].[HMS] format nul -f -n H:\HMS.fmt
I get the following error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
I have tried several format for the destination, but no success.
Any suggestions???
March 19, 2012 at 2:02 pm
Try this
exec master..xp_cmdshell 'bcp "database.dbo.tablename" in "C:\location\file.bcp" -E -n -T -S'
EG
exec master..xp_cmdshell 'bcp "adventureworks.dbo.employee" in "C:\temp\employeer.bcp" -E -n -T -S'
***The first step is always the hardest *******
March 22, 2012 at 7:29 am
I'm not sure if this is the intended solution, but after many tries, I stumbled on this solution.
The original format, copied from MS was
bcp [DataStore].[dbo].[HMS] format nul -f -n H:\HMS.fmt
I found that if I changed the switch order to:
bcp [DataStore].[dbo].[HMS] format nul -n -f H:\HMS.fmt
(moved the -f switch to just in front of the output file) the thing worked.
It appears that BCP is order sensitive.
March 23, 2012 at 10:19 am
Actually bcp is not 'order sensitive' in its parameters.
When you specify -f it is expecting the format_file name immediately afterwards.
Below is an excerpt of the parameters from: http://msdn.microsoft.com/en-us/library/ms162802.aspx
bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-m max_errors] [-f format_file] [-x] [-e err_file]
[-F first_row] [-L last_row] [-b batch_size]
[-d database_name] [-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )]
[-q] [-C { ACP | OEM | RAW | code_page } ] [-t field_term]
[-r row_term] [-i input_file] [-o output_file] [-a packet_size]
[-S [server_name[\instance_name]]] [-U login_id] [-P password]
[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply