How to know the sql server environment run time

Question

How to know the sql server environment run time

RCRock

Right there with Babe

Points: 718
More actions
October 22, 2019 at 3:30 pm

#3691031

HI,
I have a sql server stored procedure and it uses hard coded value (e.g sql server service account).
The stored procedure for Production environment uses Production service account and stored procedure script for test environment uses test service account.
The issue is if we refresh test sql server database from production environment, the script is replaced by Production stored procedure script and then the production service account throws exception while running in test environment.

Is there any way I can know the environment of sql server e.g Test or Production at run time and then use case if else, case statement to set the service account value accordingly to sql server environment?
Or you have any other suggestion.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246938 More actions · Answer 1

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

@@servername is normally the way to go, but it depends on how you refresh your environment

I worked with a company who cloned all of their EPOS systems in 400 stores, they didn't just "restore database" they did a full Clone of the pc (i'm not calling it a server, it was a Compaq form factor desktop)

all of the pc's were reporting the same @@servername

so we had to use select serverproperty('machinename') - thankfully we got them to include sp_dropserver and sp_addserver into their buildscripts, so that they all lined up

however serverproperty won't show you the instance name if it is a named instance

MVDBA

ScottPletcher SSC Guru Points: 101073 More actions · Answer 3

Isn't the physical machine name different?

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

not used that one before.. Does it include the instance name?... That's the kicker.

MVDBA

ScottPletcher SSC Guru Points: 101073 More actions · Answer 5

No, that is purely the physical name.

On a cluster, SERVERPROPERTY('MachineName') shows the instance name.

For a non-clustered named instance, if that doesn't show the instance name, then use:

SERVERPROPERTY('InstanceName')

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

Not on my server

never has on any server i've ever had

Untitled

MVDBA