Having trouble with a BCP statement

Question

Post reply

Having trouble with a BCP statement

itmasterw 60042

SSCrazy

Points: 2858
More actions
October 23, 2020 at 7:21 pm

#3800565
Hi,
I am trying to run a procedure that would select some data and out put it to an HTML file so that I can open it in a browser. I would be able to do this in an VB.Net program that I have.
I found an example that seemed to work, and I could use this for my situation, but the last part that sends it out to the html file is not working, I must be missing something.
can someone tell me I am missing here
This is the bcp code that they have with my file and server in it.
```
bcp "exec dbo.usp_ConvertQuery2HTMLTable 'SELECT Product, UnitPrice FROM dbo.Products'" queryout "C:\Temp\report.html" -c -T -S DESKTOP-JCK9FM6
```
the error I get is
Incorrect syntax near 'queryout'.

Thank you
- This topic was modified 3 years, 5 months ago by itmasterw 60042.

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

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

Step one is to look at your code and, using only the code for direction, tell me which database you want the code to run against. Yeah... I'm pretty sure that SQL Server can't figure that out either. 😉 Your code needs to include the database switch.

For bullet proofing, you should include the server name in double quotes... especially if it's going to have things in it like dashes.

There might still be an error after making those two fixes but give it a try and let's see what the new error might be, if any.

--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)

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 3

Thank you for the reply I did have that earlier and it gave me the same error, but you are right it, of course, needs it.

I have put it in again and am getting the same error, I am not seeing how I would put quotes inhere.

can you give me an example?

Here is what I have with the name:

bcp "exec North2.dbo.usp_ConvertQuery2HTMLTable 'SELECT Product, UnitPrice FROM North2.dbo.Products'" queryout "C:\Temp\report.html" -c -T -S DESKTOP-JCK9FM6

This reply was modified 3 years, 5 months ago by itmasterw 60042.

frederico_fonseca SSCoach Points: 16008 More actions · Answer 4

how are you executing this? syntax is ok if executing on a command prompt but depending on how you are doing it you will require additional quotes.

for example if doing a .net start process you will need further quotes

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 5

I am running it in SSMS. the site I got this form seems to be doing it here, but I could be wrong.

PhilPacha Hall of Fame Points: 3645 More actions · Answer 6

The double quote at the end of the SQL query ism't matched by a double quote at the beginning of the query. It isn't needed, anyway.

(Edit:) Sorry. Small screen.

This reply was modified 3 years, 5 months ago by PhilPacha.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

PhilPacha wrote:

The double quote at the end of the SQL query ism't matched by a double quote at the beginning of the query. It isn't needed, anyway.

Actually, it is matched. It appears just before the EXEC, which is running a stored procedure with a SELECT passed in as the operand.

--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)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

itmasterw 60042 wrote:

I am running it in SSMS. the site I got this form seems to be doing it here, but I could be wrong.

If you're running the command from an edit window in SSMS, it's not going to run no matter how correct it is. Where in SSMS are you trying to run the command from (which type of window in SSMS)?

--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)

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 9

I am running it in the edit window. I have done BCP here before but it was a long time ago. Do I need to rewrite this or some how maybe using xpommand shell or something?

frederico_fonseca SSCoach Points: 16008 More actions · Answer 10

in SSMS you need to use xp_cmdshell.

but on your original post you stated that this is to be executed from a vb.net program - that being the case the correct thing would be to just issue the stored proc with your required select and write the output to a file within vb.net - and then open it on the browser.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 11

itmasterw 60042 wrote:

I am running it in the edit window. I have done BCP here before but it was a long time ago. Do I need to rewrite this or some how maybe using xpommand shell or something?

Yes... you need to use xp_CmdShell if you're going to run it either from the Edit window in SSMS or from a stored procedure. But you still need to add the database switch like I said.

I'm also confused. Like Frederico said, you originally stated that you wanted to run this from VB. It's tough for us to shoot the arrow through the hole of a rolling donut. 😉

--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)

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 12

Okay, I felt this would be more efficient and extensible ti done in SQL server, and I guess I thought it would be easier, but thanks I will do this in Vb

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 13

itmasterw 60042 wrote:

Okay, I felt this would be more efficient and extensible ti done in SQL server, and I guess I thought it would be easier, but thanks I will do this in Vb

Lordy... You certainly do jump to conclusions. 😉 I've certainly NOT suggested that you should avoid doing this in T-SQL.

But, no matter... whether you did it in T-SQL or VB or whatever, the BCP command is still going to need to be correct and, so far, I've not yet seen you produce the correct command. Like I said, you're going to need to use the database switch in the command to do what you need to do and it's not going to matter from where you run it.

--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)

frederico_fonseca SSCoach Points: 16008 More actions · Answer 14

itmasterw 60042 wrote:

Okay, I felt this would be more efficient and extensible ti done in SQL server, and I guess I thought it would be easier, but thanks I will do this in Vb

you still do it in SQL - at least the code containing on your sql proc.

note that using BCP its not really strictly doing it fully in SQL on your case - you still needed to code your VB program to do a system call to execute the BCP command.

in vb you would code logic like this

open connection to sql
execute sp
get returning recordset
write it to file

@jeff - Server is optional - if not supplied it run on the local machine where bcp is executed and connects to its default instance.

But doing this is bad practice and should be avoided

This reply was modified 3 years, 5 months ago by frederico_fonseca.