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 |
|
alph2004
Starting Member
7 Posts |
 Posted - 2005-01-04 : 11:32:34
|
| Does anyone have any idea why when I specify the actual Server Name in my DSN it'll allow me to create multiple connections to my SQL Server using a variety of logins. But, when I specify the Server I.P. address I can only create one connection with my DSN, if I try and create any more I get this error:Connection FailedSQL State: '0100o'SQL Server Error: 14[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpnen (Invalid Instance()).Thanks in advance.Adam |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-04 : 19:28:54
|
| While I can't think of any reason why this happens, I can think of a solution: don't use the IP address to connect.Wish I could say I put some thought into that, but it would be a lie. |
 |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2005-01-05 : 00:59:05
|
| do you have multiple instances in the server?The IP of the server may not be the that you need to be using. If NATing is being done, you would need to use the IP address that you get when you "ping" to the server.As Rob suggessted, it is always recommended to use the server name instead of IP Address.Hemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
|
alph2004
Starting Member
7 Posts |
Posted - 2005-01-05 : 03:44:02
|
| Thanks for your replies. As a little history:We use a mixture of Windows 2000 and XP machines on our system. Windows 2000 works fine when you specify the server name, however to avoid the dreaded ODBC call failed I have to specify the IP address for the XP machines otherwise they just won't connect. No idea why???Adam. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-05 : 07:50:27
|
| Sounds like Win2K is using named pipes to connect, and I don't think XP supports named pipes, at least not without some special configuration.Add the following bits to your ODBC connection string:Network=DBMSSOCN;Address=ServerName,1433;Change the server name, and the port if need be, to match the settings for your SQL Server. This will force ODBC to use TCP/IP to connect. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-05 : 20:02:06
|
| What version of MDAC do you have on the XP machines? Look at that. Upgrade it if necessary. Also, check the Server Network Utility on SQL Server. Make sure you have TCP/IP enabled.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
alph2004
Starting Member
7 Posts |
Posted - 2005-01-06 : 05:09:14
|
| Thanks again for your help.We are using MDAC 2.8 RTM on both the 2000 and XP machines. TCP/ IP is enabled on SQL Server.On the XP machines I had to add an additional line to the Registry:"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\IP Address", "DBMSSOCN,IP Address,1433"If I used the Server Name instead - I got the same ODBC Call Failed error when connecting.It was kinda annoying this because I struggled to replicate this problem on my machine. I found that because I had the SQL Server Client installed on my XP machine it would work when I used the Server Name. However, users that didn't not have SQL Server installed could not connect using the server name. Another thing I had to do on the XP Machines was to unclick the 'Dynamically Determine Port' option in the DSN Client Configuration and specify port 1433.Adam |
|
|
|
|
|
|
|
|
DSN Anomaly