March 10, 2011 at 3:18 pm
Hi ALL,
I am trying to return a result set which has 17 columns and more than 90000 rows on report Manager. Can report manager handle this amount of volume? Is there a better solution(Excel...?)?
I get the following error
Not enough storage is available to complete this operation.
Thanks,
Suri
March 11, 2011 at 7:57 am
Why on earth would you want a report with 90000 rows? That is not a report, that is a data dump.
Check out the bcp utility to dump data from SQL Server into a file (and with file I mean a flat file, such as a .csv. Excel is a spreadsheet application):
http://msdn.microsoft.com/en-us/library/ms162802.aspx
Or use Integration Services (SSIS).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 11, 2011 at 10:14 am
Its a report which takes user input (Date Parameters). Can the user give dynamic data range values to a bcp?
March 11, 2011 at 2:19 pm
suri.yalamanchili (3/11/2011)
Its a report which takes user input (Date Parameters). Can the user give dynamic data range values to a bcp?
The MSDN article I provided you states you can input a TSQL query into bcp, so I guess the answer is yes.
Just a little handywork with dynamic SQL, and you'll get there.
On a sidenote, and that's totally my own opinion: users shouldn't be able to produce a 90000 rows report, even if it with parameters. Unless you want your reporting server to bleed of course 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 11, 2011 at 2:31 pm
Maybe get them to download the PowerPivot addin, use Excel to grab the 90k rows, and then push it into powerpivot and get them using aggregated data. Over time, you can then push them towards centrally managed SSAS cubes too.
Steve.
March 11, 2011 at 2:54 pm
My idea is to grab the date range from the user from Report Manager and in a proc behind the scenes, I will run the bcp command to create the file. Then email the file over to the user.
To do this will I have to enable xp_cmdShell , is it secure allowing the server to run Adhoc queries?
March 11, 2011 at 3:01 pm
Why not write up a quick asp.net/ asp (or any other quick and easy web dev language) page to collect the parameter and then pass it to SSIS to do the export. In general, doing the heavy lifting in SSIS will give you a single location for all of the pieces (extract, zip, email).
Steve.
March 11, 2011 at 4:20 pm
I will try that. On the other solution you had proposed about creating cubes.....
Is it not a security risk to give out the login permissions to connect through excel? Even if the user is trusted, what about hackers/encryption?
March 11, 2011 at 4:29 pm
Depends a little on how u implement this.
If you take on the work yourself, so, you'll need powerpivot (and optionally sharepoint 2010) and excel 2010. *You* download the data and drop it into a PPivot workbook. Then you can simply distribute the workbook to the users directly , or via Sharepoint (where sharepoint will take the memory hit for returning results).
Depending on the full dataset size, you could even do this without Powerpivot, as excel 2010 will support up to 1MM rows and you could create a standard pivot table over this worksheet. As far as I know, the performance (querying) and size( overall workbook) will be (much) worse for a standard pivot table than it would be with powerpivot.
To answer your original question, still with the 'it depends' - if you provided access via a read-only view, then there is basically zero security risk (though performance could be an issue if 20 people all hit the view at the same time).
Steve.
March 11, 2011 at 4:30 pm
Oh, forgot to add - i'd be significantly more comfortable giving users direct DB access (with dbreader priv) than allowing xpcmdshell to be executed on the server. just my 2c.
Steve.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply