Write PowerShell Output to SQL Server Table

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  August 11, 2014 at 5:50 am

  #1736810

  ajiteshmalhotra (8/11/2014)

  ---

  Hi,

  I have created the power shell script for database information.Please find the below query:-

  ForEach ($instance in Get-Content "C:\temp\test\sqlserverlist.txt")

  {

  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

  $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

  $dbs=$s.Databases

  $dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

  ForEach ($dbs in $dbss)

  {

  $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=AITSSQL22; Initial Catalog=Workdb; Integrated Security=SSPI")

  $conn.Open()

  $cmd = $conn.CreateCommand()

  $cmd.CommandText = $insert_stmt

  $name=$dbs.name

  $Collation=$dbs.Collation

  $CompatibilityLevel=$dbs.CompatibilityLevel

  $AutoShrink=$dbs.AutoShrink

  $RecoveryModel=$dbs.RecoveryModel

  $Size=$dbs.Size

  $SpaceAvailable=$dbs.SpaceAvailable

  $insert_stmt = "INSERT INTO dbo.temp_ajmalh2(Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable)

  VALUES('$Name', '$Collation', '$CompatibilityLevel', '$AutoShrink', '$RecoveryModel', '$Size', '$SpaceAvailable')"

  $cmd.ExecuteNonQuery()

  $conn.Close()

  }

  }

  But data is not going on database table. Could you please help me out for this.

  I am new in powershell.

  Thanks

  Ajitesh Malhotra

  $insert_stmt = "INSERT INTO dbo.temp_ajmalh2(Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable)

  needs to be before

  $cmd.CommandText = $insert_stmt

  Otherwise you have no statement to run. This is because strings are immutable objects that are treated as value types i.e. you assigned the value of $insert_stmt at that time i.e. null.

  Hope that helps.

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• Atul DBA

  Hall of Fame

  Points: 3610

  More actions

  June 1, 2018 at 6:33 pm

  #1992732

  I have made a simple process and used the Script as SQL Job to populate the Table for historical as well Reporting purpose.
  

  #Connection information for the database server where the SQLInstances and DatabaseBackups tables reside
  $params

  = @{'server'='MySQLServer1';'Database'='Atul_Test'}

  Function writeDiskInfo

  {

  param($server,$devId,$volName,$frSpace,$totSpace)

  try{

  if($totSpace -ne 0 -or $frSpace -ne 0)

  {

  $totSpace=[math]::Round(($totSpace/1073741824),2)

  $frSpace=[Math]::Round(($frSpace/1073741824),2)

  $usedSpace = $totSpace - $frspace

  $usedSpace=[Math]::Round($usedSpace,2)

  $freePercent = ($frspace/$totSpace)*100

  $freePercent = [Math]::Round($freePercent,0)

  } else

  {

  $totSpace=[math]::Round(($totSpace/1073741824),2)

  $frSpace=0

  $usedSpace = $totSpace

  $usedSpace=[Math]::Round($usedSpace,2)

  $freePercent = 0

  }}

  catch

  {    $freePercent=0 }

  $InsertResults = @"
  INSERT INTO [Atul_Test].[dbo].[DiskDetails](servername,DriveName,TotalCapacity,UsedCapacity,FreeSpace,FreeSpacePercentage)

  VALUES ('$SERVER','$devId',$totSpace,$usedSpace,$frSpace,$freePercent)

  "@     

  invoke-sqlcmd @params -Query $InsertResults

  }

  Function Get-ServerDiskStatus ($SQLInstance)

  {

  $dp = Get-WmiObject win32_logicaldisk -ComputerName $SQLInstance| Where-Object {$_.drivetype -eq 3}

  foreach ($item in $dp)

  {

  writeDiskInfo $SQLInstance $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size

  }}

  #Grab our list of servers, iterate through them and call the function which rights to the database

  $Srv = invoke-sqlcmd @params -Query "SELECT ServerName from [Atul_Test].[dbo].[MyServersList]"

  foreach ($Instance in $srv){    Get-ServerDiskStatus $Instance.ServerName}

  Table Script :

  CREATE TABLE [dbo].[DiskDetails](

  [servername] [varchar](100) NULL,

  [DriveName] [varchar](3) NULL,

  [TotalCapacity] [decimal](15, 2) NULL,

  [UsedCapacity] [decimal](15, 2) NULL,

  [FreeSpace] [decimal](15, 2) NULL,

  [FreeSpacePercentage] [int] NULL,

  [logDate] [date] NULL

  ) ON [PRIMARY]

  GO

  ALTER TABLE [dbo].[DiskDetails] ADD DEFAULT (CONVERT([varchar](10),getdate(),(112))) FOR [logDate]

  GO