sqlcmd-runner
Node wrapper for the sqlcmd.
Install
$ npm install sqlcmd-runner --save
Usage
A promise is returned and fulfilled when sqlcmd succeeds or rejected if it fails.
var sqlcmd = require('sqlcmd-runner'); sqlcmd({ ... }) .catch(function(error) { console.log('Failed: ' + error.message); }) .done(function() { console.log('Done.'); });
Options
The folowing are the options supported by this module. These map directly to the sqlcmd
CLI parameters here.
Login
sqlcmd({ // Specifies the instance of SQL ServerIt sets the sqlcmd scripting variable // SQLCMDSERVER. Specify server_name to connect to the default instance of SQL Server // on that server computer. Specify the server name and instance to connect to a named // instance of SQL Server on that server. If no server is specified, sqlcmd connects // to the default instance of SQL Server on the local computer. This option is required // when you execute sqlcmd from a remote computer on the network. protocol can be // tcp (TCP/IP), lpc (shared memory), or np (named pipes). If you do not specify a // server name or instance name when you start sqlcmd, SQL Server checks for and uses // the SQLCMDSERVER environment variable. (-S) server: 'myserver' | { name: 'myserver', protocol: 'tcp|lpc|np', instance: 'default', port: 1433 } // : Issues a USE db_name statement when you start sqlcmd. This option sets the sqlcmd // scripting variable SQLCMDDBNAME. This specifies the initial database. The default is // your login's default-database property. (-d) database: 'dbname', // Uses a trusted connection instead of using a user name and password to log on to // SQL Server. By default, sqlcmd uses a trusted connection. This option ignores // possible user name and password environment variable settings such as // SQLCMDPASSWORD. (-E) trustedConnection: true|false, // The user login ID. If neither the username or password are specified, sqlcmd tries to // connect by using Microsoft Windows Authentication mode. Authentication is based on // the Windows account of the user who is running sqlcmd. (-U) username: // The user-specified password. Passwords are case sensitive. If the username is // specified but not the password, and the SQLCMDPASSWORD environment variable // has not been set, sqlcmd prompts the user for a password. (-P) password: 'p@$$w0rd', // Logs in to SQL Server with a Dedicated Administrator Connection (DAC). This kind of // connection is used to troubleshoot a server. This will only work with server // computers that support DAC. If DAC is not available, sqlcmd generates an error // and then exits. (-A) dedicatedAdminConnection: true|false, // This switch is used by the client to configure it to implicitly trust the server // certificate without validation. This option is equivalent to the ADO.NET option // TRUSTSERVERCERTIFICATE = true. (-C) trustServerCert: true|false, // Specifies the number of seconds before a sqlcmd login to the ODBC driver times out // when you try to connect to a server. This option sets the sqlcmd scripting variable // SQLCMDLOGINTIMEOUT. The default time-out for login to sqlcmd is eight seconds. The // login time-out must be a number between 0 and 65534. A value of 0 specifies // time-out to be infinite. (-l) loginTimeout: 30, // A workstation name. This option sets the sqlcmd scripting variable SQLCMDWORKSTATION. // The workstation name is listed in the hostname column of the sys.processes catalog // view and can be returned using the stored procedure sp_who. If this option is not // specified, the default is the current computer name. This name can be used to // identify different sqlcmd sessions. (-H) workstationName: 'hostname', // Declares the application workload type when connecting to a server. The only // currently supported value is ReadOnly. If this is not specified, the sqlcmd utility // will not support connectivity to a secondary replica in an AlwaysOn /// availability group. (-K) applicationIntent: 'ReadOnly', // Always specify this option when connecting to the availability group listener of a // SQL Server availability group or a SQL Server Failover Cluster Instance. This // option provides for faster detection of and connection to the (currently) active // server. If this option is not specified, it is off. (-M) multisubnetFailover: true|false, // This switch is used by the client to request an encrypted connection. (-N) encryptedConnection: true|false, // Change a password. (-Z) newPassword: 'p@$$w0rd' });
Input/Output
sqlcmd({ // Identifies the file that contains a batch of SQL statements or stored procedures. // Multiple files may be specified that will be read and processed in order. sqlcmd will // first check to see whether all the specified files exist. If one or more files do // not exist, sqlcmd will exit. Cannot be used in conjunction with a query. // NOTE: Forward slashes are automatically converted to back slashes. (-i) inputFiles: ['path/to/input1', 'path/to/input2'], // Identifies the file that receives output from sqlcmd. If unicode is specified, the // output_file is stored in Unicode format. sqlcmd does not support concurrent writing // of multiple sqlcmd processes to the same file. The file output will be corrupted or // incorrect. See the codepage for more information about file formats. This file will // be created if it does not exist. A file of the same name from a prior sqlcmd session // will be overwritten. The file specified here is not the stdout file. If a stdout file // is specified this file will not be used. (-o) // NOTE: Forward slashes are automatically converted to back slashes. outputFile: 'path/to/output', // Specifies the input and output code pages. The codepage number is a numeric value // that specifies an installed Windows code page. // Code-page conversion rules: // If no code pages are specified, sqlcmd will use the current code page for both input // and output files, unless the input file is a Unicode file, in which case no // conversion is required. sqlcmd automatically recognizes both big-endian and // little-endian Unicode input files. If the unicodeOutput option has been specified, // the output will always be little-endian Unicode. If no output file is specified, the // output code page will be the console code page. This enables the output to be // displayed correctly on the console. Multiple input files are assumed to be of the // same code page. Unicode and non-Unicode input files can be mixed. // Accepts either a string with the codepage for both input and output files or an // object with seperate codepages for input and output. (-f) codepage: '65001' | { // Input files codepage. input: '65001', // Output file codepage. output: '65001' }, // Redirects the error message output to the screen (stderr). When enabled, only error // messages that have a severity level of 11 or higher are redirected. By default // messages are sent to stdout. (-r) errorRedirection: true | { // All error message output, regardless of severity level, including PRINT is // redirected. Has no effect if you specify an output file. (-r1) all: true|false }, // Causes sqlcmd to localize numeric, currency, date, and time columns retrieved from // SQL Server based on the client’s locale. By default, these columns are displayed // using the server’s regional settings. (-R) localizeResults: true|false, // Specifies that output_file is stored in Unicode format, regardless of the format of // input_file. (-u) unicodeOutput: true|false });
Query Execution
sqlcmd({ // Executes a query when sqlcmd starts and then immediately exits sqlcmd. Multiple- // semicolon-delimited queries can be executed. Use quotation marks around the query, // as shown in the following example. Do not use the GO terminator in the query. (-Q) query: 'SELECT * FROM Users', // Creates a sqlcmdscripting variable that can be used in a sqlcmd script. (-v) variables: { name: 'value', ... }, // Specifies the number of seconds before a command (or SQL statement) times out. This // option sets the sqlcmd scripting variable SQLCMDSTATTIMEOUT. If a time_out value is // not specified, the command does not time out. The query time_out must be a number // between 1 and 65534. The actual time out value may vary from the specified time_out // value by several seconds. (-t) queryTimeout: 30, // Writes input scripts to the standard output device (stdout). (-e) printInputScripts: true|false, // Sets the SET QUOTED_IDENTIFIER connection option to ON. By default, // it is set to OFF. (-I) quoteIdentifier: true|false, // Causes sqlcmd to ignore scripting variables. This is useful when a script contains // many INSERT statements that may contain strings that have the same format as regular // variables, such as $(variable_name). (-x) ignoreVariables: true|false });
Formatting
sqlcmd({ // Specifies the number of rows to print between the column headings. The default is to // print headings one time for each set of query results. This option sets the sqlcmd // scripting variable SQLCMDHEADERS. Use -1 to specify that headers must // not be printed. (-h) headers: 1, // Removes all control characters, such as tabs and new line characters from the output. // This preserves column formatting when data is returned. (-k) removeControlChars: true | { // Control characters are replaced by a single space. (-k1) single: true|false, // Consecutive control characters are replaced by a single space. (-k2) consecutive: true|false, }, // Specifies the column-separator character. The default is a blank space. This option // sets the sqlcmd scripting variable SQLCMDCOLSEP. To use characters that have special // meaning to the operating system such as the ampersand (&), or semicolon (;), enclose // the character in quotation marks ("). The column separator can be any // 8-bit character. (-s) columnSeperator: ';', // Specifies the screen width for output. This option sets the sqlcmd scripting variable // SQLCMDCOLWIDTH. The column width must be a number greater than 8 and less than 65536. // If the specified column width does not fall into that range, sqlcmd generates and // error message. The default width is 80 characters. When an output line exceeds the // specified column width, it wraps on to the next line. (-w) columnWidth: 10, // This option removes trailing spaces from a column. Use this option together with the // columnSeperator option when preparing data that is to be exported to another // application. Cannot be used with the display width options. (-W) removeTrailingSpaces: true|false, // Sets the sqlcmd scripting variable SQLCMDMAXFIXEDTYPEWIDTH. The default is 256. It // limits the number of characters that are returned for large variable length types: // varchar(max), nvarchar(max), varbinary(max), xml, UDT, text, ntext and image // UDTs can be of fixed length depending on the implementation. If this length of a // fixed length UDT is shorter that display_width, the value of the UDT returned is // not affected. However, if the length is longer than display_width, the output is // truncated. If display_width is 0, the output is truncated at 1 MB. Specify 0 with // extreme caution because it may cause serious performance issues on both the server // and the network, depending on the size of data returned. (-y) variableLengthDisplayWidth: 200, // Sets the sqlcmd scripting variable SQLCMDMAXVARTYPEWIDTH. The default is 0 // (unlimited). Limits the number of characters that are returned for the // following data types: char(1-8000), nchar(1-4000), varchar(1-8000), // nvarchar(1-4000), varbinary(1-8000). (-Y) fixedLengthDisplayWidth: 200 });
Error Reporting
sqlcmd({ // Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. // The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server // error message has a severity level greater than 10; otherwise, the value returned // is 0. If the severity level has been set in addition to this, sqlcmd will not report // an error if the severity level is lower than the level configured. Command prompt // batch files can test the value of ERRORLEVEL and handle the error appropriately. // sqlcmd does not report errors for severity level 10 (informational messages). // If the sqlcmd script contains an incorrect comment, syntax error, or is missing a // scripting variable, ERRORLEVEL returned is 1. (-b) failOnSqlErrors: true|false, // Controls which error messages are sent to stdout. Messages that have a severity level // greater than or equal to this level are sent. When this value is set to -1, all // messages including informational messages, are sent. This option also sets the sqlcmd // scripting variable SQLCMDERRORLEVEL. This variable has a default of 0. (-m) errorLevel: 1, // Controls the severity level that is used to set the ERRORLEVEL variable. Error // messages that have severity levels greater than or equal to this value set // ERRORLEVEL. Values that are less than 0 are reported as 0. Batch and CMD files can // be used to test the value of the ERRORLEVEL variable. (-V) errorSeverityLevel: 1 });
Miscellaneous
sqlcmd({ // Requests a packet of a different size. This option sets the sqlcmd scripting variable // SQLCMDPACKETSIZE. packet_size must be a value between 512 and 32767. The default = // 4096. A larger packet size can enhance performance for execution of scripts that have // lots of SQL statements between GO commands. You can request a larger packet size. // However, if the request is denied, sqlcmd uses the server default for // packet size. (-a) packetSize: 512, // Specifies the batch terminator. By default, commands are terminated and sent to SQL // Server by typing the word "GO" on a line by itself. When you reset the batch // terminator, do not use Transact-SQL reserved keywords or characters that have special // meaning to the operating system, even if they are preceded by a backslash. (-c) batchTerminator: 'GO', // Prints performance statistics for every result set. (-p) perfStats: true | { // Indicates that performance stats should be comma seperated. (-p1) colonSeperated: true|false }, // Disables the ED and !! commands as they might compromise system security when sqlcmd // is executed from a batch file. The disabled commands are still recognized; sqlcmd // issues a warning messageand continues. It prevents environment variables from being // passed on to sqlcmd. It also prevents the startup script specified by using the // SQLCMDINI scripting variable from being executed. (-X) enhancedSecurity: true | { // sqlcmd generates an error message and then exits. (-X1) failOnErrors: true|false } })
License
MIT License