Script DBMail Profiles

User Lowell posted a script to script out DBMail’s profile at this thread:
http://www.sqlservercentral.com/Forums/Topic982618-391-1.aspx

I include the script here for future reference:

USE msdb
GO

Declare @TheResults varchar(max),
        @vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)        
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults  + '
--#################################################################################################
-- BEGIN Mail Settings ' + p.name + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''') 
  BEGIN
    --CREATE Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profile_sp
      @profile_name = ''' + p.name + ''',
      @description  = ''' + ISNULL(p.description,'') + ''';
  END --IF EXISTS profile
  '
  +
  '
  IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''')
  BEGIN
    --CREATE Account [' + a.name + ']
    EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name            = ' + CASE WHEN a.name                IS NULL THEN ' NULL ' ELSE + '''' + a.name                  + '''' END + ',
    @email_address           = ' + CASE WHEN a.email_address       IS NULL THEN ' NULL ' ELSE + '''' + a.email_address         + '''' END + ',
    @display_name            = ' + CASE WHEN a.display_name        IS NULL THEN ' NULL ' ELSE + '''' + a.display_name          + '''' END + ',
    @replyto_address         = ' + CASE WHEN a.replyto_address     IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address       + '''' END + ',
    @description             = ' + CASE WHEN a.description         IS NULL THEN ' NULL ' ELSE + '''' + a.description           + '''' END + ',
    @mailserver_name         = ' + CASE WHEN s.servername          IS NULL THEN ' NULL ' ELSE + '''' + s.servername            + '''' END + ',
    @mailserver_type         = ' + CASE WHEN s.servertype          IS NULL THEN ' NULL ' ELSE + '''' + s.servertype            + '''' END + ',
    @port                    = ' + CASE WHEN s.port                IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',
    @username                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity   + '''' END + ',
    @password                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ', 
    @use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
    @enable_ssl              = ' + CASE WHEN s.enable_ssl = 1              THEN ' 1 ' ELSE ' 0 ' END + ';
  END --IF EXISTS  account
  '
  + '
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysmail_profileaccount pa
                INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
                INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id  
              WHERE p.name = ''' + p.name + '''
                AND a.name = ''' + a.name + ''') 
  BEGIN
    -- Associate Account [' + a.name + '] to Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
      @profile_name = ''' + p.name + ''',
      @account_name = ''' + a.name + ''',
      @sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;
  END --IF EXISTS associate accounts to profiles
--#################################################################################################
-- Drop Settings For ' + p.name + '
--#################################################################################################
/*
IF EXISTS(SELECT *
            FROM msdb.dbo.sysmail_profileaccount pa
              INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
              INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id  
            WHERE p.name = ''' + p.name + '''
              AND a.name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''
  END 
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''
  END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''') 
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''
  END
*/
  ' 
FROM msdb.dbo.sysmail_profile p
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON  p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account a         ON pa.account_id = a.account_id 
LEFT OUTER JOIN msdb.dbo.sysmail_server s     ON a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials c    ON s.credential_id = c.credential_id

   ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1), --         10 or 10E01 rows  
         E02(N) AS (SELECT 1 FROM E01 a, E01 b),  --        100 or 10E02 rows  
         E04(N) AS (SELECT 1 FROM E02 a, E02 b),  --     10,000 or 10E04 rows  
         E08(N) AS (SELECT 1 FROM E04 a, E04 b),  --100,000,000 or 10E08 rows  
         --E16(N) AS (SELECT 1 FROM E08 a, E08 b),  --10E16 or more rows than you'll EVER need,  
         Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),  
       ItemSplit(  
                 ItemOrder,  
                 Item  
                ) as (  
                      SELECT N,  
                        SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))  
                      FROM Tally  
                      WHERE N < DATALENGTH(@vbCrLf + @TheResults)  
                      --WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf  
                        AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter  
                     )  
  select  
    row_number() over (order by ItemOrder) as ItemID,  
    Item  
  from ItemSplit  
 

 

Find Broken Loop COnnection from Two Columns

 
 Create table Sample
 (
  Rec_Id Int Not null,
  Name varchar(30) null,
  ID1 varchar(10) null,
  ID2 varchar(10) null,
 CONSTRAINT [PK_Sample] PRIMARY KEY NONCLUSTERED 
(
  [Rec_Id] ASC
 ))
  

Insert into sample (Rec_ID,Name,ID1,ID2) values ( 1 ,'A', 'A1X31','A2XX1')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 2 ,'A', 'A2XX1','B2C45')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 3 ,'A', 'B2C45' ,'A1X31')  --> No need to display these 3 records becoz they form one loop( 1st row ID1='A1X31' value matches 3rd row ID2 ='A1X31'value)
 
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 4 ,'B',    'B1Y35' , 'B2Y15')
 Insert into sample (Rec_ID,Name,ID1,ID2) values (  5 , 'B',  'B2Y15', 'B5Y13')
 Insert into sample (Rec_ID,Name,ID1,ID2) values (  6 , 'B' , 'B5Y13' , null) --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='B1Y35' value doesn't matches 3rd row ID2 = null value)
 
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 7 ,'C',   'C5Z19' , 'C5B12')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 8 ,'C' , 'C5B12' , 'C5JPQ')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 9 ,'C' , 'C5JPQ' , 'C3JPQ')
  Insert into sample (Rec_ID,Name,ID1,ID2) values (10 ,'C' , 'C6JPQ' , 'C5Z19')  --> Need to display this 4th record becoz it is broken loop ( 3rd row ID2='C3JPQ' value doesn't matches 4th row ID1 = 'C6JPQ' value)

Insert into sample (Rec_ID,Name,ID1,ID2) values ( 11 ,'D', 'D5Z16' , 'D5Z17')
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 12 , 'D', 'D5Z17', 'D5Z18')
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 13 , 'D' , 'D5Z18' , 'D5Z17')  --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='D5Z16' value doesn't matches 3rd row ID2 =  'D5Z17' value)
 
;with mycte as
(select Rec_Id,name, id1, id2  
,ROW_NUMBER() over(partition by name order by Rec_Id) as rn1 
,ROW_NUMBER() over(partition by name order by Rec_Id DESC) as rn2
 
 
from sample )
, mycte1 as(
select m0.Rec_id,m0.name, m0.id1,  m0.id2 , Coalesce (m1.id2, m2.id2,'') id2_2, m0.rn2
, Sum(Case when m0.id1=Coalesce (m1.id2, m2.id2,'')   Then 0 Else 1 END) Over(Partition By m0.Name)  as cnt
from mycte m0 
Left Join mycte m1 On  m0.name =m1.name and m0.rn1=m1.rn1+1
Left Join mycte m2 On  m0.name =m2.name and m0.rn2=m2.rn1
)

Select Rec_id,name, id1,id2 
from mycte1
WHERE rn2=1 AND cnt>0
Order by Rec_id

/*
Rec_id	name	id1	id2
6	B	B5Y13	NULL
10	C	C6JPQ	C5Z19
13	D	D5Z18	D5Z17

*/



drop table sample

 

Convert String Type British Datetime Data (DD/MM/YYYY hh:mm:ss ) in Varchar/NVarchar to Standard Datetime with T-SQL



create table test (strdt varchar(255))
--British datetime in string
insert into test values 
('13/12/2013 13:33:00 +00:00')
,('01/01/2013 13:33:00 +00:00')
,('28/02/2013 13:33:00 +00:00')
,('31/10/2013 13:33:00 +00:00')
Select 
--string manipulation and cast to datetime
Cast(substring(strdt,7,4)+substring(strdt,4,2)+substring(strdt,1,2) +' '+ substring(strdt,12,8) as datetime),

--Convert string to British datetime first and then convert the British datetime to standard
  convert(datetime, convert(datetime, Left(strdt,20), 103),112)
   
FROM test


--clean up
drop table test

 


 

Strip non-numeric character in a column



create table test (t_id int primary key, col varchar(50))
INSERT INTO test
VALUES      (1,'462'),
            (2,'E08R'),
            (3,'E07'),
            (4,'E09'),
            (5,'E06'),
            (6,'360'),
            (7,'E04'),
            (8,'E11R'),
            (9,'E02R') 

 

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1), 
processTable as (
    select col,Cast(col1 as int) col1   from test
        cross apply (
            select (select C + ''
            from (select n, substring(Col, n, 1) C 
			FROM Num1  where n<=datalength(Col)) [1]
            where PATINDEX('%[0-9]%',C)> 0
            order by n
            for xml path(''))
        ) p (Col1)

		)

SELECT col,col1 FROM processTable
 
 --http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters


--Another option
;with
mycte1 as
(select  col, REPLACE(col,SUBSTRING(col,PATINDEX('%[^0-9]%',col),1),'') col1 from test )
,mycte2 as (
select col, REPLACE(col1,SUBSTRING(col1,PATINDEX('%[^0-9]%',col1),1),'') col2 from mycte1)
,mycte3 as (
select col, REPLACE(col2,SUBSTRING(col2,PATINDEX('%[^0-9]%',col2),1),'') col3 from mycte2)
,mycte4 as (
select col, REPLACE(col3,SUBSTRING(col3,PATINDEX('%[^0-9]%',col3),1),'') col4 from mycte3)

Select col, Cast(col4 as int) from mycte4

drop table test

--Another example
create table test (COLUMN_A varchar(100))
INSERT INTO test VALUES
('AAAA AAAAAAA CO A.A. # 4030'),      	
('BBBBBB BBB BBB CO. #  4260.'),  	
('CCC CCCCC #3001'),
('CCC CCCCC 3001')

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1), 

processTable as (

select Substring(COLUMN_A, 0,Charindex('#',  COLUMN_A)) COLUMN_A , Cast(col1 as int) COLUMN_B   from test
        cross apply (
            select (select C + ''
            from (select n, substring(COLUMN_A, n, 1) C 
            FROM Num1  where n<=datalength(COLUMN_A)) [1]
            where PATINDEX('%[0-9]%',C)> 0
            order by n
            for xml path(''))
        ) p (Col1)
 
        )

 Select * from processTable

 Drop table test

 

A Sample to remove non-numeric values from columns


CREATE TABLE [dbo].[DecoderModels](
	[PK_DecoderModelID] [bigint] IDENTITY(1,1) NOT NULL Primary Key,
	[ModelName] [nvarchar](50) NOT NULL,
	[SN_First] [varchar](50) NOT NULL,
	[SN_Last] [varchar](50) NOT NULL)

declare @sn varchar(50)='aao255'

Insert into [DecoderModels] Values 
('aaa','om000001','om499999')
,('bbb','0001','9999')
,('ccc','aao000001','aao9999999')

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1),

processTable as (
    select [SN_First], [SN_Last]
	, ISNULL(Cast(Col1 as int),0) as Col1
	, ISNULL(Cast(Col2 as int),99999999) as Col2
	, Cast(Col3 as int) Col3
	,Col4
    from [DecoderModels]
        cross apply (
            select (select C + ''
            from (select N, substring([SN_First], N, 1) C from Num1  where N<=datalength([SN_First])) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p (Col1)

		   cross apply (
            select (select C + ''
            from (select N, substring([SN_Last], N, 1) C from Num1  where N<=datalength([SN_Last])) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p2 (Col2)

		   cross apply (
            select (select C + ''
            from (select N, substring(@SN, N, 1) C from Num1  where N<=datalength(@SN)) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p3 (Col3)

		   cross apply (
            select (select C + ''
            from (select N, substring(@SN, N, 1) C from Num1  where N<=datalength(@SN)) t
            where PATINDEX('%[^0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p4 (Col4)

 
)

SELECT [SN_First], [SN_Last], Col1,Col2,Col3,col4
FROM processTable
WHERE Cast(Col3 as int) Between cast(Col1 as int) And cast(Col2 as int) 
AND ([SN_First] Like Col4+'%' AND  [SN_Last]  Like Col4+'%') Or (IsnuMeric([SN_First])=1 And Isnumeric([SN_Last])=1 And Col4 Is NUll)



drop table [DecoderModels]


 

http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters


Format File for BCP Or Bulk Insert

------bcp create non-XML format file

--One option:
 DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [myDB].[dbo].[myTable] format nul -c  -f  C:\temp\myFormat2.fmt -T -t\t  -S'+ @@servername
exec master..xp_cmdshell @sql

--Another

exec master..xp_cmdshell  'bcp [myDB].[dbo].[myTable] format nul   -f  C:\temp\myFormatFile.fmt  -c -T -S myServer\MyInstance';

go


------bcp create XML format file

----Another Sample:
DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [myDB].[dbo].[myTable] format nul -c -x -f  C:\temp\yourFORMATFIle.xml -T  -t\t -S'+ @@servername
exec master..xp_cmdshell @sql


exec master..xp_cmdshell  'bcp [myDB].[dbo].[myTable] format nul -c -x -f  C:\temp\myFormatFile.xml  -T -S myServer\MyInstance';

go

--http://technet.microsoft.com/en-us/library/ms179250.aspx




 

Use a Format File to Map Table Columns to Data-File Fields (SQL Server)
http://technet.microsoft.com/en-us/library/ms191175.aspx

Use a Format File to Skip a Table Column (SQL Server)
http://technet.microsoft.com/en-us/library/ms179250.aspx

Use a Format File to Skip a Data Field (SQL Server)
http://technet.microsoft.com/en-us/library/ms187908.aspx

XML Format file
–Simply remove the that column from section

Non-XML Format File
— Keep the leftmost row number as is (Host file field order) and change the Server coloumn order value to 0 and renumber all other columns as well.


Running Total (deduct value) (T-SQL)


DECLARE @Products TABLE (ProductID INT, ProductName VARCHAR(200), Price int)
 
INSERT INTO @Products(ProductID, ProductName,Price)
VALUES ( 1, 'A', 1000),(2, 'B',500),(3, 'C', 200),(4, 'E', 300)
 

 ;with mycte1 as (
select ProductID, ProductName, row_number() Over(Order by ProductID)  rn,Price
 
from @Products)
 
, mycte2 AS
(
 SELECT ProductID, ProductName, rn, Price
 FROM mycte1
 WHERE rn = 1
 UNION ALL
 SELECT n.ProductID, n.ProductName, n.rn,  (m.Price - n.Price) as price 
 FROM mycte2 m INNER JOIN mycte1 n
 ON n.rn = m.rn + 1
)
SELECT ProductID, ProductName,Price FROM mycte2
 ORDER BY ProductID
 
--OPTION (MAXRECURSION 0)
;