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/



"Joe K." wrote:

select host_name()

resolves to the cluster node.

--
Hilary 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
"Joe K." <Joe K. (AT) discussions (DOT) microsoft.com> wrote

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.

--
Hilary 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 (AT) gmail (DOT) com> wrote

LOL!

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Hilary Cotter" <hilary.cotter (AT) gmail (DOT) com> wrote

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.

--
Hilary 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 (AT) gmail (DOT) com> wrote

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.

--
Hilary 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
"Uttam Parui[MS]" <uttamkp (AT) online (DOT) microsoft.com> wrote

http://www.microsoft.com/security.
vulnerabilities. The easiest way to do this is to visit the following
websites:

Of course, having this as a feature of SERVERPROPERTY() would be real cool.
;-)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Uttam Parui[MS]" <uttamkp (AT) online (DOT) microsoft.com> wrote

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

This 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. :-(

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Uttam Parui[MS]" <uttamkp (AT) online (DOT) microsoft.com> wrote

Sure. Feel free to email

sqlwish (AT) microsoft (DOT) com

Best Regards,

Uttam Parui
Microsoft Corporation

This 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

I have an application that would like to know which is the current active
node name in SQL 2000.
If I use 'cluster clusterName res resourceName ' to retrieve the active node
name and parse the output in my code; do I need to take care of any locale
specific parsing of the application runs on a non-english locale?

Appreciate all your help.

"Uttam Parui[MS]" wrote: