Stored Procedure & API Calls

  • Quick question about architecture design ethics.

    How good / bad would it be to call scripts on the server from a Stored Procedure, those are python scripts to call third party web API's. The output of the script would be parsed as the result of the call and then populated in the database within the Stored Procedure.

    If it's bad, then, the solution would be to externalize those calls and then call the database to insert / update the records as more "conventional" way.

    Thank you,

  • removed - you're talking SSIS ( I assumed SQL DB engine )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I am evaluating the architecture that was implemented in our instance. We have integrations done with SQL Server for our Warehouse production operations to track the products we build. The integrated tool is called Aveva WorkTask.

    Some of the developed Stored Procedure are doing such as described in my post and I am asking the community to give me feedback about this so I can make a final decision about the acceptance.

    My personal feeling is it's not ethic and I have issues with this decision, but since I am new in warehouse automation, I though I should look at it with an open mind and see if it's more frequent than I think it should be.

  • Just my two cents - if you have an external process (SSIS, Aveva, Informatica, etc.) calling a stored procedure that subsequently calls out of SQL Server to do something that the external process can do then why are you even using the external process?

    Another example would be building an SSIS package that calls ServerA - which then uses a view/stored procedure to pull data from ServerB and loads that data into a table on ServerA.  Either use SSIS to directly pull the data from ServerB - or don't use SSIS.

    The same thing applies here - if the tool can perform that functionality then it can probably do that better than SQL Server calling out to a script.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Stuff grows over time and we will absolutely, occasionally, make some poor choices. I would say external-internal-external-internal calls to the database are less than efficient and so, should be avoided. That's not saying you can't call externally if it makes sense, but having an external process call a stored procedure that then calls an external process, well, why not just call the external process from the external process? What does calling in to the proc add to the equation, other than complexity?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply