Processing Cube Dimension Incrementally

I didn't find much about how to process dimensions incrementally in a cube. To be clear, I am focusing on processing cube dimensions not dimension table. I have seen and work with different setting of the cube and work with "Process Update".

This process the data incrementally in the Cube dimensions.

If you using SSIS drag and drop analysis service item and set the setting as "Process Update".

sqlmanagment studio: cannot find one or more components. Please reinstall the application

 

This can 

(after installing vs2013)

SSMS Depends on Visual Studio 2010 IDE, which if not installed the SQL Server Setup will do, but if any version is present, the setup will ignore it, even at repair

Re Install, I figured that the SSMS needs Visual Studio 10 IDE to run, which I've removed by mistake after uninstalling VS 2010.

So, I Opened the Setup Media and searched for Visual Studio Setup. The .msi file run quitly and Filled the missing parts, and the Management Studio Run OK.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3dd6f643-720f-4878-9594-894da73efd46/sqlmanagment-studio-cannot-find-one-or-more-components-please-reinstall-the-application-after?referrer=http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3dd6f643-720f-4878-9594-894da73efd46/sqlmanagment-studio-cannot-find-one-or-more-components-please-reinstall-the-application-after?forum=sqlexpress 

I found that if you do not have the CD you can get the VS 2010 IDE component that SSMS 2012 relies on here:

http://www.microsoft.com/en-us/download/confirmation.aspx?id=1366

It's called Microsoft Visual Studio 2010 Shell (Isolated)

After installing that, SSMS runs for me again, Microsoft needs to add this install into their repair logic!

Remove duplicates from SQL Table

Alot of time duplicates needed to be deleted. 

Case scenario,  When one reocrd is touched it can have same data with only different dates and may end up with duplicates when joining with Id

I normally use the below script to clean it off.

with cte as (
 select
Id ,row_number()over(partition by Id order by Id ) rn
from dbo.Table
)

DELETE from cte where rn>1

System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file

I develop a package in SSIS, I need to use format excel sheets and I am sure many of you will using it because of business people needing data in pretty form. Any ways, till this part when I run from SSIS it works like a champ. But as soon as I scheduled in SQL agent on server it started to fail with below error.

Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'xxxxxlsx'. There are several possible reasons:    ? The file name or path does not exist.  ? The file is being used by another program.  ? The workbook you are trying to save has the same name as a currently open workbook.     at Microsoft.Office.Interop.Excel.Workbooks


After sometime when I was about to give up because the solutions I was seeing on the web either say go play with DcomConfig or hack registry keys yeah like some one is gonna let me do that on prod server.

Finally found MS document that hints the below procedure it works and best part don't need to mess with registry keys.

Solution(1)

Create folder called "Desktop" really dont know why but it works.

Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop” (for 64 bit Windows) or "C:\Windows\System32\config\systemprofile\Desktop” (for 32 bit Windows)

In my case I had created folder on both locations and give read write permissions on it

P.S

You will still see this error even if you have created the folder but the permissions are not correct.

Cache InterSystems Database And SSIS

This Post is important as I got an experienced with Cache Intersystems Database and SSIS.  Most of you who don't know about Cache database, this is not relational database. This Database is based on classes and objects and it reside in memory.

CACHE DB CONNECTION :

Cache DB is connected with ODBC connection only. You will find ODBC connection in ADO.NET component.

In my Case Tables were not loading into the SSIS. And I had no clue what are the table names so I have to use SQL command. If you are also in this situation then you can use Access DB it uses an easy way to connect and load to ODBC and it can show you all the tables. Another way is to use SSMS and use link serves to get to the ODBC. I personally would prefer to use Access because of easiness.

Follow the screen shots and once you setup the connection for Cache database in SSIS, Right click the ADO component and select properties and Set the connection manager.

This is should fix the Cache and other ODBC connections to SSIS.

The server principal " " is not able to access the database " " under the current security context. (Microsoft SQL Server, Error: 916)

The server principal " " is not able to access the database " " under the current security context. (Microsoft SQL Server, Error: 916)

This is the solution type of solution I found on the websites. Question is does it work, I dont't know because it didnt work in my condition so I really have no idea.

To resolve this issue:

1. After starting Management Studio 2008, select Databases in the Object Explorer once.

2. If the Object Explorer Details are not already showing, select View from the toolbar then select Object Explorer Details. 

3. Right click on any column header and make sure that “collation” is not checked. 

4. Refresh the database list by clicking the Refresh button located at the top of the Object Explorer Details panel.  You should have now have full access to your database at this point. 


Enjoy Have Bless day with MS SQL.

Could not Obtain Exclusive Lock On Database

Some time this error appears and First things comes in our mind is What has possibly gone wrong, I normally see this error when I am restoring the databases or overwrite a a dump database.

Since SQL is also a software and like every software it has its own way of operating and connecting with other entities. Let solve this issue, what steps will take care of this error.




1)  If the above error appears, close your Studio Management Console. Because sometimes its Studio Management that is holding a old connection. First and normally it fixes the problem.

2)  Second reason may come because of "Auto Close" property if turn "ON", Go in DB properties and turn off this property.

3) Check if any other window is open holding a connection to the specified DB. 


Use below script to turn "AUTO Close" off

SELECT DATABASEPROPERTYEX('Your DB' , 'IsAutoClose' ) AS [AutoClose]

GO

ALTER DATABASE [Your DB] SET AUTO_CLOSE OFF

GO

Some Daily Database Tips

Some Useful information for daily life DBA, Well I writing this post just to include things we as DBA need in everyday life you can find more information on other blogs and site this is something for information for new DBA guys. There are couple of commands that  can help us. Let looks us at commands. and Why we use them.

1)    sp_who

        Provides information about current users, sessions, and processes.

      sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]

          ACTIVE excludes sessions that are waiting for the next command from the user.

2)    sp_who2

Shows all the sessions that are currently established in the database.
sp_who2 also takes a optional parameter of a SPID. If a spid is passed, then the results of   sp_who2 only show the row or rows of the executing SPID.

3)    Kill

Terminates a user process that is based on the session ID or unit of work (UOW). If the specified session ID or UOW has a lot of work to undo, the KILL statement may take some time to complete.

4)    Sys

Sys is special system table as understandably by its name. What is purpose of Sys? Sys provide various functionalities like all table names in a database, schema level information , object and their information, etc. In short Sys is key player for for many system and database which you can find over here. Lets looks at some below.

5)    Sys.Table

Returns a row for each table object, currently only with sys.objects.type = U.

6)    Sys.Objects

Contains a row for each user-defined, schema-scoped object that is created within a database.

7)    Information_schema

Information schema is another SQL built in table, it has  schema level information.

An information schema view is one of several methods SQL Server provides for obtaining metadata. Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

WAMP Server Not Starting

For more than 3 hours I would like to put my head in the wall because WAMP server simply refuse to get online and I had no idea hat going on. So here comes action open google search and found out so many answers and guess nothing worked. I need some thing that can solve the issue I re-install the WAMP disable my IIS but no progress Finally I managed to make the WAMP work. And I will List you the steps and the some tips if that will help you. I am sure that will help and yeah we'll track down that rascal stopping WAMP to go online :)


First Thing,

1) WampServer > Apache > Service >Test Port 80. This give me the following message: 

Some Message will pop up like this and show you which app is using port 80
------------- Your port 80 is actually used by : 
Server: Microsoft-IIS/7.5 ------------- 
2) Open the Application close it and make sure when they start they start after WAMP.

3) Make Sure SSRS(MS SQL Reporting )  is shutdown if you are using WAMP . 

4) Disbale IIS if nothing works because IIS and WAMP are sometime conflicting with each other


UPDATE : 02/06/2013
Ok I am writing an update for the same post because this time WAMP server again give me a pain in the @$$. But this Time nothing stated above worked. So I decided to trouble shoot more because I always believe one or other way these things will work. So Get Ready If the above steps fail in your situation also.

5) One thing is clear and must be remember problem is not your computer, its the software battle going inside the PC makes conflict and the better one win. So Remembering this strategy. I Focus my attention to Web products.

6) As I was scrolling down the Services, I found web deployment agent, And I disable that Guess My WAMP started to work like a charm. So Consider making it disable if WAMP is having problems.

P.S. If WAMP still not start then Take down Web services and and restart wamp again and again. One time you will see it must start all cost.

Take Care Guy if I found more about WAMP I will update over here

UPDATE : 08/16/2013
All of the above did'nt work so I finally banged my head into the wall. Nothing above work.
Before I finally decided to move to Linux I gave last shot and open the services, Promise to my self I'll close all services one by one it should work something is holding it.

1) I close SQL services
2) World Web publishing 
And Guess it worked.

Execution cannot continue as the language dependent resource file

Arrrrrrr!! Yes that the first reaction came from my junior DBA. And I think it necessary as he is learning. Ok what to do now. Solution is straight and simple.

As you receive this error you will be prompted for  location where the SQL cannot find the required file.
Download those files easily available put in the folder specified and you are good to go.

Is nt this simple and straight? So Join our mailing for small but effective techniques.

DTS Packages in SQL 2005/2008

 I was given a task to import DTS packages in SQL 2008 which were imported successfully done.  However when my team tried to edit them, they would not open.   --The following error was displayed:  'SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature.  Microsoft.SqlServer.DtsObjectExplorerUI) .


This is so frustrating DTS package is imported and not opening I searched the web and didnt find much except people just trying to give their advice's for NONE that worked for me.


But afterwards I found an awesome article that can actually help many of the developer looking to import and convert old DTS into SSIS packages. But it didnt work well so I need to fix more things but that didn't mean the page was crap no it was good and gave alot of information. My blogger is having issue not uploading the link so pasting the information.

1) Install Sql Server 2000 DTS Designer Components using the following link

http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en

2) Install Sql Server 2005 backward compatibility components available with Microsoft SQL Server 2008 Feature Pack using the following link depending (x86,x64 and IA64)

http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

3) We need to change the Environment Variable for the Server so that c:\Program files(x86)\Microsoft Sql Server\80\Tools\Binn is ahead and all the sql 2008 paths comes at the end and we can verify it by opening the command prompt and firing the command  “path”

C:\>path

PATH=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\PROGRA~1\CA\SHARED~1\SCANEN~1;C:\Program Files\CA\eTrust Antivirus;C:\Program Files (x86)\Windows Imaging\;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\binn\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\

OR

If you are not able to change the path Environment variable you can follow the following steps

Manually Copy the following files

%Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlsvc.rll

To the following destination respectively

%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlsvc.rll

To make the DTS designer work under BIDS for SQL Server 2008, user will need to manually copy the following files

%Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlsvc.rll

To the following destination respectively

%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\semsfc.dll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\sqlgui.dll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\sqlsvc.dll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\sqlsvc.rll

Reference: http://msdn.microsoft.com/en-us/library/ms143755.aspx

4) Run the Repair for the sql server 2005 backward compatibility from Control Panel, Add/Remove Programs, Change
And now you should be able to open the DTS packages under SSMS.


For Trouble shooting if you have further problems Click this post 
 

Implementation of Mirror Database Using SSMS

Implementation of mirror database consist of special set of step that enables the SQL to perform mirroring.
In this post I would be discussing the Mirror implementation from SSMS which is easy and in our next post we discuss about T SQL implementation.

Steps:


1. Take full full back up of principal database using right click -)Task-)backup-) full
2.Take transaction log back up of principal database using right click -)Task-)backup-) transaction log
3. Open Mirror Server right click  Mirror  database-)Task-)restore-) full
4. Open Mirror Server right click  Mirror  database-)Task-)restore-) transaction logs

5.Open Principal server go Task-) Mirror -) configure-)Set the principal-)Set the mirror-)Set the witness (if you want automatic fail over to mirror database in case principal database die)

