Execute system stored procedure

[liamk-fleetops]

I'm writing an Electron App for windows which connects to a local SQL Server and configures some CDC settings. I am having difficulties running system stored procedures. It recognizes the SP names as it doesn't error on getting them (as it does when I make a typo) but the proc.meta.params only shows an @returns param for both of the below SPs.

sys.sp_cdc_change_job

sql.open(connectionString, (err, conn) => {
    conn.procedureMgr().get('sys.sp_cdc_change_job', (proc) => {
      proc.call({
        "job_type": "Cleanup",
        "retention": 4320
      }, (err, res, output) => {
        console.log(err, res, output);
      });
    });
  });

error: proc error Error: sys.sp_cdc_change_job: illegal params on param object = job_type,retention

---

sys.sp_cdc_enable_db

sql.open(connectionString, (err, conn) => {
    conn.procedureMgr().get('sys.sp_cdc_enable_db', (proc) => {
      proc.call([], (err, res, output)=> {
        console.log(err, res, output);
      });
    });
  });

error: proc error [Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure sp_cdc_enable_db has no parameters and arguments were supplied.]

Connection string:

Driver={SQL Server};Server={*.*.*.*,1433};Database={database_name};Trusted_Connection={yes};

package	version
NodeJS	21.7.1
Electron	29.3.0
msnodesqlv8	4.1.2
node-gyp	10.1.0
visual studio c++	2022
OS	Windows Server 2016 Datacenter
database	SQL Server 2017

[TimelordUK]

https://github.com/TimelordUK/node-sqlserver-v8/blob/master/lib/queries/proc_describe.sql

So the lib tries to run this sql you would have to change obviously the object Id parameter and use your stored proc name

My best guess is the sql returns no rows for this procedure ? It is these that are used to bind procedure the return is manually added which is why you see it.

There is a way to manually add a procedure ie manually add the required parameters there should be examples of this either in unit test module or the samples. This may work

[TimelordUK]

const params = [ pm.makeParam(spName, '@last_name', 'varchar', 30, false), pm.makeParam(spName, '@first_name', 'varchar', 18, false) ]

const proc = pm.addProc(spName, params)
proc.setDialect(pm.ServerDialect.Sybase) // not this line
return proc

} catch (err) { console.error(err) }

[liamk-fleetops]

Thank you @TimelordUK

I manually create the params for the procedure sys.sp_cdc_change_job but I'm running into a different error now: Error: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

    const pm = conn.procedureMgr();
    const params: any[] = [
      pm.makeParam('sys.sp_cdc_change_job', '@job_type', 'nvarchar', 20, false),
      pm.makeParam('sys.sp_cdc_change_job', '@retention', 'bigint', undefined, false)
    ];
      
    const proc = pm.addProc('sys.sp_cdc_change_job', params)
    proc.call({
        "job_type": "cleanup",
        "retention": 4320
      }, (err: any, res: any, output: any) => {
      console.log("proc error", err); 
      console.log("proc res", res)
      console.log("proc output", output)
    });

Debugging it I get the query string { ? = call sys.sp_cdc_change_job(@job_type = ?, @retention = ?) } and the 3 params look correct too. I've also tried it leaving out the sys. prefix and get the same error.

I have found a workaround of calling the system stored procedures as a regular SQL query:

conn.query("sys.sp_cdc_change_job @job_type=N'Cleanup', @retention=4242", (err: any, res: any) => {
        console.log("error", err); 
        console.log("res", res)
      });

It gives an empty result, but all stored procedures I wish to call end up setting values in tables which I can query afterwards to check for success.