Knowledge Sharing

Today I had to restore the database from MDF file and the log file associated with this database was not available. MDF file is the place where all the data get stored and the log files stores log information which helps in restoring to the previous working condition in case of failure. it is very easy to attach the database using SSMS. Right click on the Databases folder and select Attach  Fig 0.1 which will open up the below window Fig 0.2 Click on add and browse the MDF file from the stored path  Fig 0.3 Though the log file is not available, still the reference to the file exist in the MDF and hence we need to remove the log file path information from here before proceeding. Fig 0.4 So we can either take the back up of the database for future use or copy MDF and LDF files or only MDF files.

Today I have installed SQL server 2008 R2 express edition to my local machine but when I tried to login to the server got the below error message.  Fig 0.1 I did install express edition some years back and was successful at that time and not remembering such issue came up at that time. I tried below steps but still was not successful.              - Enabled TCP\IP port              - Added exception for SQL server and browser in windows firewall              - double checked the server is running or not But I was not lucky enough to get into the server. Ignoring small things in life will cost you more some time as here in this scenario I have forgot to give the server name and the instance together before hitting the login button. I am able to login to the express edition when I have given the server name as .\SQLEXPRESS.  Catch here is server will not listen if you give it as only dot(.) or [local host].

It is very simple to identify the name of the installed instances. I have installed SQL server 2008 R2 express edition in my system and was not able to find out the name exactly. I know that SQL by default gives the name as SQLExpress but we can make sure the name from registry value. - go to start -> run and give regedit there which will open the Registry editor window. Fig 0.1 - Click on HKEY_LOCAL_MACHINIE - Expand SOFTWARE - Expand Microsoft - Expand Microsoft SQL Server - Go to Instance name to find out the actual instance name.        Fig 0.2

It is easy to map all the files in a specific disk folder and fetch the data for further processing using SSIS.  SSIS is having Foreach Loop container, drag this task tool from the tool box to the work area. For the demonstration of the usage of this task, I am going to pull data from multiple CSV files copied into folder in my system M:\Jithesh. Fig 0.1 Map the folder name in Foreach container properties and decide what kind o files we need to use it for data collection. it can find any type of files inside this folder or we can restrict based on our requirement. here I would like to consider only CSV files.  Fig 0.2  As you seen in the Fig 0.2, provide the folder path and the type file in Files section, here as I mentioned *.csv picks all the CSV files in this path for the processing. we can retrieve the file name and pass to the task inside the Foreach loop in different ways, Fully qualified pick the

Here I would like to give one of the very useful tip in SSAS cube drill through report. One of my client required to have specific order of the fields in the report. Normally drill through report in cube comes as per the order of dimension selected. This can be achieved using Analysis Service Stored Procedure (ASSP). Please visit http://asstoredprocedures.codeplex.com/  for more details. Now download and register the DLL. steps you can find from the below link. http://asstoredprocedures.codeplex.com/wikipage?title=Installation%20Instructions Here I will demonstrate how to make use of ASSP to get the custom drill through. Once you have installed, it appears under Assemblies folder of solution explorer.  Fig 0.1 Now go to Action tab of the cube and create new action Fig 0.2 Provide name to the action as per the project standard, here I have given as Custom Drill Through. Target type property decides wh

If you are a ETL developer with SSIS you will be definitely faced this issue at least once. I have spent hours of my development efforts to sort out this issue so I hope this post will help developers in future. I was pulling the data from oracle database and pushing the information into MS-Excel report after cleansing the data. I had used ADO.net provider for oracle and given the required information such as user name, password, server name etc. Tested the connection and it was perfect. But when I executed the whole package together it started throwing this error. As every developer search for this issue in internet, I also spent hours to get the answer for my issue but most of the answers says problem fixed by doing below changes to the SSIS packages.    - Change the Delay validation  property of the task true so that it will not validate the connection       immediately.    - Change protection level of the package to DontSaveSensitive    - Change Run64BitRunTime property