March 18, 2010 at 7:19 am
Hi Folks,
This is driving me around the bend:-
1) Created a UDF as follows:
CREATE FUNCTION [dbo].[fnConvertIP] (
@ISALogIPAddress bigint
)
RETURNS varchar(15) AS
BEGIN
DECLARE @ConvertedAddress varchar(15)
SET @ConvertedAddress =
CAST(@ISALogIPAddress / 256 / 256 / 256 % 256 AS VARCHAR) + '.' + CAST(@ISALogIPAddress / 256 / 256 % 256 AS VARCHAR) + '.' + CAST(@ISALogIPAddress / 256 % 256 AS VARCHAR) + '.' + CAST(@ISALogIPAddress % 256 AS VARCHAR)
RETURN @ConvertedAddress
END
2) Executing my query as follows:
SELECT
dbo.fnConvertIP([ClientIP]) AS IP
FROM WebProxyLog
3) Error:
Msg 195, Level 15, State 10, Line 3
'fnConvertIP' is not a recognized built-in function name.
What am I doing wrong?
Thanks in advance, Andrew
March 18, 2010 at 7:44 am
Not sure. One thing I'd do is rewrite the function as an in-line TVF and use it in a CROSS APPLY in the FROM clause. Before doing that, however, I'd like to test it a bit. Can you provide the DDL (CREATE TABLE statement) for the table in your query, and some sample data (INSERT INTO statements) for the table (2 or 3 rows is all that tis needed).
One question, is the function created in the same database as the table you are querying?
March 18, 2010 at 7:56 am
Its working fine at my end. Please check you created function in the same database.
March 18, 2010 at 8:02 am
It would look something like this:
CREATE FUNCTION [dbo].[fnConvertIP] (
@ISALogIPAddress bigint
)
RETURNS table AS
RETURN (
select CAST(@ISALogIPAddress / 256 / 256 / 256 % 256 AS VARCHAR) + '.' +
CAST(@ISALogIPAddress / 256 / 256 % 256 AS VARCHAR) + '.' +
CAST(@ISALogIPAddress / 256 % 256 AS VARCHAR) + '.' +
CAST(@ISALogIPAddress % 256 AS VARCHAR) as IPAddress
);
GO
SELECT
cp.IPAddress
FROM
WebProxyLog wpl
CROSS APPLY dbo.fnConvertIP(wpl.ClientIP) cp;
GO
March 18, 2010 at 8:05 am
Lynn,
Table as follows and sample data attached
USE [ISAlogs]
GO
/****** Object: Table [dbo].[WebProxyLog] Script Date: 03/18/2010 16:02:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WebProxyLog](
[ClientIP] [bigint] NOT NULL,
[ClientUserName] [nvarchar](514) NULL,
[ClientAgent] [varchar](128) NULL,
[ClientAuthenticate] [smallint] NULL,
[logTime] [datetime] NULL,
[service] [smallint] NULL,
[servername] [nvarchar](32) NULL,
[referredserver] [varchar](255) NULL,
[DestHost] [varchar](255) NULL,
[DestHostIP] [bigint] NULL,
[DestHostPort] [int] NULL,
[processingtime] [int] NULL,
[bytesrecvd] [bigint] NULL,
[bytessent] [bigint] NULL,
[protocol] [varchar](12) NULL,
[transport] [varchar](8) NULL,
[operation] [varchar](24) NULL,
[uri] [varchar](2048) NULL,
[mimetype] [varchar](32) NULL,
[objectsource] [smallint] NULL,
[resultcode] [int] NULL,
[CacheInfo] [int] NULL,
[rule] [nvarchar](128) NULL,
[FilterInfo] [nvarchar](256) NULL,
[SrcNetwork] [nvarchar](128) NULL,
[DstNetwork] [nvarchar](128) NULL,
[ErrorInfo] [int] NULL,
[Action] [varchar](32) NULL,
[GmtLogTime] [datetime] NULL,
[AuthenticationServer] [varchar](255) NULL
) ON [PRIMARY]
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply