Category: Development

PowerShell and SQL Server

To be able to use the “sqlps” PowerShell Module you first need to install it from: Link

  • SQLSysClrTypes.msi – CLR Types for SQL Server
  • SharedManagementObjects.msi – Shared Management Objects
  • PowerShellTools.msi – PowerShell Extension for SQL Server

For information on how using this cmdlets, look it up in the ISE or here.

Using .net api:

# SQL-Server settings
$Database = "Database" # Database name
$Server = "SERVER\SQLEXPRESS"; # SQL-Server Instanz
 
# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "SELECT [Report],[Filiale],[E-Mail] FROM [dbo].[verteiler]"; # The query

## Example Database Layout
## "Report","Filiale","E-Mail"
## "012","12","xyz@irgendwo.de"
## "033","33","abc@web.de"
## "112","112","caz@aol.com"
 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
$objTable = $DataSet.Tables[0]