Unable to bcp to execute because path name contains spaces. How to get bcp to accept path?

I'm trying to execute a bcp command within SSMS, however I get the following message because the directory's name contain spaces.  Can someone tell me how I can get around this issue?  Thanks.

(Note, I initially was passing the variables, but the system was unable to locate bcp file; so for the purpose of debugging, I hard coded the folder and values into the SQL string.)

Error Message:

Error = [Microsoft][ODBC Drive 11 for SQL Server]Unable to open BCP format file

======

My code is as follows:

ALTER PROCEDURE [dbo].[uspCreateFormatFile] 
	@tableName      VARCHAR(50), 
	@formatFname    VARCHAR(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @tblName    VARCHAR(50),
	        @SQL		        VARCHAR(200);

	--SET @tblName = 'dbo.' + RTRIM(@tableName);

	--SELECT @SQL = 'bcp ' + @tblName + ' format nul -T -n -f ' + @formatFname + '.fmt';

	SELECT @SQL = 'cd .. && "c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\bcp.exe" HCDA.dbo.POP_AGE_GENDER format nul -c -f s:\carmen\DemandF.fmt -t~ -r\n -T';
	
	PRINT @SQL;

	EXEC master..xp_cmdshell @SQL;

END

Open in new window

Maria TorresData AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
you can try  "c:\"Program Files"\Microsoft SQL Server\"Client SDK"\ODBC\110\Tools\Binn\bcp.exe"

try just use "BCP.exe

and check bcp syntax as well
 see this as an example
https://www.experts-exchange.com/questions/28951856/ERROR-when-i-run-EXEC-xp-cmdshell-'bcp-SELECT-FROM-tempdb-PreArchive-queryout-C-Temp-actor-csv-T-c-t-'.html
0
Maria TorresData AnalystAuthor Commented:
I tried the above (imbedding quotation marks) and it did not work.  Also, I have to include the path of BCP.EXE because the machine has several versions of SSMS.
0
Eugene ZCommented:
can you try:


Declare @SQL varchar(max)
SELECT @SQL = ' c:\"Program Files"\"Microsoft SQL Server"\"Client SDK"\ODBC\110\Tools\Binn\bcp.exe /?'
EXEC master..xp_cmdshell @SQL;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Maria TorresData AnalystAuthor Commented:
I tried the above and I got back information on the usage bcp, as well as all the options available for this command.
0
Eugene ZCommented:
cool

try (modify as needed)

ALTER PROCEDURE [dbo].[uspCreateFormatFile]
      @tableName      VARCHAR(50),
      @formatFname    VARCHAR(50)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      DECLARE @tblName    VARCHAR(50),
              @SQL                    VARCHAR(200);

      --SET @tblName = 'dbo.' + RTRIM(@tableName);

      --SELECT @SQL = 'bcp ' + @tblName + ' format nul -T -n -f ' + @formatFname + '.fmt';

      SELECT @SQL = 'c:\"Program Files"\"Microsoft SQL Server"\"Client SDK"\ODBC\110\Tools\Binn\bcp.exe "HCDA.dbo.POP_AGE_GENDER format nul -c -f s:\carmen\DemandF.fmt -t~ -r\n -T"';
      
      PRINT @SQL;

      EXEC master..xp_cmdshell @SQL;

END
0
Maria TorresData AnalystAuthor Commented:
It's not working.  All I'm getting is the bcp's usage information and its options.  When I go to the S:\ drive, the format file is not populated.
0
Eugene ZCommented:
at least the "space " issue is gone
'
small adjustment

      SELECT @SQL = 'c:\"Program Files"\"Microsoft SQL Server"\"Client SDK"\ODBC\110\Tools\Binn\bcp.exe HCDA.dbo.POP_AGE_GENDER format nul -c -f s:\carmen\DemandF.fmt -t~ -r\n -T';

ALTER PROCEDURE [dbo].[uspCreateFormatFile]
      @tableName      VARCHAR(50),
      @formatFname    VARCHAR(50)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      DECLARE @tblName    VARCHAR(50),
              @SQL                    VARCHAR(200);

      --SET @tblName = 'dbo.' + RTRIM(@tableName);

      --SELECT @SQL = 'bcp ' + @tblName + ' format nul -T -n -f ' + @formatFname + '.fmt';

      SELECT @SQL = 'c:\"Program Files"\"Microsoft SQL Server"\"Client SDK"\ODBC\110\Tools\Binn\bcp.exe HCDA.dbo.POP_AGE_GENDER format nul -c -f s:\carmen\DemandF.fmt -t~ -r\n -T';
      
      PRINT @SQL;

      EXEC master..xp_cmdshell @SQL;

END 

Open in new window

0
Maria TorresData AnalystAuthor Commented:
I've tried this before and it failed.  And I tried it again, and it still fails.  The error message is the one I noted above.\
Since I'm  not able to run it within SSMS, I will have to run it from a batch file, via cmd window.

Thank you for your help.
0
Eugene ZCommented:
make sure that  s:\carmen\DemandF.fmt is on server

and ,yes, it is a good idea to test "print @sql "outcome directly from cmd on the server  (again make sure S: is there not on your PC)

check this

Use Native Format to Import or Export Data (SQL Server)
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/use-native-format-to-import-or-export-data-sql-server?view=sql-server-2017
0
Maria TorresData AnalystAuthor Commented:
I was not able to get bcp to run in SSMS; however, please award the points to Eugene Z for all his assistance in this matter.  Thank you Eugene.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Maria TorresData AnalystAuthor Commented:
I wasn't able to get bcp to run within SSMS; however, I would like the rewards/points to go to Eugene for his time and help with this issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.