SQL SERVER – How to Catch Errors While Inserting Values in Table

Question: “I often get errors when I insert values into a table, I want to gracefully catch them, how do I do that.”

Answer: Very simple. Just use TRY… CATCH. Here is the simple example of TRY…CATCH I have blogged earlier when it was introduced.

Here is the example, I have build from the earlier blog post where user can catch the error details during inserting value in table.

First, we will create a sample table.

CREATE TABLE SampleTable (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO

Now we will attempt to insert value in this table which will throw errors and the same error we will catch into the table.

BEGIN TRY
INSERT INTO SampleTable (Col)
SELECT 'FourthRow'
UNION ALL
SELECT 'FifthRow---------'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

The second row of the above table will throw an error as the length of the row is larger than the column in which we are inserting values. It will throw an error and the same error will be caught via TRY…CATCH and it will be displayed in the SELECT statement. Here is the result set.

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

About these ads

SQL SERVER – Fix Error 7202 Could not find server in sys.servers. Verify that the correct server name was specified

The other day I received following error when I tried to restore a database from one server to another server.

Msg 7202, Level 11, State 2, Line 1
Could not find server ‘MyLinkedServer’ in sys.servers. Verify that the correct server name was specified.

Well, this is quite a popular error one receive when they attempt to restore database containing references of the linked server. The solution is to create a link server and restore database. Here is the quick script which can fix your error.

EXEC sp_addlinkedserver @server='MyLinkedServer'
EXEC sp_addlinkedsrvlogin 'MyLinkedServer', 'false', NULL, 'MyUserName', 'MyPassword'

Well, that’s it. Sometimes the solution is much simpler, even though the original problem looks complicated.

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

SQL SERVER – Convert Old Syntax of RAISEERROR to THROW

I have been quite a few comments on my Facebook page and here is one of the questions which instantly caught my attention.

“We have a legacy application and it has been a long time since we are using SQL Server. Recently we have upgraded to the latest version of SQL Server and we are updating our code as well.

Here is the question for you, there are plenty of places we have been using old style RAISEERROR code and now we want to convert it to use THROW. Would you please suggest a sample example for the same.”

Very interesting question. THROW was introduced in SQL Server 2012 to handle the error gracefully and return the error message. Let us see quickly two examples of SQL Server 2012 and earlier version.

Earlier Version of SQL Server

BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(2000), @ErrorSeverity INT
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY()
RAISERROR (@ErrorMessage, @ErrorSeverity, 1)
END CATCH

SQL Server 2012 and Latest Version

BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
THROW
END CATCH

That’s it! We are done!

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

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)

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)