Leaving security concerns aside , here's a reminder on enabling xp_cmdshell
Using xp_cmdshell to retrieve server ip address -
create table #cmdresults(ip varchar(255)) insert into #cmdresults exec xp_cmdshell'ipconfig | find "IP Address"' select ltrim(rtrim(substring(ip,charindex(':',ip)+1,len(ip)))) from #cmdresults where ip is not null drop table #cmdresults
On moving it to my server, I found it did not work. The difference being that I needed to look for 'IPv4 Address', not 'IP Address' on my Windows 2008 installs where entries for IPv4 and IPv6 could both exist.
A simpler version was suggested by Kendra Little (@Kendra_Little | blog) was to look at the connections dmv, like this
Using DMV to return session ip address -
SELECT local_net_address FROM sys.dm_exec_connections WHERE session_id=@@SPID
This returns the connected client IP, so it only returns the server IP if you are working on your server (or an rdp session on it).
For the purpose of what I needed (reports emailed from the sql server), it is perfectly adequate (and tidier than xp_cmdshell).