Servername as a Variable in View? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Servername as a Variable in View?

Author

Topic

sqlcat
Starting Member

4 Posts

Posted - 2012-03-12 : 17:04:07

I am currently developing views in a test environment using copies of database elements from several databases.

The IT folks here have passed the request "change all Views to an easily configurable Servername rather than hardcode in many places".
The goal is to enable IT to move the live data freely if necessary and then only need to enter the new Servername in 1 location and have all of the views automatically updated.

I'd like to have 1 local table that I use as a "Servername" pointer and then pass into the View, something like:

SET SERVERNAME=select dbo.pointer.sname

SELECT SERVERNAME.database.table.field

Is this possible?
Are there other methods I should explore?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-12 : 17:09:32

Why do the views need to have servername in them in the first place? Your post is not clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlcat
Starting Member

4 Posts

Posted - 2012-03-12 : 17:20:54

Why do the views need to have servername in them in the first place?

Multiple servers exist.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-12 : 17:41:49

I still don't understand why you need it. Please help us help you. Provide more information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 18:18:12

so are these servers linked servers which are already set up?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-12 : 19:42:47

quote:
The IT folks here have passed the request "change all Views to an easily configurable Servername rather than hardcode in many places".

Can you instead ask the IT to set up a CNAME for your server so you can refer to the CNAME in your queries and views? They can then move around the database to any server their heart desires as long as the CNAME points to the correct server. I don't know if it is possible to set up linked servers using CNAMEs, so this may be a non-starter. Regardless, from your perspective, a good offense is the best defense, so at least ask them to consider it.

sqlcat
Starting Member

4 Posts

Posted - 2012-03-13 : 14:01:32

Valid questions that I will review with our IT group.
Although not the exact solution I was initially looking for they are steering me to proper implementation - Thanks!

Applying the same question to the DATABASE name ... can this be done?

SET DBNAME1 = select dbo.table.sname1

SELECT DBNAME1.table.field

I don't know the logic behind wanting to change database or server names, but the goal is to be able to change the entire views using a single entry in a database rather than having to re-target every table reference in each view.

Or perhaps a better question, In general, can variables be used to populate the names of DATABASES, TABLES, FIELDS within and SQL query so they can be easily changed via the use of a variable in another table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 14:56:54

quote:
Originally posted by sqlcat

Valid questions that I will review with our IT group.
Although not the exact solution I was initially looking for they are steering me to proper implementation - Thanks!

Applying the same question to the DATABASE name ... can this be done?

SET DBNAME1 = select dbo.table.sname1

SELECT DBNAME1.table.field

I don't know the logic behind wanting to change database or server names, but the goal is to be able to change the entire views using a single entry in a database rather than having to re-target every table reference in each view.

Or perhaps a better question, In general, can variables be used to populate the names of DATABASES, TABLES, FIELDS within and SQL query so they can be easily changed via the use of a variable in another table?

you can do like that

but you can use variable for dbname and use it in query to set correct db context but involves dynamic sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlcat
Starting Member

4 Posts

Posted - 2012-03-16 : 16:15:48

visakh16,

Can you elaborate with some sample code?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 16:36:03

quote:
Originally posted by sqlcat

visakh16,

Can you elaborate with some sample code?

Thanks


DECLARE @DB_Name varchar(20)

SET @DB_Name = 'some value'

EXEC('SELECT * FROM ' + @DB_Name + '.dbo.tablename')

the db should be existing in same server wher this code is run

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 16:44:56

quote:
Originally posted by sqlcat

I am currently developing views in a test environment using copies of database elements from several databases.

The IT folks here have passed the request "change all Views to an easily configurable Servername rather than hardcode in many places".
The goal is to enable IT to move the live data freely if necessary and then only need to enter the new Servername in 1 location and have all of the views automatically updated.

Get one of these bozos on this conference call

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources