shamen


I'm trying to import some data from a data base table to a flat file. But I get a error message when I run BCP command in coomand line.

My bcp command is as follows.

C:\Documents and Settings\shamen>bcp databasename.tablename out -c-f flatfile.txt -T MSSQLSERVER

When I run this, I get " an error has occired when connecting to the server. This faillure may be caused by default SQL server does not allow remote connection.

Then I Changed it as follows.

C:\Documents and Settings\shamen>bcp databasename.tablename out flatfile.txt -T-c

Then I get " Unknow argument ' BCPfile.txt' on command line "

Does any tell me how to fix this

Thanks





Re: copy data from a DB to a flat file using bcp

MAGrimsley


Shamen,

In the SQL Management Studio, RightClick the SQL server instance, Choose
Properties, and under Select a Page list, click Connections. Now you should
see a checkbox labelled "Allow Remote Connections to This Server". Make sure
it is checked. Also make sure that the correct protocols are enabled in the SQL Server Configuratino Manager.

Michael







Re: copy data from a DB to a flat file using bcp

shamen

Hi,

I checked it and it is checked.

Now I Changed the command line as follows.

C:\Documents and Settings\shamen>bcp databasename.tablename out flatfile.txt -T-c

Then I get " Unknow argument ' BCPfile.txt' on command line "







Re: copy data from a DB to a flat file using bcp

MAGrimsley

Shamen,

Double check the Surface Area Configuration using the following

Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.

On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.

On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.

On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.






Re: copy data from a DB to a flat file using bcp

shamen

Now I used this one.

C:\Documents and Settings\shamen>bcp databasename.tablename out flatfile.txt -T

and now it promps me following questions.

Enter prefix lenght for filed (first field name) ;

Enter field terminator;

What are those Is Prefix length column width chara lenght for first field is 10. But available values given here are from 1 to 8.






Re: copy data from a DB to a flat file using bcp

MAGrimsley

Shamen,

Add the lower case c to the statement (bcp databasename.tablename out flatfile.txt -T -c). The -c tells the utility to perform the operation using a character data type. Also you don't necessary have to use a .txt extenstion for the output file. I use a .bcp extenstion, this way when I see it on my hard drive I know that it is a bcp file and not a text file. When you are copying large amounts of data to a text file and go to open it, if you use notepad, it can hang if it is a large file.

Michael






Re: copy data from a DB to a flat file using bcp

shamen

Thanks Michael,

I need to keep the data type as it is. So I hope I can use -n. But Again When I add it , I get a error message. Do I have to create a destination file before I copy it or does it automatically create a file

Thanks






Re: copy data from a DB to a flat file using bcp

Redbaron71

If I am not mistaken, the file will be created when the script is run. make sure that the Login that runs the SQL Server Service has rights to write to the particular directory (Drive)





Re: copy data from a DB to a flat file using bcp

MAGrimsley

Shamen,

Yes you can use the -n, remember that the native format is recommended when you bulk transfer data between multiple instances of Microsoft SQL Server using a data file that does not contain any extended/double-byte character set (DBCS) characters. Whether you use -c or -n is up to you. As far as creating the destination file, no, bcp will create the file if it does not exist. Is the error message the same as your post above

Michael






Re: copy data from a DB to a flat file using bcp

shamen

Thanks Micheal and Red

I gave the full path of the destination file within quotation marks.

Now It works

Thanks