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

Question

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

Makers_Mark

SSC Veteran

Points: 294
More actions
April 12, 2011 at 10:19 am

#236508

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

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

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

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 1

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 2

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

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 3

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

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 4

Sorry, double post...

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

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

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 6

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 7

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

Makers_Mark SSC Veteran Points: 294 More actions · Answer 8

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

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 9

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

iDiPs Old Hand Points: 348 More actions · Answer 10

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'

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

Cookies on SQLServerCentral