sql server programming:

I am writing Stored Procedures on our SQL 2005 server that will link with
data from an external SQL 2000 server. I have the linked server set up
properly, and I have the Stored Procedures working properly. My problem is
that to get this to work I am hardcoding the server.database names. I need to
know how to dynamically specify the server.database so that when I go live I
don't have to recompile all of my stored procedures with the production
server and database name. Does anyone have any idea how to do this?

EXAMPLE:

SELECT field1, field2 FROM mytable LEFT OUTER JOIN
otherserver.otherdatabase.dbo.othertable

OBJECTIVE:

Replace 'otherserver.otherdatabase.dbo.othertable' with some other process
(dbo.fnGetTable('dbo.othertable')????)

[quoted text, click to view]


You should be able to use Dynamic SQL w/a parameterized stored
procedure like this:

CREATE PROC SampleProc
@DBName VARCHAR(50),
@TableName VARCHAR(50)
AS

DECLARE @SQLStmt VARCHAR(MAX),
@Server VARCHAR(50)

SET @Server = @@ServerName

SET @SQLStmt = 'SELECT field1, field2 FROM mytable LEFT OUTER JOIN ['
+ CAST(@Server AS VARCHAR(50)) + '].' + CAST(@DBName AS VARCHAR(50)) +
'.dbo.' + CAST(@TableName AS VARCHAR(50))

EXEC (@SQLStmt)

Hope this helps.

Regards,

Enrique Martinez
Sr. SQL Server Developer

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





[quoted text, click to view]

[quoted text, click to view]

When you define a linked server, you can use a logical name that is
different then the server's real name. You can define linked servers
in all your environments (development, pre production, production,
staging etc') that have the same logical name but in each environment
it points to a different server. This way you don't have to change
the linked server's name each time you deploy your code into a
different environment.

As for databases' name there are 4 options that I can see. The
first option is to use the same database name in all environments.
This is my favorite option and of course this is what I do. If I use
the same name in all environments, I don't have a problem pointing to
the correct database each time that I move the code to a different
environment. Also I don't see any problem with the fact that I use
the same name through all the environments.

The second choice that you have is to modify the code each time you
deploy it into a different environment. This has the obvious down
side that you change code after you finished testing it and that you
might forget to change it one day it won't work in production although
it worked on pre production.

The third choice is to use dynamic SQL. Uri Dimant already gave you
a link that explains what is the down side for this solution.

The forth choice is not the specify the database's name at all and
relay on user's default database. I don't like this choice because if
the user's default database will be modified one day, the code will
suddenly stop working. Also if you have 2 procedures that work with
the same linked server but each one with a different database, then at
least one of the procedures will not work correctly.

To sum it all, I think that the best solution is to use the same
names for databases and the same logical names for linked servers
through all environments. At leas from my experience this worked best
for me.

Adi

Jared (Jared@discussions.microsoft.com) writes:
[quoted text, click to view]

There is a new feature for this in SQL 2005: synonyms. It's as easy
as this:

CREATE SYNONYM shorttablename FOR SERVER.db.schema.tbl

Then you use the synonym as if it was a table name.

All you need is a stored procecure that sets up the synonyms, using
dynamic SQL and taking server name and database name as input parameters.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at