winhttp.winhttprequest.5.1 the server name or address could not be resolved

Problem: When using POST or GET with sp_OAMethod in SQL Server, you receive the following error:

Error Source Description
0x80072EE7 WinHttp.WinHttpRequest The server name or address could not be resolved 

An example of a HTTP Post function is provided below:

Create function GetHttp
(
    @url varchar(8000)      
)
returns varchar(8000)
as
BEGIN
    DECLARE @win int 
    DECLARE @hr  int 
    DECLARE @text varchar(8000)

    EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT 
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

    EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

    EXEC @hr=sp_OAMethod @win,'SEND'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

    EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

    EXEC @hr=sp_OADestroy @win 
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 
    
    RETURN @text
END

The function is then called with a URL Variable:

DECLARE @url VARCHAR(500)
SET @url = 'http://google.co.uk'
exec GetHttp @url

Although there are many reasons for the error, if the simpler solutions have not worked (eg: Making sure the URL is correct, that the DNS resolves, Firewall is correctly configured) then the issue may be a hidden proxy.

WinHTTP uses its own proxy and depending on how your servers are configured, it may need to be set or unset.

For me, the proxy was enabled and i needed direct access.

Open an Elavated Powershell window:

> netsh winhttp show proxy

Current WinHTTP proxy settings:

    Proxy Server(s) :  proxy.xxxxxxxxx.co.uk:8080

    Bypass List     :  (none)

> netsh winhttp reset proxy

Current WinHTTP proxy settings:

    Direct access (no proxy server).

> netsh winhttp show proxy

Current WinHTTP proxy settings:

    Direct access (no proxy server).

Should I be using COM Objects post SQL 2000??!?

We did this for an old system, but my recommendation would be to use SQL Server CLR’s to access REST Services.

Great post here on MSDN of how to do this – http://blogs.msdn.com/b/sqllive/archive/2008/06/18/accessing-rest-based-web-services-using-sql-clr.aspx

Using DISM to enable features

From windows 2012, pkgmgr has been deprecated and cannot be used from the command line to install features on windows server.

Use the following instead to install a feature with a one line command:

dism /online /Enable-Feature /FeatureName:TelnetClient

Ive previously blogged about pkgmgr here – https://dbamohsin.wordpress.com/2013/07/05/pkgmgr-utility-to-install-windows-features-including-telnet/

Is my Windows OS 32-bit or 64-bit?

Cool little query to retrieve OS architecture version

wmic os get osarchitecture

Returns something like this:

H:0_ORA_SETUP\sql>wmic os get osarchitecture
OSArchitecture
64-bit

the wmic os get command has more variables to search on. eg:

H:0_ORA_SETUP\sql>WMIC OS GET osarchitecture, caption /value
Caption=Microsoft Windows 7 Enterprise
OSArchitecture=64-bit

A lot more commands available – http://ss64.com/nt/wmic.html

Get CPU/Core/HT Count

Several different methods available.

T-SQL:

SELECT 
  cpu_count AS NumberOfLogicalCPUs
, hyperthread_ratio
, ( cpu_count / hyperthread_ratio ) AS NumberOfPhysicalCPUs
, CASE
      WHEN hyperthread_ratio = cpu_count THEN cpu_count
      ELSE ( ( cpu_count - hyperthread_ratio ) / 
             ( cpu_count / hyperthread_ratio ) )
 END AS NumberOfCoresInEachCPU
, CASE
    WHEN hyperthread_ratio = cpu_count THEN cpu_count
    ELSE ( cpu_count / hyperthread_ratio ) 
    * ( ( cpu_count - hyperthread_ratio ) / 
            ( cpu_count / hyperthread_ratio ) )
  END AS TotalNumberOfCores
FROM sys.dm_os_sys_info

Powershell – Get Cores:

Get-WmiObject -namespace "root\CIMV2" -class Win32_Processor -Property NumberOfCores | select NumberOfCores

CMD Line:

wmic cpu get NumberofCores, NumberofLogicalProcessors

Useful Links:

http://weblogs.sqlteam.com/tarad/archive/2008/08/05/How-to-get-physical-CPU-count-on-a-server.aspx

http://www.pxserver.com/WinAudit.htm

Pkgmgr utility to install windows features including Telnet

Install Telnet Client by using a command line

On Windows 7, Windows Server 2008 R2, Windows Server 2008 or Windows Vista you can use the following command line procedure to install Telnet Client.

Open a command prompt window. Click Start, type cmd in the Start Search box, and then press ENTER.
Type the following command:

pkgmgr /iu:"TelnetClient"

If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue. When the command prompt appears again, the installation is complete.

http://technet.microsoft.com/en-us/library/cc771275(v=ws.10).aspx

Pkgmgr options:

http://technet.microsoft.com/en-us/library/cc749465(v=ws.10).aspx

Full list of windows server packages

http://technet.microsoft.com/en-us/library/cc748930(v=ws.10).aspx

View the members of an Active Directory group…Without being an Admin

From a computer that’s a member of the domain, open a command-prompt and run:

NET GROUP "group name" /DOMAIN

Unless your administrators have changed the permissions on the group object you will be able to view the membership that way.

You can use AD Users and Computers (dsa.msc) even if you’re not an administrator, but this, at least, can be done w/o installing anything.