Can I return data from this xp_cmdshell function?

[Dimm]

I'm trying to perform an NSlookup in a xp_cmdshell

The following returns the required result

DECLARE @Cmd varchar(8000)
DECLARE @Ip varchar(8000)
SET @Ip = '82.44.127.12'
SET @Cmd = 'NSLOOKUP ' + @Ip
EXEC master.dbo.xp_cmdshell @Cmd

But can this be done in a function? I tried but I cant seem to get the result back. This just returns the @Cmd String

Alter Function NSLookup (@Ip as varchar(100))
Returns varchar(8000)
AS
BEGIN
DECLARE @Cmd varchar(8000)
SET @Ip = '82.44.127.12'
SET @Cmd = 'NSLOOKUP ' + @Ip
EXEC master.dbo.xp_cmdshell @Cmd
RETURN @Cmd
END

I have a table of IP addresses and need to add the DNS Name - unless there is another way?

Select ip, NSLookup(ip) from IPaddresses

 

[SQLDenis]

take a look at this

CREATE TABLE #Results
(
Results VARCHAR(4000)
)

DECLARE @Cmd varchar(8000)
DECLARE @Ip varchar(8000)
SET @Ip  = '82.44.127.12'
SET @Cmd = 'NSLOOKUP ' + @Ip

INSERT INTO #Results
EXEC master.dbo.xp_cmdshell @Cmd

Select * from #Results
Where Results LIKE 'Name: %'

DROP TABLE #Results

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Dimm]

SQLDenis

I got there but you cant have temporary tables in a function!

 

[SQLDenis]

won't work inside a UDF
because you can't use temp tables
and you can use table variables but you can't use exec to insert into a table variable

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/