-1

Here's the scoop. I'm setting up deployments of databases using Octopus Deploy, which is all fine and dandy. The only thing I'm trying to figure out is if there is a way to target specific database instances and servers based on which deployment is running(i.e. Dev, Test, Production). Since my data is a little different between the 3, I need to run slightly different scripts for each environment. There are no schema changes, just data differences. I can add a post deployment script to my VS project for the OD deployments of the database, but I need to know how to target different instances and servers. I tried doing the whole select @@servername but this just gives me a syntax error. Anyone able to explain to me how to run certain bits of SQL based on what environment is being deployed?

Paul Abbott
  • 7,065
  • 3
  • 27
  • 45
Skrubb
  • 531
  • 1
  • 6
  • 25
  • what error do you get with select @@servername? – SqlACID Dec 21 '15 at 18:35
  • the way things are set up right now i have --> server --> instance --> database... since the database name is the same up the stack, i need to be able to target server\instance, but it gives me syntax error when doing:: select @@server\instance; – Skrubb Dec 21 '15 at 18:37
  • take off the "\instance" in your select; select @@servername returns SERVER\INSTANCE for the current connection. – SqlACID Dec 21 '15 at 18:57

1 Answers1

0

First things first. Regardless Octupus Deploy, Snow White, or how sunny is outside; the SQL Server you connect to is defined in the connection string before you make the connection. It cannot be changed within an SQL statement. You need to close the connection, then establish a new connection to a different server.

In other words: No, you can't. Deploy once in PROD, then deploy again in TEST.

About selecting the server name; you are doing it wrong. The statement is literally and should not be modified... SELECT @@SERVERNAME. It is a read only global variable that returns the name of the current server you are connected to.

You could create a linked server and run scripts that way in a single connection... which in your case would make a big mess and i certainly don't recommend for a deployment.

Think of it this way. You cannot talk to your sister in Miami, when you called your brothers' number in California. Hang up first, then call again, this time using your sister's number.

Ricardo C
  • 2,205
  • 20
  • 24