Post reply

To get system information from sqlserver

  • 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

  • January 5, 2004 at 11:13 pm

    #488757

    Hi,

    Try using these functions....

    HOST_ID() ,HOST_NAME() ,APP_NAME()

    Good luck

     


    Andy.

  • January 6, 2004 at 2:23 am

    #488762

    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]

  • January 6, 2004 at 2:30 am

    #488763

    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.

  • January 6, 2004 at 2:35 am

    #488764

    I was not sure if it works, but here is the complete thread http://qa.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]

  • January 6, 2004 at 2:38 am

    #488765

    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.

  • January 6, 2004 at 2:47 am

    #488767

    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]

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

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