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
Solved

VB & SQL

Posted on 1998-09-19
6
428 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

791 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