Node.js wrapper for sqlcmd.
JavaScript
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
src
test
.gitignore
.travis.yml
LICENSE
README.md
gulpfile.js
package.json

README.md

sqlcmd-runner

npm version build status Dependency Status npm downloads

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