And yes above Five steps are enough to start your mirroring using SSMS.

In our post we discuss about T -SQL implementation of mirroring

Database Mirroring

Database Mirroring

Mirroring was intorduce in 2005 by Microsoft. It is a low cost low maintenance solution when I say low maintenance it only means it has degree less complex than the cluster. In Mirroring, Principal database is duplicated exactly to a Mirror which is clear from its name.

Let consider pros and cons for which some people like mirroring and some don't.

Pros:
1. It easy to implement.
2. It can ensure no data loss (if using High Safety mode)
3.It provides a secure copy of database separate from the production so if production goes down Mirror database kicks in immediately without using time for manual restoration.
4.it provide three modes of operations. Asynchronous , Synchronous without witness ,Synchronous with witness.

Cons:
Most people think Mirror Server is actually a waste of resource. Reason because it waiting for principal server to die malfunction  or gone for maintenance and then it can start. It something more then standby server and database.

Asynchronous : Principal database commit data without waiting for mirror database to commit. Data loss chance present.

Synchronous without witness : Principal database does not commit data before the mirror database. Therefore principal database waits for mirror database to commit first. No Data loss. Manual fail over to Mirror Database

Synchronous with witness:Principal database does not commit data before the mirror database. Therefore principal database waits for mirror database to commit first. No Data loss. Automatic fail over to Mirror Database.

Implementation of mirror Server will be discussed in the next and above will be linked with the post.

SQL Full Back ups SSMS and T SQL

Today I was visiting my professor in the school where I met a young guy who was looking to go and make career in database technology. He was not familiar with the SSMS  in depth and requested if I can teach him basics of T sql scripts and some normal operations of SSMS. As Below I was about to write the T sql script for backup that I teached him then I though it would be nice to share the SSMS backup also. So initially I will showing the SSMS backup and then follow by T sql script back up.

Backup Using SSMS

Right click the database -> task->backup->select the database browse the database file from it path and leave all worries to SSMS for restoring.

P.S. I am not discussing any errors right now and everything goes smooth you will find a back up of the database on the mentioned folder.

Back up Using T- SQL:

I am writing the most basic script to back up database reason to just to mention a basic script is to make new people familiar with the T sql and it operation not to make them confuse in ocean of  TSQL puzzles. Once you grab the Idea you would be making more complex scripts.

Backup Database <Database Name> to disk=N'Specify the location'

The italic words are keywords and must be written as they are.

One important point SQL language is not a programming language  it a Query language but it does not mean it does not uses any programming concept what I have learn the different its not as flexible as programming language but if you know how to use the SQL and Programming this fusion makes you an excellent programmer.

P.S. For New DBA's Please keep your recovery model to Full until you earn enough experience and knowledge for the other two models.

SQL DATABASE MODES OF OPERATION

SQL Server database following states but I will only write little about the first 4 as these are most occur scenario's

1)ONLINE (Available – online for data)

2)OFFLINE

3)RESTORING

4)RECOVERING

5)RECOVERY PENDING

6)SUSPECT

7)EMERGENCY (Limited Availability)

1)ONLINE (Available – online for data): Database is operational and live.

2)OFFLINE: Database is not available

3)RESTORING: Database is alive but not available for any outside operation. This states normally shows up when we leave the DB in norecovery mode.

4)RECOVERING:Database is alive but in a process of getting recovered.

SQL:Msg 3117, Level 16, State 1, Line 7 The log or differential backup cannot be restored because no files are ready to rollforward.

I was recently trying to perform a restoration which includes a full database backup and transaction log to be restored on mirror server. and I came up with this little interesting problem

Msg 3117, Level 16, State 1, Line 1

The log or differential backup cannot be restored because no files are ready to roll forward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

I am using SSIS package to deploy all of this for specail job to be run on mirror server and now we were restoring back. The solution is very simple

restore database <DB name> from disk=N'Your Location' with norecovery.

restore log <DB name> from disk=N'Your Location' with norecovery.

And Yes its done