iLearn

Hi Guys, Hope everyone is doing well. Last week, I was working on the most exciting task on which any database developer would love to work - Performance Tuning of a Stored Procedure . Let me explain about the code that this stored procedure consisted of. The basic task was to truncate the data from an existing table and fill it with new data based on some really complex logic and calculations. There were various temp tables that were being used inside the subsequent steps of the stored procedure. Whenever the stored procedure consists of various sub-steps, my approach is to get the execution time of each one of the sub-steps (by running it as an ad-hoc query) and find out the possible solutions to decrease the execution time of this step. And yes, you can also look at the Actual/Estimated execution plans and try to check whether the code written follows the best practices or not. So, after knowing the execution time of all the steps, I began to focus on the step whic...

By First looking at the topic name, does the task sound simple to you? Well, I thought so but it turned out to be quite a mammoth task with loads of learning. The requirement is to transfer the data from a SQL Server table to an excel spreadsheet. The caveat is that it is not a one item activity. Instead, everytime we dump data into the spreadsheet the previous data should be overwritten. Before going into the solution, here is all that I tried with no luck:- Tried using BCP utility. It worked in transporting the data, however on opening the excel sheet you get some formatting error.  Using SSIS Package, I was not able to use Excel destination because it supports till office 2007 only. So had to use OLEDB destination by changing the configuration so that it picks up the destination excel file. Was not able to use the "Delete from [sheet$]" command (because we need to get rid of the data before the new data comes in) as it is not supported by Excel and OLEDB connecti...