Daniel Calbimonte

Trabajar con la línea de comando de SQL Server (sqlcmd)

September 20, 2019 by

Introducción

Sqlcmd se utiliza para ejecutar consultas, de las sentencias referidas a T-SQL y scripts de SQL Server utilizando la línea de comandos.

En el artículo anterior de Cómo trabajar con la línea de comando y Azure para automatizar tareas, nosotros hemos trabajado con el sqlcmd utilizando Azure.

En este nuevo capítulo, les mostraremos los siguientes ejemplos en un servidor SQL local que utiliza sqlcmd:

  1. Deba trabajar con el modo interactivo sqlcmd, incluido cómo
    1. Conectarse a un servidor SQL Server
    2. Verificar la base de datos actual
    3. Listar bases de datos
    4. Debe verificar si SQL Server distingue entre mayúsculas y minúsculas
    5. Debe revisar la edición de SQL Server
    6. Se debe verificar la autenticación del servidor SQL
    7. Enumerar el conjunto de variables
  2. Ejecutando sqlcmd en modo comando incluyendo cómo
    1. Copia de seguridad de una base de datos
    2. Ejecutar un script T-SQL y recibir la salida en un archivo
    3. Trabajar con variables
    4. Se debe enumerar los nombres de tabla de una base de datos
    5. Enumerar los nombres de columna de una base de datos
    6. Verificar todos los comandos
    7. Salir si el comando falla
    8. Mostrar mensajes de error según el nivel de error
    9. Aceptar entrada del usuario
  3. 3. Trabajar en SSMS en modo sqlcmd incluyendo cómo
    1. Ejecutar sqlcmd en SSMS
    2. Configurar el modo sqlcmd por defecto en SSMS
  4. Trabajar con PowerShell incluyendo cómo
    1. Invocar sqlcmd usando PowerShell
    2. Ejecutar scripts en SQL PowerShell (verificar la fragmentación de la tabla)
    3. Resultados detallados de salida
  5. DAC
    1. Cómo trabajar con una conexión de administrador dedicada (DAC)
  6. Cuándo usar el modo sqlcmd, modo interactivo, DAC, SSMS, PowerShell

Requisitos

  1. Sqlcmd instalado en una máquina con Windows (Linux admite sqlcmd, pero es ligeramente diferente).

