How to find the SQL Server's FQDN using t-sql and insert into a field in a record

  • I am trying to find my SQL Server's FQDN using T-SQL and then have it inserted into all of the fields of a particular column in a table. I am able to insert the server name, not the FQDN, using the following:

    UPDATE <table>

    SET <column> = @@SERVERNAME

    Any help is much appreciated!

    P

  • From T-SQL you'd have to read the registry or run a WMI script. I would recommend inverting your design and developing a SQL PowerShell script that can grab the host name from the OS and then run a SQL batch to update your table.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • you could look into doing an nslookup in combination with xp_cmdshell

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CirquedeSQLeil (4/12/2011)


    you could look into doing an nslookup in combination with xp_cmdshell

    That implies turning on xp_cmdshell which is off by default...for good reason.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Sorry, double post...

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (4/12/2011)


    CirquedeSQLeil (4/12/2011)


    you could look into doing an nslookup in combination with xp_cmdshell

    That implies turning on xp_cmdshell which is off by default...for good reason.

    That is true. There are also good reasons to enable it and in a controlled environment would mitigate those reasons for it being off by default. The biggest reason for it being disabled is that too many people did not know it was enabled nor how to mitigate the risk and thus left themselves exposed.

    Another option would be to use SSIS which can access the command shell. There are several methods for achieving the result through different means than straight t-sql.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CirquedeSQLeil (4/12/2011)


    There are also good reasons to enable it and in a controlled environment would mitigate those reasons for it being off by default. The biggest reason for it being disabled is that too many people did not know it was enabled nor how to mitigate the risk and thus left themselves exposed.

    Is there such a thing as "a controlled environment"? 😀

    SSIS is also a great alternative. The choice is easy [for me] when considering the amount of work needed to implement an alternate solution versus the benefits of having xp_cmdshell disabled.

    When secured properly there is nothing inherently wrong with xp_cmdshell (yeah, I said it), but securing it requires knowledge and planning and I venture to guess that in most cases it ends up running with full privileges on the machine where it runs. When I see a misuse of xp_cmdshell a trail had been blazed and the SQL Server instance ended up as a sysadmin/application script platform...enter PowerShell. I felt compelled to raise the issue because it is a slippery slope and is many times submitted as a potential solution with no disclaimer...but it deserves one. Just food for thought really, xp_cmdshell could get the OP's issue resolved.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (4/12/2011)


    CirquedeSQLeil (4/12/2011)


    There are also good reasons to enable it and in a controlled environment would mitigate those reasons for it being off by default. The biggest reason for it being disabled is that too many people did not know it was enabled nor how to mitigate the risk and thus left themselves exposed.

    Is there such a thing as "a controlled environment"? 😀

    SSIS is also a great alternative. The choice is easy [for me] when considering the amount of work needed to implement an alternate solution versus the benefits of having xp_cmdshell disabled.

    When secured properly there is nothing inherently wrong with xp_cmdshell (yeah, I said it), but securing it requires knowledge and planning and I venture to guess that in most cases it ends up running with full privileges on the machine where it runs. When I see a misuse of xp_cmdshell a trail had been blazed and the SQL Server instance ended up as a sysadmin/application script platform...enter PowerShell. I felt compelled to raise the issue because it is a slippery slope and is many times submitted as a potential solution with no disclaimer...but it deserves one. Just food for thought really, xp_cmdshell could get the OP's issue resolved.

    Agreed

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanks everyone for your recommendations. With the help of a coworker who is more skilled at PowerShell, we used did the following:

    $ipProperties = [System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()

    $FQDN = "{0}.{1}" -f $ipProperties.HostName, $ipProperties.DomainName

    invoke-sqlcmd -ServerInstance <host name> -username <sql usename> -password '<password>' -Query "UPDATE <table> SET SQLInstance = '$FQDN'" -ErrorLevel "-1" -Variable var1="$FQDN" -Verbose -QueryTimeout 600

    This script requires that the user input at least the host name (which is not exactly what I was wanting). He is working on using PowerShell to prompt the user to enter the host name, that will be inserted into the script.

    P

  • Thanks for posting back to the thread...it's good to hear what solutions people are landing on.

    Sounds like you are in good hands but I am just curious and can't help myself...in your original post it sounded like you wanted to run some T-SQL to get the FQDN of the SQL Server machine itself and then update a table in that same SQL Server so now I am confused by your comment "This script requires that the user input at least the host name". Wouldn't the local default instance be reachable using $ipProperties.HostName? Or is it that you are using named instances?

    If this script will eventually be scheduled to run unattended you can use a SQL Agent CmdExec job step to call the PowerShell script and pass the instance name dynamically as a command line parameter to the PowerShell script using the SQL Agent Token (SVR). Here is more info on SQL Agent Tokens http://msdn.microsoft.com/en-us/library/ms175575.aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Check this out!

    DECLARE @tblFQDNInfo TABLE( Value VARCHAR(8), Data VARCHAR(128))

    INSERT INTO @tblFQDNInfo

    EXEC [master]..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\ControlSet001\Services\Tcpip\Parameters', N'Hostname'

    INSERT INTO @tblFQDNInfo

    EXEC [master]..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\ControlSet001\Services\Tcpip\Parameters', N'Domain'

    SELECT tfi1.Data + N'.' + tfi2.Data FQDN

    FROM @tblFQDNInfo tfi1, @tblFQDNInfo tfi2

    WHERE tfi1.Value = N'Hostname' AND tfi2.Value = N'Domain'

Viewing 11 posts - 1 through 11 (of 11 total)

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