BCP in SQL Server causing errors

Question

All,

Here I have a simple query that can be run on anyone's machine with SQL Server where a simple table is created and then I try to export the contents of this to an XML file via BCP. All you have to do is replace @DatabaseName, @filename, @query with values pertaining to your machine.

When I run query 1 the error message is

SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file.

When I run query 2 or 3 the error message is

bcp.exe: unknown option A usage: bcp.exe {dbtable | query} {in | out | queryout | format} datafile.

I am running this from the SQL Server Management Studio. I really need a solution to this, please help.

DECLARE @cmd  As VARCHAR(8000)
use CP

IF EXISTS(SELECT * FROM dbo.Dummy) DROP TABLE dbo.Dummy

Declare @DatabaseName VARCHAR(200)
Declare @filename VARCHAR(200)
Declare @query As varchar(max)


set @DatabaseName = 'ALSCG-JPATHIL\SQLEXPRESS'
set @filename = 'C:\Users\jpathil\Desktop\sample.xml'
set @query = 'SELECT * FROM [CP].[dbo].[Dummy]'

CREATE TABLE Dummy (BCP_Sucks VARCHAR(200),);
INSERT INTO Dummy VALUES ('Jean');
INSERT INTO Dummy VALUES ('Derrick')



Declare @teststring VARCHAR(2000)

-- Query 1

--set @teststring = 'bcp.exe ' + '"' + @query + '" ' + 'queryout' + ' "' + @filename + '" ' + '-c -T -x -S '  + @DatabaseName
--Query 2

--set @teststring = 'bcp.exe ' + '"' + @query + '" ' + 'queryout' + ' "' + @filename + '" ' + '-c -T -x'  + @DatabaseName
--Query 3

set @teststring = 'bcp.exe ' + '"' + @query + '" ' + 'queryout' + ' "' + @filename + '" ' + '-c -x'  + @DatabaseName



SET @cmd = @teststring

EXEC xp_cmdshell @cmd;

The goal here is to produce a simple XML file imported to my desktop when running a script in SSMS — jp3nyc
– jp3nyc, Commented Mar 2, 2017 at 11:38
Have you tried adding a PRINT @teststring to see if the format of the command is correct? — R. Richards
– R. Richards, Commented Mar 2, 2017 at 13:28

Community · Accepted Answer · 2017-05-23 10:29:50Z

In your previous related question I told you 1) to use PRINT and search for syntax errors. And 2) search for missing blanks

This

@filename + '" ' + '-c -x'  + @DatabaseName

leads to

"C:\Users\jpathil\Desktop\sample.xml" -c -xALSCG-JPATHIL\SQLEXPRESS

One problem is the missing blank after option -x. Might be, that here's the reason for the unknown option A

The server's name follows option -S btw...

Here's a working example
Hint Be aware, that C:\Users\... points to your local machine, while BCP is running in the context of SQL-Server...

USE master;
GO
CREATE DATABASE TestDB;
GO
USE TestDB;
GO
-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
RECONFIGURE;  
EXEC sp_configure 'xp_cmdshell', 1;  
RECONFIGURE;  
GO  

CREATE TABLE dbo.Dummy (BCP_Sucks VARCHAR(200),);
INSERT INTO dbo.Dummy VALUES ('Jean');
INSERT INTO dbo.Dummy VALUES ('Derrick');

GO
DECLARE @DatabaseName VARCHAR(200)=@@SERVERNAME;
DECLARE @filename VARCHAR(200)= 'C:\Users\jpathil\Desktop\sample.xml';
DECLARE @query As varchar(max)='SELECT * FROM TestDb.dbo.Dummy FOR XML AUTO;';

DECLARE @teststring VARCHAR(2000)
SET @teststring = 'bcp.exe "' + @query + '"  queryout "' + @filename + '" -w -T -S '  + @DatabaseName

PRINT @teststring;
EXEC xp_cmdshell @teststring; 
GO
USE master;
DROP DATABASE TestDb;

CodeCabbie · Accepted Answer · 2023-11-20 11:47:53Z

0

I received 'bcp: unknown option A' when there was a SPACE character between the -e option and the path to the error file.

Remove the space and the problem is fixed.

(Explanation: The path to the error file started with '/Apps', so the '/A' was being interpreted as an option. The erroneous space was inserted by OpenAI GPT4. Users beware!)

edited Nov 20, 2023 at 11:47

answered Nov 20, 2023 at 11:40

CodeCabbie

3,5842 gold badges23 silver badges31 bronze badges

Collectives™ on Stack Overflow

BCP in SQL Server causing errors

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related