Empezando

  1. Trabajar con el modo interactivo sqlcmd

    En modo interactivo, se puede escribir la entrada e interactuar usando la línea de comando

    1. Cómo se puede conectar a SQL Server usando sqlcmd

      Para conectarse a la máquina local, especifique el nombre de la instancia de SQL y sus credenciales:

      sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -E

      El valor –S es sirve para especificar el nombre del servidor SQL de la instancia y -E es utilizado para especificar una conexión confiable. En el caso de que no se especificara el nombre de SQL Server, intentará conectarse a la máquina local

      Cuando te conectes, verás el número 1>:

      El número 1> significa que está conectado y listo para recibir oraciones para ejecutar

      Si se habilitó la autenticación de SQL Server, usted deberá especificar un nombre de usuario y una contraseña de usuario (supongo que el usuario ya está creado). Debe tener en cuenta que deberá SALIR de sqlcmd para iniciar sesión con esta credencial

      sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -U jsmith

      La línea de comando requerirá la contraseña. Opcionalmente, se puede especificar la contraseña (no se recomienda esta opción, pero a veces es la única forma de trabajar):

      sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -U jsmith -P
      Mypwd$%1234565

    2. Cómo verificar la base de datos actual en sqlcmd

      En el momento en que se crea un inicio de sesión de SQL Server, se puede definir la base de datos predeterminada en la que se desea iniciar sesión. Si no se especifica, la base de datos maestra esta es la predeterminada

    3. Cómo enumerar las bases de datos usando sqlcmd

      Las siguientes oraciones enumerarán las bases de datos en la instancia de SQL:

      En la tabla sys.databases, tiene toda la información de la base de datos:

      También puede usar el procedimiento almacenado sp_databases:

    4. Cómo verificar si SQL Server puede distinguir entre mayúsculas y minúsculas en sqlcmd

      Las siguientes oraciones T-SQL serán utilizadas para detectar la información de clasificación, incluso si la máquina distinguiera entre mayúsculas y minúsculas:

      La información que se mostrará será la siguiente:

      Modern_spanish es la clasificación, CI significa que no es sensible a mayúsculas y minúsculas y CS que es sensible a mayúsculas y minúsculas. AS significa acento sensible y AI es acento insensible

      También se puede verificar la información, con el procedimiento sp_helpsort:

      La información que se muestra a continuación es la siguiente:

      Español moderno, insensible a mayúsculas y minúsculas, sensible al acento, insensible al kanatipo, insensible al ancho

    5. Cómo verificar la edición de SQL Server en SQL

      Puede consultar la Edición de SQL Server, utilizando las siguientes oraciones T-SQL:

      El resultado es el siguiente:

    6. Cómo verificar la autenticación de SQL Server en sqlcmd

      Previamente al inicio de Azure, habían dos opciones para autenticarse en SQL Server:

      1. Autenticación de Windows donde puede usar una cuenta de Active Directory o una cuenta local de Windows
      2. Autenticación de Windows y Autenticación de SQL donde también puede autenticarse utilizando una cuenta creada en SQL Server

      Para detectar la autenticación, se pueden usar las siguientes líneas de código SQL:

      El resultado mostrado es el siguiente:

      Si el resultado que se muestra es 0, significa que ambas autenticaciones están habilitadas. Si es 1, solo está habilitada la autenticación de Windows.

    7. Cómo enumerar el conjunto de variables

      Para enumerar todas las variables establecidas, usted deberá ejecutar el siguiente comando en sqlcmd:

      :ListVar

      El mismo que Mostrará todas las variables establecidas:

  2. Ejecutando sqlcmd en modo comando

    Puede ejecutar sqlcmd como comandos. Puede ejecutar scripts en modo comando

    1. Cómo ejecutar un script T-SQL y recibir la salida en un archivo en sqlcmd

      En el siguiente ejemplo, pasamos a mostrar cómo ejecutar un script usando sqlcmd y registrar los resultados en otro archivo

      Primero debemos crear un archivo de script llamado columnas.sql con las el siguiente código:

      select * from adventureworks2014.information_schema.columns

      En el cmd, ejecute el siguiente comando para invocar sqlcmd:

      sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -E -i c:\sql\columns.sql -o c:\sql\exit.txt

      -i se usa para especificar la entrada. Usted especifica el archivo de script con las consultas
      -o se usa para mostrar los resultados de la entrada en un archivo

      Se creará el archivo exit.txt:

      Si abrimos el archivo, veremos los resultados de salida:

    2. Cómo se puede hacer una copia de seguridad en sqlcmd

      Primero deberemos crear un script para hacer una copia de seguridad de la base de datos llamada backup.sql:

      En el cmd ejecuta el siguiente comando:

      sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -E -i c:\sql\backup.sql -o
      c:\sql\output.txt

      El resultado obtenido será similar a este:

      Los comandos deberán crear una copia de seguridad en un archivo llamado backup.sql en la carpeta c:\sql:

    3. Cómo trabajar con variables en sqlcmd

      Puede trabajarse con variables en sqlcmd. El siguiente ejemplo definirá la variable DATABASENAME con el valor adventureworks2014 y luego se cambiará el contexto a la base de datos especificada:

      El resultado mostrado es el siguiente:

      Como se puede observar, SETVAR se utiliza para especificar el valor de la variable. Entonces necesita usar $ () para la variable.

      Otro ejemplo consiste en establecer la variable CONTACTTYPEID en 3 y poder usarla en la cláusula where para encontrar un ID de tipo de contacto de acuerdo con el valor de la variable como se muestra a continuación:

      El resultado sería el siguiente:

    4. Cómo se puede enumerar los nombres de tabla de una base de datos en sqlcmd

      Puede enumerar todas las tablas de la base de datos utilizando la vista information_schema.tables. Primeramente crearemos un script llamado tables.sql. Este script contiene las tablas y vistas:

      A continuación, invocaremos sqlcmd para ejecutar el script.

      sqlcmd -E -i c:\sql\tables.sql -o c:\sql\output.txt -S DESKTOP-
      5K4TURF\SQLEXPRESS

      Los resultados del comando son los siguientes y se muestran en el archivo output.txt:

    5. Cómo se puede enumerar los nombres de columna de una base de datos en sqlcmd

      Las siguientes oraciones podrán enumerar los nombres de las tablas así como los nombres de las columnas de una base de datos en un script llamado columnas.sql:

      En el cmd ejecuta este comando:

      sqlcmd -E -i c:\sql\columns.sql -o c:\sql\output.txt -S DESKTOP-
      5K4TURF\SQLEXPRESS

      El resultado del archivo output.txt es el siguiente:

    6. Cómo se puede verificar todos los comandos

      Se pueden verificar todos los comandos sqlcmd usando este comando:

      Sqlcmd -?

      Este comando enumerará todos los comandos existentes:

    7. Cómo se puede salir si el comando falla

      El siguiente comando se cerrará si falla usando el parámetro –b:

      sqlcmd -E -q “create table adventureworks” –b -S DESKTOP-
      5K4TURF\SQLEXPRESS

      El comando se cerrará si existe algún error:

    8. Cómo se puede mostrar mensajes de error según el nivel de error

      Si hay un error, entonces se mostrará el error. No obstante, de acuerdo con el nivel de error, se puede detener este comportamiento de forma predeterminada utilizando la opción -m.

      A continuación existe un ejemplo sobre esto:

      El siguiente comando muestra un mensaje de error:

      Sin embargo, si agrega –m 16, el error ya no se mostrará debido a que el error tiene el nivel de 15:

      sqlcmd -E -q “create table adventureworks” -m 16 -S
      DESKTOP-5K4TURF\SQLEXPRESS

      -m 16 mostrará solamente los errores superiores a 16. Como puede ver, el mensaje de error ya no se muestra

    9. Cómo se puede aceptar la entrada del usuario

      El siguiente ejemplo ejecutará un script SQL con una variable. El ejemplo permitirá crear una base de datos especificada por el usuario

      Primero crearemos un script llamado createdb.sql con el siguiente contenido:

      A continuación, en el cmd deberemos ejecutar la base de datos especificando el nombre de la base de datos:

      sqlcmd -E -v DATABASENAME=”Userinput” -i
      c:\sql\createdb.sql

      El comando creará una base de datos llamada Userinput.

      En sqlcmd puede ejecutar el procedimiento almacenado sp_databases:

      Y podrá ver la base de datos creada:

  3. Trabajando en SSMS en modo sqlcmd

    1. Cómo ejecutar sqlcmd en SSMS

      En SSMS, haga clic en su consulta y seleccione Consulta>Modo SQLCMD:

      El siguiente ejemplo creará una base de datos denominada ventas en SSMS.

      Si todo está bien, seguidamente se creará una base de datos llamada ventas:

    2. ¿Cómo podemos establecer el modo sqlcmd por defecto en SSMS?

      Para hacer esto, vaya a Herramientas>Opciones en SSMS y marque Por defecto, abra nuevas consultas en modo SQLCMD

  4. Trabajando con PowerShell

    1. Cómo invocar sqlcmd usando PowerShell

      PowerShell se puede usar para invocar sqlcmd. Para poder abrir PowerShell para SQL Server, vaya a Windows Search y escriba sqlps:

      En sqlps, escriba estos cmdlets para ejecutar el procedimiento almacenado sp_who:

      invoke-sqlcmd -query “sp_who”

      Es muy importante tomar en cuenta que, si tiene SSMS 17 o posterior, SQL PowerShell se instala por separado. Para obtener más información sobre la instalación de SQL PowerShell, consulte nuestro enlace:

    2. Cómo se puede ejecutar scripts en SQL PowerShell (verificar la fragmentación de una tabla)

      Es posible ejecutar scripts de SQL Server con PowerShell. El siguiente ejemplo mostrará la fragmentación de la tabla de la tabla Person.Address en la base de datos Adventureworks

      Primero se debe crear un script llamado fragmentation.sql:

      En PowerShell para SQL Server, ejecute el siguiente script:

      Invoke-sqlcmd –inputfile “c: \sql\fragmentation.sql” | Out-File
      -filePath “C:\sql\outps.txt”

      La salida del archivo outps.txt será la siguiente:

    3. Cómo se puede usar la salida detallada

      Verbose se utiliza para mostrar información que no se muestra de manera predeterminada. Por ejemplo, el comando print no se muestra por defecto. Veamos un ejemplo

      En sqlps, ejecute este cmdlet:

      Invoke-Sqlcmd -Query “PRINT ‘HELLO SQLSHACK'”

      El cmdlet no devolverá ningún valor. Sin embargo, si ejecuta con el parámetro detallado, entonces la salida se puede mostrar:

      Invoke-Sqlcmd -Query “PRINT ‘HELLO SQLSHACK'”
      –verbose

  5. DAC

    1. Cómo trabajar con una conexión de administrador dedicada (DAC) en sqlcmd

      Si SQL Server no se conecta en SSMS u otras herramientas, es posible probar una conexión DAC. Esta conexión no es otra cosa que la conexión que permite diagnosticar y verificar los problemas del servidor de bases de datos. En el caso de que cuando el SQL Server está dañado y no es posible conectarse a él, la conexión DAC generalmente funciona

      El siguiente ejemplo muestra cómo conectarse a una base de datos de SQL Server:

      sqlcmd -S DESKTOP-5K4TURF -E -A -d master

      -A se usa para especificar una conexión DAC y -d se usa para especificar la base de datos a conectar

      Una conexión DAC requiere que se inicie y habilite el servicio del navegador SQL. Para habilitar el servicio del navegador SQL, si el mismo está deshabilitado, entonces puede usar los siguientes comandos:

      sc config sqlbrowser start=demand

      Si está habilitado, el mensaje será el siguiente:

      Para iniciar el servicio, puede usar los siguientes comandos:

      net start sqlbrowser

  6. When to use sqlcmd mode, interactive mode, DAC, SSMS, PowerShell

    Es importante indicar que cuando use el modo interactivo entonces es necesario ejecutar múltiples consultas y tareas administrativas. El modo de línea de comando sqlcmd se utiliza cuando tiene tareas específicas como una copia de seguridad. Úselo cuando tenga una tarea específica en mente. DAC se utiliza para la recuperación ante desastres (por ejemplo, cuando la base de datos maestra está dañada y no puede acceder a SQL Server usando SSMS u otras herramientas convencionales). SSMS en modo sqlcmd se puede usar para crear scripts. Es genial depurar y programar scripts grandes para usarlos más tarde en el modo de línea de comandos

    Use PowerShell si tiene otros scripts de PowerShell y necesita integrar algunas invocaciones sqlcmd

    Conclusión

    Sqlcmd es una herramienta muy poderosa que puede ayudarnos a automatizar tareas en SQL Server. Permite ejecutar scripts y guardar los resultados de sus consultas en un archivo de texto.

    Artículo anterior de esta serie:

    Daniel Calbimonte

    Daniel Calbimonte

    Daniel Calbimonte es un Microsoft Most Valuable Professional, Microsoft Certified Trainer y Microsoft Certified IT Professional en SQL Server. He is an accomplished SSIS author, Intructor en academias de IT y tienedécadas de experience trabajando con diferentes bases de datos.

    Realizó consultorias para empresas gubernamentales, empresas petroleras, sitios web, revistas y universidades alrededor del mundo. Daniel participa regularmente en conferencias de SQL Server y blogs. Escribe material sobre SQL Server y exámenes de certificación.

    También realiza la traducción de artículos de SQLShack al español

    Ver todas las publicaciones de Daniel Calbimonte
    Daniel Calbimonte
1,911 Views