I was working with SQL2000 on a windows cluster. I needed to write the results of a query to a test file on the local drive. I was using @@servername to get the server name to write the file. Unfortunately that appends the instance name and gave me a problem. So I went looking for how to remove the instance name and I found a pretty neat trick.
I ran the command: select @@servername and received ‘server\instance’
I ran the command: select @@servicename and received ‘instance’
So I thought there must be a way to subtract @@servicename from @@server name, then I tried this:
select replace(@@servername,’\’ + @@servicename,”)
My result was: ‘server’
That I can work with.