@@SERVERNAME
I was working on a SQL Server project and came across a piece of code that I wanted to only execute if the code was running in production. This is a fairly common problem — code that should only execute if on a specific environment.
Most of the time in my career, I’ve seen people just comment out the code in a given environment. [And yes, I've seen bad mistakes with this method.] While this has obvious potential side effects I didn’t have time to give it much thought… Until recently.
I realized that SQL Server 2000 has a system variable called @@ServerName that will always give you the server name for the active connection.
Using this system variable, you can write some very simple code that will only execute other code in a given environment.
How can you use the variable?
Usage of the system variable (and any system variable) is easy. You can use either Select, Print or any logical statements (such as If or While loops).
All of the following are validate:
1 2 3 4 5 | SELECT @@ServerName PRINT @@ServerName IF @@Servername = 'Me' BEGIN END |
How to Implement It
How I implemented it was a bit unique; I will first start with a very simple example.
1 2 3 4 5 6 7 | Exec ValidateData IF @@ServerName = 'ProductionSrvr' BEGIN UPDATE Users SET Processed = 1 WHERE UserID = @UserID END |
To quickly walk though this code, it is executeing a stored procedure call ValidateData and then we check the @@ServerName variable to find if it matches “ProductionSrvr”. If @@ServerName does, it will execute the update; otherwise the code would go right over the if. This would be great if you didn’t want to update the Users table in the staging or development environments but you did in production.
My twist on implementation
My primary employer does a thing where they lease their servers (and desktop stations) from a provider. The leases expire every 4 years. This means that every four years we migrate databases and web servers to new machines (or virtual machines, as of recent). To my knowledge, we have hundreds of Servers, serving all sorts of software and information (Web sites, databases, Cubes, etc)
Since our SQL Servers are no exception, I wanted to be prepared for such a switch but I still wanted to implement this because of it’s obvious benefits.
I obviously didn’t want to do a find and replace on all of my Stored Procedures and DTS packages (don’t ask) and so I used a function to check the @@ServerName variable against. The function was simple and an example definition follows:
1 2 3 4 5 6 | CREATE FUNCTION dbo.fn_ExampleServer () RETURNS VARCHAR(100) AS BEGIN RETURN 'ExampleServer' --Simply return a constant with the server's name END |
My @@ServerName example above would simply become:
1 2 3 4 5 6 7 | EXEC ValidateData IF @@ServerName = dbo.fn_ExampleServer() BEGIN UPDATE Users SET Processed = 1 WHERE UserID = @UserID END |
That’s it! I hope others will find this useful.
Recent Comments