0

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 ?

Pio Sammut
  • 23
  • 1
  • 10
  • No, SQLCLR is no more outdated than trying to use the old, insecure, discouraged `xp_cmdshell`. In fact, trying to "open a URL" from inside a database sounds like a very bad design. That's a job for SSIS or an external application. *NOT* the database. Just use the appropriate tool for the job – Panagiotis Kanavos May 31 '17 at 12:51
  • 1
    BTW that *outdated SQLCLR* is what powers hierarchyid and the spatial types – Panagiotis Kanavos May 31 '17 at 12:52
  • I'm new to this job, whats SSIS, and the method we tried of SQLCLR was outdated – Pio Sammut May 31 '17 at 12:56
  • Where did you get the idea that it's outdated? Anyway, SO is about programming questions, not tutorials. SSIS is an entire subsystem of SQL Server, with its own documentation section. ETL is *not* a trivial subject. Hitting unknown URLs with disabled security and a high-privilege account (which is required to execute xp_cmdshell) is not a good idea – Panagiotis Kanavos May 31 '17 at 12:57
  • Then what would be a good idea to somehow open a URL with T-SQL ? (Yes I already knew about the security issues of going forawrd with that practice but my employers still want to go with it) – Pio Sammut May 31 '17 at 13:03
  • 2
    None. Opening a URL through SQL is a very, very, VERY bad idea. The employers most definitely don't want you to do that. They want to import data from a URL. There are safer and infinitely easier ways to do this, like reading the data you need to generate the calls from the database, sending the GET requests, parsing the results and inserting them in the database – Panagiotis Kanavos May 31 '17 at 13:03
  • Your SQL is called by some Application, isn't it ? Why don't you open the URL from that Application directly instead of trying through SQL-Server ? – Hybris95 May 31 '17 at 13:05
  • I've explained that to them multiple times, they will not take my advice, is there some sort of external tool i can use, or maybe a safe enough 3rd party application – Pio Sammut May 31 '17 at 13:05
  • Yes. Any application you write, is already a third party, external application. A powershell script is the same. Besides - who is going to call that `xp_cmdshell` anyway? Most likely a SQL Server Agent job that can call an external application just as easily – Panagiotis Kanavos May 31 '17 at 13:06
  • Who is going to *call* that job? Who is going to start it? Why not create an agent job? You can have it run with a restricted account, launch it using a T-SQL command. No need to weaken the server when an easy alternative is available – Panagiotis Kanavos May 31 '17 at 13:08
  • And can i get the agent job to work the same way a trigger does so that it will open the url when a database had any of the CRUD operations performed – Pio Sammut May 31 '17 at 13:09
  • @PioSammut Um, why would you want to "_open the url when a database had any of the CRUD operations performed_"? That is critical info that needs to be included in the question. Please update the question with the overall goal so that you actually have a chance of getting an appropriate answer here. But FYI: using a WebService for auditing changes is a ***horribly dangerous*** idea. Also, now that another person (me) has added a comment, you need to use our `@` names in comments so that we get notified of your replies. – Solomon Rutzky May 31 '17 at 13:36
  • @PioSammut the problem you are having is because you have chosen a path without understanding (or at least communicating) your goal. No one can help you without understanding **WHY** you want to "open a url". – SMor May 31 '17 at 13:49

1 Answers1

0

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

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • @PioSammut Start working on what, exactly? You are being very vague and if you really are trying to do this within a Trigger for auditing changes then I might change, or clarify at least, my advice. Please update the question with what, specifically, you are trying to accomplish. – Solomon Rutzky Jun 01 '17 at 13:44
  • Listen dude, you don't need to get so pissed, its my workplace pushing me to do this, while i have beginner knowledge on SQL, thanks for the advice and sorry for not giving out much detail, i'm not allowed to give out detial because its under the government, i was asking a question i knew nothing about, so sorry for not knowing – Pio Sammut Jun 06 '17 at 08:31
  • @PioSammut You misunderstand me: I'm not upset all. I'm actually deeply concerned. I want to help you achieve the overall goal as best as possible. I get that you can't share details, but worry that being so general you risk getting a correct _technical_ answer that only allows you to proceed in a disastrous direction. Having only beginner knowledge in SQL Server makes it more likely to misapply a particular feature, and makes it more important to share at least enough detail to get the best answer for the goal, not the question. Please see the updates I just made to my answer. – Solomon Rutzky Jun 07 '17 at 18:07
  • sorry for being mad, i have asked the officails above me, and i have read through the entire documentation, they are instead going to go forward with my original idea and change the source code of the application we use(which in reality is way easier, they just didn't want to do it), instead of doing this which is quite risky for the database, and keep in mind its a hospital db, so its very important data – Pio Sammut Jun 08 '17 at 07:47
  • @PioSammut Is ok. It is easy for comments to come off as being snippy. I'm glad to hear that folks there are finally listening to you (after all, they are paying you to be the technical one rather than doing it themselves). I will just say that the best approach is based on the actual needs. If the data audit is for legal compliance / regulation, then you might need triggers since updating the app won't catch DML changes made in SSMS, etc. But there are more appropriate mechanisms, which is the topic of the question linked at the bottom of my answer. Either way, good luck!! – Solomon Rutzky Jun 08 '17 at 16:34