bcp help

Question

bcp help

MercuryNewt

Grasshopper

Points: 22
More actions
February 20, 2008 at 8:58 am

#63013

I'm trying to use the following command to create an XML-like file from a SQL query:
bcp "SELECT 1 as Tag, NULL as Parent, RnD.dbo.Users.UserID as [User!1!UserID
], RnD.dbo.Users.FirstName as [User!1!FirstName!element], Rnd.dbo.Users.LastName
as [User!1!LastName!element] FROM Rnd.dbo.Users FOR XML EXPLICIT" queryout "C:\
Test.xml" -n -T
The command seems to work fine, however the fiules always contains a few funky characters at the beginning. Is there any way to ensure only the results of the query are actually written to the file?
Thanks in advance

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 1

Huh... looks like you've done everything correctly... what are the clunky characters and are they always the same or not?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MercuryNewt Grasshopper Points: 22 More actions · Answer 2

Not exactly sure. I'm reading the output in Notepad, but it appears to be some special character symbol (it sort of looks like an "o" with a "~" above it and then what appears to be a representation of a carriage return or line feed or both. During earlier testing the special character was an 8, which I took to mean the bcp version, but once I changed the SQL statement (I can't recall at what point) the 8 changed to the special character. Make sense?

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 3

I've never seen BCP do such a thing... what are you using to call the BCP with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Would this have anything to do with using Native format (as opposed to character format?)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

MercuryNewt Grasshopper Points: 22 More actions · Answer 5

Currently I'm developing/testing from the command prompt, but I've also implemented inside a stored procedure with the same results.

MercuryNewt Grasshopper Points: 22 More actions · Answer 6

Matt Miller (2/20/2008)
Would this have anything to do with using Native format (as opposed to character format?)

Is that an argument I can add to the command call?

MercuryNewt Grasshopper Points: 22 More actions · Answer 7

MercuryNewt (2/20/2008)
Matt Miller (2/20/2008)
Would this have anything to do with using Native format (as opposed to character format?)
Is that an argument I can add to the command call?

I just figured this out. I used the -c argument rather than the -n argument and the special character is gone!!! 😀

Here is the final call:

bcp "SELECT 1 as Tag, NULL as Parent, RnD.dbo.Users.UserID as [User!1!UserID], RnD.dbo.Users.FirstName as [User!1!FirstName!element],Rnd.dbo.Users.LastName as [User!1!LastName!element] FROM Rnd.dbo.Users FOR XML EXPLICIT" queryout "C:\BCPTest.xml" -c -T

Now I just need to figure out how to get some element wrapped around the invalid XML. Any suggestions?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

MercuryNewt (2/20/2008)
Matt Miller (2/20/2008)
Would this have anything to do with using Native format (as opposed to character format?)
Is that an argument I can add to the command call?

the -n on the command line tells it to use "native format". I'm thinking you want to use -c (character) instead.

Note: character mode tends to get fouled up if the file is too big. so - try setting the row terminator and column terminator to "".

So - command line looks like:

bcp "SELECT 1 as Tag, NULL as Parent, RnD.dbo.Users.UserID as [User!1!UserID

], RnD.dbo.Users.FirstName as [User!1!FirstName!element], Rnd.dbo.Users.LastName

as [User!1!LastName!element] FROM Rnd.dbo.Users FOR XML EXPLICIT" queryout "C:Test.xml" -T -c -t "" -r ""

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

As to creating the root - you need to UNION it in.

it looks something ugly like:

SELECT 1 as Tag, NULL as Parent,

'123' as [Root!1!Dummy],

Null as [User!2!UserID],

null as [User!2!FirstName!element],

null as [User!1!LastName!element]

UNION ALL

SELECT 2 as Tag, 1 as Parent,

null,

RnD.dbo.Users.UserID,

RnD.dbo.Users.FirstName,

Rnd.dbo.Users.LastName

FROM Rnd.dbo.Users

ORDER BY TAG

FOR XML EXPLICIT

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 10

Matt Miller (2/20/2008)
Would this have anything to do with using Native format (as opposed to character format?)

Man, I can't wait to get off this cold medication... I totally missed that 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MercuryNewt Grasshopper Points: 22 More actions · Answer 11

Matt Miller (2/20/2008)
As to creating the root - you need to UNION it in.
it looks something ugly like:
SELECT 1 as Tag, NULL as Parent,
'123' as [Root!1!Dummy],
Null as [User!2!UserID],
null as [User!2!FirstName!element],
null as [User!1!LastName!element]
UNION ALL
SELECT 2 as Tag, 1 as Parent,
null,
RnD.dbo.Users.UserID,
RnD.dbo.Users.FirstName,
Rnd.dbo.Users.LastName
FROM Rnd.dbo.Users
ORDER BY TAG
FOR XML EXPLICIT

That worked great! Thanks Matt!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

You're welcome! Thanks for the feedback.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?