Call Stored procedure DataSource does not exist in this gateway

Ignition
1

I have a Stored Procedure where DBConnection in the Gateway has the name Production_DB
Here is my test script.
dbnameconnect =system.db.beginTransaction(database = “Production_DB”)
call = system.db.createSProcCall(“Production_DB.dbo.usp_iiot_IgnitionDatasetfromSP”,dbnameconnect)
dataset = system.db.execSProcCall(call)
normaldataset = system.dataset.toDataSet(dataset)

The following errors occur
Error executing system.db.execSProcCall()
caused by: com.inductiveautomation.ignition.gateway_interface.Gatewayexception: DataSource “c9c2f1f7-5fe3-4979 etc” does not exist in this gateway.

The Production_DB is configured as a name database connection.

Thank you

2

Reposting formatted:

dbnameconnect =system.db.beginTransaction(database = "Production_DB")
call = system.db.createSProcCall("Production_DB.dbo.usp_iiot_IgnitionDatasetfromSP",dbnameconnect)
dataset = system.db.execSProcCall(call)
normaldataset = system.dataset.toDataSet(dataset)

Edit: Corrected quotes

3

beginTransaction doesn’t return a database connection. It just returns a transaction ID. You need to pass the database and that transaction ID separately to createSProcCall.

Also, execSProcCall doesn’t return anything that can be converted to a dataset. You need to use getResultSet() on that returned object. The manual I linked has examples.

4

You failed. Look at the quotes in your reposted code block. They've been auto-converted to "pretty" quotes. The whole point of asking the OP edit their own comment's pasted text is because their post still has the original characters and spaces.

5

Thanks for the feedback. I'll do better next time.

6

What would be the syntax of system.db.createSProcCall(“Production_DB.dbo.usp_iiot_IgnitionDatasetfromSP”,dbnameconnect)
if my Ignition Database connection Name is Production_DB.

7

Please edit your original post to mark the pasted code as a code block (highlight the code and click the </> button). Then I can cut and paste to show you.

{ Did you look at the documentation I linked? }

8

@Stephanie_VanDelinde
See picture below to illustrate what @pturmel is requesting

image
image740×274 12.7 KB

system.db.createSProcCall("Production_DB.dbo.usp_iiot_IgnitionDatasetfromSP",dbnameconnect)

11 
call = system.db.createSProcCall("Production_DB.dbo.usp_iiot_IgnitionDatasetfromSP","Production_DB")
system.db.execSProcCall(call)
results = call.getResultSet()

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Database ‘Production_DB’ does not exist. Make sure that the name is entered correctly.

Production_DB is the name of the Database connection in Galaxy
The DB in SQL server that this is connected to is Production-DB.
Thank you for help.

12

Probably needs to be this:

call = system.db.createSProcCall("[Production-DB].dbo.usp_iiot_IgnitionDatasetfromSP","Production_DB")
system.db.execSProcCall(call)
results = call.getResultSet()

Or possibly just:

call = system.db.createSProcCall("dbo.usp_iiot_IgnitionDatasetfromSP","Production_DB")
system.db.execSProcCall(call)
results = call.getResultSet()
13

The first call worked. Thank you so much.