Hi

I am running SQL Server 2000 and have two different databases with different names on two different machines.

The servers (machines) are linked and I want to use a single set of stored procedures on both databases.

I want to know if and how I can use variables in the four naming conventions in a stored procedure. This way I do not have to write an if statement and rewrite the code for each database.

Example:

Select * from SERVER_A.DATABASE_1.dbo.CENSUS

Eventually, I want SERVER_A and DATABASE_1 to become the variables that will change depending on which database that is being requested.


Any help would be appreciated.

You can do that but that you have to use dynamic SQL.

SQL Server Helper
For question on date formats: http://www.sql-server-helper.com/tips/date-formats.aspx

Hi FJInfante!

Although, I do not get your actual requirement. But,
In this case, I would suggest you to create two stored procedures -
[proc1] Created at server1
[proc2] Created on other servers that call proc1 located at server1.


Proc1
------------------------------------------------------
create proc proc1
@server varchar(128) , @database varchar(128),
@table varchar(128)
as

declare @stm nvarchar(2000)

set @stm = 'select * from '+ @server + '.' + @database + '.dbo.' + @table

Execute sp_executesql @stm

go
-------------------------------------------------------

proc2
-------------------------------------------------------
create proc proc2
as

declare @dbname varchar(128)
set @dbname = db_name()

execute server1.master.dbo.proc1 @@servername , @dbname , 'table1'

-------------------------------------------------------

So, all the servers have to create proc2 that will just call proc1 located at server1.
@@servername would produce the server name that calls and db_name() would produce the current database.

Explain server and database related details if you do not understand this example or may be, I do not understand your problem.

Reply soon..

Thank for the help. I going to try a dynamic SQL in a SP with an EXEC.