1

How can i resolve a hostname in t-sql? a 2000 compatible method is preferred. Although something that works on 2005/2008 would also be helpful.

eg. If i have the hostname stackoverflow.com i want to return 69.59.196.211

Nick Kavadias
  • 7,542
  • 2
  • 37
  • 46
  • I'm not sure this would be an appropriate function for a database to perform. Also, are you aware that a single hostname may resolve to multiple IP addresses? – John Saunders Jun 09 '09 at 00:43
  • yeah, i know there are things such as round-robin dns that will give you different IP's but i dont have to deal with that in the environment im working in. – Nick Kavadias Jun 09 '09 at 00:50
  • Sometimes, when all you have is a hammer.. – ahains Jun 09 '09 at 01:07
  • This is not really a database question at all. I think this could be rephrased as 'how can I do an arbitrary external work via sql 2005?' – ahains Jun 09 '09 at 05:58

2 Answers2

6

Well, I suppose you could use xp_cmdshell to execute nslookup and parse the results. Seems like a really awkward thing for SQL Server to be doing, though.

exec master..xp_cmdshell 'nslookup intel.com'

.. then you'll probably want to stuff that in a temp table and walk through the results.

You could also, if you can get access to SQL Server 2005 or 2008, build a stored procedure or function in .NET and do a simple call to Dns.GetHostAddresses().

Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
  • 1
    Completely untested, from the Forum That Shall Not Be Named: CREATE Procedure xp_Nslookup @ip varchar(17) as declare @cmd varchar(255) /*set @ip = '192.168.100.1'*/ set @cmd = 'For /F "skip=2 tokens=1,2" %i in (''c:\winnt\system32\nslookup.exe '+@IP+''') do @echo '+@IP+'^|%i^|%j^|'+ convert(varchar(20),getdate()) insert into Nslookup exec master..xp_cmdshell @cmd delete Nslookup where Data is null GO – Michael Petrotta Jun 09 '09 at 00:55
0

SQL Server may block access to procedure 'sys.xp_cmdshell' as part of the security configuration. As a system administrator you can enable the use of 'xp_cmdshell' as follows:

-- Allow advanced options.
EXEC sp_configure 'show advanced options', 1;
GO
-- Update the currently configured value for advanced options.
RECONFIGURE;
GO
-- Then, enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- Update the currently configured value for this feature.
RECONFIGURE;
GO
-- Then you can go ahead and run ...
exec master..xp_cmdshell 'nslookup microsoft.com'
GO
Jorge Garcia
  • 2,042
  • 23
  • 25