April 12, 2011 at 10:19 am
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
April 12, 2011 at 10:59 am
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
April 12, 2011 at 11:19 am
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
April 12, 2011 at 11:25 am
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
April 12, 2011 at 11:25 am
Sorry, double post...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 12, 2011 at 11:31 am
opc.three (4/12/2011)
CirquedeSQLeil (4/12/2011)
you could look into doing an nslookup in combination with xp_cmdshellThat 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
April 12, 2011 at 1:35 pm
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
April 12, 2011 at 1:41 pm
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
April 21, 2011 at 1:48 pm
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
April 22, 2011 at 9:44 pm
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
April 23, 2011 at 12:22 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy