Powershell: Loop through Multiple Servers and Export Result to a File



## server list from a string
###   $ServerList = "dev", "qa"  

## serverlist from a file
$ServerList=Get-Content C:\temp\Serverlist.txt

##The combined file
$finalfilepath = "C:\temp\filefinal.csv"
##clear the file content if it exists
if (Test-Path $finalfilepath) {Clear-Content C:\temp\filefinal.csv }

foreach ($CurrentServer in $ServerList) 
{  

##Save one serverinstance as a file
  $csvFilePath ="C:\temp\($CurrentServer)list.csv"
   
  Invoke-Sqlcmd -Server $CurrentServer -Database master  -Query "SELECT @@servername as 'Server', name as 'DB', convert(varchar(8), getdate(), 1) as 'Date' from sys.databases WHERE database_id > 4 AND database_id NOT IN (select database_id from sys.dm_hadr_database_replica_states)"  -querytimeout 65534  | export-csv $csvFilePath   -NoTypeInformation
	Get-Content  $csvFilePath >>  $finalfilepath
}

##Open in notepad
notepad C:\temp\filefinal.csv



 

Datetime (Date) Data to Excel Become String Type — How to change Back as Datetime

When your export datetime data from SQL Server to excel the result excel column become text type. You cannot format it as either date/time format or custome format within excel directly.
There are a few solutions to solve this problem.
You can simply to use TXET function to format the text directly.
Use the TEXT function in cell as formula along with the formated datetime style.
For example: =TEXT(B2,”mm/dd/yyyy”) or =TEXT(A2,”yyyy-mm-dd”) (you need to copy and paste this formula the entire column ). You can choose any other valid datetime style to format the data in the column But this is not optimum since the result is still text.

There are other functions in Excel to we can use to retrieve datevalue and timevalue from the string datetime.

=DATEVALUE(LEFT(A1,10))
–yyyy-mm-dd “2017-01-16” If this value is shwing an integer (which is a date value in integer form),
you need to use built-in format function to format date to the style you need.

=TIMEVALUE(MID(A1,12,8))
–hh:mm:ss

A simply form without using these functions is to use a + sing to add date and time together to force the concatenation and implict conversion to real datetime value.

=LEFT(A1,10)+MID(A1,12,8)

Apply this formula to this column, the datetime now is real datetime in excel and you can use any format within Excel now.
If the data column is DATE string, we can simply use formula: =DATEVALUE(A1).

Another option is to use Data>>Text to Columns >>(delimited default tab)>>chosse Date — pick format YMD. After this conversion, you can format this column (datetime value) to the style you need.

https://jingyangli.wordpress.com/2016/08/03/format-date-column-in-excel-from-sql-server-export-file/

If your query result is not large, you can copy and past the result with header directly to Excel spreadsheet and format the datetime column afterwards.


Cannot connect to SQL Server box from local SSMS as a domain user

Error:
SSPI handshake failed with error code 0x8009030c, state 14 while establishing
a connection with integrated security;
the connection has been closed. Reason: AcceptSecurityContext failed.

Resolution:

From the SQL Server box,Add the domain account “ifbf\iruxxxxxx” to “Access this computer from the network”
local security policy (secpol.msc)
on the SQL Server box and retry and the connection is successfully from local SSMS.


Format _MDDyy to a standard date

  
  
 


select *, Cast('20'+ Right(policyexpire,2)+ Left(Right('0'+policyexpire,6) ,4) as date)  

,datefromparts(2000+ cast(Right(policyexpire,2)as int),Cast(Left(Right('0'+policyexpire,6) ,2) as int) , cast(Left(Right(policyexpire,4),2) as int) )
,datefromparts(2000+  Right(policyexpire,2) , Left(Right('0'+policyexpire,6) ,2)  , Left(Right(policyexpire,4),2)   )

 ,CAST(STUFF(STUFF(Right('0'+policyexpire,6),3,0,'/'),6,0,'/') as DATE)
 ,CONVERT(DATE,STUFF(STUFF(Right('0'+policyexpire,6),3,0,'/'),6,0,'/') )

,CONVERT(DATE,Right( policyexpire ,2)+ Left(Right('0'+policyexpire,6) ,4) ,12)
,Cast(Right( policyexpire ,2)+ Left(Right('0'+policyexpire,6) ,4) as DATE)
 
,TRY_CONVERT(DATE,Right( policyexpire ,2)+ Left(Right('0'+policyexpire,6) ,4) ,12)
--,TRY_CAST(Right( policyexpire ,2)+ Left(Right('0'+policyexpire,6) ,4) as DATE) 
,TRY_PARSE(Right( policyexpire ,2)+ Left(Right('0'+policyexpire,6) ,4) as DATE)
from atable


 

How to fix scientific notation (like 1.23E+004) number data in varchar column that are imported from Excel in SQL Server

When you import data from Excel, sometimes the numeric column (float type) can be implicit converted to scientific notation.
A number 142972 can be saved as 1.42972E+005 as a string, which may cause problem.
But you can convert the data back to its normal numeric format with some T-SQL functions.
Here is a sample code snippet:

  
  
  

  
create table test (col varchar(15))

insert into test values('1.42972E+007'),('12345678'),('ABCDE12345')
select  ISNULL(Str(Try_CONVERT(float, col)),col) as col 
from test

 

 

