qualify servername

Question

qualify servername

candide

Ten Centuries

Points: 1164
More actions
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

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

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

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.

candide Ten Centuries Points: 1164 More actions · Answer 2

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

candide Ten Centuries Points: 1164 More actions · Answer 3

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

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 4

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.

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 5

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