March 25, 2003 at 12:38 pm
Greetings!
I cannot get a connection to SQL Server.
Two computers. Both have been set up for the same domain and workgroup. (I watched that done.) One computer is XP Pro and the other is 2k Pro. The XP machine has SQL Server.
I created a VB program that connects using a DSN-less connection. It works on my machine, duh, but it won't work on the 2k machine.
I can ping the 2k machine and it can ping me. I turned on sharing (and then off) and it could see my shared drive. I connected via a DSN, using my computer's IP address but not through the program.
When the VB program tries to connect, I get an error. (-2147467259) Client unable to establish connection. I looked it up at Microsoft.com and didn't find my error after the first sixty hits. I tried looking in MSDN and didn't find anything there to help me either.
The only thing I can think of is that MDAC versions might be off. I am using 2.7 but I don't know what is on the 2k machine. I am going to try and update that. (I need to get it and have the person do it as they will need to login after a reboot.)
Anyone have any other ideas? Network? VB?
The line I am using is:
goDBCon.Open "driver={SQL Server};server=" & rsServer & ";database=" & gsDatabaseName & ";uid=[uid];pwd=[pwd]"
Thanks!
turlough
March 25, 2003 at 12:49 pm
I think is a problem of version with MDAC.
March 25, 2003 at 1:17 pm
Do you run SQL Server default instance or named instance?
The MDAC version is 2.5 in W2K and it may be sp1 or sp2 depending on which W2K service pack you have. Use MDAC component checker to verify it.
MDAC is installed not only from W2K but also many otehr applications such as SQL Server, O2K Office etc.
Edited by - Allen_Cui on 03/25/2003 1:17:40 PM
March 25, 2003 at 1:22 pm
What network protocol are you using to connect?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 25, 2003 at 2:22 pm
Thanks for the responses!
I am going to try and run the mdac again to see if that works.
I am trying to connect via TCP/IP instead of named pipes. I am not sure how to use named pipes. Is there a way to verify that I am using TCP/IP?
Also, I have checked and I do have NetBUI running on my machine as well.
I will post how it goes.
Thanks!
turlough
March 26, 2003 at 2:27 am
quote:
I am trying to connect via TCP/IP instead of named pipes. I am not sure how to use named pipes. Is there a way to verify that I am using TCP/IP?
There's a program called "Client Network Utility" (cliconfig.exe) that lurks in the MSSQL Server Start Menus. This will allow you to set up preferred network protocols for an individual client PC.
Thomas Rushton
blog: https://thelonedba.wordpress.com
March 26, 2003 at 3:00 am
quote:
I am trying to connect via TCP/IP instead of named pipes. I am not sure how to use named pipes. Is there a way to verify that I am using TCP/IP?
You choose which protocol to use on the second dialog screen of setting up your DSN.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 26, 2003 at 6:14 am
Thanks for the responses!
I updated the mdac on the client computer and it worked! Thanks for the help! After reading here I thought that was it but in case it wasn't, I wanted the great brains of this site to be thinking!
Thanks for the information on named pipes. I take it, then, on a DNS-less connection that it goes to the basic protocol? Which ever is specified as the default? Is there a way to force the protocol to TCP/IP in the connection string? Or is that left up to SQL Server?
Again, thanks to all!
turlough
March 26, 2003 at 6:50 am
If you're using SQL Server OLE DB provider for a DSN-less connection you can specify protocol (TCP/IP in this case) like this:
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Network Protocol=DBNETLIB;Network Address=192.168.0.1,1433"
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 27, 2003 at 3:09 am
Try the following: create a file anywhere (eg. on the desktop) named for example "x.udl" (the ".udl" is important). Then double click the file -> the system built-in connection string builder shows up. Play around with the settings and try out "Test connection".
Use Cliconfg.exe to alter your client networking configuration: In a first step disable all but TCP/IP and NamedPipes, and try one time with TCP/IP on top and on time the other way round (don't forget that you'll have to close the connection builder dialog inbetween to bring new settings into effect).
If one of your connection strings works, use it for your application. The minimum parts of a connection string are: "Provider=SQLOLEDB.1;Data Source=MyServer;Initial Catalog=MyDB". In addition you can use "IntegratedSecurity=SSPI" which uses NT security to connect to SQL Server, or the combination "UserID=MyUser;Password=MyPwd" which uses built in accounts.
Your problem with connecting can also be security related, so try out both ways with different client network libraries.
best regards
March 30, 2003 at 2:58 pm
I've had the same problem before. You must put mdac 2.7 on all the machines connecting to your SQL Server. This should solve your problem. There were some bugs in mdac 2.5 and 2.6 that were corrected with service packs and mdac 2.7. Again can't explain why this works but I can almost guarantee it will...
March 30, 2003 at 3:01 pm
Secondly... when you check your database errors do not use the err object. It will only return a garbage number -2298239290 something. This is only telling you it is an error in some external library. You should use the errors object in the connection object. In your case this would be goDBCon.errors(0).NativeError. This will return a more meaningful error for SQL Server or ADO.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply