Tukukkk

When i try to execute a long query using NodeJs mssql@4.2.2 for some reason i get an unknown error, not sure if it is because the query uses a UNION to join result from other similar table with the same column names or what. When i use the same sqlHelper.js code to run other short queries from the same tables and it works fine, like for example executing something line this:

Select * from tblMenuVerticalLevel0

All the tables have the same schema, and i have added the schema below. Also i want to note that when i run the same query using SQL Studio it works fine, noy sure why nodejs won't even execute, i place a break point in visual studio the line after the query has been added to memory to the sqlQuery variable, then copied and pasted it into sql studio ran it and it returns the result not sure why nodejs mssql module won't execute it, anyone has any idea why this is happening?

var txtSearch = 'labor';

var intRecordID_Language = 1;

var intRecordID_Lingo = 1;

var intRecordID_Company = 5;

var intRecordID_Division = 15;



var sqlQuery = 'SELECT DISTINCT tblMenuVerticalLevel0.txtComponentPath, tblMenuLanguage.txtDisplayName, '

    + 'tblMenuVerticalLevel0.txtModule, tblMenuLanguage.txtSystemCrossReferenceName '

    + 'FROM tblMenuVerticalLevel0 '

    + 'INNER JOIN tblMenuLanguage ON tblMenuLanguage.intRecordID_Menu = tblMenuVerticalLevel0.intRecordID '

    + 'WHERE (((tblMenuLanguage.txtDisplayName LIKE '%' + txtSearch + '%') '

    + 'AND (tblMenuVerticalLevel0.intRecordID_Form > 0) AND (tblMenuLanguage.intRecordID_MenuLevel = 1)) '

    + 'OR (CAST(tblMenuVerticalLevel0.intRecordID_Form AS VARCHAR(25)) LIKE '%' + txtSearch + '%''

    + 'AND tblMenuVerticalLevel0.intRecordID_Form > 0 AND (tblMenuLanguage.intRecordID_MenuLevel = 1)) '

    + 'OR (tblMenuLanguage.txtSystemCrossReferenceName LIKE '%' + txtSearch + '%')'

    + 'AND (tblMenuVerticalLevel0.intRecordID_Form > 0) AND (tblMenuLanguage.intRecordID_MenuLevel = 1)) '

    + 'AND Len(tblMenuVerticalLevel0.txtComponentPath) > 0 '

    + 'AND (tblMenuLanguage.intRecordID_Language = ' + intRecordID_Language + ') '

    + 'AND (tblMenuLanguage.intRecordID_Lingo = ' + intRecordID_Lingo + ') '

    + 'AND (tblMenuLanguage.intRecordID_Company = ' + intRecordID_Company + ') '

    + 'AND (tblMenuLanguage.intRecordID_Division = ' + intRecordID_Division + ') '

    + 'AND tblMenuVerticalLevel0.txtModule <> 'System Used Forms' '

    + 'UNION SELECT DISTINCT tblMenuVerticalLevel1.txtComponentPath, tblMenuLanguage.txtDisplayName, '

    + 'tblMenuVerticalLevel1.txtModule, tblMenuLanguage.txtSystemCrossReferenceName '

    + 'FROM tblMenuVerticalLevel1 '

    + 'INNER JOIN tblMenuLanguage ON tblMenuLanguage.intRecordID_Menu = tblMenuVerticalLevel1.intRecordID '

    + 'WHERE (((tblMenuLanguage.txtDisplayName LIKE '%' + txtSearch + '%') '

    + 'AND (tblMenuVerticalLevel1.intRecordID_Form > 0) AND (tblMenuLanguage.intRecordID_MenuLevel = 2)) '

    + 'OR (CAST(tblMenuVerticalLevel1.intRecordID_Form AS VARCHAR(25)) LIKE '%' + txtSearch + '%' '

    + 'AND tblMenuVerticalLevel1.intRecordID_Form > 0 AND (tblMenuLanguage.intRecordID_MenuLevel = 2)) '

    + 'OR (tblMenuLanguage.txtSystemCrossReferenceName LIKE '%' + txtSearch + '%') '

    + 'AND (tblMenuVerticalLevel1.intRecordID_Form > 0) AND (tblMenuLanguage.intRecordID_MenuLevel = 2)) '

    + 'AND Len(tblMenuVerticalLevel1.txtComponentPath) > 0 AND (tblMenuLanguage.intRecordID_Language = 0) '

    + 'AND (tblMenuLanguage.intRecordID_Lingo = ' + intRecordID_Lingo + ') '

    + 'AND (tblMenuLanguage.intRecordID_Company = ' + intRecordID_Company + ') '

    + 'AND (tblMenuLanguage.intRecordID_Division = ' + intRecordID_Division + ') '

    + 'AND tblMenuVerticalLevel1.txtModule <> 'System Used Forms' '

    + 'UNION SELECT DISTINCT tblMenuVerticalLevel2.txtComponentPath, tblMenuLanguage.txtDisplayName, '

    + 'tblMenuVerticalLevel2.txtModule, tblMenuLanguage.txtSystemCrossReferenceName '

    + 'FROM tblMenuVerticalLevel2 '

    + 'INNER JOIN tblMenuLanguage ON tblMenuLanguage.intRecordID_Menu = tblMenuVerticalLevel2.intRecordID '

    + 'WHERE (((tblMenuLanguage.txtDisplayName LIKE '%' + txtSearch + '%') '

    + 'AND (tblMenuVerticalLevel2.intRecordID_Form > 0) AND (tblMenuLanguage.intRecordID_MenuLevel = 3)) '

    + 'OR (CAST(tblMenuVerticalLevel2.intRecordID_Form AS VARCHAR(25)) LIKE '%' + txtSearch + '%' '

    + 'AND tblMenuVerticalLevel2.intRecordID_Form > 0 AND (tblMenuLanguage.intRecordID_MenuLevel = 3)) '

    + 'OR (tblMenuLanguage.txtSystemCrossReferenceName LIKE '%' + txtSearch + '%') '

    + 'AND (tblMenuVerticalLevel2.intRecordID_Form > 0) AND (tblMenuLanguage.intRecordID_MenuLevel = 3)) '

    + 'AND Len(tblMenuVerticalLevel2.txtComponentPath) > 0 '

    + 'AND (tblMenuLanguage.intRecordID_Language = ' + intRecordID_Language + ') '

    + 'AND (tblMenuLanguage.intRecordID_Lingo = ' + intRecordID_Lingo + ') '

    + 'AND (tblMenuLanguage.intRecordID_Company = ' + intRecordID_Company + ') '

    + 'AND (tblMenuLanguage.intRecordID_Division = ' + intRecordID_Division + ') '

    + 'AND tblMenuVerticalLevel2.txtModule <> 'System Used Forms' '

    + 'UNION SELECT DISTINCT tblMenuVerticalLevel3.txtComponentPath, tblMenuLanguage.txtDisplayName, '

    + 'tblMenuVerticalLevel3.txtModule, tblMenuLanguage.txtSystemCrossReferenceName '

    + 'FROM tblMenuVerticalLevel3 '

    + 'INNER JOIN tblMenuLanguage ON tblMenuLanguage.intRecordID_Menu = tblMenuVerticalLevel3.intRecordID '

    + 'WHERE (((tblMenuLanguage.txtDisplayName LIKE '%' + txtSearch + '%') '

    + 'AND (tblMenuVerticalLevel3.intRecordID_Form > 0) AND (tblMenuLanguage.intRecordID_MenuLevel = 4)) '

    + 'OR (CAST(tblMenuVerticalLevel3.intRecordID_Form AS VARCHAR(25)) LIKE '%' + txtSearch + '%' '

    + 'AND tblMenuVerticalLevel3.intRecordID_Form > 0 AND (tblMenuLanguage.intRecordID_MenuLevel = 4)) '

    + 'OR (tblMenuLanguage.txtSystemCrossReferenceName LIKE '%' + txtSearch + '%') '

    + 'AND (tblMenuVerticalLevel3.intRecordID_Form > 0) AND (tblMenuLanguage.intRecordID_MenuLevel = 4)) '

    + 'AND Len(tblMenuVerticalLevel3.txtComponentPath) > 0 '

    + 'AND (tblMenuLanguage.intRecordID_Language = ' + intRecordID_Language + ') '

    + 'AND (tblMenuLanguage.intRecordID_Lingo = ' + intRecordID_Lingo + ') '

    + 'AND (tblMenuLanguage.intRecordID_Company = ' + intRecordID_Company + ') '

    + 'AND (tblMenuLanguage.intRecordID_Division = ' + intRecordID_Division + ') '

    + 'AND tblMenuVerticalLevel3.txtModule <> 'System Used Forms' '

    + 'UNION SELECT DISTINCT tblMenuVerticalLevel4.txtComponentPath, tblMenuLanguage.txtDisplayName, '

    + 'tblMenuVerticalLevel4.txtModule, tblMenuLanguage.txtSystemCrossReferenceName '

    + 'FROM tblMenuVerticalLevel4 '

    + 'INNER JOIN tblMenuLanguage ON tblMenuLanguage.intRecordID_Menu = tblMenuVerticalLevel4.intRecordID '

    + 'WHERE (((tblMenuLanguage.txtDisplayName LIKE '%' + txtSearch + '%') '

    + 'AND (tblMenuVerticalLevel4.intRecordID_Form > 0) AND (tblMenuLanguage.intRecordID_MenuLevel = 5)) '

    + 'OR (CAST(tblMenuVerticalLevel4.intRecordID_Form AS VARCHAR(25)) LIKE '%' + txtSearch + '%' '

    + 'AND tblMenuVerticalLevel4.intRecordID_Form > 0 AND (tblMenuLanguage.intRecordID_MenuLevel = 5)) '

    + 'OR (tblMenuLanguage.txtSystemCrossReferenceName LIKE '%' + txtSearch + '%') '

    + 'AND (tblMenuVerticalLevel4.intRecordID_Form > 0) AND (tblMenuLanguage.intRecordID_MenuLevel = 5)) '

    + 'AND Len(tblMenuVerticalLevel4.txtComponentPath) > 0 '

    + 'AND (tblMenuLanguage.intRecordID_Language = ' + intRecordID_Language + ') '

    + 'AND (tblMenuLanguage.intRecordID_Lingo = ' + intRecordID_Lingo + ') '

    + 'AND (tblMenuLanguage.intRecordID_Company = ' + intRecordID_Company + ') '

    + 'AND (tblMenuLanguage.intRecordID_Division = ' + intRecordID_Division + ') '

    + 'AND tblMenuVerticalLevel4.txtModule <> 'System Used Forms' '

    + 'UNION SELECT DISTINCT tblMenuVerticalLevel5.txtComponentPath, tblMenuLanguage.txtDisplayName, '

    + 'tblMenuVerticalLevel5.txtModule, tblMenuLanguage.txtSystemCrossReferenceName '

    + 'FROM tblMenuVerticalLevel5 '

    + 'INNER JOIN tblMenuLanguage ON tblMenuLanguage.intRecordID_Menu = tblMenuVerticalLevel5.intRecordID '

    + 'WHERE (((tblMenuLanguage.txtDisplayName LIKE '%' + txtSearch + '%') '

    + 'AND (tblMenuVerticalLevel5.intRecordID_Form > 0) AND (tblMenuLanguage.intRecordID_MenuLevel = 6)) '

    + 'OR (CAST(tblMenuVerticalLevel5.intRecordID_Form AS VARCHAR(25)) LIKE '%' + txtSearch + '%' '

    + 'AND tblMenuVerticalLevel5.intRecordID_Form > 0 AND (tblMenuLanguage.intRecordID_MenuLevel = 6)) '

    + 'OR (tblMenuLanguage.txtSystemCrossReferenceName LIKE '%' + txtSearch + '%') '

    + 'AND (tblMenuVerticalLevel5.intRecordID_Form > 0) AND (tblMenuLanguage.intRecordID_MenuLevel = 6)) '

    + 'AND Len(tblMenuVerticalLevel5.txtComponentPath) > 0 '

    + 'AND (tblMenuLanguage.intRecordID_Language = ' + intRecordID_Language + ') '

    + 'AND (tblMenuLanguage.intRecordID_Lingo = ' + intRecordID_Lingo + ') '

    + 'AND (tblMenuLanguage.intRecordID_Company = ' + intRecordID_Company + ') '

    + 'AND (tblMenuLanguage.intRecordID_Division = ' + intRecordID_Division + ') '

    + 'AND tblMenuVerticalLevel5.txtModule <> 'System Used Forms' '

    + 'ORDER BY tblMenuLanguage.txtDisplayName ASC ';



sqlHelper.GetMSSQLSelectQuery('OS', sqlQuery, function (error, formSearchRecordSet) {



    if (error) return next(error);



    var jsonResults = '';

    var sqlQueryResults = ;



    sqlQueryResults.push({ txtSearchType: txtSearchType });



    if (!utilities.IsUndefined(formSearchRecordSet) && formSearchRecordSet.length > 0) {



        jsonResults = JSON.stringify(formSearchRecordSet);



        sqlQueryResults.push(jsonResults);



        response.send(sqlQueryResults);

    }

    else {



        jsonResults = JSON.stringify(formSearchRecordSet);



        sqlQueryResults.push(jsonResults);



        response.send(sqlQueryResults);

    }

});

sqlHelper.js

var mssql = require('mssql'); // version: mssql@4.2.2



const MSSQL_VERSION = '2000';



function getMsSqlConfig(version, databaseName) {



    switch (version) {

        case '2000':

            return getMsSql2000Config(databaseName);

            break;

        case '2008R2':

            return getMsSql2008R2Config(databaseName);

            break;

    }



    return void (0);

}



function getMsSql2000Config(databaseName) {



    var config = {

        server: 'localhost',

        user: '<someUserName>',

        password: '<somePassword>',

        database: databaseName,

        options: {

            encrypt: false,

            tdsVersion: '7_1',

            appName: APPLICATION_NAME,

            debug: {

                packet: true,

                data: true,

                payload: true,

                token: true,

                log: true

            }

        },

        connectTimeout: 6000000,

        requestTimeout: 120000

    };



    return config;

}



function getMSSQLSelectQuery(databaseName, sqlQuery, callback) {



    var config = getMsSqlConfig(MSSQL_VERSION, databaseName);



    var dbConn = new mssql.ConnectionPool(config);



    dbConn.on('debug', function (msg) { console.log(msg) });



    dbConn.connect().then(function (conn) {



        var request = new mssql.Request(conn);



        request.query(sqlQuery).then(function (resultObject) {



            //console.log(resultObject);

            dbConn.close();



            return callback(void (0), resultObject['recordset']);

        }).catch(function (error) {



            console.log(error);

            dbConn.close();



            return callback(error, void (0));

        });

    }).catch(function (error) {



        console.log(error);



        return callback(error, void (0));

    });

}





module.exports = {

    GetMSSQLSelectQuery: getMSSQLSelectQuery

}

Table Schema

    CREATE TABLE [dbo].[tblMenuVerticalLevel0](

    [intRecordID_DatabaseID] [int] NULL,

    [lblMenuVerticalLevel0Information] [varchar](10) NULL,

    [ixtCode] [varchar](150) NULL,

    [txtComponentDisplayName] [varchar](250) NULL,

    [txtModule] [varchar](50) NULL,

    [intRecordID_ModuleList] [int] NULL,

    [intRecordID_Form] [int] NULL,

    [txtComponentPath] [varchar](250) NULL,

    [blnOpenNewWindow] [varchar](3) NULL,

    [blnSubmenu] [varchar](3) NULL,

    [intSequence] [int] NULL,

    [blnAlphabetizeChildren] [varchar](3) NULL,

    [blnShowRecordCount] [varchar](3) NULL,

    [imgMenuImage] [varchar](250) NULL,

    [lblMenuVerticalLevel0RecordInformation] [varchar](10) NULL,

    [blnMakePrivate] [varchar](3) NULL,

    [blnActive] [varchar](3) NULL,

    [txtRecordCreationUser] [varchar](50) NULL,

    [dteRecordCreationDate] [smalldatetime] NULL,

    [dteRecordUpdateDate] [smalldatetime] NULL,

    [blnCursor] [varchar](3) NULL,

    [intRecordID_Company] [int] NULL,

    [intRecordID_Division] [int] NULL,

    [intRecordID] [int] NOT NULL,

    [blnAttachment] [varchar](3) NULL,

    [blnDeleted] [varchar](3) NULL,

    [blnHighlight] [varchar](3) NULL,

    [blnMemo] [varchar](3) NULL,

    [blnProject] [varchar](3) NULL,

    [blnProcess] [varchar](3) NULL,

    [intRecordID_User_Creation] [int] NULL,

    [intRecordID_ErrorLog] [int] NULL,

    [intRecordID_Status] [int] NULL,

    [blnPrint] [varchar](3) NULL,

    [blnSecurity] [varchar](3) NULL,

    [blnErrorLog] [varchar](3) NULL,

    [blnAuditTrail] [varchar](3) NULL,

    [blnSystemRecord] [varchar](3) NULL,

    [txtFieldValueGenerator] [varchar](3) NULL,

    [dteRecordSynchronization] [varchar](3) NULL,

    [blnEcommerceTransmit] [varchar](3) NULL,

    [blnWebSearch] [varchar](3) NULL,

    [txtRecordUpdateUser] [varchar](50) NULL,

    [blnFavorite] [varchar](3) NULL,

 CONSTRAINT [PK_tblMenuVerticalLevel0] PRIMARY KEY CLUSTERED 

(

    [intRecordID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]