Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 27th, 2006, 10:21 AM
Registered User
 
Join Date: Apr 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Variables in the four naming conventions

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.
 
Old April 28th, 2006, 02:00 PM
Authorized User
 
Join Date: Oct 2005
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old April 28th, 2006, 03:05 PM
Authorized User
 
Join Date: Mar 2006
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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..
 
Old May 1st, 2006, 01:06 PM
Registered User
 
Join Date: Apr 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
recordsets- displaying and conventions jerryjet Dreamweaver (all versions) 6 February 23rd, 2008 05:43 AM
Namespace Naming Conventions For Layerd Applicatio Muhammad Zeeshan General .NET 2 September 1st, 2007 02:03 PM
Naming a new table SKE Classic ASP Databases 2 March 23rd, 2005 03:25 PM
Code Writing Conventions richard.york PHP FAQs 0 April 5th, 2004 08:23 PM
Naming conventions jara VS.NET 2002/2003 3 June 22nd, 2003 04:21 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.