Linked Stored Procedure Question
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?
Comments
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.
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.