2

I've been trying for a while to call an existing SOAP web service from InTouch Wonderware or MS SQL 2014. It doesn't matter which of the two calls it, as the Wonderware project reads/writes to the SQL Server database anyway.

My knowledge is mostly limited to Wonderware and its in-program options to access the SQL Server database, which doesn't work well with the usual guides on the web service topic.

The web service is provided by the customer, over a decade old and outsourced, which rules out any changes to it. The goal is to request raw material data from the web service by ID, and later returning the produced material data. The parameters of each function is documented, but the only existing URL requires username/password and directly shows the content of the WSDL file. That file (when saved locally) works on SoapUI, and test cases deliver the required results.

My problem is the first step - connecting to the web service and sending/receiving the messages. WebSVC (the InTouch web service client) cannot handle the basic authentification, even failing to connect when including username and password in the link (which works in any browser). Guides on that topic are often outdated, or don't match the problem at hand.

As my knowledge of databases and web programming is limited, how can I use the web service (in a preferably uncomplicated way)? Performance doesn't matter - the database handles less than 10 requests per minute on average, and delays don't disrupt anything essential.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dynat
  • 161
  • 5
  • what code have you tried so far? – Yuca Aug 15 '18 at 13:08
  • 2
    It *is* possible to call web services from SQL Server directly by leveraging OLE objects, with sufficient dedication. I strongly suggest you don't do that, however, because of the many caveats with stability, performance and monitoring. Look at using Integration Services, or any client programming language of your choice -- if you're on Windows, PowerShell's `New-WebServiceProxy` allows you to wire up SOAP services quite easily, and inserting things into SQL Server from it is equally simple (`Invoke-Sqlcmd`). – Jeroen Mostert Aug 15 '18 at 14:07
  • Usually when I encounter limitations like you describe in Wonderware I take to looking at building a Client Control in .NET that I can import as a Script Function Library (within the ArchestrA IDE). Do you have enough .NET knowledge (VB or C#) to build a basic Client Control Library and test outside of Wonderware? If you can get it working outside Wonderware then try importing the .DLL and running it from an ArchestrA graphics script. – Grambot Aug 15 '18 at 20:37
  • @JeroenMostert Thank you, the Powershell proxy seems to do the trick. Never used PS before (or heard of the proxy), but setting the credentials and using the web service's test method worked fine, I'll figure out the details later. Now...how do I accept that as answer? – Dynat Aug 16 '18 at 10:13
  • Easy, you write it up and accept it. Self-answering questions because someone's too lazy to turn a throwaway comment into a complete answer is absolutely allowed. :-) – Jeroen Mostert Aug 16 '18 at 10:31

1 Answers1

4

To quote Jeroen Mostert's comment: "if you're on Windows, PowerShell's New-WebServiceProxy allows you to wire up SOAP services quite easily, and inserting things into SQL Server from it is equally simple (Invoke-Sqlcmd)."

This approach works. To sum up the steps until I got an answer from the web service:

  • Upgrade to PowerShell 5.1 (Win7 VM didn't have it)
  • Set up WebServiceProxy by microsoft guide
  • Add credentials, attributes and test method by external guide, solving the authentication issue and getting the required input format
Grambot
  • 4,370
  • 5
  • 28
  • 43
Dynat
  • 161
  • 5