Output as CSV file.

Question

Output as CSV file.

BBSR

SSCommitted

Points: 1921
More actions
February 16, 2012 at 11:50 am

#255771

i got this query which gives me the read and write count of all the tables in the database. the output whihc i get has 6 columns and 381 rows evrytime. now is there any way to run this as a job and have the output as a csv file. I have to run it at several times a day to get the numbers.

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

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

Revenant SSC-Forever Points: 42467 More actions · Answer 1

That's what SSIS jobs are for. In this case, an ADO input and a simple CSV output, with optional (and recommended) error handling.

BBSR SSCommitted Points: 1921 More actions · Answer 2

thank you for the reply. can you please explain it a bit more how to go about it.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

take a look at "SQL Server Import and Export Wizard".....

its reasonably intuitive and you can save the results as a SSIS file for future mods and also schedule with SQL Server Agent

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

bbsr (2/16/2012)
i got this query which gives me the read and write count of all the tables in the database. the output whihc i get has 6 columns and 381 rows evrytime. now is there any way to run this as a job and have the output as a csv file. I have to run it at several times a day to get the numbers.

Why not just store the output of the query in a table to start with so that you can build a bit o' history?

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

Jeff Moden (2/16/2012)
bbsr (2/16/2012)
i got this query which gives me the read and write count of all the tables in the database. the output whihc i get has 6 columns and 381 rows evrytime. now is there any way to run this as a job and have the output as a csv file. I have to run it at several times a day to get the numbers.
Why not just store the output of the query in a table to start with so that you can build a bit o' history?

....and then point excel at the table as a data source...and voila, all the functionality of filters et al in excel and an up todate data set.

do this day in day out with our users...they love it...as do I I cos it is so easy.

ps...just remember to set the permissions on the query/table correctly

edit...this of course assumes you have the necessary SQL licences,,,,which is another thread/debate 😉

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

BBSR SSCommitted Points: 1921 More actions · Answer 6

getting back to my question.

can i use this in a job and get the output of the stored procedure (which always returns 6 columns and 381 rows) to the output.csv file?

EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "<my stored procedure name here>" -o "E:\output.csv"'

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

bbsr (2/16/2012)
getting back to my question.
can i use this in a job and get the output of the stored procedure (which always returns 6 columns and 381 rows) to the output.csv file?
EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "<my stored procedure name here>" -o "E:\output.csv"'

Yes but don't include the user name or password. Use a "trusted" connection, instead.

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

BBSR SSCommitted Points: 1921 More actions · Answer 8

thank you. how do i create a new output file everytime that job runs. i dont want to append but want to have seperate CSV files everytime the job runs.

Revenant SSC-Forever Points: 42467 More actions · Answer 9

(Sorry, I was tied in admin stuff, not at my keyboard.)

Two thoughts on security:

- calling the shell is generally considered unsafe; and

- if you want to give users access to the data, don't give them access to the table itself, only to a view.

I suggested SSIS because I read 'several times a day' as a requirement to export data at certain user-independent intervals, which would make them available to users who would access them at their convenience. (That's how the upstream of our ingestion processes work.)

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

bbsr (2/16/2012)
thank you. how do i create a new output file everytime that job runs. i dont want to append but want to have seperate CSV files everytime the job runs.

It will simply overwrite the file that is there. If the file doesn't already exist, it well create it. You actually have to go a bit out of the way to get it to append 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)

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

Revenant (2/16/2012)
- calling the shell is generally considered unsafe; and

I absolutely agree but only because most people don't take the time to set up for its use properly. It can, in fact, be done very safely.

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

Revenant SSC-Forever Points: 42467 More actions · Answer 12

bbsr (2/16/2012)
thank you. how do i create a new output file everytime that job runs. i dont want to append but want to have seperate CSV files everytime the job runs.

You may wish to use the STRTDT and STRTTM job tokens as the filename.

http://msdn.microsoft.com/en-us/library/ms175575.aspx

Revenant SSC-Forever Points: 42467 More actions · Answer 13

Jeff Moden (2/16/2012)
Revenant (2/16/2012)
- calling the shell is generally considered unsafe; and
I absolutely agree but only because most people don't take the time to set up for its use properly. It can, in fact, be done very safely.

Absolutely correct, Jeff, but that is how the cookie (usually) crumbles.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 14

Revenant (2/16/2012)
Jeff Moden (2/16/2012)
Revenant (2/16/2012)
- calling the shell is generally considered unsafe; and
I absolutely agree but only because most people don't take the time to set up for its use properly. It can, in fact, be done very safely.
Absolutely correct, Jeff, but that is how the cookie (usually) crumbles.

So help me out here a bit. Instead of saying something like it's "generally considered unsafe", explain why instead of just falling in line with a bunch of people that don't know how to implement it correctly. xp_CmdShell can be setup so that it's perfectly safe. What's MUCH more dangerous are apps that have any more than PUBLIC privs on the database.

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