Utility : How to query sql server from servicenow using a script

[Narsing1]

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 = "<Mid Server Name>";
this.SQLDriver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver';
this.ConnectionString = "jdbc:sqlserver://<ipaddress>:<port>;instanceName=<instancename>;databaseName=<DBName>;user=<username>;password=<pwd>";
},
parseQueryXML:function(sqlQuery){
var xml = '<?xml version="1.0" encoding="UTF-8"?>';
xml = '<parameters>';
xml += '<parameter name="jdbc_driver" value="'+this.SQLDriver+'"/>';
xml += '<parameter name="connection_string" value="'+this.ConnectionString+'"/>';
xml += '<parameter name="query" value="Specific SQL"/>';
xml += '<parameter name="sql_statement" value="'+sqlQuery+'"/>';
xml += '</parameters>';
return xml;
},
runMyQuery:function(){
var sql = "select top 1 * from <table> where <column>='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.

[Service Manager]

Hi,

I was trying to insert the record via JDBC probe into SQL database. Below script was working as expected 

var mid = new GlideRecord("ecc_agent");

mid.addQuery('name','MID_Serverxxx');

mid.query();

if(mid.next()){

}

var data = new GlideRecord("sys_data_source");

data.addQuery('name','xxname of datasourcexx');

data.query();

if(data.next()){

}

var r1 = new JDBCProbe(mid.name);

r1.setDataSource(ds.sys_id);

jr1.setFunction("INSERT");

r1.setTable("xxdatabase tablexx");

r1.addParameter("skip_sensor","true");

r1.addField("Number", "INC7845129");

r1.addField("Contact", "david");

r1.create();

 But when I tried to insert the records from target table "Incident" assigned to Service desk group. It throws me an error

r1.addField("Number", current.number);

r1.addField("Contact", current.caller);

r1.addField("opened",current.opened_at);

r1.create();

[Niamul Arifin]

@Service Manager  I have similar requirement. Once a catalog item is submitted from the portal and approved, an INSERT call is send via JDBC to SQL database. Did you use this script in script include ?? what was your use case? would love to learn.