MDAC update caused access problems

I just updated MDAC on one of my machines and I'm now having trouble accessing my SQL server. It's a local connection using SQLOLEDB in a VB dll. It was working fine before the update and now I get:

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

/cms25/CMS.asp, line 33

Most of the information I can find on this refers to TCP/IP connections but the SQL server is local. My connect function looks like this:

Public Sub ADOConnect()

With m_ADOConnection
.Provider = "SQLOLEDB"
.CursorLocation = adOpenStatic
.CommandTimeout = 20
.Mode = adModeReadWrite
.ConnectionString = "Data Source=" & m_strServer & ";Database=" & m_strName & ";" & m_strUsernamePassword
.open
End With

End Sub

On this server, m_strServer = "(local)". What am I missing? Thanks for any help in advance!

a.From which version of MDAC to which version?

On what platform?

Biggest headache I had was that 2.5 default connection library was Named pipes while 2.7 and later, the default was TCP. If you didn't have TCP and DNS configured properly, then the upgrade would result in errors that you may not have noticed previously.

Regards,

hmscott|||I believe I went from something like 2.5 to 2.8 so you might be right. How do I make sure the server is configured properly? Where do I set up named pipes?

UPDATE:

As soon as I enabled the TCP/IP access, it worked so I guess I'm not using named pipes... I'll have to look into this some more because I'd like to avoid using TCP/IP entirely. I'm assuming named pipes is faster?|||Check this KBA http://support.microsoft.com/kb/328306/en-us for the relevant reasons.sql

Maxis 3G Connection

I cannot connect to SQL Server 2000(Remote Server) through Maxis 3G connection. The remote server does not has firewall.

I'm using Windows Application to connect to SQL Server 2000.

dim strsqlcon as string = "Server=000.000.000.000;databse=dbname;usr=sa;pws=1234"

dim sqlcon as sqlconnection = new sqlconnection(strsqlcon)

sqlcon.open()

"It's Error : Sql not exist or access denied."

But I can connect to SQL Server 2000(Remote Server) through Local Area Connection.

Have any solution?

Thank you.

try to ping the server.|||

I can ping the server through 3G Connection.

Result :

"Reply from xxx.xxx.xxx.xxx: bytes=32 time=369ms++ TTL=115"

|||

Can you telnet to your SQL Server? [YES | NO, please specify the error message ]

