Search code examples
sql-serverbatch-filecmdsqlclrsqlcmd

open a URL with SQL Server 2012

I am trying to open a web URL through SQL Server 2012, we have tried SQLCLR but its outdadted, we tried to run a batch file and it would get stuck in the executing process

EXEC xp_cmdshell 'c:\PATH.bat'

that's the code we used to open the batch file and then it gets stuck in executing query and i waited 5 minutes still nothing popped up

enter image description here

we have checked through file permissions and everything is allowed, its the 4th time ive tried to this and i couldnt manage can someone please show me an alternate solution ?

Solution

  • While there are pros and cons to accessing a URL from within SQL Server, SQLCLR is most definitely not outdated. Even if you have no custom Assemblies, it is still being used internally for several things:

    • Hierarchy, Geometry, Geography datatypes
    • Replication
    • Several built-in functions such as FORMAT, TRY_PARSE, etc
    • etc

    For more info on what SQLCLR actually is and can do, please see the series of articles I am writing on this topic on SQL Server Central: Stairway to SQLCLR (free registration is required to read content on that site, but it's worth it :-). Level 1 ("What is SQLCLR?") is a fairly comprehensive look at what SQLCLR both is and is not.

    If you want a command line utility then you might be able to get away with using curl.

    If you want a pre-made SQLCLR function that can handle this so that you don't need to worry about the learning curve of doing such an operation in SQLCLR, then that is available in the SQL# library that I created (but it is not in the Free version; only available in the Full / paid version).

    IF you are going to be making this URL / Web Sevice call from within a Trigger (whether it is a SQLCLR Trigger or T-SQL Trigger calling a SQLCLR object), then you need to be very careful since Triggers execute within a system-created Transaction (if no explicit Transaction already exists). What this means is that the actual committing of the Transaction (i.e. the true saving of the change to the DB) will wait until the external call completes. The two problems you run into here are:

    • The Web Service does not respond super quickly (and it needs to respond super quickly)
    • There are more concurrent requests made to the specific URI such that .NET waits until there is an opening. This is controlled by ServicePointManager.DefaultConnectionLimit, which can be accessed via the HttpWebRequest object (I think there is a ServicePoint property). The default limit is 2, so any more than 1 - 3 calls to the Web Service per second (generally speaking) can cause blocking, even if the Web Service has the ability to respond quickly. 1 - 3 calls per second might not seem like much, but if using this approach in an audit Trigger scenario on multiple tables, it becomes quite easy to reach this limit. So you need to increase the limit to something much higher than 2, and per each call as it is stored in the App Domain which sometimes gets unloaded due to memory pressure.

    For more info and considerations, please see my related answers to similar questions here on S.O.:

    Also, this S.O. question is very similar in terms of wanting to get near real-time notification of DML changes, and might apply to your goal:

    SqlDependency vs SQLCLR call to WebService