November 20, 2008 at 3:12 am
Hi all,
I have a distributes database include : Database on Server A , Database on Server B , Database on Server C and an application.
How use distributes queries of SQL Server 2005 to select data from Database on Server A then insert into Database on Server B or C .
I used store sp_addlinkedsrv and sp_addlinkedloginsrv of SQL Server 2005 but 'SQLNCLI' and 'Named Piped Provider' can not open connection to SQL Server 2005.
Please help me!
Thank you very much !
November 20, 2008 at 7:40 am
Can you provide some more information, like:
Are all the SQL Servers at the same service pack level?
What are the parameters you used when executing the linked server stored procedures?
Can you successfully create linked servers between any of the servers?
Are remote connections allowed on all the servers? TCP/IP and Named Pipes enabled?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 20, 2008 at 6:14 pm
Hi Jack Corbett,Thank very much for your reply!
-TCP/IP and Named Piped is enable .
-both Server and Client same domain.
- This is store procedure :
EXEC EZV2.dbo.sp_addlinkedserver @server = N'REMOTE_DB', @provider='SQLNCLI', @srvproduct=N'', @datasrc='remote_site_dns_or_ip', @provstr='server=ipaddress;database = DB;uid = sa;pwd=sa;'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DB', @optname=N'collation compatible', @optvalue=N'false'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DB', @optname=N'data access', @optvalue=N'true'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DB', @optname=N'rpc', @optvalue=N'false'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DB', @optname=N'rpc out', @optvalue=N'false'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DB', @optname=N'connect timeout', @optvalue=N'0'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DB', @optname=N'collation name', @optvalue=null
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DB', @optname=N'query timeout', @optvalue=N'0'
EXEC EZV2.dbo.sp_serveroption @server=N'REMOTE_DB', @optname=N'use remote collation', @optvalue=N'true'
EXEC EZV2.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'REMOTE_DB', @locallogin = NULL , @useself = N'true'
EXEC EZV2.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'REMOTE_DB', @useself = N'true',@locallogin = null,@rmtuser ='sa',@rmtpassword = 'sa'
I want execute query :
select * from REMOTE_DB.DB.dbo.Number
I executed :
create synonym dbo.remote_number for REMOTE_DB.DB.dbo.Number;
Then :
select * from dbo.remote_number
There here Error Message :
This here is error :
OLE DB provider "SQLNCLI" for linked server "REMOTE_DB" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "REMOTE_DB" 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_DB" returned message "Invalid connection string attribute".
I performed :
+Configuration Tools - > SQL Server Surface Area Configuration -> Remote Computer .
+Configuration Tools - > SQl Config Manager -> Enable TCP/IP and Named Pipes.
But error!
Please help me!
November 21, 2008 at 2:19 am
Hi
set rpc and rpc out options to 'true'
then you can access the data through remote server queries
regards,
tharves
November 23, 2008 at 7:27 pm
Hi,
Thank you very much!
Regards!
November 23, 2008 at 7:33 pm
Hi all,
How I can update linkedserver?
Example :
Begin Set @provstr = 'Server = Server1;database =DB;uid =sa;pwd=sa'
Now update : linkedserver
Set @provstr = 'Server = Server2;database =DBNEW;uid =sa;pwd=sa'
Thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply