The Business Intelligence Blog

July 6, 2010

SSIS Error – The value violated the integrity constraints for the column

Filed under: SSIS — Tags: , , , — Shahfaisal @ 5:51 pm

This blog post has been moved to http://shahfaisalmuhammed.blogspot.com/2011/09/ssis-error-value-violated-integrity.html

 

Advertisements

March 31, 2010

Error handling in SQL Server

Filed under: SQL Server Database — Tags: , , , , , , — Shahfaisal @ 10:41 pm

SQL Server provides the following functions/commands to help in troubleshooting Transact-SQL code:

@@ERROR function
The @@ERROR function is  used to capture the number of an error generated by the previous Transact-SQL statement. A value of 0 indicates success. Though this function is quite handy, it is a little tricky because its value gets updated for every command. So you will have to save the value of this function to a variable to make use of it in the code.

@@ROWCOUNT function
The @@ROWCOUNT function returns the number of rows affected by the last Transact-SQL statement. Like the@@ERROR function, the value of @@ROWCOUNT gets updated for every command but can be captured in variable for use in the code.

RAISERROR function
This function can be used to return custom error messages when troubleshooting errors in Transact-SQL code. Here is the syntax:

RAISERROR (msg_ id or msg_str, severity, state, optional arguments) WITH option

  • msg_id – A user-defined error message number stored in the sys.messages .
  • message – A user-defined error message.
  • severity – The severity level associated with the message. The severity code used you’ll use most of the time will be 16 indicating critical error. Please note that 11 is the minimum severity that will cause a CATCH block to fire.
  • state – error state
  • Argument – Parameters used in the substitution for variables defined in msg_str  or the message corresponding to msg_id
  • WITH option – There are three values that can be used with this option:
  • LOG – Logs error in the error log and the application log
  • NOWAIT – Sends the message immediately to the client
  • SETERROR – Sets the @@ERROR value to msg_id or 50000

Example:

USE [TestDB]
CREATE PROCEDURE [dbo].[UpdateEmployees]
(
@DepartmentID INT,
@EmpID INT
)
AS
BEGIN
DECLARE @ErrorMsg INT
DECLARE @RecordCount INT
UPDATE dimEmployees SET DepartmentID = @DepartmentID WHERE EmpID = @EmpID
SELECT  @ErrorMsg = @@ERROR, @RecordCount = @@ROWCOUNT
IF (@ErrorMsg <> 0 OR @RecordCount = 0)
BEGIN
RAISERROR (‘An error occured when updating the dimEmployees table’,16,1)
END
END

Try…Catch:
Try Catch technique consists of a Try block which contains the code that might cause an exception and a Catch block where the control will be transferred if an error is encountered in the Try block. If there are statements in the TRY block that follow the  statement that caused the error, they will not be executed.

BEGIN TRY
{ SQL statements block }
END TRY
BEGIN CATCH
{ Error Handling Code }
END CATCH

Example:

BEGIN TRY
SELECT 1/0;
PRINT ‘Statement after error’
END TRY
BEGIN CATCH
PRINT ‘Divide by zero error’
END CATCH

And here is the output:
Divide by zero error

December 26, 2009

Configuring Integration Services named instance

When you have named instance of SQL Server Integration Services, you will get the following error if you try to access the packages that are stored in the MSDB database.

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic “Configuring the Integration Services Service” in SQL Server 2008 Books Online.

Login Timeout Expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Named Pipes Provider: Could not open a connection to SQL Server [53]. (Microsoft SQL Server Native Client 10.0).

To get around this error, you need to configure the MsDtsSrvr.ini.xml file which is available under the path C:\Program Files\Microsoft SQL Server\100\DTS\Binn. Open the MsDtsSrvr.ini.xml file in notepad and edit it as shown in the figure below.

Please note that in the original file, the server name which is denoted by a ‘.‘ has been replaced by ServerName\InstanceName, where ServerName is the name of the server on which Integration Services has been installed and InstanceName is the named instance of Integration Services. After saving the xml file, be sure to start the Integration Services service for changes to take effect.

Managing multiple instances of SQL Server Integration services

This blog post has been moved to http://shahfaisalmuhammed.blogspot.com/2011/09/managing-multiple-instances-of-sql.html

Create a free website or blog at WordPress.com.