February 16, 2012 at 11:50 am
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.
February 16, 2012 at 12:09 pm
That's what SSIS jobs are for. In this case, an ADO input and a simple CSV output, with optional (and recommended) error handling.
February 16, 2012 at 12:18 pm
thank you for the reply. can you please explain it a bit more how to go about it.
February 16, 2012 at 12:46 pm
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
February 16, 2012 at 1:56 pm
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
February 16, 2012 at 2:05 pm
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
February 16, 2012 at 2:10 pm
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"'
February 16, 2012 at 2:32 pm
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
February 16, 2012 at 3:28 pm
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.
February 16, 2012 at 3:35 pm
(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.)
February 16, 2012 at 3:53 pm
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
February 16, 2012 at 3:54 pm
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
February 16, 2012 at 3:57 pm
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.
February 16, 2012 at 4:01 pm
Jeff Moden (2/16/2012)
Revenant (2/16/2012)
- calling the shell is generally considered unsafe; andI 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.
February 17, 2012 at 6:11 am
Revenant (2/16/2012)
Jeff Moden (2/16/2012)
Revenant (2/16/2012)
- calling the shell is generally considered unsafe; andI 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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply