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.
| Author |
Topic |
|
Scottie Doesnt Know
Starting Member
2 Posts |
 Posted - 2010-10-15 : 10:44:52
|
| Hello everyone,I'm having a lot of problems with linked servers, in both SQLMS 2005 and 2008.I can create a linked server using ODBC DSNs easily enough, but any time that I try to run a query, test the connection, or change the linked server details, SQLMS hangs. The only 'fix' is to kill the process, then reopen it. This happens even if I explicitly set a timeout.The lack of error message has made it hard for me to troubleshoot. Additionally, I have tried to create linked servers to a number of data sources (Access DBs, Excel, Another SQL server (2005), .gdb database, etc.) What this tells me is that it is not a ole db driver issue. Has anyone else had this same issue, or can someone point my in the direction of a solution? I would appreciate any advice:System specs:OS: VistaSQL SERVER INSTANCES: Both 2005 and 2008 express editions. |
|
|
Scottie Doesnt Know
Starting Member
2 Posts |
Posted - 2010-10-15 : 11:26:21
|
| Some additional details:- The linked servers were all created without any reported error.- Any form of interaction with the linked server causes the management studio to hang. This includes: - Trying to expand the catalog of the linked server in 2008 - Trying to test connection to the linked server in 2008 - Running any query to the linked server ("SELECT * FROM serverName...tableName", "SELECT * FROM OPENQUERY(serverName, 'SELECT * FROM tableName')", etc.) - Changing any options after it is created.As I've said, This issue has persisted across independant systems, server instances, data sources... I have no idea where to even begin.If there are any other details that would be helpful to know, please just ask. I'd appreciate any thoughts you all might have. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-16 : 02:52:14
|
| The whole process of creating a Linked Server is fragile IME.Creating the linked server doesn't do anything to establish that the link is viable. You need to do that independently.If you can use an external tool to see data on an ODBC DSN then SQL should be able to use that as a Linked Server - assuming you get UserIDs / passwords right and so on (yeah, an error message would be nice, and I would not expect a wrong password to HANG, but the darn thing is fragile - drivers written by third parties that have some weakness somewhere ...).Prove the connect outside SQL itself, prove that you can see the server (PING / whatever) first, then that the database transport layers work. Do this as the same UserID that SQL will be using.Have a look at www.connectionstrings.com to make sure you connection strings are water tight - there are lots of parameters available, typically unused, and some of them may improve things for you. |
|
|
|
|
|
|
|
|
Linked Servers