Post reply

qualify servername

  • April 16, 2010 at 8:50 am

    #220611

    Hello,

    I can't get it running and it should be simple for you. In SS2008-Object-Explorer I connected to a second SQL-Server (2005). I can see all databases and tables I need.

    How to read data from this server?

    SELECT * FROM [PC9-01].[Northwind].[dbo].[Orders]

    says something like "wrong object name":w00t:

    thanks

    --
    candide
    ________Panta rhei

  • April 16, 2010 at 9:21 am

    #1152931

    Connecting to two different servers in management studio does not create a link between them, your client is simply establishing a connection to each independently.

    You can only use a 4 part naming convention to connect to another server if it is added as a linked server. See below:

    http://msdn.microsoft.com/en-us/library/ms188279.aspx

    Also, think about what you want to acheive. Do you just need to copy data back and forth intermittently? What kind of performance do you need? Does it fit with your security policy?

    Using SSIS to transfer data back and forth may be a better solution - you can even get the import/export wizard to do 95% of the work for you.

  • April 16, 2010 at 11:21 am

    #1153037

    thanks HowardW for your answer

    I just need readonly access to one table which will grow silently to some hundred rows.

    SSIS seems not to be the way because table updates should be seen immediately (or would you recommend an SSIS-package fired every minute?).

    I tested with sp_addlinkedserver and sp_addlinkedsrvlogin and can read the data:-) great.

    For sp_addlinkedsrvlogin I used the "sa" user, which is not ok for production.

    Whats the easiest way to get read access? Should the linked Server database create a separate user account with ReadOnly or what?

    --
    candide
    ________Panta rhei

  • April 16, 2010 at 11:44 am

    #1153043

    well, I read "Configuring Linked Servers for Delegation"

    and can't understand:

    I ran

    EXEC sp_addlinkedserver 'SQLSERVER2', N'SQL Server'

    EXEC sp_addlinkedsrvlogin 'SQLSERVER2', 'true'

    Only when I have sp_addlinkedsrvlogin run with user "sa" it works. 😎

    But sa is no option at all?!

    --
    candide
    ________Panta rhei

  • April 16, 2010 at 12:33 pm

    #1153063

    u can build the linked server in the managment studio interface by going to server objects then linked servers. Add new linked server and there is a security tab for logins.

  • April 16, 2010 at 12:35 pm

    #1153064

    i would also go into sql server config manager and create an alias for the box however u dont have to.

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

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