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


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


 --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


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',
insert into #base
values (17693200, '5/2/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)
--RecID=17669977  And 
Dateadd(month,n-1,FromDate) Between FromDate and EOMONTH(ToDate)


-- 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


   create table test (customer_no int,	membership_year int)
Insert into test values
  ;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)

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

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

create table Classtests (ClassID  int,  DepartID int, year_code int, trm_code int)
Insert into Classtests (ClassID,DepartID,year_code ,trm_code )
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;
create procedure usp_Classtest 
@year_code int
,@trm_code varchar(100)
Select * from Classtests
WHERE year_code=@year_code and trm_code= @trm_code;
If object_id('dbo.bcpClassFile','P') is not null
drop procedure dbo.bcpClassFile;

Create PROCEDURE bcpClassFile 
 @year_code int
,@trm_code varchar(100)
	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'
	EXEC master..xp_cmdshell  @sql

  exec bcpClassFile 2016,'20'

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



Subset From Different Groups


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

Select product from (
select product from   #a
Where quality=2
select product from   #a
Where quality=3) t
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
cast(col as int)+ 
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

from test

drop table test



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