Using BCP with SQL Server to get Data into Excel (1 Viewer)

[proballin]

I have a query that works in SQL Server. However when I try to put it in a scheduled job using the BCP function I cannot get it to work. I want the output of my query to be written to an excel file (.csv file is fine).

SET QUOTED_IDENTIFIER OFF
SELECT EMP.EMPCODE, EMP.LASTNAME, EMP.FIRSTNAME, EMP.CRAFT, EMP.CREW, EMPWAGE.WAGECODE, EMPWAGE.RATE
FROM EMP INNER JOIN EMPWAGE ON EMP.EMPCODE = EMPWAGE.EMPCODE
GROUP BY EMP.EMPCODE, EMP.LASTNAME, EMP.FIRSTNAME, EMP.CRAFT, EMP.CREW, EMPWAGE.WAGECODE, EMPWAGE.RATE, EMPWAGE.PRIMARYWAGE
HAVING (((EMPWAGE.PRIMARYWAGE)="Y"))
ORDER BY EMP.LASTNAME"

 

[SQL_Hell]

Query looks ok, but what does the group by do? doesn't look it does much

Post the BCP code, or are you using DTS / SSIS?

What version of SQL SERVER?

 

[proballin]

I know that the query works...I have ran it in SQL Server (version is 2000). However I cant get it to run using the BCP command. I may be using the wrong format. I wrote it like:

BCP "SET QUOTED_IDENTIFIER OFF
SELECT EMP.EMPCODE, EMP.LASTNAME, EMP.FIRSTNAME, EMP.CRAFT, EMP.CREW, EMPWAGE.WAGECODE, EMPWAGE.RATE
FROM EMP INNER JOIN EMPWAGE ON EMP.EMPCODE = EMPWAGE.EMPCODE
GROUP BY EMP.EMPCODE, EMP.LASTNAME, EMP.FIRSTNAME, EMP.CRAFT, EMP.CREW, EMPWAGE.WAGECODE, EMPWAGE.RATE, EMPWAGE.PRIMARYWAGE
HAVING (((EMPWAGE.PRIMARYWAGE)="Y"))
ORDER BY EMP.LASTNAME"

queryout C:\employee.csv -c -T

 

[SQL_Hell]

Ok... interesting you didn't offer any explaination to the group by clauses, but instead you just said the query works. I suspect you dont need those group bys...but anyway. Query looks like MS Access wrote it for you!

Personally I would just do this in the DTS wizard..so much easier.

Right click on your database select "all tasks" then select "export data". Run through the wizard and select the save package and schedule package options. This will create a saved DTS package and a job to run that package.

 

[proballin]

I wasn't to sure about the GROUP BY question, I forgot to address that. I was given the query and told to just schedule it to be able to export. The DTS solution you suggested worked perfectly. Thanks!

 

[SQL_Hell]

Good I am glad you were able to execute the DTS, is it very easy using the wizard but you can also do some clever stuff when creating DTS packages manually.

DTS actually uses BCP when it does data transfers, so it's essentially the same thing.