Here's a sample program that allows you to access SQL Server from PowerShell. It may work with other versions but was tested only with PowerShell 4.0.
- # sqltst.ps1 -- just a test program
- #
- $SqlConn = 0
- $Server = 'Your Server Name'
- $Database = 'Your Database'
- $UserName = 'Server Login'
- $Password = 'Login Password'
- function getEnvId
- {
- $envName = $args[0];
- $sqlStmt = @'
- SELECT [envId]
- FROM [PEDB].[dbo].[Environments]
- WHERE [envName] = @envName
- '@
- $sth = New-Object -typeName System.Data.SqlClient.SqlCommand($sqlStmt, $SqlConn)
- $Null = $sth.Parameters.Add('@envName', [System.Data.SqlDbType]::NVarChar, 80)
- $sth.Parameters['@envName'].Value = $envName
- $prevErrAct = $ErrorActionPreference
- $ErrorActionPreference = 'Stop'
- try {
- $res = $sth.ExecuteReader()
- }
- catch {
- Write-Error "Cannot execute SELECT -- $($_.Exception.Message)"
- }
- $ErrorActionPreference = $prevErrAct
- $envId = 0
- while ($res.Read()) {
- $envId = $res['envId']
- }
- $res.Close()
- $envId
- }
- # open the connection
- $dsn = "Server=$Server;Database=$Database;Integrated Security=False;UID=$Username;PWD=$Password";
- $ErrorActionPreference = 'Stop'
- try {
- $SqlConn = New-Object -typeName System.Data.SqlClient.SqlConnection
- $SqlConn.ConnectionString = $dsn
- $SqlConn.Open()
- }
- catch {
- Write-Error "Cannot open connection to $Server"
- Write-Error " -- $($_.Exception.Message)"
- }
- $ErrorActionPreference = 'Continue'
- # get the environment id for the name
- getEnvId('PROD')
No comments:
Post a Comment