Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 1 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 2 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 2

The MAK

Part I and Part II of this series showed PowerShell and SMO settings, simple WMI cmdlets. This part 3 will cover how to write code for the PowerShell cmdlet and execute them. Script code is essential for automatic and repetitive operations.

Enforcement policy

The four different types of Windows PowerShell enforcement policies are Restricted, AllSigned, RemoteSigned and Unrestricted. We will find the Windows PowerShell execution policy on the workspace. [Figure 1.0]

Cmdlet:
Get-executionpolicy
Result:
Restricted

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 3
Figure 1.0

Suppose we have the following line of code on PowerShell script 'a.ps1'. [Figure 1.1]

Echo 'test'

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 4
Figure 1.1

If you try to execute the code when PowerShell's execution policy is restricted, the following error message will appear. [Figure 1.2]

The command to execute PowerShell code

. /a.ps1

Result

File C: psa.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 <<<<

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 5
Figure 1.2

Please change the execution policy to unrestricted. The command to execute PowerShell code can already be executed by the following cmdlet. [Figure 1.3]

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 6
Figure 1.3

Now try executing the a.ps1 script as shown in the image below. [Figure 1.4]

Order

./a

Result

Kiểm TRA

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 7
Figure 1.4

Control Input in PowerShell code

When performing repetitive operations, we like applications to interact more and build applications to require user input. We can do the same with PowerShell.
Create a PowerShell code that accepts the name of the SQL Server field and the database name. Also, let PowerShell display all tables on that database. This can be done with the read-host cmdlet.

Example 1: [Figure 1.5]

Read-host 'Please Enter Second Number'.

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 8
Figure 1.5

Example 2

We can assign the cmdlet value to be a variable. [Figure 1.6]

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

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 9
Figure 1.6

Combine example 1 and example 2 connect to SQL Server.

Generating PowerShell code is called connectsql.ps1. [Figure 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]

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 10
Figure 1.7

Now let's execute the above connectsql.ps1 code above. [Figure 1.8]

./connectsql
Enter SQL Server Name :: HOMESQLEXPRESS
Enter Database Name :: AdventureWorks

Note : HOME is the server and SQLEXPRESS is the instance name of SQL Server. Replace this name with your server name and SQL Server. AdventureWorks is the database name. You also need to replace this database name for the database name on the server.

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 11
Figure 1.8

The connectsql code assigns the value entered into the $ SQLSERVER and $ DATABASE variables, connects the 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 writing code automatically, you don't want users to enter data. Instead, we should accept the parameters.

Please upgrade the code above by accepting the parameters. [Figure 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] 

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 12
Figure 1.9

Perform the code as shown below. [Figure 2.0]

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 13
Figure 2.0

The connectsql assigns values ​​as a parameter to the $ SQLSERVER and $ DATABASE separate variables, connects strings using those variables and displays the results.

Result

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

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 14 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 4
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 15
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 16
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 17
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 18
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 19
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 20
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Picture 21
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11

4 ★ | 1 Vote

May be interested

  • Move the Tempdb and Master databases on SQL ServerPhoto of Move the Tempdb and Master databases on SQL Server
    when you want to move a typical user database to another drive to increase performance or to split logs, you can run sp_detach and sp_attach to perform the migration. however, for moving the master database and
  • Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4Photo of Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4
    part i and part ii of this series showed simple power shell settings, smo and wmi cmdlets. part iii instructs writing powershell and connecting to sql server. part 4 will show you how to use powershell code to iterate file content and connect to other servers.
  • Create upload file with VB.NETPhoto of Create upload file with VB.NET
    uploading files is a common feature used by users to upload documents to server-based applications. this is an essential part of many applications from basic to more complex. in this article, i will show you how to add upload file to asp.net applications and create it in vb.net.
  • New points in SQL 2008 (Part 1)Photo of New points in SQL 2008 (Part 1)
    sql server 2008 will be released around february 2008, along with new versions of visual studio and windows. ctp (community technology preview) of sql 2008 is now available on the internet and you can download it from the microsoft url. in this article we will highlight some of the new features and benefits of sql server 2008
  • Query XML data from a table with XML data typePhoto of Query XML data from a table with XML data type
    this article illustrates how to create an xml schema, create a table with an xml data type, import an xml file into a table with an xml data type, query the xml file, and produce the same result as the actual result set. shown by sql transact commands.
  • Improvements in ASMPhoto of Improvements in ASM
    asm enhances the performance provided by raw disks, making it difficult to administer these disks. in addition, asm provides storage mapping policy, which can be configured at the file level instead of the drive level according to the retrieval method.