create Linked Server

Question

create Linked Server

ekonagu

SSChasing Mays

Points: 657
More actions
July 1, 2009 at 11:30 pm

#135858

Hai to All
i need some help in creating linkedserver.while creating the linkedserver the name should be same as the server name rt?? like \\server1
and
i have created like this thru the management studio... the linked server is created successfully but when i am testing the error coming.. time out expires etc etc
so any configuration needed in the server to create linkedserver??
what is registering spn ? is it necessary
pls help

Viewing 10 posts - 1 through 9 (of 9 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

ekonagu (7/1/2009)
Hai to All
i need some help in creating linkedserver.while creating the linkedserver the name should be same as the server name rt?? like \\server1
and
i have created like this thru the management studio... the linked server is created successfully but when i am testing the error coming.. time out expires etc etc
so any configuration needed in the server to create linkedserver??
what is registering spn ? is it necessary
pls help

\\server1 is when u share a folder, not linked server.

you create linked server using sp_addlinkedserver

and u refer to objects in your linked server using four part notation

select * from server2.db2.dbo.Mytable

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 2

to increase the timeout period, you can run

sp_configure 'remote login timeout', 30

go

reconfigure with override

go

here 30 secs will be timeout duration.

Pradeep Singh

ekonagu SSChasing Mays Points: 657 More actions · Answer 3

actually if we are crating a linked server eith script

EXEC sp_addlinkedserver 'Server1', N'SQL Server'

how u come toknow to which server in the network is geting connected ?we need to give the servername as linkedserver rt ???

ps. One Orange Chip Points: 29422 More actions · Answer 4

ekonagu (7/2/2009)
actually if we are crating a linked server eith script
EXEC sp_addlinkedserver 'Server1', N'SQL Server'
how u come toknow to which server in the network is geting connected ?we need to give the servername as linkedserver rt ???

In the example u gave, Server1 is the linked server. From the server where u created the linked server, you can write queries as

select * from server1.db1.dbo.myTable

Pradeep Singh

- Win. SSCertifiable Points: 6790 More actions · Answer 5

Exec sp_addlinkedserver

@server='SERVERNAME/ IP', --//Logical name given to the linked server.

@srvproduct='', --//optional . Just for description

@provider='SQLServer', --//OLEDB Provider name, check BOL for more providers

@datasrc='SERVERNAME', --//actual remote server name

@catalog='DBNAME', --//default database for this linked server

@provstr = 'DRIVER={SQL Server};SERVER=MyServer;UID=User name;WD=pwd'

GO

Cheers,
- Win.

" Have a great day "

- Win. SSCertifiable Points: 6790 More actions · Answer 6

Exec sp_addlinkedsrvlogin

@useself='false', --//false means we are going to use remote login/password

--//true means use local login/password to connect to remote machine (If local login/password does not match on remote machine then will fail)

@rmtsrvname='LinkedServerName', --//Exising Linked server name

@localLogin='null'

@rmtuser='Login' , --//remote login

@rmtpassword='pwd' --/

--------------------------------------------------------------------------------------------

--//Enable/disable some options

Exec sp_serveroption 'DBName', 'data access', 'true' --Enables and disables a linked server for distributed query access

Exec sp_serveroption 'DBName', 'rpc', 'true' --//Enables RPC from the given server.

Exec sp_serveroption 'DBName', 'rpc out', 'true' --//Enables RPC to the given server (required to call SP using Linked Server).

Exec sp_serveroption 'DBName', 'collation compatible', 'true'

Cheers,
- Win.

" Have a great day "

ekonagu SSChasing Mays Points: 657 More actions · Answer 7

when i did this the linked server is created successfully,but when i test the connection

error is cmg as

test connection to linked server failed

the ole db provider "sql server" has not been registred

pls help

ekonagu SSChasing Mays Points: 657 More actions · Answer 8

THANKS ITS TESTED SUCCESSFULLY.THE PROVIDER NEEDS TO BE REGISETRED THANKS GUYS

THANKS A LOT:-)

- Win. SSCertifiable Points: 6790 More actions · Answer 9

Sounds Good........ But dont write or respond in CAPS as its referred as shouting on others....

Any how have good times......

Rgds,

CH&HU@Win..

Cheers,
- Win.

" Have a great day "