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

History of SQL Server 1989 – 2012

Saw this on another blog and thought it was a pretty cool, concise history lesson for SQL Server versions.

(http://robbagley.wordpress.com/2012/03/19/the-history-of-sql-server/)

Timeline:

SQL Server Release History
Version Year Release Name Codename
1 1989 SQL Server 1.0
(OS/2) (16bit)
1.1 1991 SQL Server 1.1
(OS/2) (16bit)
4.21 1993 SQL Server 4.21 SQLNT
(WinNT)
6 1995 SQL Server 6.0 SQL95
6.5 1996 SQL Server 6.5 Hydra
7 1998 SQL Server 7.0 Sphinx
1999 SQL Server 7.0 Palato mania
OLAP Tools
8 2000 SQL Server 2000 Shiloh
8 2003 SQL Server 2000 Liberty
64-bit Edition
9 2005 SQL Server 2005 Yukon
10 2008 SQL Server 2008 Katmai
10.25 2010 SQL Azure DB CloudDatabase
10.5 2010 SQL Server 2008 R2 Kilimanjaro (aka KJ)
11 2012 SQL Server 2012 Denali

Progress of a Backup or Restore in SQL 2000?

In SQL 2005 onwards, you can use DMV’s to monitor percentage completed of certain operations (https://dbamohsin.wordpress.com/2011/02/08/dmvs-sys-dm_exec_requests-and-percent_complete/)

Is this possible in SQL 2000 apart from using the STATS flag on the BACKUP or RESTORE syntax…

Yes…

Use the following command against the SPID running the backup or Restore to see buffer information

DBCC OUTPUTBUFFER(spid)

Output Buffer                                                                
—————————————————————————–

00000000   04 00 00 5d 00 38 06 00 79 01 00 00 00 ab 44 00   …].8..y….«D.

00000010   8b 0c 00 00 01 00 14 00 33 00 30 00 20 00 70 00   ‹…….3.0. .p.

00000020   65 00 72 00 63 00 65 00 6e 00 74 00 20 00 72 00   e.r.c.e.n.t. .r.

00000030   65 00 73 00 74 00 6f 00 72 00 65 00 64 00 2e 00   e.s.t.o.r.e.d…

Free eBooks from Microsoft

Came across this the other day – http://blogs.msdn.com/b/microsoft_press/archive/2012/05/04/free-ebooks-great-content-from-microsoft-press-that-won-t-cost-you-a-penny.aspx

Some great content :

image

DTS packages fail after changing server host name

Executed as user: DOMAIN\SQL.agent. DTSRun:  Loading...      
Error:  -2147467259 (80004005); Provider Error:  17 (11)      
The step failed.

After changing the host of a SQL server, i found that the DTS packages were still referencing the old server as the DTS Package Owner. This was resulting in the error above when run as a Agent job.

To resolve I’ve created a script which will dynamically create the SQL to change the owner of a DTS package. sp_reassign_dtspackageowner is an undocumented stored procedure (not that this matters in 2012 when SQL 2000 is unsupported! 🙂 )

SELECT DISTINCT [name], [id], 
'exec sp_reassign_dtspackageowner @name='''+[name]+''', @id='''
+CAST([id] as varchar(36))+''', @newloginname='''+'DESIRED\DTSPACKAGEOWNER'''
FROM sysdtspackages

Run the output from the SQL above (for the packages required), and this should rename the packages. 

The error can also occur if the SQL Agent Job which is running the DTS Package, has an old reference relating to the old server. This can be resolved by recreating the job by right clicking the package and scheduling.

Also see: https://dbamohsin.wordpress.com/2012/01/05/p2v-a-sql-server-2000-machine/

P2V a SQL Server 2000 Machine

P2V (Physical to Virtual) is a method of migrating a Physical server to a Virtual Machine. We did this on an old SQL 2000 machine and there are some considerations to be aware of when either the IP or Hostname changes…

The following applied to SQL Server 2000, 2005, 2008.

Step 1 (All versions)

The master sysservers table holds the local machine name and this will not match if the server has been renamed.

EXEC sp_dropserver '<old_name>' 
GO 
EXEC sp_addserver '<new_name>', 'local' 
GO 

If the computer has any remote logins, for example, if it is a replication publisher or distributor, sp_dropserver may generate the following error (which it did in our case)

Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
There are still remote logins for the server 'SERVER1'

To resolve for replication, disable replication before running sp_dropserver. Otherwise, drop the remote logins.

Step 2 (SQL 2000)

When a hostname is changed, the sysjobs table in the MSDB database keeps the old hostname in the column originating_server. This creates the assumption that the jobs are target server (MSX) and not local, which can lead to issues with modifying job definition on the new server. If a job is created on a master server, you cannot modify the job definition on the target server; all modifications has to be done on the master server.

This can be resolved in 2 ways:

1. Rename machine to old name, script job definitions, delete jobs, rename machine to new name and use the script to re-create the jobs. See Error 14274 Occurs When You Update a SQL Agent Job After Renaming Windows Server for more information.

2. If you are comfortable with msdb system tables, then modifying the sysjobs table directly makes the task a lot easier.

DECLARE @srv sysname 
SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname) 
UPDATE sysjobs SET originating_server = @srv 
--If this is a target server (you have jobs sent from a master server), 
--you have to exclude them, so you don't convert those jobs into local jobs:
WHERE originating_server = '<old_name>' 

Distributed Transaction Coordinator (DTC)

If deploying images with SQL Server installed, then consider that the DTC creates a GUID in the registry when installed. for a local instance in SQL 2000, it is located in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\ResourceMgrID

For a named instance of SQL Server it is located:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\INSTANCE1\MSSQLServer\ResourceMgrID

The key can be regenerated uniquely by Deleting and then restarting SQL Server making sure that no distributed transactions are open.

Also Consider: https://dbamohsin.wordpress.com/2012/01/16/dts-packages-fail-after-changing-server-host-name/