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

MDAC 2.7 in Windows NT Cluster with SQL 7.0

Hope fully there is someone who has done a similar stupid thing! We have got
ourselves into a bit of trouble when MDAC 2.7 was accidentally (?) installed
onto our Windows NT Cluster wich still runs SQL 7.0.
The result was that the SQL Agent stopped working. However the 2
applications that access the database running on the same cluster are all
working 100% and all client application accessing the database are working
correctly.
The solution offered by Microsoft in KB 820754 is that we should either:
- uncluster SQL
- re-install the sytem from scratch
- Install SQL 2000
Neither of these options are very appealing at this point as the system is
due for a major upgrade to Windows 2003/SQL 2000 in the near future (2-6
month)
Has anyone had a similar problem and how was the problem solved ? What risk
would we have if we continued to run like this and just removed the SQL Agent
resource. Currently we use the SQL Agent to schedule a few database
maintenace tasks which could easily be done manually.
Andrew
You could try to use the MDAC rollback functionality to recover the 2.5
installation. You could also try to reinstall 2.5. Otherwise, you are
pretty much as how the KC describes it, leave it alone and do without or do
some sort of rebuild.
Sincerely,
Anthony Thomas

"Andrew Caldwell" <AndrewCaldwell@.discussions.microsoft.com> wrote in
message news:D721101F-CEDB-4E01-9AF3-1F79882AD1F2@.microsoft.com...
Hope fully there is someone who has done a similar stupid thing! We have
got
ourselves into a bit of trouble when MDAC 2.7 was accidentally (?) installed
onto our Windows NT Cluster wich still runs SQL 7.0.
The result was that the SQL Agent stopped working. However the 2
applications that access the database running on the same cluster are all
working 100% and all client application accessing the database are working
correctly.
The solution offered by Microsoft in KB 820754 is that we should either:
- uncluster SQL
- re-install the sytem from scratch
- Install SQL 2000
Neither of these options are very appealing at this point as the system is
due for a major upgrade to Windows 2003/SQL 2000 in the near future (2-6
month)
Has anyone had a similar problem and how was the problem solved ? What risk
would we have if we continued to run like this and just removed the SQL
Agent
resource. Currently we use the SQL Agent to schedule a few database
maintenace tasks which could easily be done manually.
Andrew
|||Thanks
We found a way to rollback the MDAC to 2.5 which seems to have worked.
Andrew
Andrew
"Anthony Thomas" wrote:

> You could try to use the MDAC rollback functionality to recover the 2.5
> installation. You could also try to reinstall 2.5. Otherwise, you are
> pretty much as how the KC describes it, leave it alone and do without or do
> some sort of rebuild.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Andrew Caldwell" <AndrewCaldwell@.discussions.microsoft.com> wrote in
> message news:D721101F-CEDB-4E01-9AF3-1F79882AD1F2@.microsoft.com...
> Hope fully there is someone who has done a similar stupid thing! We have
> got
> ourselves into a bit of trouble when MDAC 2.7 was accidentally (?) installed
> onto our Windows NT Cluster wich still runs SQL 7.0.
> The result was that the SQL Agent stopped working. However the 2
> applications that access the database running on the same cluster are all
> working 100% and all client application accessing the database are working
> correctly.
> The solution offered by Microsoft in KB 820754 is that we should either:
> - uncluster SQL
> - re-install the sytem from scratch
> - Install SQL 2000
> Neither of these options are very appealing at this point as the system is
> due for a major upgrade to Windows 2003/SQL 2000 in the near future (2-6
> month)
> Has anyone had a similar problem and how was the problem solved ? What risk
> would we have if we continued to run like this and just removed the SQL
> Agent
> resource. Currently we use the SQL Agent to schedule a few database
> maintenace tasks which could easily be done manually.
> --
> Andrew
>
>

Maximum number of columns exception

Folks, I'm having trouble with one of my DB's (on SQL server 2005).
I can't seem to be able to add a new column due to the following:
System.Data.SqlClient.SqlException: CREATE TABLE failed because column
'_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
columns.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
The offending call looks like:
ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
But this also doesn't work just using the Management Studio UI.
The mystery to me is that the table doesn't have anywhere close to 1024
columns. It has 22.
Any ideas?
Eloy
Can you post your .NET code + table's structure to be tested?
"Eloy Perez" <EloyPerez@.discussions.microsoft.com> wrote in message
news:CA48A7AA-6FE0-45F0-A3FD-AF366E852428@.microsoft.com...
> Folks, I'm having trouble with one of my DB's (on SQL server 2005).
> I can't seem to be able to add a new column due to the following:
> System.Data.SqlClient.SqlException: CREATE TABLE failed because column
> '_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
> columns.
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
> exception, Boolean breakConnection)
> The offending call looks like:
> ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
> But this also doesn't work just using the Management Studio UI.
> The mystery to me is that the table doesn't have anywhere close to 1024
> columns. It has 22.
> Any ideas?

Maximum number of columns exception

Folks, I'm having trouble with one of my DB's (on SQL server 2005).
I can't seem to be able to add a new column due to the following:
System.Data.SqlClient.SqlException: CREATE TABLE failed because column
'_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
columns.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
The offending call looks like:
ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
But this also doesn't work just using the Management Studio UI.
The mystery to me is that the table doesn't have anywhere close to 1024
columns. It has 22.
Any ideas?Eloy
Can you post your .NET code + table's structure to be tested?
"Eloy Perez" <EloyPerez@.discussions.microsoft.com> wrote in message
news:CA48A7AA-6FE0-45F0-A3FD-AF366E852428@.microsoft.com...
> Folks, I'm having trouble with one of my DB's (on SQL server 2005).
> I can't seem to be able to add a new column due to the following:
> System.Data.SqlClient.SqlException: CREATE TABLE failed because column
> '_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
> columns.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> The offending call looks like:
> ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
> But this also doesn't work just using the Management Studio UI.
> The mystery to me is that the table doesn't have anywhere close to 1024
> columns. It has 22.
> Any ideas?

Maximum number of columns exception

Folks, I'm having trouble with one of my DB's (on SQL server 2005).
I can't seem to be able to add a new column due to the following:
System.Data.SqlClient.SqlException: CREATE TABLE failed because column
'_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
columns.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
The offending call looks like:
ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
But this also doesn't work just using the Management Studio UI.
The mystery to me is that the table doesn't have anywhere close to 1024
columns. It has 22.
Any ideas?Eloy
Can you post your .NET code + table's structure to be tested?
"Eloy Perez" <EloyPerez@.discussions.microsoft.com> wrote in message
news:CA48A7AA-6FE0-45F0-A3FD-AF366E852428@.microsoft.com...
> Folks, I'm having trouble with one of my DB's (on SQL server 2005).
> I can't seem to be able to add a new column due to the following:
> System.Data.SqlClient.SqlException: CREATE TABLE failed because column
> '_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
> columns.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> The offending call looks like:
> ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
> But this also doesn't work just using the Management Studio UI.
> The mystery to me is that the table doesn't have anywhere close to 1024
> columns. It has 22.
> Any ideas?