Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1
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
Figure 1.0
Suppose we have the following line of code on PowerShell script 'a.ps1'. [Figure 1.1]
Echo 'test'
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 <<<<
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]
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
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'.
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
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]
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.
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]
Figure 1.9
Perform the code as shown below. [Figure 2.0]
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 4
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11
You should read it
- Instructions on how to use PowerShell in Windows Server 2012
- What to do when Windows can't find PowerShell?
- Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1
- Use PowerShell to download any file on Windows 10
- Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4
- PowerShell command in Windows
- How to Install or Update PowerShell on Windows 11
- How to check PowerShell version in Windows 10
May be interested
- Move the Tempdb and Master databases on SQL Serverwhen 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 4part 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.NETuploading 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)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 typethis 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 ASMasm 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.