June 28, 2011 at 10:51 am
Hello All,
I need to retrieve data from a large table and deliver it in small "chunks" (via Excel) to many individuals. The "chunks" would be based on an attribute in the table such as manager name or geographic district. This is very easy to in individual queries but I cannot afford the time to change my where criteria thousands of times :).
I have toyed with the idea of a cursor that creates temporary tables. But after looking through the forums and seeing how certain people feel about cursors I thought I would simply ask for advice.
All suggestions welcome.
Thank you,
Lonnie
June 28, 2011 at 11:51 am
Have you considered storing the recipients and the Where Conditions in Tables and dynamically generating the SQL Statements?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2011 at 11:56 am
In my testing (with a cursor....shhhh) I did throw the predicate for the where clause in a separate table. I haven't used dynamic SQL in years so I would appreciate a jump start. I was thinking though this process might best sit in SSIS and looking for advice in that direction??
Thanks,
Lonnie
June 28, 2011 at 12:17 pm
I can't think of a reason that would necessitate SSIS.
The following link has some simple examples of Dynamic SQL.
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2011 at 12:20 pm
SSRS should do this, setting up reports, but that might be a lot of work. However it does subscriptions in the Enterprise edition.
Failing that, are you looking to generate the Excel files dynamically? A few things that might help you:
http://qa.sqlservercentral.com/articles/68842/
http://qa.sqlservercentral.com/articles/64838/
http://qa.sqlservercentral.com/articles/64556/
http://qa.sqlservercentral.com/scripts/31525/ - Script
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 28, 2011 at 12:26 pm
Thank you both for the links....I will investigate. SSRS is not a viable option for this process.
Lonnie
June 28, 2011 at 12:38 pm
it kind of sounds like some dynamic sql and using sp_sendmail would do the trick, just as Welsh Corgi suggested; i'd like to see a little more detail, like a couple of example queries , but it doesn't sound difficult at all.
Lowell
June 28, 2011 at 12:45 pm
The code would be along the lines of:
Select A.RVP, A.DM, A.BRANCH_CODE, A.Exclusive_BRAND, A.Cat_1, A.Cat_2, A.Cat_3,
Jan11_Sales, Feb11_Sales, Mar11_Sales,
Apr11_Sales, May11_Sales,
Jan11_COGS, Feb11_COGS, Mar11_COGS,
Apr11_COGS, May11_COGS,
Jan_11_Num_Of_SKUs, Feb_11_Num_Of_SKUs, Mar_11_Num_Of_SKUs,
Apr_11_Num_Of_SKUs, May_11_Num_Of_SKUs,
Jan11_Adj_Value, Feb11_Adj_Value, Mar11_Adj_Value,
Apr11_Adj_Value, May11_Adj_Value
From #FinalGeographicEB As A
Where A.RVP = 'XXXXX XXXXXXXX'
Where the A.RVP could be fed dynamically from another table....
I have no output strategy as of yet other than it needs to end up in Excel. I am reading some of the links from Steve on how best to handle that.
Thank you,
Lonnie
June 28, 2011 at 12:48 pm
You may want to read the following article:
Forum Etiquette: How to post data/code on a forum to get the best help
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2011 at 7:53 pm
It all depends on your requirements, probably any of the articles Steve provided will serve your requirements, but it can be done in SSIS as well.
The approach would require initially generating a list of criteria (hopefully the data supports this) then creating a loop within the control flow of the package to iterate through the list. Create a dataflow within the loop and update the criteria of the sql query within the dataflow for each iteration.
You're probably going to want to update the excel destination for each iteration as well in order to send each 'chunk' to a different file, and you'd have to control the file 'creation' (probably easier to copy from a template) from the package control flow.
Ofcourse you could just do a simple dataflow with a source read and handle everything in a vb/c script component destination but then what's the point of using SSIS at all? 😉
Like the others have said, SSRS is probably best suited for this sort of reporting, you can do it in SSIS, or you can do it in SQL server. Depends on your specific requirements and what you're comfortable with.
June 28, 2011 at 8:24 pm
Could you switch this up and provide the users with an Excel spreadsheet that accesses the database and refreshes the data?
Excel has the ability to run queries against ODBC/OLEDB data sources. I haven't done something like this in years, but you should be able to instruct the recipients to input the correct parameter to get their data - or maybe prompt them for it, or possibly use their domain credentials and a cross-map table.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 29, 2011 at 7:22 am
Unfortunately I cannot provide ole/odbc access to the end users
Thank you,
Lonnie
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply