SQL Query runs fine In Management Studio but will not run under BCP

Question

This query runs fine within Management Studio, but got a few errors when trying to use bulk copy utility.

Here is what I am getting...

C:\>BCP     "SELECT title1, [precinct percent] AS [PrecinctPercent], leader, [leader
     percent] AS [LeaderPercent], Winner, WinningVotes, leader2, [leader2 percent] A
    S [LeaderPercent2], Loser, LosingVotes FROM dbo.[RACE] r inner join (select rc.[
    race number], max(case when seqnum = 1 then [candidate num] end) as Winner, max(
    case when seqnum = 1 then Votes end) as WinningVotes, max(case when seqnum = 2 t
    hen [candidate num] end) as Loser, max(case when seqnum = 2 then Votes end) as L
    ossingVotes (select rc.*, row_number() over (partition by rc.[race.number] order
     by votes desc) as seqnum from dbo.[RACE CANDIDATES] rc ) rc group by rc.[race n
     umber] ) rc on r.[race number] = rc.[race.number] FOR XML PATH ('WQAD'), ROOT('r
    oot')" QUERYOUT "C:\Users\andersse\RESULTS222.XML" -c -t -T -S WQAD-ENG7\SQLEXPR
    ESS
    "SQLState = 37000, NativeError = 102
    Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax n
    ear '('.
    SQLState = 37000, NativeError = 102
    Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax n
    ear 'rc'.
    SQLState = 37000, NativeError = 8180
    Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could
    not be prepared."

Does anyone have any ideas why the () are not being read correctly as a query und BCP?

Thanks for reading this.

UPDATED QUERY

SELECT title1, 
    [precinct percent] AS [PrecinctPercent], 
    leader, 
    [leader percent] AS [LeaderPercent], 
    Winner, 
    WinningVotes, 
    leader2, 
    [leader2 percent] AS [Leader2Percent], 
    Loser, 
    LosingVotes 
    FROM dbo.[RACE]  r inner join
     (select rc.[race number],
             max(case when seqnum = 1 then [candidate num] end) as Winner,
             max(case when seqnum = 1 then Votes end) as WinningVotes,
             max(case when seqnum = 2 then [candidate num] end) as Loser,
             max(case when seqnum = 2 then Votes end) as LosingVotes
      from (select rc.*,
                   row_number() over (partition by rc.[race number] order by votes desc) as seqnum
            from dbo.[RACE CANDIDATES] rc
           ) rc
      group by rc.[race number]
     ) rc
     on r.[race number] = rc.[race number]
     FOR XML PATH ('WQAD'), ROOT('root')

BCP QUERY

C:\>BCP "SELECT title1, [precinct percent] AS [PrecinctPercent], leader, [leader
 percent] AS [LeaderPercent], Winner, WinningVotes, leader2, [leader2 percent] A
S [LeaderPercent2], Loser, LosingVotes FROM dbo.[RACE] r inner join (select rc.[
race number], max(case when seqnum = 1 then [candidate num] end) as Winner, max(
case when seqnum = 1 then Votes end) as WinningVotes, max(case when seqnum = 2 t
hen [candidate num] end) as Loser, max(case when seqnum = 2 then Votes end) as L
ossingVotes, (select rc.*, row_number() over (partition by rc.[race.number] orde
r by votes desc) as seqnum from dbo.[RACE CANDIDATES] rc ) rc group by rc.[race
number] ) rc on r.[race number] = rc.[race.number] FOR XML PATH ('WQAD'), ROOT('
root')" QUERYOUT "C:\Users\andersse\RESULTS222.XML" -c -t -T -S WQAD-ENG7\SQLEXP
RESS

ERROR CODES

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object nam
e 'dbo.RACE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could
 not be prepared.

If you post code, XML or data samples, **PLEASE** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! If you post error messages, **PLEASE** use the blockquotes ( ` " ` ) to properly format the error message. — marc_s, Jan 29 '14 at 19:52

score 4 · Answer 1 · edited May 23 '17 at 12:21

4

You need to make sure BCP has the same database context as SSMS. For most queries, you can do this simply by saying:

FROM [databasename].dbo.[RACE]
...
FROM [databasename].dbo.[RACE CANDIDATES]

You may also need to ensure the correct database context by passing in the -d argument (as you need to do when using FOR XML with sqlcmd, as you discovered in this answer, though I couldn't reproduce that either with the 2012 tools), e.g.

BCP "your query with db prefixes" QUERYOUT "file" -c -t -Sserver -T -dDatabase

(See the full syntax and argument list in the documentation.)

You also need to make sure you are using the right copy of BCP; the -d argument wasn't added until 2012, so you may need to update your client tools to the most recent version. Here are the differences between the 2008 and 2012 version of BCP; note that -d is not available :

enter image description here

Note that for me the older version is the one that is called by default, due to my environment paths being set up based on the first versions of SQL Server I installed, not the last. So make sure that you are using the newest version explicitly.

edited May 23 '17 at 12:21

Community

1
1

answered Jan 29 '14 at 19:58

Aaron Bertrand

272,866
37
466
490

that comma was one of my issues. thank you for pointing that out. – user3242661 Jan 29 '14 at 20:01
Now I am down to 2 errors.
SQLState = S0002, NativeError = 208 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object nam e 'dbo.RACE'.
SQLState = 37000, NativeError = 8180 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. – user3242661 Jan 29 '14 at 20:02
@user3242661 so you need to get your query working in SSMS, before you start worrying about whether it "works in SSMS but not in BCP" when, clearly, you're not even talking about the same query. – Aaron Bertrand Jan 29 '14 at 20:03
forget about the breaks in there. I was trying to put them on seperate lines – user3242661 Jan 29 '14 at 20:03
it works in SSMS.....with the comma of course. Oops, I missed a comma.. – user3242661 Jan 29 '14 at 20:03
@user3242661 the query in your question can't possibly work in SSMS. Next, you are probably not getting it to work in BCP because in SSMS you have the database context set to a specific database, but BCP is looking in the wrong database. – Aaron Bertrand Jan 29 '14 at 20:04
How can I prove that it runs in SSMS? Because it does.... How would I get BCP to look at the right database? – user3242661 Jan 29 '14 at 20:07
1

@user3242661 You prefix the objects with the database name, as I showed in my updated answer. And once again, the query in the question, as it is currently posted, cannot possibly work in SSMS, that is my point (you later edited your comment to add "...with the comma of course"). When you first tried to run the query in BCP, you got those errors because it wasn't the same code as what was working in SSMS (it was at least missing a comma, and possibly had other syntax errors). – Aaron Bertrand Jan 29 '14 at 20:08
Well, I can run the query in SSMS succesfully and am able to view the properly formatted XML file. I am trying to use BCP to create the XML file hard disk. Do you think I would try to use BCP if the query wouldn't work in the Server Management Studio? – user3242661 Jan 29 '14 at 20:11
@user3242661 I know all of this, NOW. The query you posted initially, with the missing comma, didn't work in either place. I am trying to help you here, I'm not interested in quibbling with you over whether the query works now because you fixed the missing comma I pointed out. Can you just update the question with the *right* query and the *current* error message, and can we stop talking about the initial version of the question already? – Aaron Bertrand Jan 29 '14 at 20:13
I just updated my question. Hopefully the format is acceptable. – user3242661 Jan 29 '14 at 20:21
@user3242661 So, as Aaron said, try using `[DatabaseName].[dbo].[RACE]` instead of `dbo.[RACE]` – Lamak Jan 29 '14 at 20:23
Lamak, try this in the BCP? – user3242661 Jan 29 '14 at 20:27
@user3242661 yes, as this part of the anwer says: *Next, you need to make sure BCP has the same database context as SSMS* – Lamak Jan 29 '14 at 20:30
that made it really mad. threw me about 13 errors. Invalid column names and a few others. – user3242661 Jan 29 '14 at 20:31
@user3242661 SQL Server doesn't have feelings. Maybe start with a much simpler query, like `SELECT title1 FROM dbname.dbo.RACE`. – Aaron Bertrand Jan 29 '14 at 20:33
3

@user3242661 Then try using the tables where you got an error for the columns. Seriously, this can't be a step by step debugging of your code. The core of the problem is known, try applying those concepts to fix your current query – Lamak Jan 29 '14 at 20:45
5

At this point you're not even trying to get help, you're just asking someone else to do all the work for you. Not what SO exists for. – jcolebrand Jan 29 '14 at 20:45

SQL Query runs fine In Management Studio but will not run under BCP

1 Answers1