PowerShell--Use of SQL Server (2)

Reference:

Http://www.shangxueba.com/jingyan/105946.html

First, do not SqlServerCmdletSnapin100 this snapin to write a few operations common data script

1. As a reader asks how to display a table in a database with PowerShell, here is a simple function for reference

#==============================================
# sql Server 2008-powershell
# Show User table
#zivsoft
#==============================================
function showcustomizeddatatable{
$SQLSERVER =read-host "Enter SQL Server Name:"
$Database =read-host "Enter Database Name:"
$SqlConnection = New-object System.Data.SqlClient.SqlConnection
$CnnString = "server= $SQLSERVER;D atabase= $DATABASE; Integrated security=true"
$SqlConnection. ConnectionString = $CnnString
$SQLCMD = New-object System.Data.SqlClient.SqlCommand
$SqlCmd. CommandText = "SELECT Name fromsysobjects 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]
}

2. Displaying data from SQL queries

#==============================================
# SQL Server 2008 - PowerShell
# 显示查询数据内容
#zivsoft
#==============================================
function Get-DataTable([string]$query)
{
　　　 $dataSet= new-object "System.Data.DataSet" "DataSetName"
　　　 $da = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $CnnString)
　　　 [void] $da.Fill($dataSet)
　　　 return $dataSet.Tables[0]
}

3. Building a Database Join string

################################################################################### ################
# www.zivsoft.com
# settingsDatabase ConnectionString
############################################################################################ #######
functionGlobal: set-sqlconnection ($Server = $ (read-host "SQL Server Name"), $Database = $ (read-host "Default Database"), $UserName, $P Assword)
{
#如果用户名和密码都不为空
if ($UserName-gt $null)-and ($Password-gt $null)) {
$login = "User Id = $UserName; Password = $Password "
}
else {
#采用整合安全机制登陆
$login = "Integrated Security = True"
}
#Database ConnectionString
$SqlConnection. ConnectionString = "Server = $Server; Database = $Database; $login "
}

4. Another style of acquiring database data

#================================================
# Similar to DataTable getdatatable (String strSQL)
#周利华
#================================================
functionGlobal: get-sqldatatable ($Query = $ (read-host "input SQL statement"))
{
#打开Database
if (-not ($SqlConnection. State-like "Open")) {$SqlConnection. Open ()}

#实例化SQLCommand
$SQLCMD = New-object System.Data.SqlClient.SqlCommand $Query, $SqlConnection

$SqlAdapter = New-object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter. SelectCommand = $SQLCMD

$DataSet = New-object System.Data.DataSet
$SqlAdapter. Fill ($DataSet) | Out-null

$SqlConnection. Close ()

#返回DatabaseTable
Return $DataSet. Tables[0]
}

Second, the above is ordinary PowerShell through the ADO Operation database , listed below are the more cool SQL Server integrated PowerShell commands

Let's take a look at the help information for this key cmdlet: Invoke-sqlcmd

NAME
Invoke-sqlcmd

Synopsis
Runs a script. Containing statements fromThe languages (Transact-SQL and XQuery) and commands supported by the SQL ServerSQLCMD utility.

--------------Example 1--------------

C:\ps>invoke-sqlcmd-query "Select GETDATE () as Timeofquery;"-ServerInstance "Mycomputer\myinstance"

This example connects to a named instance of the Database Engine on a computer and runs a basic Transact-SQL script.

Timeofquery
-----------
10/7/2007 1:04:20 PM

--------------Example 2--------------

C:\ps>invoke-sqlcmd-inputfile "C:\MyFolder\TestSqlCmd.sql" | Out-file-filepath "C:\MyFolder\TestSqlCmd.rpt"

This example reads a file containing Transact-SQL statements andSQLCMD commands, runs the file, and writes the output to another file. Ensure all output files is secured with the appropriate NTFS permissions.

Output sent to Testsqlcmd.rpt.

--------------Example 3--------------

c:\ps> $MyArray = "myvar1= ' String1 '", "myvar2= ' String2 '"
Invoke-sqlcmd-query "SELECT ' $ (MYVAR1) as Var1, ' $ (MYVAR2) as Var2;"-variable $MyArray

This example uses a array of character strings as input to the-variable parameter. The array defines multipleSQLCMD variables. The $ signs in the SELECT statement that identify the SQLCMD variables is escaped using the Back-tick (') character.

Var1 VAR2
----　　　　　　　　　　　　　　　　　　　　　　　 ----
String1 String2

--------------Example 4--------------

C:\ps>set-location sqlserver:\sql\mycomputer\myinstance
Invoke-sqlcmd-query "Select GETDATE () as Timeofquery;"-serverinstance (Get-item.)

This example uses Set-location to navigate to the SQL Server PowerShell provider path for a instance of the Database Engi Ne. Then the example uses Get-item to retrieve a SMO Server object for use as the-serverinstance parameter of Invoke-sqlcmd.

Timeofquery
-----------
10/18/2007 8:49:43 PM

--------------Example 5--------------

C:\ps>invoke-sqlcmd-query "PRINT N ' abc '"-verbose

This example uses the Powershell-verbose parameter to return the message output of the PRINT command.

Verbose:abc

--------------Example 6--------------

C:\ps>set-location Sqlserver:\sql\mycomputer\default\databases\adventureworks
Invoke-sqlcmd "Select Db_name () as DatabaseName;"

This examples uses a positional string to supply the input to the-query parameter. It also shows how Invoke-sqlcmd uses the current path to set theDataBase context to AdventureWorks.

Warning:using provider Context. Server = MyComputer, Database = AdventureWorks.

DatabaseName
------------
AdventureWorks

After reading this help carefully, we found that all of the above operations in the. NET framework can be replaced by Invoke-sqlcmd, which is very simple and convenient.

For example, get all the user tables in the home data:

Invoke-Sqlcmd -Query "use home;SELECT name as tablename from sysobjects where xtype=‘U‘" -QueryTimeout 3 | ft -auto

For example, display the contents of the UserInfo table in the home database:

Invoke-Sqlcmd -Query "use home;SELECT * from UserInfo" -QueryTimeout 3 | ft - auto

650) this.width=650; "Title=" Click to view large image "alt=" 916x203 "src=" Http://img.shangxueba.cn/jyimg/20130206/214343JT-0.jpg " Width= "630" height= "139"/>

Finally, add that if you open PowerShell directly with Management Studio in SQL Server 2008, you can directly manipulate cmdlets like Invoke-sqlcmd, but if you don't have management What about Studio?

Very simple, with add-pssnapin SqlServerCmdletSnapin100 easy to fix.

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1643937

PowerShell--Use of SQL Server (2)