Post reply

TO manage mutiple connections

  • May 21, 2012 at 8:20 am

    #258507

    Is there a way, I can use a Global TAG (connection STRING) to refer to DB objects?

    For example:

    SERVER1.DB1.DBO.Table1

    SERVER1.DB1.DBO.Table2

    SERVER1.DB1.DBO.Table3

    SERVER2.DB2.DBO.Table1

    SERVER2.DB2.DBO.Table2

    SERVER2.DB2.DBO.Table3

    Let us say

    CS1 = SERVER1.DB1.DBO

    CS2 = SERVER2.DB2.DBO

    My query is

    select * from CS1.Table1

    Select * from CS2.Table1

    Can I do query I this, the problem I have is, some times the Connection information changes, so that I can maintain them in one place.

    Thanks in advance,

  • May 21, 2012 at 8:30 am

    #1490453

    personally, I disklike them, but synonyms... http://msdn.microsoft.com/en-us/library/ms177544.aspx

  • May 21, 2012 at 9:20 am

    #1490485

    Thanks, this is what I was looking for. But one issue I found. It needs all four parts. Is there a way I can create without using object, so that I can use one SYNONYM for multiple objects.

    for example CREATE SYNONYM SER1 for SERVER1.DB1.DBO

    then use as below:

    select top 10* from SER1.Table1

    select top 10* from SER1.Table2

    select top 10* from SER1.Table3

  • May 21, 2012 at 9:43 am

    #1490500

    No. you're looking for some type of delayed resolution or a scalar replacement at run time, but that's not allowed.

    You can't do a

    declare @C varchar

    select @C = 'Sales'

    select * from @C

    The target of your FROM clause needs to be resolved. A synonym allows for that, but only with the complete naming.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 21, 2012 at 11:07 pm

    #1490817

    Yes, I agree with Steve....you need to use Dynamic SQL for this as follows:

    Declare @name1 varchar(max) = 'abhi.Test.dbo', @name2 varchar(50) =

    'Test', @name3 varchar(max), @sql varchar(max)

    Select @name3 = @name1+'.'+@name2

    Select @sql = 'Select * From '+@name3+';'

    Execute (@sql)

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • May 23, 2012 at 5:48 am

    #1491684

    adb2303 (5/21/2012)

    personally, I disklike them, but synonyms... http://msdn.microsoft.com/en-us/library/ms177544.aspx%5B/quote%5D

    Out of curiosity, why do you dislike synonyms?


    The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

  • May 23, 2012 at 6:19 am

    #1491696

    SYNONYM is perfectly suit my need, and already implemented in my project. Thanks for all your help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply