Open liamk-fleetops opened 3 months ago
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
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) }
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.
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
error:
proc error Error: sys.sp_cdc_change_job: illegal params on param object = job_type,retention
sys.sp_cdc_enable_db
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: