SQL SERVER – Fix Error – Package ‘Microsoft SQL Management Studio Package’ failed to load in SQL Server Management Studio

I recently formatted my computer and reinstalled my machine and I end up on following error:

Package ‘Microsoft SQL Management Studio Package’ failed to load

I was able to fix the error quickly, but while I was searching online, I noticed quite a few of the people have similar error and they struggle to fix it. I have decided to list a few of the ways to fix the error here.

Method 1: Reinstall .NET Framework

Method 2: Start SSMS with Admin privileges

Method 3: Reinstall SQL client with either command prompt or with wizard

Method 4: Delete the registry HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools and try to load the server again.

Let me know if there is any method which works to fix this error and I have missed it.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Fix – Agent Starting Error 15281 – SQL Server blocked access to procedure ‘dbo.sp_get_sqlagent_properties’ of component ‘Agent XPs’ because this component is turned off as part of the security configuration for this server

SQL Server Agent fails to start because of the error 15281 is a very common error.

When you start to restart SQL Agent sometimes it will give following error.

SQL Server blocked access to procedure ‘dbo.sp_get_sqlagent_properties’ of component ‘Agent XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, search for ‘Agent XPs’ in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)

To resolve this error, following script has to be executed on the server.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

When you run above script, it will give a very similar output as following on the screen.

Now, if you try to restart SQL Agent it will just work fine.

That’s it! Sometimes there is a simpler solution to complicated error.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix: Error: Compatibility Level Drop Down is Empty

I currently have SQL Server 2012 and SQL Server 2014 both installed on the same machine. My job requires me to travel a lot and I like to travel light. Hence, I have only one computer with all the software installed in it. I can install Virtual Machines but as I was able to install SQL Server 2012 and SQL Server 2014 side by side, I just went ahead with that option.

Now one day when I opened up my SQL Server 2014 and went to the properties of the my database, I realized that the dropdown box for Compatibility level is empty. I just can’t select anything there or see what is the current Compatibility level of the database. This was the first time for me so I was bit confused and I tried to search online. Upon searching online I realize that if I was not the first, there are very few questions on this subject on various forums as well as there is no convincing answer to this problem online. That means, I was pretty much first one to face this error.

See the image of the situation I was facing.

Now I decided to resolve this issue as soon as I can. I spent a few minutes here and there and realize my mistake.

I had connected to SQL Server 2014 instance from SQL Server 2012 Management Studio. Hence, I was not able to see any compatibility related settings. Once I connected to SQL Server 2014 instance with SQL Server 2014 Management Studio – this issue was resolved.

Well, simple things sometimes keep us very busy.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix – Error 1759 Computed column in table is not allowed to be used in another computed-column definition

This is a very common error with beginning developers when they start working with computed columns.

When a new developer finds a joy of computed column, it is very natural to use the same computed column for other columns. However, a computed column cannot be used in another computed column or it will give an error.

Here is a simple reproduction of the scenario.

-- Create table
CREATE TABLE Table1 (Col1 INT, Col2 AS Col1*10);

Now we will add another computed column Col3 based of Col2 (which is computed column itself).

-- Create Column
ALTER TABLE Table1
ADD Col3 AS Col2*10;

When we execute above statement, it will give us following error.

Msg 1759, Level 16, State 0, Line 2
Computed column ‘Col2′ in table ‘Table1′ is not allowed to be used in another computed-column definition.

This is because we can not use computed columns in another computed column. This kind of referencing is not allowed in SQL Server.

Workaround:

Here is a quick workaround. Our end goal is to create another column which is multiply of Col2 by 10. Now Col2 is Col1 multiplied by 10. That means Col3 is Col1 multiplied by 100. Let us create a new column which is Col1 multiplied by 100.

-- Create Column
ALTER TABLE Table1
ADD Col3 AS Col1*100;

That’s it! We are done.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Error – Resolution – Could not allocate space for object in database because the filegroup is full

Just a day ago on my local box I received following error.

Could not allocate space for object ‘<object_name>’ in database ‘<database_name>’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I was a bit surprised as this was not a production server or even real server. It was my laptop with SQL Server installed in it for testing and presentation purpose. I was a bit shocked and decided to figure out what is wrong with my SQL Server. Just like everyone I searched online and I end up having few solutions, however, after a while I realized then what exactly I was missing. However, I encourage all of you to read various steps I took to reach to a solution to this problem.

Solution / Fix :

Step 1: I checked if my Hard Drive has more space available.

If your Hard Drive is full, please empty out any unnecessary stuff. This may not directly fix your problem, but it is a necessary exercise to do before you do Step 2.

Step 2: Delete unnecessary object from your file group.

If your primary filegroup (in most cases) or filegroup which has generated this error is restricted to fix size, you may get this error. You have two workarounds here.

Step 2a: Delete objects from your filegroup which you do not need. You can move objects to another filegroup if you have multiple filegroups.

Step 2b: Increase the fixed size of your primary filegroup.

Step 3: Check your SQL Server version.

SQL Server Express version 2008 and earlier had the limitation of 4GB maximum Database size. SQL Server Express 2008 R2 and onwards have limitation 10GB maximum database size.

In my case, I was running SQL Server Express version, hence I had faced above error. I moved my database to standard edition and everything went fine.

Reference: Pinal Dave (http://blog.sqlauthority.com)

MYSQL – Could not Drop Object [Content] (‘Cannot delete or update a parent row: a foreign key constraint fails’, 1217) DROP DATABASE DatabaseName

If you are using MySQL following, you must have faced following error when you want to drop a table or database. This error happens when we try to drop a table or database which has a foreign key constraint associated with another table, schema or database.

Could not Drop Object [Content] (‘Cannot delete or update a parent row: a foreign key constraint fails’, 1217) DROP DATABASE DatabaseName

Here is a simple workaround to this problem. Disable the keys right before you drop a table or a database. Remember to practice this on the database which you are confident to drop it.

SET FOREIGN_KEY_CHECKS=0;
DROP DATABASE humanresources;
DROP TABLE MyTable;
SET FOREIGN_KEY_CHECKS=1;

Let me know if you are using any method for this purpose.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix – Login failed for user ‘username’. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

Earlier this week, I was on vacation and did not check my emails for three days and when I returned, I found six different emails from 4 different people asking for the same problem.

When they attempted to login using their application to SQL Server they faced following error:

Login failed for user 'username'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

This is quite a common error and can happen when incorrect username is communicated to SQL Server. The fix is to provide correct username. This can also happen if you are attempting to login to SQL Server with SQL Username but it is only configured to connect to Windows Login. You can quickly fix this error by following steps listed below.

Solution/Workaround/Fix:

Open SQL Server Management Studio and right click on Server Node and select Properties

Now go to Security Tab and go to Server Authentical Area. Select SQL Server and Windows Authentication Mode.

You should be able to login with your SQL Server username. If you are not able to login with your SQL Server username after following above steps, it is quite possible your username is incorrect, please check again.

Reference: Pinal Dave (http://blog.sqlauthority.com)