Event 10016 and How to Grant Permissions to COM Component and Network Service Account

Windos Server 2012 R2

Grant the correct permissions to the Network Service account
Grant the user permissions to start the COM component

Source:
https://support.microsoft.com/en-us/kb/899965

A question from T-SQL Forum MSDN: Grouping of rows, every second then every third

https://social.msdn.microsoft.com/Forums/en-US/def432c4-0e62-44b7-ba13-d0444ed39511/grouping-of-rows-every-second-then-every-third?forum=transactsql

  
 
 --Naomi's setup and solution
--option 1

 declare @t table (id int identity(1,1) primary key, dataCol varchar(10))
insert into @t (dataCol) values ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k')

;with cte as (

select *, ROW_NUMBER() over (order by id) - ROW_NUMBER() over (partition by case when id % 5 in (1,2) then 1 else 2 end order by id) as [Grp]
from @t)

SELECT Id, DataCol, dense_rank() over (order by [Grp]) as GrpNumber

from cte  order by Id
   
  
--option 2

  --Here is mine

select id, dataCol ,Sum(val) Over(Order by id)  from  @t   
cross apply (values (Case when id%5 in (1,3) then  1 else 0 end)) d(val)
   
 --better one

--option 3

select id, dataCol ,Sum((Case when id%5 in (1,3) then  1 else 0 end)) Over(Order by id)  from  @t   
 

 

Pivot 12 Months’ User Counts

https://social.msdn.microsoft.com/Forums/en-US/bb4c1214-cbbf-42e7-8626-145c0a8bfef4/sql-server-count-1-for-all-active-months-during-reporting-period?forum=transactsql

  
 
create table #base
(RecID int, FromDate
date , ToDate date
)
insert into #base
values (17669977,'5/13/2016','11/4/2016')
insert into #base
values (17683279, '4/19/2016',
'10/4/2016')
insert into #base
values (17693200, '5/2/2016',
'11/4/2016')
;with mycte0 as (
Select RecID, Datename(Month,Dateadd(month,-n+1,getdate())) mth
, Dateadd(month,-n+1,getdate())dt,n 
From #base, (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ) d(n)
)
 
 

, mycte1 as ( 
 Select RecID,  Dateadd(month,n-1,FromDate) dt, Datename(Month,  Dateadd(month,n-1,FromDate)) mth from #base
Cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ) d(n)
WHERE 
--RecID=17669977  And 
Dateadd(month,n-1,FromDate) Between FromDate and EOMONTH(ToDate)

)


select
-- m0.RecID ,
Sum(Case when m0.mth='September' and  m1.mth is not null then 1 else 0 End)  'September'
,Sum(Case when m0.mth='August' and  m1.mth is not null then 1 else 0 End)  'August'
,Sum(Case when m0.mth='July' and  m1.mth is not null then 1 else 0 End)  'July'
,Sum(Case when m0.mth='June' and  m1.mth is not null then 1 else 0 End)  'June'
,Sum(Case when m0.mth='May' and  m1.mth is not null then 1 else 0 End)  'May'
,Sum(Case when m0.mth='April' and  m1.mth is not null then 1 else 0 End)  'April'
,Sum(Case when m0.mth='March' and  m1.mth is not null then 1 else 0 End)  'March'
,Sum(Case when m0.mth='Feburary' and  m1.mth is not null then 1 else 0 End)  'Feburary'
,Sum(Case when m0.mth='January' and  m1.mth is not null then 1 else 0 End)  'January'
,Sum(Case when m0.mth='December' and  m1.mth is not null then 1 else 0 End)  'December'
,Sum(Case when m0.mth='November' and  m1.mth is not null then 1 else 0 End)  'November'
,Sum(Case when m0.mth='October' and  m1.mth is not null then 1 else 0 End)  'October'
 



 from mycte0 m0 left join mycte1 m1 on m0.RecID=m1.RecID and m0.mth=m1.mth
 --Group by  m0.RecID



drop table #base

  
   
 
 

 

Count of Consecutive Decending Values Up to the First Gap

https://social.msdn.microsoft.com/Forums/en-US/e083c5fa-c82b-47bc-bc9e-657a58344986/count-of-consecutive-decending-values-up-to-the-first-gap?forum=transactsql

  
 
   create table test (customer_no int,	membership_year int)
Insert into test values
(1,2016),(1,2015),(1,2014),(1,2008),(1,2007)
,(2,2012),(2,2011),(2,2010),(2,2007),(2,2006),
(3,2017),(3,2016),(3,2015),(3,2014),(3,2013),(3,2012)
 
 
  ;with mycte as (
select  customer_no, membership_year
, DATEADD(year, - ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY membership_year), datefromparts(membership_year,1,1)) AS grp
  from test
)
,mycte1 as (
select customer_no,  membership_year, grp , max(membership_year) Over (PARTITION BY customer_no) maxYear, Year(getdate()) curYear
, DENSE_RANK() OVER(PARTITION BY customer_no  ORDER BY grp desc) AS Ranking 
from mycte
)
Select customer_no, ISNULL(SUM(case when maxYear>=curYear and Ranking=1 then 1 else null end) -1 ,0)  as  cnt  
From mycte1
Group by customer_no
order by customer_no


/*
Customer 1 would have 2 consecutive years
Customer 2 would have 0 (zero) because they are no longer a member as of 2012
Customer 3 would have 5 consecutive years. 
*/

Drop table test


  
   
 
 

 

Fill Dataset to 10 Multipliers

  
 
   
  
