Ole Automation Procedures - SQL Server special settings

Special settings that you need to apply in some cases like sending out SMSes through SQL Server ...

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

Generic lists comparer - gridview sorting

Following is a good-code-snippet to sort the generic lists in C#.

Another snippet presented right below it can be used to sort the GridView in ASP .net.

-------------------------------------------------------------------------------------

public class GenericComparer<T> : IComparer<T>

{

    private SortDirection sortDirection;  

    public SortDirection SortDirection

    {

        get { return this.sortDirection; }

        set { this.sortDirection = value; } 

    }

    private string sortExpression; 

    public GenericComparer(string sortExpression, SortDirection sortDirection)

    {

        this.sortExpression = sortExpression;

        this.sortDirection = sortDirection; 

    }

    public int Compare(T x, T y)

    {

        PropertyInfo propertyInfo = typeof(T).GetProperty(sortExpression);

        IComparable obj1 = (IComparable)propertyInfo.GetValue(x, null);

        IComparable obj2 = (IComparable)propertyInfo.GetValue(y, null);

        if (SortDirection == SortDirection.Ascending)

        {

            return obj1.CompareTo(obj2);

        }

        else return obj2.CompareTo(obj1); 

     }

}

--------------------------------------------------

Capture the "sorting" event of the ASP .NET gridview and put the following code in the event handler. You need to have the above GenericComparer class in order to make this work. Enjoi!

        private SortDirection GridViewSortDirection

        {

            get

            {

                if (ViewState["sortDirection"] == null)

                    ViewState["sortDirection"] = SortDirection.Ascending;

                return (SortDirection)ViewState["sortDirection"];

            }

            set { ViewState["sortDirection"] = value; }

        }

        protected void gvEvents_Sorting(object sender, GridViewSortEventArgs e)

        {

            if (GridViewSortDirection == SortDirection.Ascending)

            {

                GridViewSortDirection = SortDirection.Descending;

            }

            else

            {

                GridViewSortDirection = SortDirection.Ascending;

            }

            events.Sort(new GenericComparer<EWent.BusinessEntities.Event>(e.SortExpression, GridViewSortDirection));

            PopuplateEventsGridview();

        }

--------------------

Looping through temporary table and CASE statements

Below is a nice stored procedure written to demonstrate looping through a temporary table and using the CASE statement.

Hope this helps.

Ishan

 <pre style="font-family:arial;font-size:12px;border:1px dashed #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;background-image:URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhg8j6CvyCjQOs5FbK6wq5m1sqYLT9_LTBPyeVOoHj6TzkggyesWA5YsBav67dqWRLGY24VJqSiSFmkt99TTg79vfwDwWdmSywnWEW4stFYhHpIhcdQJnxdNjvnIIlSTMFfkV9OIj3I-s_e/s320/codebg.gif);padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;"> -- Insert statements for procedure here   

CREATE TABLE #temp_tblMessage

(

nId INT IDENTITY(1,1),

aEventId INT,

nGlobalCaseId INT,

mMsgContent NTEXT,

mMsgHTMLContent NTEXT,

dCDServerTime DATETIME,

nType INT,

nMediaType INT,

nStatus INT,

)

INSERT #temp_tblMessage

SELECT aEventID,

nGlobalCaseID,

mMsgContent,

mMsgHTMLContent,

dCDServerTime,

nType,

nMediaType,

nStatus

FROM dbo.tblMessage

WHERE nGlobalCaseId = @nInteractionId AND (nType = 32 OR nType = 64 or nType = 96)

DECLARE @tFullMessage NVARCHAR(MAX)

DECLARE @tHTMLMessageContent NVARCHAR(MAX)

DECLARE @tPlainMessageContent NVARCHAR(MAX)

DECLARE @tMessage NVARCHAR(MAX)

DECLARE @tMessagePrefix NVARCHAR(MAX)

DECLARE @dMessageDatetime DATETIME

DECLARE @nType INT

DECLARE @nCount INT

DECLARE @nIndex INT

SET @tFullMessage = ''

SET @nIndex = 0

SELECT @nCount = Count(nId) From #temp_tblMessage

WHILE (@nCount > @nIndex)

BEGIN

SET @nIndex = @nIndex + 1

SELECT @dMessageDatetime = dCDServerTime,

@tPlainMessageContent=mMsgContent,

@tHTMLMessageContent = mMsgHTMLContent,

@tMessagePrefix =

CASE nType

WHEN 32 THEN 'Message sent by you at : '

WHEN 64 THEN 'Message from System at : '

WHEN 96 THEN 'Message from System at : '

END

FROM #temp_tblMessage

WHERE nId = @nIndex

SET @tMessagePrefix = @tMessagePrefix + CAST(@dMessageDatetime AS NVARCHAR(255)) + CHAR(13)

SET @tMessage = ISNULL(NULLIF(@tPlainMessageContent, ''), @tHTMLMessageContent)

SET @tMessage = @tMessagePrefix + @tMessage

SET @tFullMessage = @tFullMessage + @tMessage + CHAR(13)

END

SET @tMessageContent = @tFullMessage

</code></pre>

Ever got stuck due to SQL Server processes ?

Ever got stuck due to SQL Server processes ?

Here is an easy way of finding the culprit and resolving :)

 select * from master..sysprocesses where blocked <> 0  

dbcc inputbuffer(<processid>)

kill <processid>