BCP output path: spaces allowed? 1

[LNBruno]

The following code works as long as the output path doesn't have any spaces, so I'm thinking the answer to my question is "no."

SELECT Q.* INTO BCPtable FROM
    (SELECT * FROM DataTable
    WHERE CreatedDate > '01-DEC-2005') Q

DECLARE @xp_cmd varchar (500)
SET @xp_cmd='BCP "SELECT * FROM TestDatabase..BCPtable" queryout "C:\DataTableExport.xls"  -c' 
EXEC master..xp_cmdshell @xp_cmd

DROP TABLE BCPtable

Anyone have a solution?

TIA

< M!ke >

 

[SQLDenis]

This works for me

DECLARE @xp_cmd varchar (500)
SET @xp_cmd='BCP "SELECT * FROM pubs..publishers" queryout "C:\test bcp\DataTableExport.xls" -c'
EXEC master..xp_cmdshell @xp_cmd

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Just tried similar code with spaces in path and filename("c:\program files\blah blah.xls") - works for me.

Can you post output returned by xp_cmdshell?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrdenny]

I've never had any problems using BCP to export data where the path had a space. Just make sure that the path exists and that the SQL Service has access to it.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LNBruno]

Wow! You guys are really on top of this forum! ;-)

Ok, I simply changed the path from C:\ to C:\Program Files and it worked, so I'm guessing mrdenny's headed down the right path (no pun...well, no, I'll take credit for it!)

Okay, a little more info: this is a SQL 2K Personal Edition installed on the local machine. The path I'm trying to use is on our network (M:\Sales Work\blah blah\blah). Since I have all rights to that path, that doesn't necessarily mean this instance of SQL Server does?




< M!ke >

 

[mrdenny]

Nope. SQL doesn't have access to any of your mapped drives unless the SQL Service is running under your domain account (not recommended).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LNBruno]

Thank you, sir! I appreciate your help.

< M!ke >

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com