Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2011-12-15 : 14:00:58
|
| No. Seriously.One of my weekly mundane tasks is pulling data from a db and storedproc that i wrote which monitors table size and growth across a large number of dbs. I am to pull this data from specific rows in a table in the db, export it to excel, reformat it, and then produce bar charts for the people in bigger offices than mine to pretend to pay attention to. What i'm using to pull the data i need from the table is pretty straightforward:SELECT datecreated as [Date Created], ROWS AS Rows, reserved AS Reserved, DATA AS Data, index_size AS [Index Size], unused AS Unused FROM dbtablestats WHERE NAME = 'arr_weapon'SELECT datecreated as [Date Created], ROWS AS Rows, reserved AS Reserved, DATA AS Data, index_size AS [Index Size], unused AS Unused FROM dbtablestats WHERE NAME = 'Arrest'SELECT datecreated as [Date Created], ROWS AS Rows, reserved AS Reserved, DATA AS Data, index_size AS [Index Size], unused AS Unused FROM dbtablestats WHERE NAME = 'Errorarr'SELECT datecreated as [Date Created], ROWS AS Rows, reserved AS Reserved, DATA AS Data, index_size AS [Index Size], unused AS Unused FROM dbtablestats WHERE NAME = 'Insert_text'SELECT datecreated as [Date Created], ROWS AS Rows, reserved AS Reserved, DATA AS Data, index_size AS [Index Size], unused AS Unused FROM dbtablestats WHERE NAME = 'ARRESTATTACHMENTS'SELECT datecreated as [Date Created], ROWS AS Rows, reserved AS Reserved, DATA AS Data, index_size AS [Index Size], unused AS Unused FROM dbtablestats WHERE NAME = 'Arr_ESubmitLog'SELECT datecreated as [Date Created], ROWS AS Rows, reserved AS Reserved, DATA AS Data, index_size AS [Index Size], unused AS Unused FROM dbtablestats WHERE NAME = 'CfgArrType_uf'SELECT datecreated as [Date Created], ROWS AS Rows, reserved AS Reserved, DATA AS Data, index_size AS [Index Size], unused AS Unused FROM dbtablestats WHERE NAME = 'OutOfSynchRecsARR_PER'SELECT datecreated as [Date Created], ROWS AS Rows, reserved AS Reserved, DATA AS Data, index_size AS [Index Size], unused AS Unused FROM dbtablestats WHERE NAME = 'CfgArrTypeStatusXref'SELECT datecreated as [Date Created], ROWS AS Rows, reserved AS Reserved, DATA AS Data, index_size AS [Index Size], unused AS Unused FROM dbtablestats WHERE NAME = 'ARR_AFSS'SELECT datecreated as [Date Created], ROWS AS Rows, reserved AS Reserved, DATA AS Data, index_size AS [Index Size], unused AS Unused FROM dbtablestats WHERE NAME = 'ARR_AFSS'There are about 400 other lines in the script fo the other columns in the database. when i export those results to an excel instance (which i do with another third party app) i then have to do a few extra steps before i can make the charts i need.It would make my life easier, if i could add something to this script that would automatically export these results to individual excel sheets or indiviudal excel files. I could then write that into a sproc and schedule it as a job. Is there any code i can use to push these results out to excel?thanksJames |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-12-15 : 17:14:48
|
| Do they have to be on separate Excel tabs? What if the powers-that-be had an interactive chart that summarized all the data and they could pick and choose which categories to view? If that's possible, save yourself (the headache) and do this:SELECT NAME, datecreated as [Date Created], ROWS AS Rows, reserved AS Reserved, DATA AS Data, index_size AS [Index Size], unused AS Unused FROM dbtablestats WHERE NAME IN('arr_weapon','Arrest','Errorarr','Insert_text','ARRESTATTACHMENTS','Arr_ESubmitLog','CfgArrType_uf','OutOfSynchRecsARR_PER','CfgArrTypeStatusXref','ARR_AFSS')Dump that data out to a single Excel sheet, then use the Pivot Chart wizard to set up the graph accordingly. You'll want to put the Name on the Report filter level (Page level), then put the row and column headings based on the rest of the data. Then the user can use the dropdowns on the report to select which items to view or hide. |
 |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-12-16 : 09:11:25
|
| Wow. Excellent suggestion. Thank you! |
|
|
|
|
|
|
|
|
A script to make life easier