ansaurus

Question

Answer 1

+1 A:

You do not need the -q argument and the quotation marks. The documentation states the following about the q argument:

Use this option to specify a database, owner, table, or view name that contains a space or a single quotation mark. Enclose the entire three-part table or view name in quotation marks ("").

Your database name contains neither space nor single quotation mark.

Try this:

bcp [D001Test.Restore].dbo.GeneralComments in "<DataFile>" -c -t "|_|" -r "|+~+|"
-k -V80 -a33000 -E -STest

Bill 2010-02-18 08:18:03

I appreciate the answer. I will be testing it out. However, I still have the issue where the database name has a space and a period in the name. I've tentatively implemented a solution to use a temporary table with underscores replacing the periods, but its quite an ugly concept.

Kevin 2010-02-18 14:14:13

@Kevin If you have space and period in the database name then you need the quotation marks.bcp "[D001 Test.Restore].dbo.GeneralComments" in "<DataFile>" -c -t "|_|" -r "|+~+|" -k -V80 -a33000 -E -STest

Bill 2010-02-18 16:04:15

@Bill Unfortunately this doesn't work. If you use the quotes, the bcp process must be programmed to look for the periods, regardless of the brackets. So this: EXEC master..xp_cmdshell 'bcp "[D019Restore.Generic Test].dbo.GeneralComments" out "<datafile" -q -c -t "|_|" -r "|+~+|" -k -V80 -a33000 -E does not work. If I can't find a solution to this I'm pretty sure I'm going to have to do some hacky database renaming to get this to work. Also not the issue applies to both "in" and "out" bcp processes.

Kevin 2010-02-18 18:50:04

@Kevin Please note that I did not include -q argument in the argument list. Remove -q from your command and try again. Without -q it works for me.

Bill 2010-02-18 19:41:24

strange. I tried it without the -q argument and no quotes and just received an error message in the output file. I'll try it again. I forgot to mention, and this may be the kicker, this has to be successful on Sql 2000, 2005, and 2008.

Kevin 2010-02-18 21:12:23

@Kevin Remove only the -q argument, but do not touch the quotation marks. I triedbcp "[D001 Test.Restore].dbo.GeneralComments" in "<DataFile>" -c -t "|_|" -r "|+~+|" -k -V80 -a33000 -E -STeston SQL Server 2005 and it works.

Bill 2010-02-19 06:25:29

@Bill I appreciate your help on this. It looks as though you are correct, this does work on '05 and '08, but unfortunately it must be different in SQL 2000. I have tried pretty much every combination, and it still reads the period as the context delimiter, even inside the brackets. I get: "Cannot open database requested in login 'D001 Test'. Login Fails". This is with brackets and quotations, and no -q. Thanks for your help on this!

Kevin 2010-02-19 13:46:27

@Kevin Have you tried to use bcp.exe installed with SQL Server 2005 (C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe, File version: 2005.90.4035.0) to export/import data from/into SQL Server 2000? Try -V80 argument. I can not try it, because I have only SQL Server 2005 and 2008.

Bill 2010-02-19 14:15:53

@Bill, unfortunately, this process is running on customer's servers, so I really don't have the power to install a newer version of bcp on their system. The primary use of this feature is to allow them to backup a SQL 2008 db, and restore it on SQL 2000 (or 2005).

Kevin 2010-02-19 14:27:01

@Kevin I have no more idea. Thanks for accepting my answer.

Bill 2010-02-19 14:37:44

ansaurus

tags:

views:

answers:

BCP database with period in name

related questions