Linked Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Linked Server

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 02:41:36

How to add a Linked Server in Designer Mode??
Mean In designer like Enterprises Manager in sql 2000 or some way designer in sql 2005....

M very new to these linked Server n Sql also,,
Guide me,,,,,,
how to add a Linked server,,,

Thanks.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:48:55

use sp_addlinkedserver

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

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 02:55:17

Yes Sir I go through this link n tried this as above in this but .....there r problems in this,,,
i Donn have any idea about this,,,
So guide me in this all about the linked server,,,as i never use that...linked server in past.
n just want to try it out,,
so any other HELPFUL INFORMATION if u can provide about this?

Thanks,,,waiting for reply,,,,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 03:05:40

http://support.microsoft.com/kb/280106

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 03:08:06

Ohkkk,,,,Sir here are some problemm,.,,,,,,

i use these queries,,,,,,, and i got stuck,,,,

first i made a linked server like this,,,,,

EXEC sp_addlinkedserver
@server='Tech1_instance1',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='TECH1\SQLEXPRESS'

Query Completed Succesfully..

then i tried to execute,,,,,

select * from openquery(Tech1_instance1,'select custid from testtable')

then i got these error,,,,,,,,,,,,,,,

OLE DB provider "SQLNCLI" for linked server "Tech1_instance1" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'testtable'.
---------------------------------------------------------------

i know i m wrong in this somw where but i dont know where....
So i need your help in this ,,,,,,,,,,
thanks waiting for reply,,,,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 03:10:35

quote:
Originally posted by ashishashish

Ohkkk,,,,Sir here are some problemm,.,,,,,,

i use these queries,,,,,,, and i got stuck,,,,

first i made a linked server like this,,,,,

EXEC sp_addlinkedserver
@server='Tech1_instance1',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='TECH1\SQLEXPRESS'

Query Completed Succesfully..

then i tried to execute,,,,,

select * from openquery(Tech1_instance1,'select custid from testtable')

then i got these error,,,,,,,,,,,,,,,

OLE DB provider "SQLNCLI" for linked server "Tech1_instance1" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'testtable'.
---------------------------------------------------------------

i know i m wrong in this somw where but i dont know where....
So i need your help in this ,,,,,,,,,,
thanks waiting for reply,,,,

does testtable exist in linked server?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 03:14:03

also try using db schema names also in query

select * from openquery(Tech1_instance1,'select custid from db.schema.testtable')

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 03:14:09

yes I made testtable in this server for refrence,,,,,,,,,,

the tech node node is also present in my office,,,so i made a testtable in this.

any idea gimme this,,,,
if there any kind of authentication is required when we add linked server.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 03:16:46

yup...you need to set up linked server login as per second article

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 03:17:49

quote:
Originally posted by visakh16

also try using db schema names also in query

select * from openquery(Tech1_instance1,'select custid from db.schema.testtable')

thanks For your reply,,,
but it gives this error again,,,,,

Error Is..

OLE DB provider "SQLNCLI" for linked server "Tech1_instance1" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'schema'.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 03:20:23

Ok
can u plzzz gimme me syntax....like if
login Name = Sa
Login Password = ashish

den kindly post the query for setup a linked server using these......
Thanks,,,,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 03:21:12

i gave it for example you need to replace db and schema with your actual db and schema name.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 03:24:23

ohhh sorry ......
that time i dont get u may,,,,,,,,
sorry for that,,
i try this also..

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 03:29:36

Sorry this One also not working,,,,,
ohkk i give u more information which one i just get,,
when i execute this,,,,

EXEC sp_linkedservers
EXEC sp_helpserver

then the results are...

SRV_NAME SRV_PROVIDERNAME SRV_PRODUCT SRV_DATASOURCE SRV_PROVIDERSTRING SRV_LOCATION SRV_CAT
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
CREATIVE8\SQLEXPRESS SQLNCLI SQL Server CREATIVE8\SQLEXPRESS NULL NULL NULL
Tech1_instance1 SQLNCLI TECH1\SQLEXPRESS NULL NULL NULL

(2 row(s) affected)

name network_name status id collation_name connect_timeout query_timeout
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- --------------- -------------
CREATIVE8\SQLEXPRESS CREATIVE8\SQLEXPRESS rpc,rpc out,use remote collation 0 NULL 0 0
Tech1_instance1 NULL data access,use remote collation 1 NULL 0 0

that all want i have with me.

help me in this..
thanks..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 03:29:58

for creating login i think this should be fine

EXEC sp_addlinkedsrvlogin 'Tech1_instance1', 'FALSE', 'sa', 'ashish'

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 03:34:39

quote:
Originally posted by visakh16

for creating login i think this should be fine

EXEC sp_addlinkedsrvlogin 'Tech1_instance1', 'FALSE', 'sa', 'ashish'

this is the error message when i tried to run the query again after make Login query successful..

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'ashish'.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 05:05:05

Hello if some other opinions or suggestions reagarding this,,,,
these r valuable to me?
Plzz do reply,,,and guide me still i cant figure it out.......
thanks,,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 05:12:02

do you have a valid login existing in other server?

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 05:16:44

Ohkk the server whichi want to link TECH1\SQLEXPRESS has the login details like this......

Login Name: sa
Login password: ashish

and the system on which i make that linkes server has thde details like this...

CREATIVE8\SQLEXPRESS
Login Name: sa
Login Password: 1

ohkk,,,
i use both the systems so if u want me to alter the login details or any thing else then i am able to do this at my end...

Thanks For Reply.....
Waiting For your Reply,

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-12 : 00:22:04

Ohk One More Thing.........

there is an Error which i m facing,,,
that under the default settings SQL Server does not allow remote connections

so anyone help me in this that how can i change my settings to that it allows remote connection,,,,

Thanks in Advance,,,,,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 02:24:00

You need to configure server for remote connections from surface area configuration manager from start->programs->microsoft sql server

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources