Solved

VB & SQL

Posted on 1998-09-19
6
424 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
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now