In cmd.exe console, type “telnet <server name> port, where port can be 135, 445 or sql_server_tcp_port. If your cmd.exe console turns into a complete black screen with a cursor flushing on top left corner, you are connected. Type ctrl+’[‘ to bring up telnet prompt and type “quit” <enter>.

|||

I enabled telnet service in Services Tool.

In cmd, i type "telnet xxx.xxx.xxx.xxx:1433"

Error " Connecting To xxx.xxx.xxx.xxx:1433...Could not open connection to the host, on port 23: Connect failed"

|||

run the profiler.

check to see if your connection is connecting to sql server

if there's nothing in the profiler you are not connecting

if you are connecting to sql server but encountering permission problem

you should see it there

|||

Type "telnet xxx.xxx.xxx.xxx 1433", instead of "telnet xxx.xxx.xxx.xxx:1433". No ":" in front of the port 1433.

Please also try "telnet xxx.xxx.xxx.xxx 135".

Please also try "telnet xxx.xxx.xxx.xxx 445".

If non of them can connect you, it is the network that block you from being able to connect.

|||

Its show error:

"Connecting To x.x.x.x ...Could not open connection to the host, on port 1433: Connect failed."

How do i open the port?

What should i do?

|||Server didn't install network firewall.

If telnet 1433 port through our normally connection on pc, its fine. Only 3G connection cannot telnet 1433 port but can telnet 80 port, 9119 port.

Is it SQL Server Network Utility or Client Network Utility to do setting for 3G connection?

|||

Mostlikely, your 3G network provider might selectively block the port, you can either find a port that the 3G network dosn't block, and make the SQL Server listening on that port, using SQL Server Configuration Manager, or you can ask your network provider to unblock the port. Either way, exposed your SQL Server directly to the public network is not a good idea in practice from security perspectie.

Good Luck.

|||

Normally, network connection can access 1433 port and 3G network still cannot access.

How to configure Server Network Utility or Client Network Utility in SQL Server 2000?

sql

Maxis 3G connection

I cannot connect to SQL Server 2000(Remote Server) through Maxis 3G connection.

I'm using Windows Application to connect to SQL Server 2000.

dim strsqlcon as string = "Server=000.000.000.000;databse=dbname;usr=sa;pws=1234"

dim sqlcon as sqlconnection = new sqlconnection(strsqlcon)

sqlcon.open()

"It's Error : Sql not exist or access denied."

But I can connect to SQL Server 2000(Remote Server) through Local Area Connection.

Have any solution?

Thank you.

Hello,

I have no idea what "Maxis 3G Connection" is, but I assume it's what one of your network connections is called. The host 000.000.000.000 is invalid - you must specify either an IP or hostname of a valid SQL Server host.

What exactly is it that you're trying to connect to?

Cheers,

Rob

|||

000.000.000.000 is example ipaddress. =>203.164.125.956

Maxis is mobile gateway from Malaysia.(http://www.maxis.com.my/3g/main.asp)

I want connect to SQL Server 2000(Remote Server) through 3G connection from my laptop. But its show me error :"Sql Server is not exist or access denied.

|||Sound like a case of firewall problem. Since you're trying to connect to a default instance, be sure tcp port 1433 is open.

http://support.microsoft.com/kb/287932|||

Server does not has any firewall.

I can connect to SQL Server 2000 through Lan Area Connection.

Is it 3G Connection TCP different with Lan Area Connection TCP?

|||The firewall mentioned here is the network/ISP firewall. You need to check with your network adminstrator to make the port is open.|||

The Port is open if not, i cannot connect through Lan Area Connection.

Windows Application(VB.Net) + SQL Server 2000

now, i cannot connect through 3G connection in my laptop.

|||

I can ping the server through 3G Connection.

Result :

"Reply from xxx.xxx.xxx.xxx: bytes=32 time=459ms++ TTL=115"

|||

LAN connection is not the same as WAN connection. For a WAN user to connect to an internal LAN, it has to go through a firewall - imagine if there isn't a firewall. Every one out in the internet can get to your internal LAN.

Can you telnet to your server on port 1433?

|||

I didnt install any firewall on my Server.

If firewall is blocking, how do i set it off?

|||As I mentioned in earlier post, you need to contact your network administrator to open the port on the network firewall/router.

Basically, you have to get through the network firewall/router before you can get to the server.|||

I enabled telnet service in Services Tool.

In cmd, i type "telnet xxx.xxx.xxx.xxx:1433"

Error " Connecting To xxx.xxx.xxx.xxx:1433...Could not open connection to the host, on port 23: Connect failed"

|||You want to telnet from your laptop to the server as:

telnet x.x.x.x 1433

There is a space between the server IP and TCP port.

If it's successful, you should see the laptop IP on the server's netstat output.

netstat -n|||

Error :

"Connecting To x.x.x.x ...Could not open connection to the host, on port 1433: Connect failed."

How do i open the port?

|||You need to talk to you network administrator for your company and ask him/her to open the port on the network firewall.

Maximum Databases in ODBC

What is the maximum number of Databases you can have connections to using
ODBC connection to SQL? I am talking hear about the entries in ODBC Data
Sources as System DSN's.
Anyone got any ideas? Assume Windows XP SP2 workstation and Windows 20003
server with MS SQL 2000 SP4.
In fact is there any limit to the number of MSSQL databases you can have on
one server. I know its a bit like how longs a piece of string, but lets
assume as an average that each database is about 20Mb in size and has about
100 tables.
Hi
Maximum number of databases per SQL Server instance: 32'767.
As to ODBC Data Sources, well since ODBC is nearing end of life I don't
think anyone is interested anymore. I could imagine that one of the 16 bit
number limits could be hit (256, 1024 or 32'737).
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rowland Costin" <technical@.selven.co.uk> wrote in message
news:%23TMk65EwFHA.1148@.TK2MSFTNGP11.phx.gbl...
> What is the maximum number of Databases you can have connections to using
> ODBC connection to SQL? I am talking hear about the entries in ODBC Data
> Sources as System DSN's.
> Anyone got any ideas? Assume Windows XP SP2 workstation and Windows 20003
> server with MS SQL 2000 SP4.
> In fact is there any limit to the number of MSSQL databases you can have
> on one server. I know its a bit like how longs a piece of string, but lets
> assume as an average that each database is about 20Mb in size and has
> about 100 tables.
>
|||What is replacing ODBC?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OSaSXGIwFHA.3548@.tk2msftngp13.phx.gbl...
> Hi
> Maximum number of databases per SQL Server instance: 32'767.
> As to ODBC Data Sources, well since ODBC is nearing end of life I don't
> think anyone is interested anymore. I could imagine that one of the 16 bit
> number limits could be hit (256, 1024 or 32'737).
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rowland Costin" <technical@.selven.co.uk> wrote in message
> news:%23TMk65EwFHA.1148@.TK2MSFTNGP11.phx.gbl...
>
|||Hi,
OLE DB. It's not really a "replacement" for ODBC but a newer technology.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:OebocSIwFHA.3720@.TK2MSFTNGP14.phx.gbl...
> What is replacing ODBC?
[vbcol=seagreen]

Maximum Databases in ODBC

What is the maximum number of Databases you can have connections to using
ODBC connection to SQL? I am talking hear about the entries in ODBC Data
Sources as System DSN's.
Anyone got any ideas? Assume Windows XP SP2 workstation and Windows 20003
server with MS SQL 2000 SP4.
In fact is there any limit to the number of MSSQL databases you can have on
one server. I know its a bit like how longs a piece of string, but lets
assume as an average that each database is about 20Mb in size and has about
100 tables.Hi
Maximum number of databases per SQL Server instance: 32'767.
As to ODBC Data Sources, well since ODBC is nearing end of life I don't
think anyone is interested anymore. I could imagine that one of the 16 bit
number limits could be hit (256, 1024 or 32'737).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rowland Costin" <technical@.selven.co.uk> wrote in message
news:%23TMk65EwFHA.1148@.TK2MSFTNGP11.phx.gbl...
> What is the maximum number of Databases you can have connections to using
> ODBC connection to SQL? I am talking hear about the entries in ODBC Data
> Sources as System DSN's.
> Anyone got any ideas? Assume Windows XP SP2 workstation and Windows 20003
> server with MS SQL 2000 SP4.
> In fact is there any limit to the number of MSSQL databases you can have
> on one server. I know its a bit like how longs a piece of string, but lets
> assume as an average that each database is about 20Mb in size and has
> about 100 tables.
>|||What is replacing ODBC?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OSaSXGIwFHA.3548@.tk2msftngp13.phx.gbl...
> Hi
> Maximum number of databases per SQL Server instance: 32'767.
> As to ODBC Data Sources, well since ODBC is nearing end of life I don't
> think anyone is interested anymore. I could imagine that one of the 16 bit
> number limits could be hit (256, 1024 or 32'737).
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rowland Costin" <technical@.selven.co.uk> wrote in message
> news:%23TMk65EwFHA.1148@.TK2MSFTNGP11.phx.gbl...
>|||Hi,
OLE DB. It's not really a "replacement" for ODBC but a newer technology.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:OebocSIwFHA.3720@.TK2MSFTNGP14.phx.gbl...
> What is replacing ODBC?
[vbcol=seagreen]