November 20, 2008 at 1:45 am
have problem with distributes queries . Can I help me?
Problem : I have a distributes database. I want connect client with server to get data .
I used SQLSEVER 2005 connect to SQLSEVER 2005.
Above is my query :
EXEC EZV2.dbo.sp_addlinkedserver @server = N'REMOTE_DISTRIBUTEDDB2', @provider='SQLNCLI', @srvproduct=N'', @datasrc='remote_site_dns_or_ip', @provstr='server=ipaddress;database = DistributedDB;uid = sa;pwd=sa223;'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DISTRIBUTEDDB2', @optname=N'collation compatible', @optvalue=N'false'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DISTRIBUTEDDB2', @optname=N'data access', @optvalue=N'true'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DISTRIBUTEDDB2', @optname=N'rpc', @optvalue=N'false'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DISTRIBUTEDDB2', @optname=N'rpc out', @optvalue=N'false'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DISTRIBUTEDDB2', @optname=N'connect timeout', @optvalue=N'0'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DISTRIBUTEDDB2', @optname=N'collation name', @optvalue=null
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DISTRIBUTEDDB2', @optname=N'query timeout', @optvalue=N'0'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DISTRIBUTEDDB2', @optname=N'use remote collation', @optvalue=N'true'
EXEC EZV2.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'REMOTE_DISTRIBUTEDDB2', @locallogin = NULL , @useself = N'true'
EXEC EZV2.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'REMOTE_DISTRIBUTEDDB2', @useself = N'true',@locallogin = null,@rmtuser ='sa',@rmtpassword = 'sa223'
select * from REMOTE_DISTRIBUTEDDB2.DistributedDB.dbo.Number
create synonym dbo.remote_northwind_categories2 for REMOTE_DISTRIBUTEDDB2.DistributedDB.dbo.Number;
select * from dbo.remote_northwind_categories2
This here is error :
OLE DB provider "SQLNCLI" for linked server "REMOTE_DISTRIBUTEDDB2" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "REMOTE_DISTRIBUTEDDB2" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI" for linked server "REMOTE_DISTRIBUTEDDB2" returned message "Invalid connection string attribute".
Please help me! Thank you verry much !
November 20, 2008 at 1:54 am
Are the network protocols enabled in the target server.
"Keep Trying"
November 20, 2008 at 2:13 am
I enable Names pipes in SqlServer Configuration Manager.But then execute this query -> error.
If connect by Login Form of Sqlserver 2005 -> BDEnger on server ok
But can not excute query :
"select * from dbo.remote_northwind_categories2"
November 20, 2008 at 2:45 am
both server are in same domain or different domain ?
check the remote server settings thro' SQL server Surface area configuration (in that enable Remote Connections...)
November 20, 2008 at 3:21 am
Hi,
both server same domain . I Configure Surface Area for ... is Remote Compute. Then Configure Surface Area for Service and Connection ->
Service Connection -> Local and Remote Connection - > TCP/IP and Named Pipes but can not.
Thank very much for these reply!
Please hepl me!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply