Solved

VB & SQL

Posted on 1998-09-19
6
431 Views
Last Modified: 2010-03-19
Hi folks,

   I'm trying to use ODBC to connect to my SQL Server but I'm getting error messages from the ODBC driver.

   I have 6 PCs in the development environment.  Out of these, 2 can run without any problems.  The other 4 have problems connecting to the server.

   1.  Cannot connect to server.  These PCs initially couldn't and we resorted to upgrading the SQL Server ODBC driver to version 3.5++ and it worked after that but yesterday we couldn't connect again.  I doubt its the driver because the two PCs that are working fine are using older versions of the driver (version 2.65++).  The error message for this situation is:

     Connection failed:
     SQLState:'01000'
     SQL Server Error: 5
     [Microsoft][ODBC SQL Server Driver][dbnmpntw]ConnectionOpen (CreateFile())
     Connection failed:
     SQLState:'08001'
     SQL Server Error: 2
     [Microsoft][ODBC SQL Server Driver][dbnmpntw]Access Denied.

    2. Specified SQL Server Not Found.  This PC was not upgraded at all (The ODBC Driver)  It is identical with one of the PCs that's running fine in terms of ODBC setup.  The SQL Server ODBC driver version is identical to the two PCs that's running fine.

    However, when we run the application, the SQL Server Login window will appear requesting for User and Password although it has been included as parameters in the OpenConnection statement.  (I assume it's because it assumes that our password is incorrect, although it is correct).  When I re-enter the items requested, I will get the following message:

     Connection failed:
     SQLState:'01000'
     SQL Server Error: 53
     [Microsoft][ODBC SQL Server Driver][DBNMPNTW]ConnectionOpen (CreateFile())
     Connection failed:
     SQLState:'08001'
     SQL Server Error: 6
     [Microsoft][ODBC SQL Server Driver][DBNMPNTW]Specified SQL server not found

     All the PCs have the same configuration in terms of ODBC so I can't figure out why I'm getting different result on different terminals.  

     I've tried everything I can think of and I still can't come up with anything.  Any suggestions would be deeply appreciated.  Please help me.

     Thanks in advance.

                 palim
0
Comment
Question by:palim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 4

Expert Comment

by:mcix
ID: 1090188
When you set up the ODBC DSN, does the connection test properly?
0
 
LVL 4

Expert Comment

by:vvk
ID: 1090189
What Network protocol you using?
0
 

Author Comment

by:palim
ID: 1090190
If I use the older version of Administrator (Version 3.0), it doesn't check but when you run the application, you hit the problem.  With version 3.5, I can't get through the part where they ask for the password and I click [Next] after entering the necessary fields.

I'm using Named Pipes.  I've tried changing it to TCP/IP but I still hit the same problem.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Accepted Solution

by:
vplusplus earned 50 total points
ID: 1090191

Hi,

The errors
     "ConnectionOpen (CreateFile()) Connection failed" and
     "Specified SQL server not found"
are not coming from ODBC. This is coming from SQL Server
client(DBNMPNTW).

So, first you have to assert weather you can see the SQL Server.

The best way to try this out is using iSQL/W tool.
If you can't connect to SQL Server from iSQL/W, you will not
be able to connect through ODBC, since ODBC works on top
of the SQL Server client DLLs.

From the error, it looks you are using NamedPipes as the protocol. Are the client and server machines seperated
by a router or are they on the same network? If seperated
by a router, depending on configuration, named pipes
may not be routed. So you may not see the server.

You had mentioned you tried to switch to TCP/IP. Well,
when you install SQL Server, by default, the service is
not available on TCP/IP protocol. You can re-enter setup
(on server) and add support to TCP/IP protocol. Remember,
I am not talking about TCP/IP support by NT. The SQL Server
should be specifically told to make itself available on
TCP/IP.

SQL Server connectivity is very simple if you follow some
simple steps by step approach.

Good luck...





0
 

Author Comment

by:palim
ID: 1090192
vplusplus,

   What do you mean when you say that SQL Server should be configured to make it available on TCP/IP?  When I installed SQL Server, I added TCP/IP, Named Pipes and a few other protocols.  Is that what you meant?

   If not, what do I do to configure SQL Server to make itself available on other protocols such as TCP/IP?  As well, does the use of the Named Pipes protocol have a limitation on the number of connections and if so, how many is it limited to?

0
 

Expert Comment

by:dave091653
ID: 4799184
When I got to the point of getting an error message of not being able to connect, I actually mapped a drive from the source machine and then attempted a connection.  
I was connecting across Domains, so this may not be your problem. In my case, it worked as soon as we mapped a drive.  
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question