A few Queries that are related to Agent Information

You can tweak a few useful queries Datta Sindol compiled to make them useful for you if you want to get some information about agent jobs from MSDB.
Here is the link:

http://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/


Execute Stored Procedure with Parameters and Insert the Resultset Into a Table

 
 --Set parameters
declare	@did int = 1,
		@eid int =88

declare @sqltax varchar(1000);
select @sqltax = 'SET NOCOUNT ON;SET FMTONLY OFF; EXEC [mydb].[dbo].[getMytaxes]
	 @did = '+cast(@did as varchar(15))
	 +', @eid = '+cast(@eid as varchar(15))
		
EXEC ('SELECT *  INTO mytaxes  FROM  OPENROWSET(''SQLNCLI'', ''Server=MC12\MSSQL08R2;Trusted_Connection=yes;'',''' +  @sqltax + ''') AS t;')
;

select eid, sum(tax) as taxtotal 
into mytaxes2
from mytaxes
group by eid


Drop table mytaxes
Drop table mytaxes2

 

How to SUM odd and even digits ina big number with T-SQL

http://social.msdn.microsoft.com/Forums/en-US/958861e1-786b-4b51-8915-5ef33dc5657b/how-to-calculate-odd-and-even-place-digits-in-sql?forum=transactsql

 create table test (number varchar(50))
Insert into test values('196421744643'),('12121212121212')
select number,  SUM(Case WHEN num%2 =1 then Cast(substring(number,num,1) as int) end) OddSum,
SUM(Case WHEN num%2 =0 then Cast(substring(number,num,1) as int) end) EvenSum
 from test
 cross apply (Values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) d(num) 
 WHERE substring(number,num,1)<>''
 Group by number
 
 drop table test


 

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

 

Merge Update Sample



Create table MinuteCategory([Minute] int, MinuteCategory varchar(50))
Insert into MinuteCategory values (1,'a1'),(33,'a1'),(65,'a1')
Create table MinuteLookup( MinuteCategoryId int identity(1,1), MinuteLow int, MinuteHigh int, MinuteCategory varchar(50))
Insert into MinuteLookup values(1,15,'<15min'),(16,30,'<30 min'),(31,45,'<45 min'),(46,60,'<60 minutes'),(61,75,'<1 hour 15 minutes')


Merge MinuteCategory mc 
Using MinuteLookup ml on mc.[Minute] between ml.MinuteLow and ml.MinuteHigh
When Matched Then
UPDATE 

SET MinuteCategory = ml.MinuteCategoryId;


select * from MinuteCategory mc inner join MinuteLookup ml on mc.MinuteCategory = ml.MinuteCategoryId

----Clean up
drop table MinuteCategory,MinuteLookup


 

How to select a max row for each group in SQL


CREATE TABLE [dbo].[test1]([Country] [varchar](4) NULL,  [grpid] [int] NOT NULL,  [Value] [int] NULL,  [Row_num] [int] NULL) 

INSERT [dbo].[test1]  VALUES (N'US', 49707, 604456458, 1)
, (N'US', 909, 604456458, 2),(N'US', 231, 604456457, 3)
, (N'GB', 5086, 497654945, 4), (N'GB', 50147, 405759433, 5),(N'GB', 909, 405759433, 6),(N'GB', 231, 405759433, 7)
, (N'CA', 49707, 353500201, 8), (N'CA', 5086, 353500201, 9),(N'CA', 909, 353500201, 10),(N'CA', 231, 353500201, 11)
, (N'JP', 49707, 198291290, 12),(N'JP', 5086, 198291290, 13), (N'JP', 909, 198291290, 14)
,(N'JP', 231, 198291290, 15)
, (N'MX', 49707, 181884714, 16), (N'MX', 909, 181884714, 17)
,(N'MX', 231, 181884714, 18),(N'BR', 49707, 105147054, 19), (N'BR', 5086, 105147054, 20),(N'BR', 909, 105147054, 21)
, (N'AR', 49707, 94774929, 22), (N'AR', 5086, 94774929, 23), (N'AR', 909, 94774929, 24),(N'AR', 231, 94774929, 25)
, (N'ZA', 49707, 84560514, 26),(N'ZA', 909, 84560514, 27),(N'ZA', 231, 84560514, 28), (N'CN', 49707, 68966682, 29)
,(N'CN', 909, 68966682, 30),(N'CN', 231, 68966682, 31),(N'CB', 49707, 65020665, 32)
, (N'CB', 5086, 65020665, 33), (N'CB', 909, 65020665, 34),(N'CB', 231, 65020665, 35)
,(N'GT', 49707, 42016510, 36), (N'GT', 5086, 42016510, 37),(N'GT', 909, 42016510, 38), (N'CR', 49707, 37116544, 39)
,(N'CR', 5086, 37116544, 40), (N'CR', 909, 37116544, 41),(N'CR', 231, 37116544, 42), (N'SV', 49707, 31103322, 43)
, (N'SV', 5086, 31103322, 44), (N'SV', 909, 31103322, 45),(N'NI', 49707, 17415843, 46),(N'NI', 5086, 17415843, 47)
, (N'NI', 909, 17415843, 48), (N'HN', 49707, 12516273, 49),(N'HN', 5086, 12516273, 50),(N'HN', 909, 12516273, 51)

GO

/*
Country	grpid	Value	Row_num
US	49707	604456458	1
GB	5086	497654945	4
CA	909	353500201	10
JP	231	198291290	15

*/

--source table with a generated row number based on value and grpid
;with mytest as (
select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn   from test1
)

,mycte1 as (select grpid,country,value, Row_num, row_number() over(Order by value DESC, grpid DESC) rn
from mytest a where not exists(select 1  from mytest b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte2 as (select grpid,country,value , Row_num, row_number() over(  Order by value DESC, grpid DESC) rn 
 from mycte1 a where not exists(select 1  from mycte1 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )  
,mycte3 as (select grpid,country,value , Row_num, row_number() over(  Order by value DESC, grpid DESC) rn from mycte2 a 
where not exists(select 1  from mycte2 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) ) 

 --Add more
--,mycte4 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn
-- from mycte3 a where not exists(select 1  from mycte3 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) ) 
--,mycte5 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn from mycte4 a 
--where not exists(select 1  from mycte4 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
  

--Final combine all
select grpid,country,value , Row_num from mytest WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte1 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte2 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte3 WHERE rn=1


 ----Add more
--Union ALL
--select grpid,country,value , Row_num from mycte4 WHERE rn=1
--Union ALL
--select grpid,country,value , Row_num from mycte5 WHERE rn=1

 

drop table test1
 


--Early version
CREATE TABLE [dbo].[test1](
       [Country] [varchar](4) NULL,
       [grpid] [int] NOT NULL,
       [Value] [int] NULL,
       [Row_num] [int] NULL
) 

INSERT [dbo].[test1]  VALUES (N'US', 49707, 604456458, 1)
, (N'US', 909, 604456458, 2),(N'US', 231, 604456457, 3)

, (N'GB', 5086, 497654945, 4), (N'GB', 50147, 405759433, 5),(N'GB', 909, 405759433, 6),(N'GB', 231, 405759433, 7)
, (N'CA', 49707, 353500201, 8), (N'CA', 5086, 353500201, 9),(N'CA', 909, 353500201, 10),(N'CA', 231, 353500201, 11)
, (N'JP', 49707, 198291290, 12),(N'JP', 5086, 198291290, 13), (N'JP', 909, 198291290, 14),(N'JP', 231, 198291290, 15)
, (N'MX', 49707, 181884714, 16), (N'MX', 909, 181884714, 17)
,(N'MX', 231, 181884714, 18),(N'BR', 49707, 105147054, 19), (N'BR', 5086, 105147054, 20),(N'BR', 909, 105147054, 21)
, (N'AR', 49707, 94774929, 22), (N'AR', 5086, 94774929, 23), (N'AR', 909, 94774929, 24),(N'AR', 231, 94774929, 25)
, (N'ZA', 49707, 84560514, 26),(N'ZA', 909, 84560514, 27),(N'ZA', 231, 84560514, 28), (N'CN', 49707, 68966682, 29)
,(N'CN', 909, 68966682, 30),(N'CN', 231, 68966682, 31),(N'CB', 49707, 65020665, 32)
, (N'CB', 5086, 65020665, 33), (N'CB', 909, 65020665, 34),(N'CB', 231, 65020665, 35)
,(N'GT', 49707, 42016510, 36), (N'GT', 5086, 42016510, 37),(N'GT', 909, 42016510, 38), (N'CR', 49707, 37116544, 39)
,(N'CR', 5086, 37116544, 40), (N'CR', 909, 37116544, 41),(N'CR', 231, 37116544, 42), (N'SV', 49707, 31103322, 43)
, (N'SV', 5086, 31103322, 44), (N'SV', 909, 31103322, 45),(N'NI', 49707, 17415843, 46),(N'NI', 5086, 17415843, 47)
, (N'NI', 909, 17415843, 48), (N'HN', 49707, 12516273, 49),(N'HN', 5086, 12516273, 50),(N'HN', 909, 12516273, 51)

GO

/*
Country	grpid	Value	Row_num
US	49707	604456458	1
GB	5086	497654945	4
CA	909	353500201	10
JP	231	198291290	15

*/

;with mytest as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn   from test1)

,mycte1 as (select grpid,country,value , Row_num from mytest a where not exists(select 1  from mytest b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte2 as (select grpid,country,value , Row_num, row_number() over(Order by value DESC, grpid DESC) rn   from mycte1 )
,mycte3 as (select grpid,country,value , Row_num from mycte1 a where not exists(select 1  from mycte2 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte4 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn   from mycte3 )
,mycte5 as (select grpid,country,value , Row_num from mycte3 a where not exists(select 1  from mycte4 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte6 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn   from mycte5 )
,mycte7 as (select grpid,country,value , Row_num from mycte5 a where not exists(select 1  from mycte6 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte8 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn  from mycte7 ) 
,mycte9 as (select grpid,country,value , Row_num from mycte7 a where not exists(select 1  from mycte8 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
  

--Final combine all
select grpid,country,value , Row_num from mytest WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte2 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte4 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte6 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte8 WHERE rn=1

 

drop table test1


 

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc8f52e2-a03d-4183-bf35-dc2ab14f2632/how-to-select-a-max-row-for-each-group-in-sql?forum=transactsql#5118d2c5-2068-417e-9578-865877eb8925