Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

VB & SQL

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
palim
Asked:
palim
1 Solution
 
mcixCommented:
When you set up the ODBC DSN, does the connection test properly?
0
 
vvkCommented:
What Network protocol you using?
0
 
palimAuthor Commented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
vplusplusCommented:

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
 
palimAuthor Commented:
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
 
dave091653Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now