create table test (id int, col1 int)

insert into test values(1,1),(2,2),(3,3),(4,4), (5,1),(6,2),(7,3),(8,4),(9,1),(10,2)
,(11,3),(12,4),(13,3),(14,4)
 

declare @cnt int
select @cnt=count(*) from test

--Select @cnt%10

;with mycte as (
select *  from test
union all
select 999,9 from 
(values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))   d(n)
where n<=10 - @cnt%10  and @cnt%10<>0
)
 
Select *  from mycte 

drop table test

 
 

 

A Sample To Export a Text File with Parameterized Stored Procedure through BCP

  
 
   
  
Use mydb1 --the DB name
go

If object_id('dbo.Classtests','U') is not null
drop table dbo.Classtests;
go--

create table Classtests (ClassID  int,  DepartID int, year_code int, trm_code int)
Insert into Classtests (ClassID,DepartID,year_code ,trm_code )
values(1,1,2015,20),(2,1,2017,20),(3,1,2016,20),(4,1,2016,20),(5,1,2016,20),(6,1,2016,20)
insert into Classtests   
Values (7,1,2017,20),(8,1,2017,20),(9,1,2017,20),(10,1,2017,20)


If object_id('dbo.usp_Classtest','P') is not null
drop procedure dbo.usp_Classtest;
go
create procedure usp_Classtest 
@year_code int
,@trm_code varchar(100)
As
Begin
Select * from Classtests
WHERE year_code=@year_code and trm_code= @trm_code;
End
Go
If object_id('dbo.bcpClassFile','P') is not null
drop procedure dbo.bcpClassFile;
go

Create PROCEDURE bcpClassFile 
 @year_code int
,@trm_code varchar(100)
AS
  BEGIN
	DECLARE  @sql VARCHAR(8000)=''	
	SET @sql = 'bcp "EXEC mydb1..usp_Classtest ''' + CONVERT(VARCHAR(4), @year_code) + ''',''' + @trm_code +  '''" Queryout "c:\temp\test\TheFile'
				 + @trm_code + CONVERT(VARCHAR(4), @year_code)
				 + '.txt " -c -t"\t" -T -S'
				 + @@SERVERNAME
	EXEC master..xp_cmdshell  @sql
  END


  --Test 
  exec bcpClassFile 2016,'20'

  --Check file in  c:\temp\test\TheFile202016.txt

 
 

 

Subset From Different Groups

–http://forums.asp.net/t/2102617.aspx?Select+data+without+use+subselect

  
 
create table #a(product varchar(10),quality int)
insert into #a values('tri',2),('vse',2),('sve',2),('bun',1),('kal',3),('sve',3),('vse',3),('bun',2),('bun',1),('tri',3),('bun',3),('tri',3),('kal',2),('kal',1), ('kal111',1),('kal333',3)

-----Condition for select is: select all of product which are in quality 2 and 3 but not in quality 1. 
 
  
 --  option 1
 ;with mycte as (
select product, quality, Max( Case when quality=1 then POWER(2*1,1) 
when quality=2 then POWER(2*1,2) 
when quality=3 then POWER(2*1,3)  end) bits
from #a
group by product, quality
) 
,mycte1 as (
select  product, quality,
Case when  2 &  Sum( bits) Over(Partition by product) =2 then 1 else 0 End as val1 
,Case when  4 &  Sum(bits) Over(Partition by product) =4 then 1 else 0 End as val2 
,Case when  8 &  Sum(bits) Over(Partition by product) =8 then 1 else 0 End as val3 
from mycte)

Select distinct product from mycte1
WHERE val1=0 and val2=1 and val3=1


--option2 
Select product from (
select product from   #a
Where quality=2
intersect
select product from   #a
Where quality=3) t
except
select product from   #a
Where quality=1

 
drop table #a

 
 

 

Round Money to Nearest Quarter

  
 
  
 create table test (col decimal(10,2))
Insert into test values(1.33),(2570.47),(100020.10),(100.62),(100.63),(99.99)

select Cast(Round(col * 4,0)/4.0 as decimal(10,2))  col from test
 
select 
cast(col as int)+ 
Case
when col%1 <= 0.12     then 0.00
when col%1 > 0.12  And col%1 <= 0.37  then .25
when col%1 > 0.37  and col%1 <= 0.62  then .50
when col%1 > 0.62  and col%1 <= 0.87  then .75
when col%1 > 0.87  then 1.00
end
 

from test


drop table test


 --https://social.msdn.microsoft.com/Forums/en-US/c22b2cac-0a78-4d61-bfcc-00d2e2d22303/round-to-nearest-25-cents?forum=transactsql
 

 

Script to Drop Constraints from Linked Tables Before Drop Table

  
 
  

DECLARE @sql varchar(max)=''
SELECT @sql = @sql + 'ALTER TABLE ' +  Quotename(OBJECT_SCHEMA_NAME(parent_object_id)) +'.' + Quotename(OBJECT_NAME(parent_object_id) )+ 
' DROP CONSTRAINT ' + name + ';'+ CHAR(13)
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('myscm.thetable' )
or referenced_object_id = object_id( 'myscm.anothertable')

SELECT @sql = @sql+ CHAR(13)+ ' DROP TABLE ' +s.name+ '.'+ t.name  
FROM sys.Tables t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
where s.name='myscm' and  t.name in ('thetable', 'anothertable')


-----uncomment below line to execute the generated script
 --EXEC sp_executesql @sql 

PRINT @sql