iLearn

Seldom are the days when you run into complex issues but resolve them in the shortest interval of time. Thanks to Larry Page and my fellow developers :-). But jokes apart I learned something new today and that always feel wonderful. Issue #1:-  Unable to enlist in the distributed transaction Symptom:- Running a package (having sequence container and TransactionOption property set to  required) on your local machine Solution  #1: Activate all the options for MSDTC (Distributed Transaction Cordinator) #2: Set the TransactionOption property to Supported Issue #2:-  The OLE DB Source.Outputs[OLE DB Source Output].Columns[XXXXXXXX] on the non-error output has no corresponding output Symptom:- You will get the error message on OLEDB Source particularly when you add extra columns after the initial design/development of the package. Solution Remove the OLEDB Source and drag n drop a new one. "Life is 10 % what happens and 90 % of how you react to it...

Sometimes the mistakes you make in the coding lead you to a better understanding of a particular concept. This happened recently with me. I accidentally replaced the 'WHERE' clause in a SQL query with "AND" and the results confused the heck out of me.  Lets look at the coding part now. Suppose we have two tables #t1 and #t2.  #t1 consists of id and name #t2 consist of id and department Now the requirement is to fetch name and department of id = 2.  CREATE TABLE #T1 (ID INT, NAM VARCHAR(100)) INSERT INTO #T1 VALUES (1, 'AMIT') INSERT INTO #T1 VALUES (2, 'AMITT') INSERT INTO #T1 VALUES (3, 'AMITTT') CREATE TABLE #T2 (ID INT, DEP VARCHAR(100)) INSERT INTO #T2 VALUES (1, 'HR') INSERT INTO #T2 VALUES (2, 'FINANCE') SELECT * FROM #T1  LEFT JOIN #T2 ON #T1.ID = #T2.ID  where #T1.ID = 2  This is quite simple  But what if the select query becomes  SELECT * FROM #T1  LE...

I got a massive satisfaction last week when I was able to bring down the execution time of a sql stored procedure from > 3 hours to mere 2 minutes. It all boiled down to a missing joining key that led to a cartesian product within the tables. Here is how the basic structure of the query (there were plenty of joining columns, however for our convenience lets consider 2 columns only) looked like Select * from                      table1 t1 left join table2 t2 on t1.k1 =t2,k1 and t1.k2 = t2.k2                      left join table3 t3 on t1.k1 = t3.k1 and t1.k2 = t2.k2                      left join table4 t4 on t1.k1 = t4.k1 And this query ran forever when given a particular set of parameters. I started with the approach of joining the parent table with each subsequent table until I met the road block. For examp...

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...

Requirements : A valid azure account, Local Instance of SQL Server (>= 2012), SQL Server Data Tools, Excel 2007 or above Scenario : Dump Excel File Contents to a Table in SQL Azure Steps :    Develop SSIS Package- Drag Data Flow Task on the control flow window and double click on it to open the Data flow window Drag Excel source and configure it to the source file and the worksheet. Drag ADO.NET Destination. Create a new ADO.NET connection by selecting provider - .NetProviders\SQLClient Data Provider Put in the server name of a valid SQL Azure database. User SQL Server Authentication and type in the username and password. Go to ALL window and set the properties Select the target database. Configure ADO.NET destination with this new connection manager. Point to the target table. Map the columns correctly. Save the package by specifying the password (using encryptsensitivewithpassword) Go to ProjectProperties --> Debugging and set the Run64bitruntim...