Link to home
Start Free TrialLog in
Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America

asked on

-2147467259 DBNMPNTW ConnectionOpen CreateFile error in SQL Server with ECSQ machine on network.

Set rsSQLData = CreateObject("ADODB.Recordset")
Set cnSQLData = CreateObject("ADODB.Connection")
cnSQLData.Open "Provider=sqloledb;" & _
             "Data Source=ECSQ; Password=smiley" & _
             "Initial Catalog=Access;" & _
             "User Id=Access;" & _
             "Password=catalyst"

Right where after I have cnSQLData.Open - I get all of the coding highlighted and the following error:
-2147467259 # [DBNMPNTW]ConnectionOpen (CreateFile()).

I do have my SQL Server installed on a computer with its Full computer name:  ecsq.
For me to get on ECSQ machine I have to type a UserName and Password:
My UserName is eqcollab and my password is simon.
So I am aware that I do not have any of this information in the cnSQLData.Open string- could this be causing my problem?

Please also note that I can run this on any machine that I have already mapped to the ECSQ machine without any errors.  I notice that this problem only occurs on machines that are not mapped to the ECSQ.   If the only way I can use the above coding works with having the client's machine mapped to ECSQ then I am in trouble - I would still have to figure a way to map this through coding still because some users on a Novell network are unable to go into their Tools - Map Network Drive.
What code or syntax I could write that would open the SQL Server connection between the client and user with the above?

Thanks for any input.


Avatar of connah0047
connah0047

If you get the error AFTER the .Open line, then let's have a look at the line of code after it to see what the error is.
Avatar of stephenlecomptejr

ASKER

Nope sorry for the misexplanation but the highlight is not after the line of code - it is highlighting all of "Provider=sqloledb;" & _
             "Data Source=ECSQ; Password=smiley" & _
             "Initial Catalog=Access;" & _
             "User Id=Access;" & _
             "Password=catalyst"
ASKER CERTIFIED SOLUTION
Avatar of PSSUser
PSSUser
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
can I get a more detailed explanation as to the difference between named pipes and using a TCP/IP connection?

also what kind of security is involved?  If everything is behind your firewall would that make it safe to tie into the SQL Server machine?
Named pipes and TCP/IP are 2 different net library protocols. This is from SQL Server Books Online (for SQL Server 7 hence no mention of windows 2000/XP):

"Named Pipes
Named Pipes support is required on Microsoft Windows NT® installations of SQL Server. Server-side Named Pipes is not supported on Microsoft Windows® 95/98. By default, SQL Server listens on the standard pipe, \\.\pipe\sql\query, for Named Pipes Net-Library connections. After SQL Server is installed, you can change the pipe name. You can also drop named pipe support and set SQL Server to listen only on other Net-Libraries.

TCP/IP Sockets
This Net-Library allows SQL Server to communicate by using standard Windows Sockets as the IPC method across the TCP/IP protocol. By default, Windows 95/98 uses the TCP/IP Sockets Net-Library.
If you set SQL Server to listen on TCP/IP, type the TCP/IP port number in the Port number box. This is the port that SQL Server listens on when accepting connections from TCP/IP Sockets clients. The default is 1433, the official Internet Assigned Number Authority (IANA) socket number for SQL Server.

If you set SQL Server to listen on a proxy server using Microsoft Proxy Server over TCP/IP Sockets, type the proxy server address in the Remote WinSock proxy address box when you set up the TCP/IP Sockets Net-Library."


With both network library types you can have either Windows Integrated Security or SQL Server Authentication. The firewall can be used to block external connections to your SQL server, you will want to block the port SQL server is using. By default this is port 1433. If you run the Server Network utility you can see what it currently set to and change it if you want.

One thing I would recommend doing - make sure a secure password is set for the SA username. I've been to client sites where the username was left with the default (blank) password!