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

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>

Formatting SQL Server code

Need to format some lengthy difficult to read sql server code ? Here are a couple of online instant code-formatter :)

http://www.dpriver.com/pp/sqlformat.htm

http://www.sqlinform.com/free_online_sw.html

How to find SQL Server object

If you need to find, if so and so object (stored procedure, table, function) exists in the database, following queries could be of help to you :

SELECT * FROM SYSOBJECTS WHERE NAME LIKE '%ObjectName%'

If you know the type of object, even better :

SELECT * FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME LIKE '%ObjectName%'

-- 'U' denotes a User Table

If you need to find out if the object exists in any of the databases on the system :

EXEC sp_MSforeachdb 'SELECT * FROM ?..SYSOBJECTS WHERE XTYPE= ''U'' AND NAME LIKE '%ObjectName%'

Looping through comma-separated string in SQL Server

Ever needed a snippet to loop through the comma separated string in SQL Server?

Here is one :

DECLARE @tString AS NVARCHAR(MAX)
SET @
IF SUBSTRING(@tString, LEN(@tString)-1,1)<>','
SET @tString = @tString + ',' --add a comma to the end if it isn't there

SET @nCount = 0

WHILE CHARINDEX(',',@tString) > 0
BEGIN
SET @tSeparatedString = SUBSTRING(@tString,0, CHARINDEX(',',@tString))

SET @tString = SUBSTRING(@tString, CHARINDEX(',',@tString)+1, LEN(@tString) - @nCount) --remove the first item from the list

IF(@tSeparatedString IS NOT NULL AND @tSeparatedString <> '')
BEGIN
-- DO whatever with that separated string
END