January 15, 2010 at 8:39 am
Hi
Is there any TSQL code that will bring back the real name of the server?
I have a cluster and all server name scripts that I know return the virtual server name. That's fine but I would like to know which node is currently active - without logging on to the server.
I'm building some server report pages - SSRS - for people to use when I am not in.
Ideally I want to get this without reading the registry (I'm not sure I'll be able to on all servers)
Thanks
Seth
January 15, 2010 at 9:17 am
Try this:
Select ServerProperty('ComputerNamePhysicalNetBIOS')
January 15, 2010 at 9:21 am
Spot on 😛
Thank-you
Seth
January 15, 2010 at 11:57 am
Seth Lynch (1/15/2010)
Spot on 😛Thank-you
Seth
.
Keep in mind that may execute under the service account context, which could show you the location of the IIS server serving up the page, and NOT the workstation. In other words it will do great if you are in SSMS, but masy return a number of other avlues if you are using a .NET application, or a web site, or a reporting server app or etc....
This works with direct connections, but not with things that essentially proxy in dta for you (like, the SSRS service account, any service acocunt used by a web site, etc.....)
Sorry to rain on your parade. Just be suer to test this using the context and setup you want it to work with, and see how it does there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 18, 2010 at 2:06 am
Thanks for the warning - that is the sort of thing I have been caught out on before.
I am running this in SSRS and it is working fine for me.
I use a dynamic data source so it is running against the individual servers rather that the local IIS server etc.
January 18, 2010 at 2:28 am
Matt Miller (#4) (1/15/2010)
Seth Lynch (1/15/2010)
Spot on 😛Thank-you
Seth
.
Keep in mind that may execute under the service account context, which could show you the location of the IIS server serving up the page, and NOT the workstation. In other words it will do great if you are in SSMS, but masy return a number of other avlues if you are using a .NET application, or a web site, or a reporting server app or etc....
This works with direct connections, but not with things that essentially proxy in dta for you (like, the SSRS service account, any service acocunt used by a web site, etc.....)
Sorry to rain on your parade. Just be suer to test this using the context and setup you want it to work with, and see how it does there.
Are you sure about that? This sounds strange to me, and I can’t check it right now. In my opinion when you run the statement – select serverproperty(‘ComputerNamePhysicalNetBIOS’) it runs on the SQL server regardless of how you ran it. you can run it from IIS server, reporting server, .NET application, VB6 application, SSMS, good old QA or any other tool, but the fact remains that the statements runs on the SQL Server and therefore it should return the same output regardless of how and from where the query was submitted to the SQL Server. I’d be very surprised if this select statement will return different results depending on the origin of the query itself.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 18, 2010 at 8:15 am
Adi Cohn-120898 (1/18/2010)
Matt Miller (#4) (1/15/2010)
Seth Lynch (1/15/2010)
Spot on 😛Thank-you
Seth
.
Keep in mind that may execute under the service account context, which could show you the location of the IIS server serving up the page, and NOT the workstation. In other words it will do great if you are in SSMS, but masy return a number of other avlues if you are using a .NET application, or a web site, or a reporting server app or etc....
This works with direct connections, but not with things that essentially proxy in dta for you (like, the SSRS service account, any service acocunt used by a web site, etc.....)
Sorry to rain on your parade. Just be suer to test this using the context and setup you want it to work with, and see how it does there.
Are you sure about that? This sounds strange to me, and I can’t check it right now. In my opinion when you run the statement – select serverproperty(‘ComputerNamePhysicalNetBIOS’) it runs on the SQL server regardless of how you ran it. you can run it from IIS server, reporting server, .NET application, VB6 application, SSMS, good old QA or any other tool, but the fact remains that the statements runs on the SQL Server and therefore it should return the same output regardless of how and from where the query was submitted to the SQL Server. I’d be very surprised if this select statement will return different results depending on the origin of the query itself.
Adi
The server name is fairly safe. That said - depending on where you call it from, you may get unexpected results (again - they'd be right, just not what the user wanted to get back). As in - if SSRS is running on a local instance on ServerA, and pulling data from ServerB, depending on how the call is made - you may get either one of the names.
Of course - if you're running SSRS on the same box as the DB you're querying, chances are pretty good that's the name you will get...:)
Just making sure he was mindful of how the context might play in.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 18, 2010 at 9:22 am
Matt, I am not sure how you would state that the result depends on where it is being run from.
As per BOL:
ComputerNamePhysicalNetBIOS
NetBIOS name of the local computer on which the instance of SQL Server is currently running.
For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster.
On a stand-alone instance of SQL Server, this value remains constant and returns the same value as the MachineName property.
Note:
If the instance of SQL Server is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property.
January 18, 2010 at 9:47 am
Richard M. (1/18/2010)
Matt, I am not sure how you would state that the result depends on where it is being run from.As per BOL:
ComputerNamePhysicalNetBIOS
NetBIOS name of the local computer on which the instance of SQL Server is currently running.
For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster.
On a stand-alone instance of SQL Server, this value remains constant and returns the same value as the MachineName property.
Note:
If the instance of SQL Server is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property.
I'm not disagreeing with that. Just pointing out that the SSRS instance need not be where the data actually resides. So depending on where you put that call, in an SSRS setting, you could pull the SSRS instance name, or the name of the instance with the data.
Perhaps I am being overly nitpicky, but we had a LOT of oddities like that when we moved from having SSRS on the same box as the "actual" data, to separate instances. All of the "oddities" were self-inflicted (the DBA team moved the reporting procedures from one box to the other, and switched the queries to using linked servers), but the effect was still bad from the user perspective.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 18, 2010 at 9:53 am
Matt, I see what you mean now.... it is related to the SSRS Instance, not the SQL instance what you are talking about. My bad..... I guess it would require some testing to validate the results then, but the sql connection for the "data" part should still be back to the SQL instance, irregardless where the SSRS is running at... but again, testing...
February 3, 2010 at 2:56 pm
erm hey my friend just told me to find out what node i'm on, i don't know what a node is or anything so i signed up to this to ask you, tell me what one is and to find out what node im on please 🙂
February 3, 2010 at 3:01 pm
Lucyhampson1 (2/3/2010)
erm hey my friend just told me to find out what node i'm on, i don't know what a node is or anything so i signed up to this to ask you, tell me what one is and to find out what node im on please 🙂
Nodes are used in SQL Server clustering:
More specifically, clustering refers to a group of two or more servers (generally called nodes) that work together and represent themselves as a single virtual server to a network.
reference: http://www.sql-server-performance.com/articles/clustering/clustering_intro_p1.aspx
February 3, 2010 at 3:08 pm
thanks 🙂 but how do i find what node i'm on? 😮
February 3, 2010 at 3:11 pm
Open a new query window and execute this SQL statement:
Select ServerProperty('ComputerNamePhysicalNetBIOS')
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply