Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    1

    Unanswered: dynamically change db servers

    hi all,

    I am confronted with multiple database servers with database names DB-1, DB-2, etc., table names are all the same.
    I'm passing the DBServerName and DBname as variables in my stored procedure.
    Is there any better ways to choose different server names other than using EXEC as shown in the sp below?


    Any comments are greatly appreciated


    CREATE PROCEDURE sp_MyTest
    @SERVERNAME varchar(50),
    @DBNAME varchar(50),
    @CUST_ID varchar(10),
    AS
    Begin
    DECLARE @strsql VARCHAR(800)

    SET @strsql='Select Cust_ID From ['+@SERVERNAME+'].['+@DBNAME+'].dbo.[CUSTOM] WHERE CUST_ID='''+@CUST_ID+'''
    EXEC (@strsql)
    End

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    thats pretty much like i would go with
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Dec 2003
    Location
    Bangalore
    Posts
    2

    Suggestion

    Originally posted by Enigma
    thats pretty much like i would go with
    Hi,
    One small suggestion if u have different servers , configure for remote server or linked server options u can use any object by giveing the servername.databasename.username.objectname.

    If in same database u can use databasename.username.objectname

    irrespective of procedure where ever it is.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Hi,
    One small suggestion if u have different servers , configure for remote server or linked server options u can use any object by giveing the servername.databasename.username.objectname.

    If in same database u can use databasename.username.objectname

    irrespective of procedure where ever it is.
    i believe thats what he is doing when he says

    SET @strsql='Select Cust_ID From ['+@SERVERNAME+'].['+@DBNAME+'].dbo.[CUSTOM] WHERE CUST_ID='''+@CUST_ID+'''
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •