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 Hot patch

With regards to MS04-003 that just came out a few days ago,does one need to
install this MDAC update on SQL Servers servers and on
clients ? Can I just go ahead and apply it on our production systems without
testing ? If i install it only on the server, does it still leave me
vulnerable if I dont apply it on the clients ? Does this require a server
reboot ?> Can I just go ahead and apply it on our production systems without

quote:

---

> testing ?

---

LOL !!!!! Famous last words, if I've ever heard any.
Ideally, you would install the patch on clients and servers in your test
environment, run a battery of sanity tests just to make sure you don't miss
anything. I've witnessed many problems where the client and server had
provider versions out of sync, so you would be best off making sure both the
server and all of its clients have the same version / patch level applied.
NOTHING in your environment should change, ever, without testing first!
You're begging for disaster. Just because it worked for someone else,
doesn't mean it will work for you, unless they have the exact same
network/hardware/software config and are using the exact same applications
and the same users.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks Aaron... That got me laughing too.. I guess I was serious when I
issued the post and really would have thought about applying without testing
it if I just got a reply from somone stating that its safe..
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ul1BZ$F3DHA.556@.TK2MSFTNGP11.phx.gbl...

quote:

---

> LOL !!!!! Famous last words, if I've ever heard any.
> Ideally, you would install the patch on clients and servers in your test
> environment, run a battery of sanity tests just to make sure you don't

---

miss

quote:

---

> anything. I've witnessed many problems where the client and server had
> provider versions out of sync, so you would be best off making sure both

---

the

quote:

---

> server and all of its clients have the same version / patch level applied.
> NOTHING in your environment should change, ever, without testing first!
> You're begging for disaster. Just because it worked for someone else,
> doesn't mean it will work for you, unless they have the exact same
> network/hardware/software config and are using the exact same applications
> and the same users.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

---

MDAC Hot patch

With regards to MS04-003 that just came out a few days ago,does one need to
install this MDAC update on SQL Servers servers and on
clients ? Can I just go ahead and apply it on our production systems without
testing ? If i install it only on the server, does it still leave me
vulnerable if I dont apply it on the clients ? Does this require a server
reboot ?> Can I just go ahead and apply it on our production systems without
> testing ?
LOL !!!!! Famous last words, if I've ever heard any.
Ideally, you would install the patch on clients and servers in your test
environment, run a battery of sanity tests just to make sure you don't miss
anything. I've witnessed many problems where the client and server had
provider versions out of sync, so you would be best off making sure both the
server and all of its clients have the same version / patch level applied.
NOTHING in your environment should change, ever, without testing first!
You're begging for disaster. Just because it worked for someone else,
doesn't mean it will work for you, unless they have the exact same
network/hardware/software config and are using the exact same applications
and the same users.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks Aaron... That got me laughing too.. I guess I was serious when I
issued the post and really would have thought about applying without testing
it if I just got a reply from somone stating that its safe..
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ul1BZ$F3DHA.556@.TK2MSFTNGP11.phx.gbl...
> > Can I just go ahead and apply it on our production systems without
> > testing ?
> LOL !!!!! Famous last words, if I've ever heard any.
> Ideally, you would install the patch on clients and servers in your test
> environment, run a battery of sanity tests just to make sure you don't
miss
> anything. I've witnessed many problems where the client and server had
> provider versions out of sync, so you would be best off making sure both
the
> server and all of its clients have the same version / patch level applied.
> NOTHING in your environment should change, ever, without testing first!
> You're begging for disaster. Just because it worked for someone else,
> doesn't mean it will work for you, unless they have the exact same
> network/hardware/software config and are using the exact same applications
> and the same users.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

MDAC 2.8 Update Causes Problems

Hello,
I have a 2000 server hosting a site using SQL Server 7. I have been having
problems accessing the database and I kept getting an error 80004005 Data
source name not found and no default driver specified. I went to the
Microsoft site and found the white paper and followed a link to download an
MDAC 2.8 update that was compatible with Windows 2000. However now my site
doesn't work at all and I get:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'.
Reason: Not associated with a trusted SQL Server connection.
/includes/i_shop.asp, line 87
I've been searching, but I still can't find exactly what I need and
suggestions would be greatly appreciated.
Thanks for any help,
Med103 wrote:
> Hello,
> I have a 2000 server hosting a site using SQL Server 7. I have been
> having problems accessing the database and I kept getting an error
> 80004005 Data source name not found and no default driver specified.
> I went to the Microsoft site and found the white paper and followed a
> link to download an MDAC 2.8 update that was compatible with Windows
> 2000. However now my site doesn't work at all and I get:
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'sa'. Reason: Not associated with a trusted SQL Server connection.
> /includes/i_shop.asp, line 87
> I've been searching, but I still can't find exactly what I need and
> suggestions would be greatly appreciated.
> Thanks for any help,
It looks like you are telling ADO to connect using a trusted connection,
but it's picking up "sa" as the user name (or are you passing that in?)
Do you want to be using ODBC with ADO? You're not using the ADO drivers
according to that error. Post your connection string and also try
connecting using SQL Server security from your app if you have it
enabled.
David Gugick
Imceda Software
www.imceda.com
|||I went through and used the component checker and there is one unknown file
sqlsrv32.dll version 2000.85.1025.0 according to the Microsoft site the file
that is supposed to be included is sqlsrv32.dll version 2000.85.1022.0. Would
it be advisable to download the right file and replace it? The connection
string and everything else hasn't changed and it all worked for years. The
only change was the 2.8 update. Should I look at the i_shop.asp page for
clues? Why is it listed.
Thanks for any help,
Mike
"David Gugick" wrote:

> Med103 wrote:
> It looks like you are telling ADO to connect using a trusted connection,
> but it's picking up "sa" as the user name (or are you passing that in?)
> Do you want to be using ODBC with ADO? You're not using the ADO drivers
> according to that error. Post your connection string and also try
> connecting using SQL Server security from your app if you have it
> enabled.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Med103 wrote:
> I went through and used the component checker and there is one
> unknown file sqlsrv32.dll version 2000.85.1025.0 according to the
> Microsoft site the file that is supposed to be included is
> sqlsrv32.dll version 2000.85.1022.0. Would it be advisable to
> download the right file and replace it? The connection string and
> everything else hasn't changed and it all worked for years. The only
> change was the 2.8 update. Should I look at the i_shop.asp page for
> clues? Why is it listed.
> Thanks for any help,
> Mike
You should post the connection string anyway. Just because MDAC < 2.8
allowed a malformed connection string to pass through doesn't mean 2.8
will. You want to use ODBC in this case, i assume. No?
I wouldn't worry about the file version issue right now.
David Gugick
Imceda Software
www.imceda.com
|||I didn't create this site and the only connection string I could find was in
the siteserver console DSN=pl_commerce;UID=sa;PWD=dbsa;. Is this the one I
need to worry about?
Thnaks again,
Mike
"David Gugick" wrote:

> Med103 wrote:
> You should post the connection string anyway. Just because MDAC < 2.8
> allowed a malformed connection string to pass through doesn't mean 2.8
> will. You want to use ODBC in this case, i assume. No?
> I wouldn't worry about the file version issue right now.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Med103 wrote:
> I didn't create this site and the only connection string I could find
> was in the siteserver console DSN=pl_commerce;UID=sa;PWD=dbsa;. Is
> this the one I need to worry about?
> Thnaks again,
> Mike
>
Well, the first thing you need to do is change the password on the "sa"
account (because you posted it to the group).
Then, you should change the connection to use some other user that has
limited, but sufficient, rights to the underlying database. Better to
use integrated security in this case instead of SQL Security.
You are using a DSN, which means you are connecting using ODBC (not the
fastest). It's better to use the native ADO driver for SQL Server.
Go to the server in question and test the ODBC connection from the ODBC
Control Panel Applet and make sure it works. maybe someone already
changed the password or the server name changed.
David Gugick
Imceda Software
www.imceda.com
|||Oops I was kind of wondering about that . It doesn't work anyway, but when I
try to use the ODBC Control Panel I get the same error posted above. I also
tried changing users, but it kept giving me an error message Invalid
connection string; connection failed.. Can I just create a new database user
in the enterprise manager and see if I can change it in the connection
string?
Thanks,
"David Gugick" wrote:

> Med103 wrote:
> Well, the first thing you need to do is change the password on the "sa"
> account (because you posted it to the group).
> Then, you should change the connection to use some other user that has
> limited, but sufficient, rights to the underlying database. Better to
> use integrated security in this case instead of SQL Security.
> You are using a DSN, which means you are connecting using ODBC (not the
> fastest). It's better to use the native ADO driver for SQL Server.
> Go to the server in question and test the ODBC connection from the ODBC
> Control Panel Applet and make sure it works. maybe someone already
> changed the password or the server name changed.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Med103 wrote:
> Oops I was kind of wondering about that . It doesn't work anyway,
> but when I try to use the ODBC Control Panel I get the same error
> posted above. I also tried changing users, but it kept giving me an
> error message Invalid connection string; connection failed.. Can I
> just create a new database user in the enterprise manager and see if
> I can change it in the connection string?
Sure can. Give that a shot. Once you get it working in the ODBC control
panel applet, you should be good to go.
David Gugick
Imceda Software
www.imceda.com

MDAC 2.8 Update Causes Problems

Hello,
I have a 2000 server hosting a site using SQL Server 7. I have been having
problems accessing the database and I kept getting an error 80004005 Data
source name not found and no default driver specified. I went to the
Microsoft site and found the white paper and followed a link to download an
MDAC 2.8 update that was compatible with Windows 2000. However now my site
doesn't work at all and I get:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'.
Reason: Not associated with a trusted SQL Server connection.
/includes/i_shop.asp, line 87
I've been searching, but I still can't find exactly what I need and
suggestions would be greatly appreciated.
Thanks for any help,Med103 wrote:
> Hello,
> I have a 2000 server hosting a site using SQL Server 7. I have been
> having problems accessing the database and I kept getting an error
> 80004005 Data source name not found and no default driver specified.
> I went to the Microsoft site and found the white paper and followed a
> link to download an MDAC 2.8 update that was compatible with Windows
> 2000. However now my site doesn't work at all and I get:
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'sa'. Reason: Not associated with a trusted SQL Server connection.
> /includes/i_shop.asp, line 87
> I've been searching, but I still can't find exactly what I need and
> suggestions would be greatly appreciated.
> Thanks for any help,
It looks like you are telling ADO to connect using a trusted connection,
but it's picking up "sa" as the user name (or are you passing that in?)
Do you want to be using ODBC with ADO? You're not using the ADO drivers
according to that error. Post your connection string and also try
connecting using SQL Server security from your app if you have it
enabled.
--
David Gugick
Imceda Software
www.imceda.com|||I went through and used the component checker and there is one unknown file
sqlsrv32.dll version 2000.85.1025.0 according to the Microsoft site the file
that is supposed to be included is sqlsrv32.dll version 2000.85.1022.0. Would
it be advisable to download the right file and replace it? The connection
string and everything else hasn't changed and it all worked for years. The
only change was the 2.8 update. Should I look at the i_shop.asp page for
clues? Why is it listed.
Thanks for any help,
Mike
"David Gugick" wrote:
> Med103 wrote:
> > Hello,
> > I have a 2000 server hosting a site using SQL Server 7. I have been
> > having problems accessing the database and I kept getting an error
> > 80004005 Data source name not found and no default driver specified.
> > I went to the Microsoft site and found the white paper and followed a
> > link to download an MDAC 2.8 update that was compatible with Windows
> > 2000. However now my site doesn't work at all and I get:
> >
> > Error Type:
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> > 'sa'. Reason: Not associated with a trusted SQL Server connection.
> > /includes/i_shop.asp, line 87
> >
> > I've been searching, but I still can't find exactly what I need and
> > suggestions would be greatly appreciated.
> >
> > Thanks for any help,
> It looks like you are telling ADO to connect using a trusted connection,
> but it's picking up "sa" as the user name (or are you passing that in?)
> Do you want to be using ODBC with ADO? You're not using the ADO drivers
> according to that error. Post your connection string and also try
> connecting using SQL Server security from your app if you have it
> enabled.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Med103 wrote:
> I went through and used the component checker and there is one
> unknown file sqlsrv32.dll version 2000.85.1025.0 according to the
> Microsoft site the file that is supposed to be included is
> sqlsrv32.dll version 2000.85.1022.0. Would it be advisable to
> download the right file and replace it? The connection string and
> everything else hasn't changed and it all worked for years. The only
> change was the 2.8 update. Should I look at the i_shop.asp page for
> clues? Why is it listed.
> Thanks for any help,
> Mike
You should post the connection string anyway. Just because MDAC < 2.8
allowed a malformed connection string to pass through doesn't mean 2.8
will. You want to use ODBC in this case, i assume. No?
I wouldn't worry about the file version issue right now.
David Gugick
Imceda Software
www.imceda.com|||I didn't create this site and the only connection string I could find was in
the siteserver console DSN=pl_commerce;UID=sa;PWD=dbsa;. Is this the one I
need to worry about?
Thnaks again,
Mike
"David Gugick" wrote:
> Med103 wrote:
> > I went through and used the component checker and there is one
> > unknown file sqlsrv32.dll version 2000.85.1025.0 according to the
> > Microsoft site the file that is supposed to be included is
> > sqlsrv32.dll version 2000.85.1022.0. Would it be advisable to
> > download the right file and replace it? The connection string and
> > everything else hasn't changed and it all worked for years. The only
> > change was the 2.8 update. Should I look at the i_shop.asp page for
> > clues? Why is it listed.
> > Thanks for any help,
> > Mike
> You should post the connection string anyway. Just because MDAC < 2.8
> allowed a malformed connection string to pass through doesn't mean 2.8
> will. You want to use ODBC in this case, i assume. No?
> I wouldn't worry about the file version issue right now.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Med103 wrote:
> I didn't create this site and the only connection string I could find
> was in the siteserver console DSN=pl_commerce;UID=sa;PWD=dbsa;. Is
> this the one I need to worry about?
> Thnaks again,
> Mike
>
Well, the first thing you need to do is change the password on the "sa"
account (because you posted it to the group).
Then, you should change the connection to use some other user that has
limited, but sufficient, rights to the underlying database. Better to
use integrated security in this case instead of SQL Security.
You are using a DSN, which means you are connecting using ODBC (not the
fastest). It's better to use the native ADO driver for SQL Server.
Go to the server in question and test the ODBC connection from the ODBC
Control Panel Applet and make sure it works. maybe someone already
changed the password or the server name changed.
David Gugick
Imceda Software
www.imceda.com|||Oops I was kind of wondering about that . It doesn't work anyway, but when I
try to use the ODBC Control Panel I get the same error posted above. I also
tried changing users, but it kept giving me an error message Invalid
connection string; connection failed.. Can I just create a new database user
in the enterprise manager and see if I can change it in the connection
string?
Thanks,
"David Gugick" wrote:
> Med103 wrote:
> > I didn't create this site and the only connection string I could find
> > was in the siteserver console DSN=pl_commerce;UID=sa;PWD=dbsa;. Is
> > this the one I need to worry about?
> > Thnaks again,
> > Mike
> >
> Well, the first thing you need to do is change the password on the "sa"
> account (because you posted it to the group).
> Then, you should change the connection to use some other user that has
> limited, but sufficient, rights to the underlying database. Better to
> use integrated security in this case instead of SQL Security.
> You are using a DSN, which means you are connecting using ODBC (not the
> fastest). It's better to use the native ADO driver for SQL Server.
> Go to the server in question and test the ODBC connection from the ODBC
> Control Panel Applet and make sure it works. maybe someone already
> changed the password or the server name changed.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Med103 wrote:
> Oops I was kind of wondering about that . It doesn't work anyway,
> but when I try to use the ODBC Control Panel I get the same error
> posted above. I also tried changing users, but it kept giving me an
> error message Invalid connection string; connection failed.. Can I
> just create a new database user in the enterprise manager and see if
> I can change it in the connection string?
Sure can. Give that a shot. Once you get it working in the ODBC control
panel applet, you should be good to go.
--
David Gugick
Imceda Software
www.imceda.com

MDAC 2.8 Update Causes Problems

Hello,
I have a 2000 server hosting a site using SQL Server 7. I have been having
problems accessing the database and I kept getting an error 80004005 Data
source name not found and no default driver specified. I went to the
Microsoft site and found the white paper and followed a link to download an
MDAC 2.8 update that was compatible with Windows 2000. However now my site
doesn't work at all and I get:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
user 'sa'.
Reason: Not associated with a trusted SQL Server connection.
/includes/i_shop.asp, line 87
I've been searching, but I still can't find exactly what I need and
suggestions would be greatly appreciated.
Thanks for any help,Med103 wrote:
> Hello,
> I have a 2000 server hosting a site using SQL Server 7. I have been
> having problems accessing the database and I kept getting an error
> 80004005 Data source name not found and no default driver specified.
> I went to the Microsoft site and found the white paper and followed a
> link to download an MDAC 2.8 update that was compatible with Windows
> 2000. However now my site doesn't work at all and I get:
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed fo
r user
> 'sa'. Reason: Not associated with a trusted SQL Server connection.
> /includes/i_shop.asp, line 87
> I've been searching, but I still can't find exactly what I need and
> suggestions would be greatly appreciated.
> Thanks for any help,
It looks like you are telling ADO to connect using a trusted connection,
but it's picking up "sa" as the user name (or are you passing that in?)
Do you want to be using ODBC with ADO? You're not using the ADO drivers
according to that error. Post your connection string and also try
connecting using SQL Server security from your app if you have it
enabled.
David Gugick
Imceda Software
www.imceda.com|||I went through and used the component checker and there is one unknown file
sqlsrv32.dll version 2000.85.1025.0 according to the Microsoft site the file
that is supposed to be included is sqlsrv32.dll version 2000.85.1022.0. Woul
d
it be advisable to download the right file and replace it? The connection
string and everything else hasn't changed and it all worked for years. The
only change was the 2.8 update. Should I look at the i_shop.asp page for
clues? Why is it listed.
Thanks for any help,
Mike
"David Gugick" wrote:

> Med103 wrote:
> It looks like you are telling ADO to connect using a trusted connection,
> but it's picking up "sa" as the user name (or are you passing that in?)
> Do you want to be using ODBC with ADO? You're not using the ADO drivers
> according to that error. Post your connection string and also try
> connecting using SQL Server security from your app if you have it
> enabled.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Med103 wrote:
> I went through and used the component checker and there is one
> unknown file sqlsrv32.dll version 2000.85.1025.0 according to the
> Microsoft site the file that is supposed to be included is
> sqlsrv32.dll version 2000.85.1022.0. Would it be advisable to
> download the right file and replace it? The connection string and
> everything else hasn't changed and it all worked for years. The only
> change was the 2.8 update. Should I look at the i_shop.asp page for
> clues? Why is it listed.
> Thanks for any help,
> Mike
You should post the connection string anyway. Just because MDAC < 2.8
allowed a malformed connection string to pass through doesn't mean 2.8
will. You want to use ODBC in this case, i assume. No?
I wouldn't worry about the file version issue right now.
David Gugick
Imceda Software
www.imceda.com|||I didn't create this site and the only connection string I could find was in
the siteserver console DSN=pl_commerce;UID=sa;PWD=dbsa;. Is this the one I
need to worry about?
Thnaks again,
Mike
"David Gugick" wrote:

> Med103 wrote:
> You should post the connection string anyway. Just because MDAC < 2.8
> allowed a malformed connection string to pass through doesn't mean 2.8
> will. You want to use ODBC in this case, i assume. No?
> I wouldn't worry about the file version issue right now.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Med103 wrote:
> I didn't create this site and the only connection string I could find
> was in the siteserver console DSN=pl_commerce;UID=sa;PWD=dbsa;. Is
> this the one I need to worry about?
> Thnaks again,
> Mike
>
Well, the first thing you need to do is change the password on the "sa"
account (because you posted it to the group).
Then, you should change the connection to use some other user that has
limited, but sufficient, rights to the underlying database. Better to
use integrated security in this case instead of SQL Security.
You are using a DSN, which means you are connecting using ODBC (not the
fastest). It's better to use the native ADO driver for SQL Server.
Go to the server in question and test the ODBC connection from the ODBC
Control Panel Applet and make sure it works. maybe someone already
changed the password or the server name changed.
David Gugick
Imceda Software
www.imceda.com|||Oops I was kind of wondering about that . It doesn't work anyway, but when
I
try to use the ODBC Control Panel I get the same error posted above. I also
tried changing users, but it kept giving me an error message Invalid
connection string; connection failed.. Can I just create a new database user
in the enterprise manager and see if I can change it in the connection
string?
Thanks,
"David Gugick" wrote:

> Med103 wrote:
> Well, the first thing you need to do is change the password on the "sa"
> account (because you posted it to the group).
> Then, you should change the connection to use some other user that has
> limited, but sufficient, rights to the underlying database. Better to
> use integrated security in this case instead of SQL Security.
> You are using a DSN, which means you are connecting using ODBC (not the
> fastest). It's better to use the native ADO driver for SQL Server.
> Go to the server in question and test the ODBC connection from the ODBC
> Control Panel Applet and make sure it works. maybe someone already
> changed the password or the server name changed.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Med103 wrote:
> Oops I was kind of wondering about that . It doesn't work anyway,
> but when I try to use the ODBC Control Panel I get the same error
> posted above. I also tried changing users, but it kept giving me an
> error message Invalid connection string; connection failed.. Can I
> just create a new database user in the enterprise manager and see if
> I can change it in the connection string?
Sure can. Give that a shot. Once you get it working in the ODBC control
panel applet, you should be good to go.
David Gugick
Imceda Software
www.imceda.com

MDAC 2.8

Hi,
Recently I run Windows Update on my Windows 2000 Server with MS SQL Server
on the same machine. It update the MDAC to 2.8 version.
It broke my store procedure which work fine with MDAC 2.6 sp2.
With MDAC 2.6 it only take 1 minutes to run; however, with MDAC 2.8 it take
all day and lock up the database. Not only that, the CPU utilization is 100%
the whole day.
Please help ! Thank you in advance.
Here is the sql statement:
Create Procedure dbo.spSbcAudit_AddressMismatch
As
Select
A.ListedPhone As SbcPhone,
A.ListingInstructions As SbcListingInstructions,
A.OmitCode As SbcOmitCode,
A.LastName As SbcLastName,
A.FirstName As SbcFirstName,
A.TitleOfAddress As SbcTitleOfAddress,
A.TitleOfLineage As SbcTitleOfLineage,
A.Number As SbcNumber,
A.Directional As SbcDirectional,
A.StreetName As SbcStreetName,
A.Locality As SbcCity,
A.ListType As SbcListType,
A.RecordType As SbcRecordType,
A.InputFileName As SbcInputFileName,
Ltrim(Str(Month(A.DateFromClec)))+'/'+Ltrim(Str(Day(A.DateFromClec)))+'/'+Lt
rim(Str(Year(A.DateFromClec))) As SbcDateFromClec,
-- Icoms fields
B.AccountNumber As IcomsAccountNumber,
B.InstallDate As IcomsInstallDate,
B.TelephoneNumber As IcomsPhone,
B.TelephoneStatus As IcomsListingStatus,
B.RecordType As IcomsRecordType,
B.ListType As IcomsListType,
B.ListingIdCode As IcomsListingIdCode,
B.ListingInstructions As IcomsListingInstructions,
B.LastName As IcomsLastName,
B.FirstName As IcomsFirstName,
B.TitleOfLineage As IcomsTitleOfLineage,
B.SpecialFilingName As IcomsSpecialFilingName,
B.Title As IcomsTitle,
B.Apartment As IcomsApartmentNumber,
B.Directional As IcomsDirectional,
B.AddressNumber As IcomsNumber,
B.StreetName As IcomsStreetName,
B.City As IcomsCity,
Substring(B.ZipCode,1,5) As IcomsZipCode,
B.ClassOfService As IcomsClassOfService
Into dbo.tblSbcAuditResult_AddressMismatch
From dbo.tblPde7661 A
Inner Join dbo.tblIcomsData B On A.ListedPhone = B.TelephoneNumber
Where
A.ListType = B.ListType And
(Upper(Rtrim(Ltrim(A.StreetName))) <> Upper(Rtrim(Ltrim(B.StreetName))))
And
B.TelephoneStatus In (
Select vchListingStatus
From dbo.tblAuditListingStatus
Where bitIsInclude = 1) And
B.RateCenterId <> 9 And
-- caption only, Icoms does not support this
A.ListedPhone Not In (Select ListedPhone From dbo.tblPde7661 Where
Reference='Customer Service') And
-- Install date must be before the SBC download date
B.InstallDate <= (select intInstallDate from tblAuditInstallDate)
Order By
B.ClassOfService,
B.ListType,
A.ListingInstructions,
A.OmitCode,
B.ListingInstructionswell, latest mdac 2.8 includes security fixes and perheps
some not needed enhancements. You can go to Microsoft site
and search for mdac 2.6 service pack 2 and download it
from there. Or you can use following link to download it:
http://www.microsoft.com/downloads/details.aspx?
familyid=8e5f816c-4918-4250-b8bd-
3794582c9089&languageid=f49e8428-7071-4979-8a67-
3cffcb0c2524&displaylang=en
hth.
>--Original Message--
>Hi,
>Recently I run Windows Update on my Windows 2000 Server
with MS SQL Server
>on the same machine. It update the MDAC to 2.8 version.
>It broke my store procedure which work fine with MDAC 2.6
sp2.
>With MDAC 2.6 it only take 1 minutes to run; however,
with MDAC 2.8 it take
>all day and lock up the database. Not only that, the CPU
utilization is 100%
>the whole day.
>Please help ! Thank you in advance.
>Here is the sql statement:
>Create Procedure dbo.spSbcAudit_AddressMismatch
>As
>Select
> A.ListedPhone As SbcPhone,
> A.ListingInstructions As SbcListingInstructions,
> A.OmitCode As SbcOmitCode,
> A.LastName As SbcLastName,
> A.FirstName As SbcFirstName,
> A.TitleOfAddress As SbcTitleOfAddress,
> A.TitleOfLineage As SbcTitleOfLineage,
> A.Number As SbcNumber,
> A.Directional As SbcDirectional,
> A.StreetName As SbcStreetName,
> A.Locality As SbcCity,
> A.ListType As SbcListType,
> A.RecordType As SbcRecordType,
> A.InputFileName As SbcInputFileName,
>Ltrim(Str(Month(A.DateFromClec)))+'/'+Ltrim(Str(Day
(A.DateFromClec)))+'/'+Lt
>rim(Str(Year(A.DateFromClec))) As SbcDateFromClec,
> -- Icoms fields
> B.AccountNumber As IcomsAccountNumber,
> B.InstallDate As IcomsInstallDate,
> B.TelephoneNumber As IcomsPhone,
> B.TelephoneStatus As IcomsListingStatus,
> B.RecordType As IcomsRecordType,
> B.ListType As IcomsListType,
> B.ListingIdCode As IcomsListingIdCode,
> B.ListingInstructions As IcomsListingInstructions,
> B.LastName As IcomsLastName,
> B.FirstName As IcomsFirstName,
> B.TitleOfLineage As IcomsTitleOfLineage,
> B.SpecialFilingName As IcomsSpecialFilingName,
> B.Title As IcomsTitle,
> B.Apartment As IcomsApartmentNumber,
> B.Directional As IcomsDirectional,
> B.AddressNumber As IcomsNumber,
> B.StreetName As IcomsStreetName,
> B.City As IcomsCity,
> Substring(B.ZipCode,1,5) As IcomsZipCode,
> B.ClassOfService As IcomsClassOfService
>Into dbo.tblSbcAuditResult_AddressMismatch
>From dbo.tblPde7661 A
>Inner Join dbo.tblIcomsData B On A.ListedPhone =B.TelephoneNumber
>Where
> A.ListType = B.ListType And
> (Upper(Rtrim(Ltrim(A.StreetName))) <> Upper(Rtrim(Ltrim
(B.StreetName))))
>And
> B.TelephoneStatus In (
> Select vchListingStatus
> From dbo.tblAuditListingStatus
> Where bitIsInclude = 1) And
> B.RateCenterId <> 9 And
> -- caption only, Icoms does not support this
> A.ListedPhone Not In (Select ListedPhone From
dbo.tblPde7661 Where
>Reference='Customer Service') And
> -- Install date must be before the SBC download date
> B.InstallDate <= (select intInstallDate from
tblAuditInstallDate)
>Order By
> B.ClassOfService,
> B.ListType,
> A.ListingInstructions,
> A.OmitCode,
> B.ListingInstructions
>
>.
>

May (or CAN) I use triggers ?

I need to call a procedure/function in VB when my DB is modified
with insert, update or delete. I know that its possible to do
something called Trigger, put how can my Triggers (in SQL Server or
MSDE) call a procedure in VB ?

Just to understand, this database catch information of users
connected to a support chat and I need to know when this user get
logged in (thru the rows of my table). When I notice that a new user
have logged in, I need to support him.

Thanks.You can try to create extended stored procedure but I think it is not a good idea to use something like this in triggers (performance). Trigger could save info about new users in special table and VB could monitor this table.|||but if I do a procedure for VB to monitor a table, isn't it also bad for the permormance ?|||Originally posted by ralf_davi
but if I do a procedure for VB to monitor a table, isn't it also bad for the permormance ?

Select from table is much faster (index, etc.) than running something no-native from trigger.|||What interface are your users loggin in through? Can't that interface also alert you when the login occurs? I wonder if you aren't trying to put functionality in the wrong place.

blindman|||Originally posted by blindman
What interface are your users loggin in through? Can't that interface also alert you when the login occurs? I wonder if you aren't trying to put functionality in the wrong place.

blindman

Users are logging in thru ASP pages.
I think that using ASP is not possible to alert me or anyone else. So I try do do a VB interface.
Have another idea of what can I use ?|||All contact with your database should be done through stored procedures, and you could place code in the procedure to notify your VB application.

blindman|||Originally posted by blindman
All contact with your database should be done through stored procedures, and you could place code in the procedure to notify your VB application.

blindman

How can I work with stored procedures to notify my VB application ?
Can you give me a sign ?|||Hmmm...I'm not a VB expert, and a lot probably depends upon the particulars of your application, but some of the methods SQL Server can use to communicate with other applications are:

1) Writing files.
2) Calling xp_cmdshell to issue operating system commands.
3) Calling xp_logevent to write to the NT Event Log.
4) Creating your own extended stored procedure.
5) xp_Sendmail

