Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3

July 3, 2007

Part I and Part II of this series discussed PowerShell installation and simple SMO, WMI cmdlets. Part 3 of this series illustrates how to script PowerShell cmdlets and execute them. Scripting is essential for automation and executing repetitive tasks.

Execution Policy

The four different types of Windows PowerShell execution policies are Restricted, AllSigned, RemoteSigned and Unrestricted. Let us find the execution policies of Windows PowerShell in our workstation. Refer Fig 1.0

Cmdlet:

Get-executionpolicy

Result:

Restricted

Let us assume that we have the following code on PowerShell script 'a.ps1'. [Refer Fig 1.1]

Echo “test”

If we try to execute the script when PowerShell’s execution policy is restricted, we get the following error message. [Refer Fig 1.2]

Command to Execute a PowerShell script

. /a.ps1

Result

File C:\ps\a.ps1 cannot be loaded because the execution of scripts is disabled
on this system. Please see "get-help about_signing" for more details.
At line:1 char:3
+ ./a <<<<

Let us change the execution policy to unrestricted. This can be done by executing the following cmdlet. [Refer Fig 1.3]

Now let us try to execute the a.ps1 shell script as shown in the figure below. [Refer Fig 1.4]

Command

./a

Result

Test

Console Input in PowerShell scripting

When it comes to executing repetitive tasks, we like the application to be more interactive and construct the application to request input from the user. We could do the same in PowerShell.

Let us create a PowerShell script that accepts the name of the SQL Server instance and database name. In addition, let us have PowerShell display all of the tables on that database. This could be achieved using read-host cmdlet.

Example 1: [Refer Fig 1.5]

Read-host “Please Enter Second Number”. 

Example 2

We can assign the value of the read-host cmdlet to a variable. [Refer Fig 1.6]

$a=read-host "Please Enter Second Number" $a

Now let us combine example 1 and 2 and connect to SQL Server.

Create the following PowerShell script, connectsql.ps1, as shown below. [Refer Fig 1.7]

$SQLSERVER=read-host "Enter SQL Server Name:"
$Database=read-host "Enter Database Name:"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sysobjects where type='u'"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

Now let us execute the PowerShell script, connectsql.ps1, as shown below. [Refer Fig 1.8]

./connectsql
Enter SQL Server Name:: HOME\SQLEXPRESS
Enter Database Name:: AdventureWorks

Note: HOME is the host machine and SQLEXPRESS is the SQL Server instance name. Please replace it with your hostname and SQL instance name. AdventureWorks is the database name. Please replace the database name with your database name in your server.

Fig 1.8

The connectsql script assigns the value entered to the $SQLSERVER and $DATABASE variables, forms the connection string using those variables and displays the result.

Result:

name
----
ProductProductPhoto
StoreContact
Address
ProductReview
TransactionHistory
AddressType
ProductSubcategory
AWBuildVersion
TransactionHistoryArchive
ProductVendor
BillOfMaterials
UnitMeasure
Vendor
PurchaseOrderDetail
Contact
VendorAddress
VendorContact
PurchaseOrderHeader
ContactCreditCard
WorkOrder
ContactType
CountryRegionCurrency
WorkOrderRouting
CountryRegion
CreditCard
Culture
Currency
SalesOrderDetail
CurrencyRate
Customer
SalesOrderHeader
CustomerAddress
Department
Document
Employee
SalesOrderHeaderSalesReason
SalesPerson
EmployeeAddress
EmployeeDepartmentHistory
EmployeePayHistory
SalesPersonQuotaHistory
Illustration
SalesReason
Individual
SalesTaxRate
JobCandidate
Location
SalesTerritory
Product
SalesTerritoryHistory
ScrapReason
Shift
ProductCategory
ShipMethod
ProductCostHistory
ProductDescription
ShoppingCartItem
ProductDocument
ProductInventory
SpecialOffer
ProductListPriceHistory
SpecialOfferProduct
ProductModel
StateProvince
ProductModelIllustration
DatabaseLog
ProductModelProductDescriptionCulture
ErrorLog
Store
ProductPhoto

However, when you automate the script, you don’t want users to give input. Instead, we could accept parameters.

Let us update the above script in such a way that it accepts parameters. [Refer Fig 1.9]

param (
  [string] $SQLSERVER,
  [string] $Database
)
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=$DATABASE;Integrated  
Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sysobjects where type='P'"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

Now let us execute the script as shown below. [Refer Fig 2.0]

The connectsql assigns the value passed as a parameter to the $SQLSERVER and $DATABASE variables respectively, forms the connection string using those variables and displays the result.

Result

name
----
uspPrintError
uspLogError
uspGetBillOfMaterials
uspGetEmployeeManagers
uspGetManagerEmployees
uspGetWhereUsedProductID
uspUpdateEmployeeHireInfo
uspUpdateEmployeeLogin
uspUpdateEmployeePersonalInfo

Conclusion

As mentioned in the beginning of this article series, the illustration above demonstrates the use of PowerShell Scripting in conjunction with SQL Server.

» See All Articles by Columnist MAK