drop table test


Find 90 days continuous between date range

https://social.msdn.microsoft.com/Forums/en-US/b1e0ce27-0a54-46ee-aa4b-d55f2cd1f739/find-90-days-continuous-between-date-range?forum=transactsql

  
  
DECLARE @SourceData TABLE (
MemberID CHAR(3),
EffectiveDate DATE,
EndDate DATE)

INSERT INTO @SourceData VALUES ('123', '2002-01-01', '2100-12-31')
INSERT INTO @SourceData VALUES ('456', '2010-01-01', '2011-01-31')
INSERT INTO @SourceData VALUES ('789', '2011-11-01', '2012-12-31')
INSERT INTO @SourceData VALUES ('012', '2016-01-01', '2016-01-31')
INSERT INTO @SourceData VALUES ('012', '2016-02-01', '2016-02-29')


DECLARE @startdate DATE, @enddate DATE;
Set @startdate = (select min(EffectiveDate) from @SourceData)
Set @enddate   = (select max(EndDate) from @SourceData);
   
--****  create a calendar table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
 
   
,myDateCTE as
(
select s.MemberID, dateadd(day,n-1,@startdate) dt 
from Nums, (select distinct  MemberID  from @SourceData) s
  
)
  
,mycte as (
select MemberID, Year(c.dt)  dtYear, dt
,DATEADD(day, - ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY dt), dt) AS grp 
from  myDateCTE c
where Year(c.dt)<=Year(getdate()) AND EXISTS (
    select C.dt FROM  @SourceData sd
    where  c.MemberID=sd.MemberID and c.dt between sd.EffectiveDate and sd.EndDate  
)
)
 ,mycte1 as (
 Select MemberID, dtYear, grp , count(*) Over(PArtition by grp,MemberID) cnt
 ,count(*) Over(PArtition by grp,MemberID ORDER BY dt ROWS 89 PRECEDING) cntSliding
from mycte)

Select  Distinct  MemberID, dtYear  from mycte1
WHERE cnt>=90 and cntSliding>=90
/*

MemberID	dtYear
123	2002
123	2003
123	2004
123	2005
123	2006
123	2007
123	2008
123	2009
123	2010
123	2011
123	2012
123	2013
123	2014
123	2015
123	2016
456	2010
456	2011
789	2012

*/


 

Running Total for Two Columns

  
  
 USE [DB2]
GO
/****** Object:  Table [dbo].[test]    Script Date: 12/27/2016 10:11:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
	[FAMID] [int] NULL,
	[MEMID] [int] NULL,
	[CLM] [varchar](10) NULL,
	[DT] [date] NULL,
	[OOP] [int] NULL,
	[MEMOOP] [int] NULL,
	[FAMOOP] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[test] ([FAMID], [MEMID], [CLM], [DT], [OOP], [MEMOOP], [FAMOOP]) 
VALUES (1234, 1, N'abc', CAST(N'2016-01-01' AS Date), 15, 15, 15),
(1234, 1, N'def', CAST(N'2016-01-02' AS Date), 15, 30, 30),
(1234, 1, N'ghi', CAST(N'2016-01-03' AS Date), 15, 45, 45),
(1234, 2, N'klm', CAST(N'2016-01-04' AS Date), 15, 15, 60),
(1234, 2, N'nop', CAST(N'2016-01-05' AS Date), 15, 30, 75),
(1234, 2, N'qrs', CAST(N'2016-01-06' AS Date), 15, 45, 90),
(1234, 3, N'tuv', CAST(N'2016-01-07' AS Date), 15, 15, 105),
(1234, 3, N'wxy', CAST(N'2016-01-08' AS Date), 15, 30, 120),
(1234, 3, N'z12', CAST(N'2016-01-09' AS Date), 15, 45, 135)




Set statistics io on
Set statistics time on

;with mycte as (select *
,row_number() Over(Partition by MEMID Order by DT) rn 
, row_number() Over( Order by DT) rn1 
from test
)


,mycte1 AS
(
    SELECT FAMID,MEMID,CLM,DT,	OOP, OOP as runningtotal1,   OOP as runningtotal2
	, rn1
	-- ,rn  
	FROM mycte
    WHERE rn1 = 1  
    UNION ALL
    SELECT  m.FAMID,m.MEMID,m.CLM,m.DT,	m1.OOP, m1.runningtotal1+ m.OOP,Case when m.MEMID=m1.MEMID 
	then m1.runningtotal2+ m.OOP else m1.OOP end 	
	,m.rn1
	--, m.rn	  
      FROM mycte1 m1 INNER JOIN mycte m ON m.rn1 = m1.rn1 + 1 	
 
)

 

Select FAMID,	MEMID,	CLM, DT,OOP
, runningtotal1, runningtotal2  
from mycte1
Order by FAMID,	MEMID,DT

option(maxrecursion 0)


--SQL Server 2012 SUM(..) Over 
Select FAMID,	MEMID,	CLM, DT,	OOP
, Sum(OOP) Over(Partition by FAMID Order by DT ) runningtotal1
, Sum(OOP) Over(Partition by FAMID,MEMID Order by DT) as runningtotal2  
from test
Order by FAMID,	MEMID,DT
 

Set statistics io off
Set statistics time off