blindman|||if you're carrying a pager you'd probably be better off calling an sp from the ASP page that can page you with an info pertinent to the newly logged on user. there are 2 (that I know of) methods that do not involve SQLMail, - xp_smtp_sendmail and cdo-based sendmail. i don't remember the web-sites, but google will find them.|||Originally posted by ralf_davi
Users are logging in thru ASP pages.
I think that using ASP is not possible to alert me or anyone else. So I try do do a VB interface.
Have another idea of what can I use ?

There are so many ways to alert you through ASP page. You can call your vb dll, send an email directly to you, etc. I would not suggest you to use trigger in this case.

Maximum Number of Database Users and Roles

I unfortunately found out during a late night update that the maximum number
of security accounts for sql server is 16379
(http://support.microsoft.com/?id=303879).
I have a web site that I had/have to change the security model for which was
originally setup using a Windows Domain Group and users to access our sql
server database. We were using mangled url's to pass the username and
password to our site, which was fine, but recently, MS removed the ability
to mangle the url. Our customers want the ability to pass their users from
their site to our site seemlessly without any pop-up dialog (i.e. basic
auth). One of our clients have approximate 40,000 users that need to be
setup for the site. We are trying not to rewrite hundreds of stored procs
to add the username and password parms, so we are kinda in a bind.
How are you accessing your database using sql secrutiy accounts with > 16379
users? Each user has to have a unique login, and we would like to use a
built in security model, but we cannot use the Windows domain groups.
Thanks,
BarryDo you have a "lest level of privileges" and can use the guest user for
that?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Barry" <no_one@.home.net> wrote in message
news:Oasd4VtBEHA.1128@.TK2MSFTNGP11.phx.gbl...
> I unfortunately found out during a late night update that the maximum
number
> of security accounts for sql server is 16379
> (http://support.microsoft.com/?id=303879).
> I have a web site that I had/have to change the security model for which
was
> originally setup using a Windows Domain Group and users to access our sql
> server database. We were using mangled url's to pass the username and
> password to our site, which was fine, but recently, MS removed the ability
> to mangle the url. Our customers want the ability to pass their users
from
> their site to our site seemlessly without any pop-up dialog (i.e. basic
> auth). One of our clients have approximate 40,000 users that need to be
> setup for the site. We are trying not to rewrite hundreds of stored procs
> to add the username and password parms, so we are kinda in a bind.
> How are you accessing your database using sql secrutiy accounts with >
16379
> users? Each user has to have a unique login, and we would like to use a
> built in security model, but we cannot use the Windows domain groups.
> Thanks,
> Barry
>|||We need unique way of retrieving information for each and every user, so if
we stay with sql auth, our limit is 16K + users. The only other way around
that is rewritting the hundreds of sp's and web pages to handle passing
around a userid and/or password which we are trying to avoid.
Thanks,
Barry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O6rceo0BEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Do you have a "lest level of privileges" and can use the guest user for
> that?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Barry" <no_one@.home.net> wrote in message
> news:Oasd4VtBEHA.1128@.TK2MSFTNGP11.phx.gbl...
> number
> was
sql
ability
> from
procs
> 16379
>|||They will still have their own unique login, even if a number of them uses
the same user name. This means that functions like SYSTEM_USER will return
unique names for each person. I looked in sp_addlogin, and I did not find a
check regarding how many logins there exists.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Barry" <no_one@.home.net> wrote in message
news:uZRTEK3BEHA.3284@.TK2MSFTNGP09.phx.gbl...
> We need unique way of retrieving information for each and every user, so
if
> we stay with sql auth, our limit is 16K + users. The only other way
around
> that is rewritting the hundreds of sp's and web pages to handle passing
> around a userid and/or password which we are trying to avoid.
> Thanks,
> Barry
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:O6rceo0BEHA.2348@.TK2MSFTNGP09.phx.gbl...
which
> sql
> ability
basic
be
> procs
a
>|||I guess I don't understand what you mean by "lest level of privileges" (do
you mean "least"). Are you saying that everybody accesses generic parts of
the sites using a generic username/password, but access their stored info
using their unique username/password?
Unfortunately, almost every page requires a unique identifier for a user for
all of our sites. Everything is customizable, so we have to know who the
user is from everything from a welcome page to specialized list of products
that they work with. Without re-writing hundres of web pages and stored
procs, I don't see how this can be done with sql server auth with > 16K+
users.
The docs for sp_addlogin don't state a limit, I had to search the ms website
for this info:
http://support.microsoft.com/?id=303879
Thanks,
Barry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eLobfo3BEHA.1544@.TK2MSFTNGP09.phx.gbl...
> They will still have their own unique login, even if a number of them uses
> the same user name. This means that functions like SYSTEM_USER will return
> unique names for each person. I looked in sp_addlogin, and I did not find
a
> check regarding how many logins there exists.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Barry" <no_one@.home.net> wrote in message
> news:uZRTEK3BEHA.3284@.TK2MSFTNGP09.phx.gbl...
> if
> around
> in
for
maximum
> which
our
and
users
> basic
to
> be
>
use
> a
groups.
>|||'what I'm trying to say is that each person can have their own login. For
persons who requires special access rules, you create their own users in the
database.
But for the number of persons who can use the least level of privileges, you
don't create users in the database, you create only one guest user instead.
Is the number/percentage users who can use this least level of privileges is
high, you can cut down a substantial numbers of users you have to create in
the database and possibly get under the approach 16000 user limit.
It is important that you understand the difference between a login and a
database user. Also, the KB article you refer to documents the limit of
database users, not SQL Server logins!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Barry" <no_one@.home.net> wrote in message
news:%23kE6KC6BEHA.2380@.TK2MSFTNGP10.phx.gbl...
> I guess I don't understand what you mean by "lest level of privileges" (do
> you mean "least"). Are you saying that everybody accesses generic parts
of
> the sites using a generic username/password, but access their stored info
> using their unique username/password?
> Unfortunately, almost every page requires a unique identifier for a user
for
> all of our sites. Everything is customizable, so we have to know who the
> user is from everything from a welcome page to specialized list of
products
> that they work with. Without re-writing hundres of web pages and stored
> procs, I don't see how this can be done with sql server auth with > 16K+
> users.
> The docs for sp_addlogin don't state a limit, I had to search the ms
website
> for this info:
> http://support.microsoft.com/?id=303879
> Thanks,
> Barry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:eLobfo3BEHA.1544@.TK2MSFTNGP09.phx.gbl...
uses
rn
find
> a
so
passing
wrote
> for
> maximum
> our
> and
> users
> to
stored
with
> use
> groups.
>|||Tibor,
The unfortunate part is that there really isn't a place on the site that I
can fit a general user in, I have to know on almost each and every page who
they are. Each user has their own saved items, and each section of our site
has specific items that are unique to that user.
I understand that there is a difference in the # of logins and the number of
database users, it's the number of database users that are putting me in a
bind.
Thanks,
Barry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ONLI8d6BEHA.3928@.TK2MSFTNGP11.phx.gbl...
> 'what I'm trying to say is that each person can have their own login. For
> persons who requires special access rules, you create their own users in
the
> database.
> But for the number of persons who can use the least level of privileges,
you
> don't create users in the database, you create only one guest user
instead.
> Is the number/percentage users who can use this least level of privileges
is
> high, you can cut down a substantial numbers of users you have to create
in
> the database and possibly get under the approach 16000 user limit.
> It is important that you understand the difference between a login and a
> database user. Also, the KB article you refer to documents the limit of
> database users, not SQL Server logins!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Barry" <no_one@.home.net> wrote in message
> news:%23kE6KC6BEHA.2380@.TK2MSFTNGP10.phx.gbl...
(do
> of
info
> for
the
> products
> website
> in
> uses
retu
> rn
> find
user,
> so
> passing
> wrote
user
for
access
username
the
(i.e.
need
> stored
bind.
> with
to
>|||You can still se who the *person* is by looking at the login name instead of
the user name!
For instance, the SYSTEM_USER function name returns the login name while the
SESSION_USER function returns the user name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Barry" <no_one@.home.net> wrote in message
news:esU8GD7BEHA.1484@.TK2MSFTNGP12.phx.gbl...
> Tibor,
> The unfortunate part is that there really isn't a place on the site that I
> can fit a general user in, I have to know on almost each and every page
who
> they are. Each user has their own saved items, and each section of our
site
> has specific items that are unique to that user.
> I understand that there is a difference in the # of logins and the number
of
> database users, it's the number of database users that are putting me in a
> bind.
> Thanks,
> Barry
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:ONLI8d6BEHA.3928@.TK2MSFTNGP11.phx.gbl...
For
> the
> you
> instead.
privileges
> is
> in
> (do
parts
> info
user
> the
stored
16K+
wrote
them
> retu
> user,
way
> user
> for
> access
> username
> the
their
> (i.e.
> need
> bind.
accounts
like
> to
>

Maxdop hint

Is parallelism only triggered for select statements or do
inserts/updates/deletes also go thru parallelism such as
update a
set col1 = 1
from a join b
on a.col2=b.col3 ............
or
delete from a join b on a.col1=b.col2 ........
If so, and if we want to disable parallelism, how can we do so for
deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
hint at the end of the statement ?From Books Online , "Degree of Parallelism" topic
"The INSERT, UPDATE, and DELETE operators are executed serially; however,
the WHERE clause of either an UPDATE or DELETE, or SELECT portion of an
INSERT statement may be executed in parallel. The actual data changes are
then serially applied to the database."
You can disable parallelism for INSERT, UPDATE and DELETE where relevant, in
the same way as for SELECT, with OPTION (MAXDOP 1).
--
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23U6JVjvSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Is parallelism only triggered for select statements or do
> inserts/updates/deletes also go thru parallelism such as
> update a
> set col1 = 1
> from a join b
> on a.col2=b.col3 ............
> or
> delete from a join b on a.col1=b.col2 ........
> If so, and if we want to disable parallelism, how can we do so for
> deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
> hint at the end of the statement ?
>
>
>|||Parallelism can also be set globally for the server in SEM>
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23U6JVjvSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Is parallelism only triggered for select statements or do
> inserts/updates/deletes also go thru parallelism such as
> update a
> set col1 = 1
> from a join b
> on a.col2=b.col3 ............
> or
> delete from a join b on a.col1=b.col2 ........
> If so, and if we want to disable parallelism, how can we do so for
> deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
> hint at the end of the statement ?
>
>
>