How to Quote Database Names with Spaces Using bcp queryout

bcpsql serversql-server-2000

I'm attempting to use bcp on Microsoft SQL Server 2000 to export a couple of columns from a large database table.

The database names contain spaces. Here's an example that works that contains an export of the whole table:

bcp "database name with spaces.dbo.table name with spaces" out
    C:\database_name_with_underscores.csv -c -q -t "," 
    -S "server_address" -U username -P password -k

I'd like something along the lines of:

bcp "SELECT TOP 10
        [column name with spaces], 
        [another column name with spaces] 
    FROM [database name with spaces].dbo.[table name with spaces]" 
    queryout C:\database_name_with_underscores.csv -c -t "," -S "server_address" -k

…but I've not been able to get it to work using combinations of the -q flag and brackets [] or quotes ". I receive errors similar to:

SQLState = S0002, NativeError = 208 
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]
Invalid object name 'database name with spaces.dbo.table name with spaces'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]
Statement(s) could not be prepared.

Any help is appreciated.

Best Answer

The solution I settled on was to specify the database name using the -d flag.

bcp "SELECT char(34) + REPLACE([Code], '""""', '""""""""') + char(34),
    char(34) + REPLACE([Description], '""""', '""""""""') + char(34)
    FROM [Table Name]"
    queryout C:\Users\jwallace\out.csv -c -t ","
    -d "Database Name With Spaces" 
    -S "10.0.0.1,1848" -U username -P password -k
Related Question