Linked Stored Procedure Question

Question

Question

Scott Roth · Aug 23

#Management Portal #SQL #VSCode #HealthShare #InterSystems IRIS for Health #Health Connect #InterSystems IRIS

Instead of building a BP to execute stored procedures just to insert the data, I figured I would give Linked Stored Procedures a try since all I need to do is insert the data. I have proved in our MS SQL Development environment that it is possible, so I through I could just repoint the Linked Stored Procedure

set hdbc=$$GetJConnection^%apiGTW("xxxxx")

to the test environment. But when I made the changed in VSCode and compiled the Stored Procedure disappeared from the SQL view in the Management Portal.

So can we not simply update the connection string to point to a different MS SQL Connection?

Product version: IRIS 2024.1

$ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2024.1 (Build 267_2U) Tue Apr 30 2024 16:06:39 EDT [HealthConnect:7.2.0-1.r1]

Discussion (2)2

Log in or sign up to continue

Enrico Parisi · Aug 23

Why modify the code when you can modify the SQL Gateway definition to point to the production system?

This way the code is the same (dev/prod) but only the configuration change.

In your example "xxxxx" is the SQL Gateway name, change in management portal to point to the required environment.

0 0

score 0 · Answer 1 · 2024-08-23T12:08:15-04:00

Yes, the "xxxxxx" represent the SQL Connections, however we must maintain 3 different connections as there are 3 different environments we send data to in MS SQL.

Yes, the code should be the same, but there are times where we are testing things in DEV before they go to the TEST MS SQL environment.

Modifying the SQL Connection String at the System level isn't as logical.

I thought it would be easiest to define the Linked Stored Procedure once and just update the Connection String within the Object Script when I was ready to move it to a different MS SQL Environment.