Some times you may need to query the SQL Server Instance for different purposes. This article gives you a way on how we can query the SQL Server from Servicenow instance instantly without configuring any data source.

You may use this for

• Select Query
• Update Query
• Delete Query
• Executing Stored Procedures
• Create Query

Script Include Name : SQLServer_Utils (Customize this script as per your requirement)

Script Template:

var SQLServer_Utils = Class.create();

SQLServer_Utils.prototype = {

initialize: function() {

this.MidServer = "";

this.SQLDriver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver';

this.ConnectionString = "jdbc:sqlserver://:;instanceName=;databaseName=;user=;password=";

},

parseQueryXML:function(sqlQuery){

var xml = '<?xml version="1.0" encoding="UTF-8"?>';

xml = '';

xml += '';

xml += '';

xml += '';

xml += '';

xml += '';

return xml;

},

runMyQuery:function(){

var sql = "select top 1 * from

where ='abc'";

var xmlQuery = this.parseQueryXML(sql);

//Create ECC Record to send the payload via Mid server.

var obj_ecc = new GlideRecord("ecc_queue");

obj_ecc.initialize();

obj_ecc.agent="mid.server." + this.MidServer;

obj_ecc.topic="JDBCProbe";

obj_ecc.payload=xmlQuery;

obj_ecc.queue="output";

obj_ecc.state="ready";

var eccoutputsysid = obj_ecc.insert();

//Query for the response from ECC Queue

var obj_eccResponse = new GlideRecord("ecc_queue");

obj_eccResponse.addNotNullQuery('response_to');

obj_eccResponse.addQuery("response_to", eccoutputsysid);

obj_eccResponse.addQuery("queue", "input");

obj_eccResponse.addQuery("payload", "!=", "");

var cnt = 0;

//var flagresponse = false;

var responsesysid = '';

do{

obj_eccResponse.query();

if(obj_eccResponse.next()){

if(obj_eccResponse.payload != ''){

cnt=10; //Since it got the response, we can end the loop

responsesysid = obj_eccResponse.sys_id.toString();

}

}

obj_eccResponse.next();

this.pauseforamoment(5000); //Pause for 5 seconds

}while(!eccoutputsysid && counter < 10); //Loop 10 times to get the response from ECC. You may increase/decrease this number.

if(responsesysid != ''){

var objresponse = new GlideRecord("ecc_queue");

objresponse.addQuery('sys_id',responsesysid);

objresponse.query();

if(objresponse.next()){

var obj_XML = new XMLDocument2();

obj_XML.parseXML(objresponse.payload);

//Read the XML based on the input you got and return the value...

}

}

},

pauseforamoment: function(millisec) {

var obj_timer = new Date().getTime();

while(new Date().getTime() < obj_timer + millisec) {}

},

type: 'SQLServer_Utils'

};

Provide your comments if any updates required.

Labels:

• Integrations
• Scripting and Coding