To get system information from sqlserver

Question

Post reply

To get system information from sqlserver

punitnb

SSC Rookie

Points: 37
More actions
January 5, 2004 at 9:40 pm

#85211

I need system information of the client machine talking to Sql server database in a trigger. ie. Host name, Ip Address , the system login name.
I get this info in Oracle using sys_context function that fetched from userenv.
Is it possible to get this information in sql server as well.
Any help is highly appreciated .
Thanks in advance

With Regards
Girish

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jordanac Hall of Fame Points: 3653 More actions · Answer 1

Hi,

Try using these functions....

HOST_ID() ,HOST_NAME() ,APP_NAME()

Good luck

Andy.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Getting ip adresses is somewhat painful as there is no built in function.

However, David Burrows provided this workaround a while ago

declare @ip varchar(255),

@cmd varchar(100)

set @cmd = 'ping ' + HOST_NAME()

create table #temptb (

grabfield varchar(255)

)

insert into #temptb

exec master.dbo.xp_cmdshell @cmd

select @ip = substring(grabfield,charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) from #temptb where left(grabfield,7) = 'Pinging'

print @ip

select * from #temptb

drop table #temptb

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jordanac Hall of Fame Points: 3653 More actions · Answer 3

Hi,

I was typing a response that says exactly the same as Frank's replay. You have to use an extended sp if you wan't the actual IP.

Andy.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

I was not sure if it works, but here is the complete thread http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=18764

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jordanac Hall of Fame Points: 3653 More actions · Answer 5

Frank

I have already saved the script in my "KEEP - will need some day" safe place.

Yes it works - I have just tested it.

Andy.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hey, if it fails we could blame David

I had actually only the reference to the original thread in the old forum, and was unsure what happens when I click on the link. But it redirected me without problems to that thread in the new forum.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]