October 28, 2009 at 8:59 am
What do you mean by different DB Engines? Do you mean a different SQL Server or do you mean Oracle, Sybase, etc...?
Withing SQL Server you would use Linked Server or OPENROWSET/OPENQUERY. From an application you'd use ODBC/OLE DB/ADO.NET.
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
October 28, 2009 at 9:11 am
Jack Corbett (10/28/2009)
What do you mean by different DB Engines? Do you mean a different SQL Server or do you mean Oracle, Sybase, etc...?
server 1 : sql server A
server 2 : sql server B
How can i connect sql server A with B.
Withing SQL Server you would use Linked Server or OPENROWSET/OPENQUERY. From an application you'd use ODBC/OLE DB/ADO.NET.
ok. So i have to link them.
thanks.
i´ll try it out.
October 29, 2009 at 10:40 am
i´m having the following error;
"Mens. 233, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [233]."
any ideas ??
thanks in advance.
October 29, 2009 at 10:54 am
I'm not a fan of named pipes (anymore).. Using the config manager you want to order TCP above named pipes.. See attached screen print..
CEWII
October 29, 2009 at 11:00 am
i´ve my connfiguration like that and my servers are linked...
this is the full error.
"The OLE DB provider "SQLNCLI" for linked server "support" returned message "You have completed the waiting time logon.
The OLE DB provider "SQLNCLI" for linked server "support" returned message "Failed to connect to the server. When connecting to SQL Server 2005, the default settings SQL Server does not allow remote connections.".
Mens. 233, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [233]."
im using servername.DB.schema.table to dml sentences.
any other idea ??
October 29, 2009 at 11:10 am
Enter an alias to the server, specify TCP/IP and first try just using its name, if that doesn't work specify its ip address. Either way specify its port.. I think this is a name resolution issue. It often is.
The fact that it is trying to use named pipes means it couldn't get it with TCP/IP. Can you ping the name of the server from the server you are trying to connect from? If not then it is absolutely a name resolution issue. Does that server have the same DNS settings as you do?
CWEII
October 29, 2009 at 12:19 pm
Elliott W (10/29/2009)
Enter an alias to the server, specify TCP/IP and first try just using its name, if that doesn't work specify its ip address. Either way specify its port.. I think this is a name resolution issue. It often is.The fact that it is trying to use named pipes means it couldn't get it with TCP/IP. Can you ping the name of the server from the server you are trying to connect from? If not then it is absolutely a name resolution issue. Does that server have the same DNS settings as you do?
CWEII
i´ve done everything you suggested.
the names are resolving fine on ping.
In sql surface configuration i cheked de only tcp radio button and i got the following error now.
"The OLE DB provider "SQLNCLI" for linked server "support" returned message "You have completed the waiting time logon.
The OLE DB provider "SQLNCLI" for linked server "support" returned message "Failed to connect to the server. When connecting to SQL Server 2005, the default settings SQL Server does not allow remote connections.".
Mens. 10060, Level 16, State 1, Line 0
TCP Provider: An error occurred during the connection attempt because the connected party did not properly respond after a period of time, or failed to established connection because connected host has failed to respond."
this is the trigger im using.;
create trigger insertacliente on softland.cwtauxi
after insert as
begin
set nocount on;
insert into soporte.labmanager.dbo.sclientes(SCodAux, SNomAux, SNoFAux, SRutAux, SActAux, SGirAux, SComAux, SCiuAux, SPaiAux, SProvAux, SDirAux, SDirNum, SFonAux1, SFonAux2,
SFonAux3, SFaxAux1, SFaxAux2, SClaCli, SClaPro, SClaEmp, SClaSoc, SClaDis, SClaOtr, SDiaPlazo, SBloqueado, SEMail, SCasilla, SWebSite,
SRegion, STipoSaludo, SDirDpto, SDirOtro, SCodPostal, SCodAreaFon, SAnexoFon, SCodAreaFax, SFechaNacim, SUsername, SPassword,
SPalabraSecreta, SPreguntaSecreta, SClienteDesde, STipoUsuario, SeMailDTE, SesReceptorDTE)
select inserted.CodAux,inserted.NomAux,inserted.NoFAux,inserted.RutAux,inserted.ActAux,inserted.GirAux,inserted.ComAux,inserted.CiuAux,inserted.PaiAux,inserted.ProvAux,inserted.DirAux,inserted.DirNum,inserted.FonAux1,inserted.FonAux2,
inserted.FonAux3, inserted.FaxAux1, inserted.FaxAux2, inserted.ClaCli, inserted.ClaPro, inserted.ClaEmp, inserted.ClaSoc, inserted.ClaDis, inserted.ClaOtr, inserted.DiaPlazo, inserted.Bloqueado, inserted.EMail, inserted.Casilla, inserted.WebSite,
inserted.Region, inserted.TipoSaludo, inserted.DirDpto, inserted.DirOtro, inserted.CodPostal, inserted.CodAreaFon, inserted.AnexoFon, inserted.CodAreaFax, inserted.FechaNacim, inserted.Username, inserted.Password,
inserted.PalabraSecreta, inserted.PreguntaSecreta, inserted.ClienteDesde, inserted.TipoUsuario, inserted.eMailDTE, inserted.esReceptorDTE
from inserted;
end
thanks for your help.
October 29, 2009 at 1:13 pm
Ok.. Can you go to the machine that you are trying to connect from and see if you can connect to the other server using SSMS. Also, is the server you are trying to connect to a named instance?
CEWII
October 29, 2009 at 1:20 pm
Elliott W (10/29/2009)
Ok.. Can you go to the machine that you are trying to connect from and see if you can connect to the other server using SSMS.
i can connect
[Also, is the server you are trying to connect to a named instance?
CEWII
yes.
The instance is called soporte.
October 29, 2009 at 2:22 pm
When you defined your linked server did you include the instance name? also how did you define your linked server? did you use the SQL Server option, in that case your linked server will have the same name as the server including instance name and then it must be refered to with [servername\instancename].database.schema.object
If you defined it as an other datasource you might be having trouble there..
CEWII
October 30, 2009 at 7:11 am
Elliott W (10/29/2009)
When you defined your linked server did you include the instance name? also how did you define your linked server? did you use the SQL Server option, in that case your linked server will have the same name as the server including instance name and then it must be refered to with [servername\instancename].database.schema.objectIf you defined it as an other datasource you might be having trouble there..
CEWII
i used sp_addlinkedserver to add soporte.
now i linked [servername\instancename].database.schema.object
and i got the following error.
The OLE DB provider "SQLNCLI" for linked server "support \ Support" returned message "You have completed the waiting time logon.
The OLE DB provider "SQLNCLI" for linked server "support \ Support" returned message "Failed to connect to the server. When connecting to SQL Server 2005, the default settings SQL Server does not allow remote connections.".
Mens. 65535, Level 16, State 1, Line 0
SQL Network Interfaces: Error Locating Server / Instance Specified [xFFFFFFFF].
thanks.
October 30, 2009 at 8:14 am
Let me see if I can go down what we have tried.
1. It is a named instance
2. Using SSMS you can connect to the destination machine from the SQL machine with the linked server.
3. You can connect to the destination server from other workstations.
4. For client connections only TCP/IP is enabled.
5. You can ping the destination machine from the SQL machine with the linked server.
Provided all of those are right, what this tells me is that base conenctivity is not a problem.
From you last post you were originally only using the servername and now you have a linked server defined with servername\instance? Have you setup anything for linked server security mapping?
If not you might look at sp_addlinkedsrvlogin.
Until you have it all working you might just use the GUI and then trace a session as you recreate it to find out all the commands for scripting later.
Go to the GUI and send screen prints of the second and third tabs, they shouldn't contain any data that wouldn't be safe for the internet. You may want to blur our the server name using paint. I suspect they contain all default values..
As a side note, you didn't disable shared memory as client protocol? If you did re-enable it, that will hurt your local performance from things like Agent..
CEWII
October 30, 2009 at 8:45 am
I realise I've joined this thread a bit late, so may have missed something when skimming through the other replies, but...
The OLE DB provider "SQLNCLI" for linked server "support \ Support" returned message "Failed to connect to the server. When connecting to SQL Server 2005, the default settings SQL Server does not allow remote connections.".
Is the other server is configured to allow remote connections?
October 30, 2009 at 9:02 am
Ian Scarlett (10/30/2009)
I realise I've joined this thread a bit late, so may have missed something when skimming through the other replies, but...The OLE DB provider "SQLNCLI" for linked server "support \ Support" returned message "Failed to connect to the server. When connecting to SQL Server 2005, the default settings SQL Server does not allow remote connections.".
Is the other server is configured to allow remote connections?
If it weren't he wouldn't have been able to connect using SSMS remotely..
CEWII
October 30, 2009 at 9:12 am
Elliott W (10/30/2009)
Let me see if I can go down what we have tried.1. It is a named instance
2. Using SSMS you can connect to the destination machine from the SQL machine with the linked server.
3. You can connect to the destination server from other workstations.
4. For client connections only TCP/IP is enabled.
5. You can ping the destination machine from the SQL machine with the linked server.
Provided all of those are right, what this tells me is that base conenctivity is not a problem.
that´s a good recap.
From you last post you were originally only using the servername and now you have a linked server defined with servername\instance?
yes.
Have you setup anything for linked server security mapping?
no.
If not you might look at sp_addlinkedsrvlogin.
im checking it...
Until you have it all working you might just use the GUI and then trace a session as you recreate it to find out all the commands for scripting later.
Go to the GUI and send screen prints of the second and third tabs, they shouldn't contain any data that wouldn't be safe for the internet. You may want to blur our the server name using paint. I suspect they contain all default values..
wich GUI are we talking about ??
As a side note, you didn't disable shared memory as client protocol? If you did re-enable it, that will hurt your local performance from things like Agent..
CEWII
i didn´t do that...
thanks for all your help, im learning a lot. 😀
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply