Dynamic SQL with Output Count

  
declare @TableNameProd nvarchar(256),@TableNameTmp nvarchar(256)  
Set  @TableNameProd='test2'
Set @TableNameTmp='test'


Declare @tmpCount int
Declare @RptCount int

Declare @sqltemp nvarchar(200)
Declare @sqlprod nvarchar(200)

set @sqltemp =  'select @tmpCount=COUNT(*) from ' +   @TableNameTmp   
set @sqlprod =  'select @RptCount=COUNT(*) from ' + @TableNameProd  


EXECUTE sp_executesql @sqlprod, N'@TableNameProd SysName , @RptCount int OUTPUT'
,@TableNameProd=@TableNameProd,    @RptCount=@RptCount output
EXECUTE sp_executesql @sqltemp, N'@TableNameTmp SysName,  @tmpCount int OUTPUT '
,@TableNameTmp=@TableNameTmp,  @tmpCount=@tmpCount output  


print @tmpCount
print @RptCount


if (@tmpCount <> @RptCount)
	print 'not equal' 
--Some code here
else
--Some other code here

print 'equal'


 

Int Identity Column Outgrow Fix

When you use INT data type as identity column, you may run out of the positive limit (2147483647 or 2^31-1, which is a very large number and it is not common to reach it) in rare cases.
Besides you can schedule a process to change the column data type to BIGINT 9223372036854775807 (or 2^63-1),you can use the following quick fix to utilize the negative range of int numbers.

  
  
 DBCC CHECKIDENT('[dbo].[ALogTable]', RESEED, -2147483648)


 

The change data type for identity column may involve script out your key, constraints and index information and drop these keys/index on the table and use
Alter yourtable alter column theidentity bigint;
Reapply keys and constraints and rebuild index.
The process needs to be tested on lower environments to make sure it will work with your production environment.

Export-csv with Tab Delimiter and Remove Double Quotes

  
  
 
 
 
$file='C:\temp\theOutFile.txt'
get-service | export-csv -delimiter "`t" -path $file -notype 
(Get-Content $file) | Foreach-Object {$_ -replace '"', ''}|Out-File $file

notepad $file

 
 
 

 

Check all values in a group (Relational division)

  
  
 
 create table #t1(memid varchar(100),eventcode varchar(100))

insert into #t1 values('mem1','1stdate')
insert into #t1 values('mem1','2ndate')
insert into #t1 values('mem1','3rddate')
insert into #t1 values('mem2','1stdate')
insert into #t1 values('mem2','2ndate')
insert into #t1 values('mem2','3rddate')
insert into #t1 values('mem3','1stdate')
insert into #t1 values('mem3','2ndate')
insert into #t1 values('mem4','3rddate')



--option 1
select memid from #t1 WHERE eventcode='1stdate'
intersect
select memid from #t1 WHERE eventcode='2ndate'
intersect
select memid from #t1 WHERE eventcode='3rddate'


--option 2
select distinct memid from #t1 s 
WHERE Exists (select 1 from   #t1 t WHERE t.eventcode='1stdate' and s.memid=t.memid)
and Exists (select 1 from   #t1 t WHERE t.eventcode='2ndate' and s.memid=t.memid)
and Exists (select 1 from   #t1 t WHERE t.eventcode='3rddate' and s.memid=t.memid)
 

--Option 3
;with mycte as (
Select * 
,SUm(
Case 
when eventcode='1stdate' then POWER(2*1,1) --1stdate
when eventcode='2ndate' then POWER(2*1,2) --2ndate
when eventcode='3rddate' then POWER(2*1,3) --3rddate
--when eventcode='4thdate' then POWER(2*1,4) --4thdate
End) Over(Partition by memid)  bitSUM  
from #t1
)


Select memid,eventcode from mycte
WHERE (2 & bitSUM = 2 ) --1stdate
and  (4 & bitSUM = 4) --2ndate
AND (8 & bitSUM = 8) --3rddate
--AND (16 & bitSUM = 16) --4thdate








---Option 4
--Celko's relational division
--https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

--Celko solution 1
SELECT DISTINCT memid
  FROM #t1 AS t1 
  WHERE NOT EXISTS
       (SELECT *
          FROM  (Select distinct eventcode from #t1 ) t
         WHERE NOT EXISTS
               (SELECT *
                  FROM  #t1 AS t2
                 WHERE (t1.memid = t2.memid)
                   AND (t2.eventcode = t.eventcode)));

 --Celko solution 2
SELECT t1.memid
   FROM #t1 AS t1, (Select distinct eventcode from #t1 ) AS t2
  WHERE t1.eventcode = t2.eventcode
  GROUP BY t1.memid 
  HAVING COUNT(t1.eventcode) = (SELECT COUNT(eventcode) FROM  (Select distinct eventcode from #t1 ) AS t );

--Celko solution 3
SELECT t1.memid
  FROM #t1 AS t1
LEFT OUTER JOIN (Select distinct eventcode from #t1 ) AS t2
       ON t1.eventcode = t2.eventcode 
  GROUP BY t1.memid
HAVING COUNT(t1.eventcode) = (SELECT COUNT(eventcode) FROM (Select distinct eventcode from #t1) t)
   AND COUNT(t2.eventcode) = (SELECT COUNT(eventcode)FROM (Select distinct eventcode from #t1) t);

   
--Celko solution 4

;with mycte as (
Select distinct eventcode from #t1
)

SELECT PS1.memid 
  FROM  #t1 AS PS1    
  LEFT OUTER JOIN     mycte AS H1   ON PS1.eventcode = H1.eventcode
GROUP BY PS1.memid 
Having    COUNT(PS1.eventcode) = (SELECT COUNT(eventcode) FROM mycte)
            AND COUNT(H1.eventcode) = (SELECT COUNT(eventcode) FROM mycte) ;



--SELECT PS1.memid,
--CASE WHEN COUNT(PS1.eventcode) > 
--                              (SELECT COUNT(eventcode) FROM mycte )
--                              AND COUNT(H1.eventcode) = (SELECT COUNT(eventcode)FROM mycte )
--            THEN 'more than all'
--            WHEN COUNT(PS1.eventcode) = (SELECT COUNT(eventcode) FROM mycte)
--                 AND COUNT(H1.eventcode) 
--                     = (SELECT COUNT(eventcode) FROM mycte)
--            THEN 'exactly all '
--            WHEN MIN(H1.eventcode) IS NULL
--            THEN 'none'
--            ELSE 'some' END AS skill_level
--  FROM  #t1 AS PS1    LEFT OUTER JOIN     mycte AS H1   ON PS1.eventcode = H1.eventcode
--  GROUP BY PS1.memid;


drop table #t1

 

https://social.msdn.microsoft.com/Forums/en-US/e676bccf-9fa8-479d-ae5f-d48a39b1ee0a/need-select-if-all-my-values-are-present?forum=transactsql

Find Date Gap Between Start and End

  
  
  --SQL Server 2012 or above
  create table SampleData(ID   int,   start_dt  datetime, end_dt datetime)  
insert into SampleData values
(123,'01-Aug-14','01-Aug-14')
,(123,'01-Aug-14','01-Aug-14')
,(345,'01-Aug-14 ','01-Aug-14')  
,(456,'01-Aug-14 ','02-Aug-14')
,(456,'02-Aug-14','02-Aug-14') 
,(456,'03-Aug-14','03-Aug-14') 
,(456,'03-Aug-14','13-Aug-14') 
,(456,'15-Aug-14','23-Aug-14') 
 
 

;WITH mycte AS
(
	SELECT *
		, CASE 
			WHEN DATEDIFF(day, LAG(end_dt) OVER (PARTITION BY ID ORDER BY start_dt), start_dt) <= 1 THEN 0
			ELSE 1
		END AS [flag]
	FROM SampleData
),
mycte1 AS 
(
	SELECT *, SUM([flag]) OVER (PARTITION BY ID ORDER BY start_dt) AS grp
	FROM mycte
)
SELECT ID, MIN(start_dt) AS start_dt, MAX(end_dt) AS end_dt
FROM mycte1
GROUP BY ID, grp
Order by ID, start_dt

 
 


drop table SampleData

 

Use SFTP to send file with public/private keys along passphrase

WinSCP is a great open source tool to use sftp protocol to send files.
Besides you can use user name and password, you can use public key and private key pair to authenticate to the site.
With newer version (>=5.6), you can automate an agent job to run an SSIS package with Script Task to include passphrase used to generate the key pair.
The following code snippet is an example:

string logname = “E:\\temp\\log\\” + DateTime.Now.ToString(“yyyyMMddHHmmssfff”) + “thislog.xml”;
//// Run hidden WinSCP process
Process winscp = new Process();
winscp.StartInfo.FileName = “c:\\Program Files (x86)\\WinSCP\\winscp.com”;

winscp.StartInfo.Arguments = “/log=\”” + logname + “\””;
winscp.StartInfo.UseShellExecute = false;
winscp.StartInfo.RedirectStandardInput = true;
winscp.StartInfo.RedirectStandardOutput = true;
winscp.StartInfo.CreateNoWindow = true;
winscp.Start();

//// Feed in the scripting commands

winscp.StandardInput.WriteLine(“option batch abort”);
winscp.StandardInput.WriteLine(“option confirm off”);

winscp.StandardInput.WriteLine(“open theuser@155.111.222.101 -privatekey=E:\\keys\\mykeys\\myprivate.ppk -passphrase=MySecretPhrase “);
winscp.StandardInput.WriteLine(“put E:\\output\\exportfile.csv”);

winscp.StandardInput.Close();

string output = winscp.StandardOutput.ReadToEnd();

// Wait until WinSCP finishes
winscp.WaitForExit();
Dts.TaskResult = (int)ScriptResults.Success;