Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB & SQL

Posted on 1998-09-19
6
Medium Priority
?
433 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Accepted Solution

by:
vplusplus earned 100 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

719 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