SQL Server and MS BI With a Geek Touch

RAISERROR statement is used to return error messages to the business applications that executes SQL statements. The usual errors returned by the SQL server may not make much sense to the business applications users hence we overwrite it by using RAISERROR to display meaningful messages. The statement uses same format as a system error or warning messages generated by SQL server.

You can return a user-defined error message by using RAISERROR.  The general syntax is as below

RAISERROR ( { msg_id | msg_str | @local_variable }

    { ,severity ,state }

    [ ,argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

ExamplesRAISERROR (‘Error raised in TRY block.’, — Message text.

Read more…

A TRY ….CATCH construct includes a TRY block followed by a CATCH block. The TRY block contains a set of transact SQL statements. If an error occurs in any statements of the TRY block, the control is passed to the CATCH block, which contains another set of SQL statements.

The CATCH block contains SQL statements that executes only during errors in TRY block. The general syntax of the TRY…CATCH block is as shown below

BEGIN TRY

                <SQL Statements…..>

                <SQL Statements…..>

END TRY

BEGIN CATCH

                <SQL Statements…..>

                <SQL Statements…..>

END CATCH

  Read more…

In 64 bit operating systems, SSIS transformations ( especially excel) and tasks throws errors that could be annoying. You will often come across errors as shown below.

SSIS package “Package.dtsx” starting.

Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

Error: 0xC00F9304 at Package, Connection manager “Excel Connection Manager“: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. Read more…

You might have come across the error as highlighted below.

Cannot execute as the database principal because the principal "sec_user" does not exist, this type of principal cannot be impersonated, or you do not have permission.

This problem usually occurs when You back up a database from an instance of SQL Server 2005 and  then, you restore the database to an instance of SQL Server 2005 that is installed on another computer. The reason for the error is because SQL Server cannot find a login that matches the security identifier of the impersonated user.

The easiest way to solve this is to create a login and alter the user name/ principal used in the restored database to point to this login. The login can be created using a simple statement as below

CREATE LOGIN SEC_USER  WITH PASSWORD = 'YourPassword'

You can get more info on creating logins from http://technet.microsoft.com/en-us/library/ms189751.aspx .

now alter the principal/User in the restore database using the alter user script. A sample is as given below.

Alter User Sec_User with login  = SEC_USER;

This should solve the error caused by restoring the database. The error can also be a result of lack of permissions to the user. In these cases check / modify user permissions.