SQL Server 2005 linking another server in management studio express

If you have two servers A and B. and you want to link Server B in managment studio express within Server A so that you can use queries to get data from a database in the Server B while remaining in Server A.

or if you want to create join queries from data in both the servers. You can use the link server facility of SQL Server.

here is the query you can use to link a new server

EXEC sp_addlinkedserver
@server='linkedsql', --this will be the server name that will be used in queries later onwards
@srvproduct='',
@provider='SQLNCLI', -- this is the product information
@datasrc='ip\servername' -- this is the server information

when you have linked the server and you want to query the server you can use query like

select * from linkedsql.dbname.dbo.tablename

but it will give you this error

Msg 18452, Level 14, State 1, Line 1

Login failed for user ''. The user is not associated with a trusted SQL Server connection

you have to provide the user credentials and the password for the sql server

You can use the following query to create the password and userid for the linked server.

EXEC sp_addlinkedsrvlogin 'linkedsql', 'false', NULL, 'userid', 'password'

You can also use the object explorer->linked server->properties->security-> be made using this security context. then enter the userid and password and click ok