Remove Leading and Trailing Spaces in SQL Server

Remove Leading and Trailing Spaces in SQL Server

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


         



SQL Server has two built-in string trimming functions, LTRIM and RTRIM, these remove leading and trailing spaces
 respectively. There is no built-in function to remove arbitrary leading
 and/or trailing whitespace, including TABs, CR and/or LFs.

The built-in functions return the datatype of their input, whereas 
the UDFs below return explicit datatypes. I've put together a few 
variations of my trim whitespace function, one for non-unicode text, and
 a basic and advanced one for unicode text. For the sake of performance 
and convenience I've treated all control characters (0 - 31) as 
whitespace.

Note that from a performance point of view these UDFs will be 
considerably slower than the built-in functions so don't use them inside
 a large loop or when returning a large rowset unless you're sure you'll
 have non-space whitespace.

These functions are suitable for SQL Server 2005 and 2008 as well as 
SQL Server 2000 (just remove the RETURNS NULL ON NULL INPUT function 
option and convert the (max) types to a suitable fixed length type).





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

create function dbo.udf_NTrimWhitespace(@text nvarchar(max)) returns nvarchar(max) with returns null on null input
/*
** Return the input with leading and trailing whitespace removed.
*/
as
begin

   declare @i int; set @i = 1;
   declare @j int; set @j = len(@text); -- remember that len doesn't include trailing spaces.
   declare @u int;

   while (@i < @j)
   begin
      set @u = unicode(substring(@text, @i, 1));
      if (@u > 0x0020 and @u not in ( 0x0085, -- NEL (control character next line)
                                      0x00A0, -- NBSP (NO-BREAK SPACE)
                                      0x1680, -- OGHAM SPACE MARK
                                      0x180E, -- MONGOLIAN VOWEL SEPARATOR
                                      0x2000, -- EN QUAD
                                      0x2001, -- EM QUAD
                                      0x2002, -- EN SPACE
                                      0x2003, -- EM SPACE
                                      0x2004, -- THREE-PER-EM SPACE
                                      0x2005, -- FOUR-PER-EM SPACE
                                      0x2006, -- SIX-PER-EM SPACE
                                      0x2007, -- FIGURE SPACE
                                      0x2008, -- PUNCTUATION SPACE
                                      0x2009, -- THIN SPACE
                                      0x200A, -- HAIR SPACE
                                      0x200B, -- ZERO WIDTH SPACE
                                      0x2028, -- LS (LINE SEPARATOR)
                                      0x2029, -- PS (PARAGRAPH SEPARATOR)
                                      0x202F, -- NNBSP (NARROW NO-BREAK SPACE)
                                      0x205F, -- MMSP (MEDIUM MATHEMATICAL SPACE)
                                      0x3000, -- IDEOGRAPHIC SPACE
                                      0xFEFF  -- ZERO WIDTH NO-BREAK SPACE
                                    )
         )
         break;
      set @i = @i + 1;
   end

   while (@j >= @i)
   begin
      set @u = unicode(substring(@text, @j, 1));
      if (@u > 0x0020 and @u not in (0x0085,0x00A0,0x1680,0x180E,0x2000,0x2001,0x2002,0x2003,0x2004,0x2005,0x2006,0x2007,0x2008,0x2009,0x200A,0x200B,0x2028,0x2029,0x202F,0x205F,0x3000,0xFEFF))
         break;
      set @j = @j - 1;
   end

   return substring(@text, @i, @j - @i + 1);

end
go

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

create function dbo.udf_NTrimWhitespace(@text nvarchar(max)) returns nvarchar(max) with returns null on null input 
as
/*
** Return the input with leading and trailing whitespace removed.
*/
begin

   declare @i int; set @i = 1
   declare @j int; set @j = len(@text) -- remember that len doesn't include trailing spaces.

   while (unicode(substring(@text, @i, 1)) < 33 and @i < @j) set @i = @i + 1
   while (unicode(substring(@text, @j, 1)) < 33 and @j >= @i) set @j = @j - 1

   return substring(@text, @i, @j - @i + 1)

end
go

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

create function dbo.udf_TrimWhitespace(@text varchar(max)) returns varchar(max) with returns null on null input 
as
/*
** Return the input with leading and trailing whitespace removed.
*/
begin

   declare @i int; set @i = 1
   declare @j int; set @j = len(@text)

   while (ascii(substring(@text, @i, 1)) < 33 and @i < @j) set @i = @i + 1
   while (ascii(substring(@text, @j, 1)) < 33 and @j >= @i) set @j = @j - 1

   return substring(@text, @i, @j - @i + 1)

end
go

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

Source:http://www.rmjcs.com/SQLServer/TSQLFunctions/TrimWhitespace/tabid/829/Default.aspx

Email Sending in asp using Cdosys component in windows 2008 Server and IIS7

Email Sending in asp using Cdosys component in windows 2008 Server and IIS7
-------------------------------------------------------------------------------------------------------------------------

Generally Every developer uses the following code

dim oMail, oMailConfig
Set oMail = Server.CreateObject("CDO.Message")
Set oMailConfig = Server.CreateObject ("CDO.Configuration")
oMailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost"
oMailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
oMailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
oMailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
oMailConfig.Fields.Update
Set oMail.Configuration = oMailConfig
oMail.From = "youremailid"
oMail.To = "toemailid"
oMail.Subject = "testing subject"
oMail.HTMLBody = "testing html body"
oMail.Send
set oMail=nothing

What is SMTP Server name
1) localhost
2) computername
3) smtp.servername.com

What is SMTP Server Port
1) 25

What is send using
1) 1=email sending using pickup directory
2) 2=email sending over the network

What is SMTP Use SSL
1)smtpusessl=true
2)smtpusessl=false

What is SMTP Authentication
1)If no value is set for this field, anonymous (no authentication) is assumed.
2) Authentication=1 (Then Authentication is required)

Kill remote desktop connections

Kill Remote Desktop connections (or) View Active Remote Desktop Connections with Controlpanel

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

Start -> Programs -> Adminstrative Tools -> Terminal Services Manager

Right-click on the session and select logoff or reset.
----------------------------------------------------------------------------------------------------------------------