How to call 'stored procedure'(of an SQL database), from ServiceNow script include?

[Gopika P S]

How to call 'stored procedure'(of an SQL database), from ServiceNow script include?

[Maik Skoddow]

Hi

there are different approaches:

• via Orchestration & Workflow: deprecated and not offered any longer
• via IntegrationHub and Flow Designer: extra costs for the respective IntegrationHub license

In both approaches you also need a MID server as ServiceNow itself cannot execute the required operations

Maik

[Gopika P S]

Hi Maik..Thank you for your reply..

But, the requirement from the client is such that, we need to accomplish this using script include...and the script has been written to have connection with JDBC...but, need to know how to call the stored procedure of sql from there.

this is the code..

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'
};

[sowmyaprabhakar]

Hi Gopika,

Did this approach work. I have a similar requirement to trigger a stored procedure on SQL server from servicenow.

Can you please tell me, how this is achieved.

Appreciate all your help.

Thanks,

Sowmya