Production vs. Test Environment, Linked Servers = Lots of Dynamic SQL!

We have a SQL 2000 Database in both a production and test environment.
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegroups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegroups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>