Dynamic From Statement?

[jimmythegeek]

What is the best way to handle referring to dev and production servers without "re-referencing" all your code? I am just starting this project, so I want to do it right. Currently we get a nightly dump of data to another server and database and here is the reference to the method table in it's current location;

Select * from vlib2.heis_snapshot.dbo.method

The "vlib2" server is the dev server, and the production server is "vlib3". So how do I avoid having to change ALL server references from "vlib2", to "vlib3" (there will be hundreds) when it goes to production. I know you can use find and replace, but that is not the right solution. I would like to simply change a "constant" to 'vlib3' and have all the code use that "constant" (I realize TSQL doesn't use global constants in their true sense, but I can use a function to return the string, or whatever).

I tried using a variable, but can't seem to get the right syntax in the FROM portion of the statement.

Thanks in advance for any input.


Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[SQLDenis]

why do you reference the server at all?

Select * from heis_snapshot.dbo.method

done, doesn't matter where you run it

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[FALCONSEYE]

or ...

declare @mySQL varchar(500)
declare @myServer varchar(10)
set @myServer = 'vlib2'

set @mySQL = 'Select * from ' + @myServer +'.heis_snapshot.dbo.method'

print @mySQL
exec @mySQL

http://www.fuzzysiberians.com

 

[jimmythegeek]

Dennis,

I have to reference the server because our nightly dump is on a different server than the server our code is being performed on. I was under the impression that you had to reference the server if they are different, is that incorrect?

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[bborissov]

NO!
Why dynamic SQL when you make your life easier just ignoring the server name part in the table name (as SQDenis suggested)?
Then all will depends of your connection string to wich server you are logged in.
Imagine whole application built using dynamic SQL, Grrrrr!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[jimmythegeek]

Falcon,

I tried your method, and while it printed:

select count(*) from vlib2.heis_snapshot.dbo.method

It did not execute, I got the following error;

Server: Msg 7202, Level 11, State 2, Line 8
Could not find server 'select count(*) from vlib2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Any ideas? As I recall, this type of thing is a pain with TSQL. This is exactly what I would like to do however, so I can use a variable as opposed to hard coding all the references.

Thanks,

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[jimmythegeek]

Falcon, you were close, the exec needed to be in parenthesis, then it worked. I used the following (I use a function called getVar to store all my global variables including, in this case, my source server):

declare @sql varchar(500)
set @sql = 'select count(*) from ' + dbo.getVar('sourceServer') + '.heis_snapshot.dbo.method'
exec(@sql)

Now all I have to do is change the variable in my function, and all the code will use the production server.

Thanks.

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[gmmastros]

>> What is the best way to handle....

What you've come up with is NOT the best way. Every database call will use dynamic sql. Yuck! But, worse than that... what happens when you want to work on dev, but forget to change the function, which is still pointing at production. You could inadvertently cause yourself a lot of problems.

Please don't take this the wrong way. In my opinion, this has got to be the [!]WORST[/!] way to handle this.

Instead, I encourage you to follow Denis's advice. I understand that you want to be able to run code on production, but think about it....

What you really want to do is to isolate the code between the two databases. Queries should be developed using dev. Once it is debugged and working properly, then copy it to production. It's trivially easy to script objects from one database and execute against another. Sure... it adds another step to the process, but it is a healthy step. One that will help you prevent simple mistakes.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jimmythegeek]

I am well aware of the fact that you don't need to refer to the server IF the data you are referencing is on the SAME server.

I said this once, but for some reason it doesn't seem to be registering. I am working on one server (apsdev13), the nightly dump is on a DIFFERENT server (vlib2). It is not simply another database on the same server. So there are 2 dev servers, and there will be 2 prod servers. I have to call the other server to see the data, I cannot simply use this:

heis_snapshot.dbo.method

since the method table is on another server. I agree it is not the best method, but if there is another more efficient way, cool by me, I just don't know the best way. I find it hard to believe others have not run into the same issue.

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[SQLDenis]

hold on now....

>>the nightly dump is on a DIFFERENT server (vlib2).

so how will this affect hundreds of calls? are you importing 100s of tables?
That is what I have (actually it is SSIS packages)

If you need to import 100 tables on another server and you are using linked servers then you have 2 choices
1 dynamic sql
2 replace the names

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jimmythegeek]

Just to clarify;

All the code I am writing on "apsdev13" currently references the "vlib2" server to retrieve data from the nightly dump. When we move "apsdev13" to production, all the code THEN needs to point to "vlib3" which will be the production virutal library server. Two seperate development servers, moving to 2 seperate production servers.

I am trying to avoid having to re-reference all the code to point to "vlib3" when both databases move to production on 2 different servers. I will have many stored procedures and functions that reference tables on the other server.

Hope that makes more sense. I am open to anything.

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[jimmythegeek]

I am only dealing with 20 table or less, 2 of which are large 500K recs and 4 million recs, all the rest are smaller (under 10K records). I am not will be moving some records based on rules, but I won't be "importing" entire tables. There will not be hundreds of references, that was probably an overstatement, probably more like 40-50.

One of the db folks tried to make a linked server, but I still had to refer to 'vlib2', because I got an error when I left it out.

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[SQLDenis]

nope, see my previous suggestion

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

actually there is a way
create a linked server and name it vlib3 on the production box

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

Dynamic SQl is slower and less secure and much harder to debug. It is worth your while to script your changes and then replace rathter than use a process that is a bad practice especially if there are hundreds of sps, etc.

At least read about why dynamic SQl is a poor choice before you decide to do this.

http://www.sommarskog.se/dynamic_sql.html

And if this is nightly feed why not use DTS or SSIS to do it?

"NOTHING is more important in a database than integrity." ESquared

 

[jimmythegeek]

I understand that this is not the best option. I simply didn't know of another.

Also, DTS is not the issue because I won't be importing tables from one server to the other, I simply need to reference data from another server. Some records will get copied, but there is much logic that has to be determined before we find out which records get copied, so the DTS is really not what I am looking for.

I will look into the linked server, and at this point I think I will try to talk the DB folks into letting me put my database on the vlib servers, this will eliminate the whole problem.

Thanks for all your iput.


Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[AlexCuse]

If you can select the rows you need in a query, you can use it as the source for a DTS...

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page