 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Clustering / July 2005Finding the Active Cluster Node
I have a SQL Server 2000 cluster with Windows 2003 server. Periodically I run a job that deletes all of the files out of the P:\dallas_mrkt directory on the active node. This is active/passive cluster with two nodes. I would like to modify the job listed below so that it will test to see which server is the active node and delete the files from the P:\dallas_mrkrt\ directory. Thank You, DECLARE @BACKUPFILEPATH varchar(255) @BACKUPFILEPATH = 'del P:\dallas_mrkrt\' + '*.* /Q' exec master..xp_cmdshell @BACKUPFILEPATH Hi If it is a SQL Server job, the SQL server agent would be running on the node with the running instance on it, so it would own the P drive, so the script will work. If it is not a SQL Server Agent Job, then share that directory using cluster services, and address the share as \\virtualservername\sharename in your script and delete the file that way. Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ > I have a SQL Server 2000 cluster with Windows 2003 server. > [quoted text clipped - 12 lines] > @BACKUPFILEPATH = 'del P:\dallas_mrkrt\' + '*.* /Q' > exec master..xp_cmdshell @BACKUPFILEPATH select host_name() resolves to the cluster node.  SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > > I have a SQL Server 2000 cluster with Windows 2003 server. [quoted text clipped - 13 lines] > @BACKUPFILEPATH = 'del P:\dallas_mrkrt\' + '*.* /Q' > exec master..xp_cmdshell @BACKUPFILEPATH Someone whom has requested anonymity (and respecting the wishes of Tom Moreau to remain anonymous is a sacred trust with me), has contacted me privately to point out 1) host_name resolves to the host you are running the command from. So this could be your work station; however, if you are logged on to the virtual server or one of its nodes it will work correctly. 2) it only works with the default instance. Running it on a named instance will resolve to the node name which could be different from the cluster name. So if your virtual server is called Server\InstanceName, and the nodes in this cluster are called NodeA or NodeB, host_name will return NodeA or NodeB. SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > select host_name() > [quoted text clipped - 16 lines] > > @BACKUPFILEPATH = 'del P:\dallas_mrkrt\' + '*.* /Q' > > exec master..xp_cmdshell @BACKUPFILEPATH LOL! SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com . Someone whom has requested anonymity (and respecting the wishes of Tom Moreau to remain anonymous is a sacred trust with me), has contacted me privately to point out 1) host_name resolves to the host you are running the command from. So this could be your work station; however, if you are logged on to the virtual server or one of its nodes it will work correctly. 2) it only works with the default instance. Running it on a named instance will resolve to the node name which could be different from the cluster name. So if your virtual server is called Server\InstanceName, and the nodes in this cluster are called NodeA or NodeB, host_name will return NodeA or NodeB. SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:%23$heMpdkFHA.3436@tk2msftngp13.phx.gbl... > select host_name() > [quoted text clipped - 16 lines] > > @BACKUPFILEPATH = 'del P:\dallas_mrkrt\' + '*.* /Q' > > exec master..xp_cmdshell @BACKUPFILEPATH Yeah, host_name() will return the workstation name from where it was run. One option would be to use the cluster.exe command line utility If I run the following command from the DOS prompt cluster clusterName res resourceName It returns Resource,Group,Node,Status Example: Say my cluster name is MyCluster and I want to know the node name for the resource SQLServer (Instance1) then the command will be cluster MyCluster res "SQL Server(Instance1)" The output will be Resource Group Node Status -------------------- -------------------- --------------- ------ SQL Server (Instance1) SQL1 NODE1 Online You can run the DOS command from Query Analyzer or stored proc using xp_cmdshell. See SQLServer Books Online for syntax. Best Regards, Uttam Parui Microsoft Corporation SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security. Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites: http://www.microsoft.com/protect http://www.microsoft.com/security/guidance/default.mspx Good point! But host_name will resolve correctly when run as a job on a default instance which IIRC was what the op asked for. SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > Yeah, host_name() will return the workstation name from where it was run. > [quoted text clipped - 26 lines] > > Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security. > Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites: > http://www.microsoft.com/protect > http://www.microsoft.com/security/guidance/default.mspx Of course, having this as a feature of SERVERPROPERTY() would be real cool. ;-) SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com . Yeah, host_name() will return the workstation name from where it was run. One option would be to use the cluster.exe command line utility If I run the following command from the DOS prompt cluster clusterName res resourceName It returns Resource,Group,Node,Status Example: Say my cluster name is MyCluster and I want to know the node name for the resource SQLServer (Instance1) then the command will be cluster MyCluster res "SQL Server(Instance1)" The output will be Resource Group Node Status -------------------- -------------------- --------------- ------ SQL Server (Instance1) SQL1 NODE1 Online You can run the DOS command from Query Analyzer or stored proc using xp_cmdshell. See SQLServer Books Online for syntax. Best Regards, Uttam Parui Microsoft Corporation SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security. Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites: http://www.microsoft.com/protect http://www.microsoft.com/security/guidance/default.mspx Sure. Feel free to email sqlwish@microsoft.com Best Regards, Uttam Parui Microsoft Corporation SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security. Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites: http://www.microsoft.com/protect http://www.microsoft.com/security/guidance/default.mspx Did that a long time ago. :-( SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com . Sure. Feel free to email sqlwish@microsoft.com Best Regards, Uttam Parui Microsoft Corporation SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security. Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites: http://www.microsoft.com/protect http://www.microsoft.com/security/guidance/default.mspx |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.