En este artículo, analizaremos todas las formas efectivas de conectarse a Microsoft SQL Server y ejecutar consultas SQL desde PowerShell. Hay muchas maneras de trabajar con SQL Server usando PowerShell, y es fácil confundirse cuando estudia muchos artículos en la Web, ya que todos describen diferentes métodos, e incluso un administrador experimentado puede tener preguntas.
Consultas T-SQL en PowerShell usando System.Data.OleDb
Como PowerShell puede acceder a clases de .NET Framework, puede usar clases de Sistema.Datos.OleDb para ejecutar consultas T-SQL.
Aquí hay una secuencia de comandos de PowerShell de muestra para conectar SQL Server usando la clase System.Data.OleDb. Ejecutemos una consulta SELECT contra una tabla en la base de datos MS SQL:
$dataSource = “lon-sql01testdb”
$database = “master”
$sql = “SELECT * FROM sysdatabases”
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; “ +
“Initial Catalog=$database; “ +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
$rows=($dataset.Tables | Select-Object -Expand Rows)
echo $rows
Este es un ejemplo de un script de PowerShell para ejecutar una consulta INSERT/UPDATE/DELETE contra la base de datos MSSQL:
$dataSource = “lon-sql01testdb”
$database = “test”
$sql = "insert into test_table (test_col) Values ('Test')"
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; ” +
“Initial Catalog=$database; ” +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$command = New-Object data.OleDb.OleDbCommand $sql
$command.connection = $connection
$rowsAffected = $command.ExecuteNonQuery()
los $rowsAffected La variable contiene el número de filas añadidas o modificadas. Para ejecutar una consulta de actualización o eliminación, simplemente cambie la línea de la consulta SQL en el $sql variable.
Ejecución de consultas SQL en PowerShell mediante la clase System.Data.SqlClient
Para acceder a MS SQL Server desde PowerShell, puede usar otra clase .NET integrada: System.Data.SqlClient. Este es un ejemplo de una consulta SELECT en un script de PowerShell con SqlClient:
$server = "lon-sql01testdb"
$database = "Test"
$sql = "select * from test_table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$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]
Un ejemplo de una consulta INSERT/DELETE/UPDATE:
$server = "lon-sql01testdb"
$database = "Test"
$sql = "insert into test_table (test_col) Values ('Test')"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$rowsAffected = $SqlCmd.ExecuteNonQuery();
$SqlConnection.Close()
Nota. El código que contiene las clases SqlClient es muy parecido al código con OleDB. Estas clases funcionan de manera similar.:
- Se crea un objeto de conexión de servidor MSSQL;
- Se crea un objeto con una consulta SQL y se le asigna el objeto de conexión;
- Luego, en caso de ejecutar una consulta SELECT, se crea un objeto adaptador y la consulta se ejecuta en el contexto de este objeto;
- En caso de ejecutar una consulta INSERT/UPDATE/DELETE, el objeto con la consulta (que contiene el objeto de conexión) ejecuta la
ExecuteNonQuery()método.
Consulta SQL en PowerShell usando el módulo SQL Server Management Studio
Usar Microsoft.SqlServer.Smo (SMO) clases, Estudio de administración de SQL Server debe estar instalado en su computadora.
Cargue el módulo SMO, cree un nuevo objeto de servidor y luego ejecute una consulta SELECT:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "lon-sql01testdb"
$results = $serverInstance.Databases['test'].ExecuteWithResults('select * from test_table')
foreach ($res in $results.Tables) {
$nbsp;echo $res
}
Para una consulta de inserción/actualización/eliminación, ejecute ExecuteNonQuery:
$db = $serverInstance.Databases['test']
$db.ExecuteNonQuery("insert into test_table (test_col) Values ('123456')")
Nota. También puede instalar bibliotecas SMO a través del Administrador de paquetes NuGet:
- Descargar nuget.exe https://www.nuget.org/descargas;
- Ejecute PowerShell como administrador y vaya al directorio que contiene nuget.exe;
- Correr:
.nuget.exe Install Microsoft.SqlServer.SqlManagementObjects.
- La carpeta Microsoft.SqlServer.SqlManagementObjects con todas las DLL aparecerá en el directorio actual;
- Cargue la biblioteca SMO en su sesión de PowerShell desde un archivo DLL. Agrégalo a tu script:
add-type –Path "C:UsersusernameDownloadsMicrosoft.SqlServer.SqlManagementObjects.150.18208.0libnet45Microsoft.SqlServer.Smo.dll"
Luego, las clases de SMO estarán disponibles para su uso.
Invoke-Sqlcmd Cmdlet del módulo SQLServer PowerShell
Usar el Invocar-Sqlcmd cmdlet, instale el Módulo SqlServer para PowerShell. Ejecute PowerShell con privilegios de administrador y ejecute el comando:
Install-Module -Name SqlServer
(Presione Y y luego ENTER para aceptar las notificaciones del instalador).
Después de la instalación, puede asegurarse de que el módulo se haya instalado correctamente ejecutando este comando:
Get-Module SqlServer -ListAvailable
los Invocar-Sqlcmd cmdlet es más fácil e intuitivo que otras formas de conexión a un Microsoft SQL Server desde PowerShell. Invoke-Sqlcmd usa la misma sintaxis para las consultas SELECT e INSERT/UPDATE/DELETE.
Este es un ejemplo de una consulta Select:
Invoke-Sqlcmd -ServerInstance "lon-sql01testdb" -Query "sp_who"
Este es un ejemplo de una consulta INSERT:
Invoke-Sqlcmd -ServerInstance "lon-sql01testdb" -Database "test1" -Query "insert into test_table (test_col) Values (‘123321’)"
A diferencia de otros métodos, una consulta en el Invoke-Sqlcmd siempre se establece en el –Query parámetro.
¿Qué opción de conexión SQL debería usar?
La elección entre oledb/smo/sqlclient/invoke-sqlcmd se basa en la tarea y el entorno en el que ejecutará un script de PowerShell.
Si desea implementar una secuencia de comandos en varios servidores (por ejemplo, su secuencia de comandos recopila datos de supervisión localmente), no es razonable usar SMO o el módulo SqlServer PowerShell (Invoke-SQLcmd), ya que tendrá que instalar paquetes adicionales en los hosts remotos para ejecute el script, y es mejor evitarlo si hay muchos servidores.
A su vez, el módulo SqlServer for PowerShell ofrece muchos otros cmdlets para trabajar con su servidor SQL (puede obtener más información aquí: https://docs.microsoft.com/en-us/powershell/module/sqlserver). El módulo contiene más comandos para administrar el propio SQL Server.
Si su secuencia de comandos realizará tareas no administrativas (es responsable de alguna parte de la lógica comercial, por ejemplo), vale la pena usar System.Data.SqlClient/SMO, ya que proporcionan herramientas de desarrollo más convenientes. Una ventaja de OleDB es que puede funcionar no solo con un servidor SQL, sino también con Access/Oracle/Firebird/Interbase.
Artículos Interesantes
Relacionados:
Contenido
