Is BCP queryout limited?

Question

Is BCP queryout limited?

Viewing 4 posts - 16 through 18 (of 18 total)

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

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

So, just to be clear.... you no longer have the original "identifier" problem?

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

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 2

Jeff Moden (12/30/2015)
So, just to be clear.... you no longer have the original "identifier" problem?

I tried to put the call to their code in () but got a syntax error. I have moved away from using their code, and worked on just using BCP in my code. Will need to do what they are doing though.. create a table, get the column header names and write then into the new table, use BCP to export out the two tables. Unless you know a cleaner way to get the column names in the first row of the data... so you can then open into excel.

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

dwilliscp (12/31/2015)
Jeff Moden (12/30/2015)
So, just to be clear.... you no longer have the original "identifier" problem?
I tried to put the call to their code in () but got a syntax error. I have moved away from using their code, and worked on just using BCP in my code. Will need to do what they are doing though.. create a table, get the column header names and write then into the new table, use BCP to export out the two tables. Unless you know a cleaner way to get the column names in the first row of the data... so you can then open into excel.

If the ultimate destination is an Excel spreadsheet, wouldn't it be easier to write a stored procedure or view and have the Excel spreadsheet read from that using external data?

If that's not feasible, it will usually take two BCP calls... one to create the header and one to create the data. Both can be stored procedures so that your BCP commands can be much simpler.

BCP might not actually be the way to go for something like this, though. You might do better with SQLCMD with a redirection to a file.

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

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 4

Jeff Moden (1/1/2016)
dwilliscp (12/31/2015)
Jeff Moden (12/30/2015)
So, just to be clear.... you no longer have the original "identifier" problem?
I tried to put the call to their code in () but got a syntax error. I have moved away from using their code, and worked on just using BCP in my code. Will need to do what they are doing though.. create a table, get the column header names and write then into the new table, use BCP to export out the two tables. Unless you know a cleaner way to get the column names in the first row of the data... so you can then open into excel.
If the ultimate destination is an Excel spreadsheet, wouldn't it be easier to write a stored procedure or view and have the Excel spreadsheet read from that using external data?
If that's not feasible, it will usually take two BCP calls... one to create the header and one to create the data. Both can be stored procedures so that your BCP commands can be much simpler.
BCP might not actually be the way to go for something like this, though. You might do better with SQLCMD with a redirection to a file.

That is the only solution I thought of.. using BCP. I will look into SQLCMD though.. using queries inside of excel is not going to work for us, thanks for your help. I did get the export to work and the file is created with the first row as headers. (I have thought about trying to use SSIS to create a file specific to the user.. but I was just adding 6 new columns to the existing report + file export, so decided not to expand the work.