|
First, build the .DLL binary from the following source code (Microsoft Visual
Studio 2010 will do just fine):
using System.Net;
using
System.Security;
using System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
public class
SQLSPGetHostName
{
// static string
whatipaddress
[Microsoft.SqlServer.Server.SqlProcedure]
public
static void GetHostNameFromIPAddress(string
whatipaddress)
{
string wantipaddress =
whatipaddress;
IPHostEntry
IpEntry =
Dns.GetHostEntry(wantipaddress);
// IPHostEntry IpEntry =
Dns.Resolve(wantipaddress);
SqlContext.Pipe.Send(IpEntry.HostName.ToString());
}
[Microsoft.SqlServer.Server.SqlFunction()]
public
static string fn_GetHostname(string whatipaddress)
{
string wantipaddress =
whatipaddress;
try
{
IPHostEntry IpEntry =
Dns.GetHostEntry(wantipaddress);
// IPHostEntry IpEntry =
Dns.Resolve(wantipaddress);
return
IpEntry.HostName.ToString();
}
catch
{
return whatipaddress;
}
}
}
Then, use T-SQL to
configure:
--exec dbo.SP_GetHostName '10.5.10.81'
--select
dbo.fn_GetHostname('10.5.10.81')
alter database admindb set trustworthy on
ALTER AUTHORIZATION on
database::admindb to sa;
create assembly CLR_GetHostName from 'D:\LogTrace\CLR_GetHostname.dll'
with permission_set = EXTERNAL_ACCESS --SAFE
GO
--or--
create
assembly CLR_GetHostName from 'D:\LogTrace\CLR_GetHostname.dll' with
permission_set = SAFE
GO
drop assembly CLR_GetHostName
GO
ALTER ASSEMBLY CLR_GetHostName WITH PERMISSION_SET =
EXTERNAL_ACCESS
GO
USE [ADMINDB]
GO
/****** Object: UserDefinedFunction
[dbo].[fn_GetHostname] Script Date: 04/27/2011 10:30:25
******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[fn_GetHostname]') AND type in (N'FN', N'IF', N'TF',
N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetHostname]
GO
create function dbo.fn_GetHostname
(@ipaddress
nvarchar(20))
returns nvarchar(50)
AS
External NAME
CLR_GetHostName.SQLSPGetHostName.fn_GetHostname
GO
USE [ADMINDB]
GO
/****** Object: StoredProcedure
[dbo].[SP_GetHostName] Script Date: 04/27/2011 10:26:42
******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[SP_GetHostName]') AND type in (N'P', N'PC'))
DROP
PROCEDURE [dbo].[SP_GetHostName]
GO
USE [ADMINDB]
GO
/****** Object: StoredProcedure
[dbo].[SP_GetHostName] Script Date: 04/27/2011 10:26:20
******/
CREATE PROCEDURE [dbo].[SP_GetHostName]
@IP
[nvarchar](1024)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME
[CLR_GetHostName].[SQLSPGetHostName].[GetHostNameFromIPAddress]
GO
|