Implementing Error handling raising User Defined Errors ( part 3):

In the last post we seen how to manage UDF errors (Add , Update , delete)  UDF error messages

in SYS.MESSAGES catalog. In this post we will look in-detail in to the Use RAISERROR function

in SQL Server to raise UDF errors. When I decided to write this, I then remembered that I written

a post for SQL server 2o12 Throw statement which will explain the differences of  raiserror and throw

, disadvantages of raiserrors and how to implement raiserror and throw, and how to raise UDF

errors , so have look at this post

https://sqljunkieshare.com/2011/12/10/throw-statement-tsql-2012/

 

but I will go through  some aspects of Raiserror that I didn’t cover  in the above post

lets start with syntax of raiseerror

RAISERROR ( { msg_id | msg_str | @local_variable }
{,severity,state }
[,argument [,…n ] ] )
[ WITH option [,…n ] g

 

We talked about Msg_id in the earlier post, we will look at the msg_str

msg_str
Is a user-defined message with formatting similar to the printf function in the C standard library. The error message can have a maximum of 2,047 characters. If the message contains 2,048 or more characters, only the first 2,044 are displayed and an ellipsis is added to indicate that the message has been truncated.

 

msg_str is a string of characters with optional embedded conversion specifications. Each conversion specification defines how a value in the argument list is formatted and placed into a field at the location of the conversion specification in msg_str. Conversion specifications have this format:

% [[flag] [width] [. precision] [{h | l}]] type

flag

Is a code that determines the spacing and justification of the substituted value.

Code Prefix or justification Description
– (minus) Left-justified Left-justify the argument value within the given field width.
+ (plus) Sign prefix Preface the argument value with a plus (+) or minus (-) if the value is of a signed type.
0 (zero) Zero padding Preface the output with zeros until the minimum width is reached. When 0 and the minus sign (-) appear, 0 is ignored.
# (number) 0x prefix for hexadecimal type of x or X When used with the o, x, or X format, the number sign (#) flag prefaces any nonzero value with 0, 0x, or 0X, respectively. When d, i, or u are prefaced by the number sign (#) flag, the flag is ignored.
‘ ‘ (blank) Space padding Preface the output value with blank spaces if the value is signed and positive. This is ignored when included with the plus sign (+) flag.

width

Is an integer that defines the minimum width for the field into which the argument value is placed. If the length of the argument value is equal to or longer than width, the value is printed with no padding. If the value is shorter than width, the value is padded to the length specified in width.

An asterisk (*) means that the width is specified by the associated argument in the argument list, which must be an integer value.

precision
Is the maximum number of characters taken from the argument value for string values. For example, if a string has five characters and precision is 3, only the first three characters of the string value are used.

For integer values, precision is the minimum number of digits printed.

An asterisk (*) means that the precision is specified by the associated argument in the argument list, which must be an integer value.

{h | l} type

Is used with character types d, i, o, s, x, X, or u, and creates shortint (h) or longint (l) values.

Type specification Represents
d or i Signed integer
o Unsigned octal
s String
u Unsigned integer
x or X Unsigned hexadecimal

 

option
Is a custom option for the error and can be one of the values in the following table.

Value Description
LOG Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.
NOWAIT Sends messages immediately to the client.
SETERROR Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.

 

 

Note:

Raiserror will not work if it is used

  • Outside the scope of any TRY block.
  • With a severity of 10 or lower in a TRY block.
  • With a severity of 20 or higher that terminates the database connection.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Code:

DECLARE @LOGINNAME VARCHAR(50) = ‘testuser’
DECLARE @SERVER VARCHAR(50) = ‘testserver’
–SELECT @LOGINNAME = ORIGINAL_LOGIN()
–A custom error message using arguments
RAISERROR (‘This is a custom error message.
Login: %s,
Language: %s,
SPID: %u,
Server Name: %s’, 5,1,
@LOGINNAME, @@LANGUAGE, @@SPID, @SERVER) WITH LOG

Implementing CLR User defined Aggregates in SQL Server

In this post we will learn how to implement UDF CLR Aggregates ,

In order to implement CLR UDF aggegate assembly we need to follow set of rules  or requirements in technical terms, if any of rules are broken then it is not a valid

CLR UDF

  1. Like any other CLR assembles in SQL Server, a CLR UDF  Aggregate should have a attribute called SqlUserDefinedAggregat

  2.    In order to save the Intermediate state of Aggregation we use Four methods in the Aggregation

    1. Init
    2. Accumulate
    3. Merge
    4. Terminate

Init : 

Syntax :  public void Init();

Once this method is invoked the Query processor Initializes the aggregation , for every aggregation we need to invoke this method.

Accumulate:

Syntax: public void Accumulate ( input-type value[, input-type value, …]);

Input type is any managed sql server data types

This where actual data aggregation occurs , it may be summing or multiplying , counting.

Merge:

This method can be used to merge another instance of this aggregate class with the current instance. The query processor uses this method to merge multiple partial computations of an aggregation.

Terminate:

public return_type Terminate();

This method completes the aggregate computation and returns the result of the aggregation. The return_type should be a managed SQL Server data type.

The basic aggregate also has four attributes which are described in Andy Novak’s article.

Attribute Comment
Format This is a compulsory enum with options Native, Unknown and UserDefined.
IsInvariantToNulls Setting this to true tells the aggregate that it doesn’t care whether or not values are null or not, it will always produce the same result.
An example of an aggregate where the value for this is false is COUNT(field) which only counts instances that are NOT NULL
IsInvariantToDuplicates Setting this to true tells the aggregate that it will produce the same results even if there are duplicate entries.
MAX() is a good example of this as no matter how many duplicates there are the MAX value will still be the MAX value.
IsInvariantToOrder Setting this to true affects how SQL Server calls the Merge method of the aggregate.
If you had a string concatenation aggregate that was parallelised then it is vitally important that the streams merge back together in the correct order.

 

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct CountVowels
{
    // count only the vowels in the passed-in strings
    private SqlInt32 countOfVowels;

    public void Init()
    {
        countOfVowels = 0;
    }

    public void Accumulate(SqlString value)
    {
        // list of vowels to look for
        string vowels = "aeiou";

        // for each character in the given parameter
        for (int i=0; i < value.ToString().Length; i++)
        {
            // for each character in the vowels string
            for (int j=0; j < vowels.Length; j++)
            {
                // convert parameter character to lowercase and compare to vowel
                if (value.Value.Substring(i,1).ToLower() == vowels.Substring(j,1))
                {
                    // it is a vowel, increment the count
                    countOfVowels+=1;
                }
            }
        }
    }

    public void Merge(CountVowels value)
    {
        Accumulate(value.Terminate());
    }

    public SqlString Terminate()
    {
        return countOfVowels.ToString();
    }
}

After deploying the aggregate, test it by executing it on the SQL Server and verifying the correct data is returned.

This query returns a result set of the vowel count for all the values in the LastNames column in the Contact table.

SELECT LastName, COUNT(LastName) AS CountOfLastName, dbo.CountVowels(LastName) AS CountOfVowels
FROM Person.Contact
GROUP BY LastName
ORDER BY LastName

Implementing SQL CLR objects in SQL Server

Using  CLR integration we can create

  • CLR Stored Procedure
  • CLR User Defined Function (Scalar and Table valued)
  • CLR Trigger
  • CLR User defined data types
  • CLR Aggregates

Let see how to create a CLR object using Visual studio

Before even developing a CLR object  , we have to know what version of .Net framework is sql server is using for the CLR environment

I using the Sql sever 2008 , we can see here CLR runs in .Net framework v2

With the .net framework 4.0 we now have the ability to load two or more distinct versions of the CLR within a single process. In previous releases of the .net framework, a process could only load a single instance of the CLR. Given this restriction the CLR team recommended that hosts, such as SQL Server, use the LockClrVersion function to determine the version of the CLR to load prior to initialization. So although we are developing our objects in .Net framework v3.5 we will be still able add the assembly to the sql server

 

 

 

 

 

Creating SQL CLR project

or

CREATE ASSEMBLY [SQL CLR]
AUTHORIZATION [dbo]
FROM ‘C:\Users\akhil\Documents\Visual Studio 2010\Projects\SQL CLR\SQL CLR\bin\Debug\SQL CLR.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS

GO;

 

 

 

 

 

 

 

 

 

 

 

 

 

TITLE: Microsoft SQL Server Management Studio
——————————

Create failed for SqlAssembly ‘SQL CLR’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0026+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+SqlAssembly&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

CREATE ASSEMBLY for assembly ‘SQL CLR’ failed because assembly ‘SQL CLR’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. (Microsoft SQL Server, Error: 10327)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=10327&LinkId=20476

How to achieve Initcap functionality in SSIS , and how to use FINDSTRING(),

How to use FINDSTRING() SSIS

FINDSTRING() works as the same as CHARINDEX() in TSQL

it returns the position of character or the string in side the string that we want to search

Syntax : FINDSTRING( “search string”,”searching term”,occurrence)

to better understand we will use a simple example in validating a email address, so for the email to be valid it needs to have at least one @ character

in the email address , if not its considered as not a valid address

As we learned how to use FINDSTRING() now we will use this functionality to get the initcap functionality in SSIS , Initcap is a cool feature in oracle when this function used,

The first letter of each word into uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric

This is how it is used in Oracle example

The following example capitalizes each word in the string:

\\***** PL\SQl****\\\
SELECT INITCAP(‘the soap’) “Capitals” FROM DUAL;

Capitals
———
The Soap
\\*** PL/SQl*****\\

Now we will achieve this in SSIS using FINDSTRING, TRIM, UPPER, and LOWER functions in derived columns

In the derived column we are using

(UPPER(SUBSTRING(TRIM(Name),1,1)) + TRIM(LOWER(SUBSTRING(TRIM(Name),2,FINDSTRING(TRIM(Name),” “,1) – 1 < 0 ? LEN(TRIM(Name)) : FINDSTRING(TRIM(Name)," ",1) – 1)))) + " " + (FINDSTRING(TRIM(Name)," ",1) – 1 < 0 ? " " : UPPER(SUBSTRING(TRIM(SUBSTRING(TRIM(Name),FINDSTRING(TRIM(Name)," ",1),LEN(TRIM(Name)))),1,1)) + TRIM(LOWER(SUBSTRING(TRIM(Name),FINDSTRING(TRIM(Name)," ",1) + 2,LEN(TRIM(Name))))))

The above expression looks big but it is very simple

In this we are taking the first letter of the word and making it upper case , if there is a empty in the string we are considering as a second word and then we are selecting the

first letter of the world to upper case and all other characters are mapped to lower case