ADODB.Command (0x800A0E7D) Requested operation requires an OLE DB Session object, which is not supported by the currentprovider

[Stephan]

Hi all,

maybe someone can help me out. I have installed an ASP-App, using a
W2K server, IIS 5.0, MDAC 2.8, MS SQL 2000. I got the app running once
(first time onley), but after that I keep getting this error message.
"ADODB.Command (0x800A0E7D) Requested operation requires an OLE DB
Session object, which is not supported by the currentprovider".
Whatever I tried, nothing helped. Initialy MDAC 2.5 was installed. So
I upgraded to 2.8 but this didn't help. I found some post here about
the issue, but none of the suggestions help. Het is the code that I
use


ConnectString = "Provider=sqloledb;Data Source=127.0.0.1;Initial
Catalog=HKT30;UID=HKT30USER;PWD=HKT30USER;Trusted_ Connection=Yes;"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open ConnectString

Set cmdSP = Server.CreateObject("ADODB.Command")
cmdSP.CommandText = strStoredProcedure
cmdSP.CommandType = adCmdStoredProc
cmdSP.ActiveConnection = objConn <== This is the line on which the
error is created

This app. works fine on several other machines. Including mine, but
not on the customers server. I suspect that something is wrong with
the OLEDB connection. I even tried to use an Access DB but that didn't
work either. It's not the same message, but that's because the
connectionstring differs. I then got an error whichs says something
about "multi-step OLE DB error.". The other way I tried to solve this
problem is by connecting using ODBC. I configured a DSN and used that
string to connect and run te SP's. Didn't work either. Same error. I
haven't got a clou. Anyone else does? The only thing which comes to my
mind is user rights. De systemadministrator gave us an other
user-account. But I'm not sure if we used it before we got the app
working or after it was not working anymore. So maybe it's the
useraccount we use to login on to the server and running the
application. Does that sound stupid. Unfortunatly I'm not able to test
is, so that's why I wrote it down to.

Hope anyone can help
THX in adv.

Greetzzzz

Stephan

[Bob Barrows]

Stephan wrote:

> Hi all,
>
> maybe someone can help me out. I have installed an ASP-App, using a
> W2K server, IIS 5.0, MDAC 2.8, MS SQL 2000. I got the app running once
> (first time onley), but after that I keep getting this error message.
> "ADODB.Command (0x800A0E7D) Requested operation requires an OLE DB
> Session object, which is not supported by the currentprovider".
> Whatever I tried, nothing helped. Initialy MDAC 2.5 was installed. So
> I upgraded to 2.8 but this didn't help. I found some post here about
> the issue, but none of the suggestions help. Het is the code that I
> use
>
>
> ConnectString = "Provider=sqloledb;Data Source=127.0.0.1;Initial
> Catalog=HKT30;UID=HKT30USER;PWD=HKT30USER;Trusted_ Connection=Yes;"

Why are you passing a user id and password while setting trusted connection
to yes? I suspect you actually have a SQL login created called HKT30USER, so
you really don't want to be using a trusted connection - remove that part of
the connection string. Go to [url]www.able-consulting.com/ado_conn.htm[/url] for
examples of OLEDB connection strings.

>
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open ConnectString
>
> Set cmdSP = Server.CreateObject("ADODB.Command")
> cmdSP.CommandText = strStoredProcedure
> cmdSP.CommandType = adCmdStoredProc
> cmdSP.ActiveConnection = objConn <== This is the line on which the
> error is created

The line should be:
Set cmdSP.ActiveConnection = objConn

Without the Set keyword, a new Connection is being created using the
connection string property of the objConn connection.

<snip>

I've never seen this error. Let us know if the above suggestions fix the
problem. The only other thing I can think of is that OLEDB Session Pooling
on this particular machine has been turned off. Go to
[url]http://msdn.microsoft.com/library/techart/pooling2.htm[/url]
to see an explanation of how to turn session/resource pooling on.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Stephan]

Hi Bob,

Thanks for the reply. First of all, I'll give some more information on
what I already tried. Because I didn't found much information on the
Inet which said something about the error. After I received this
error, I tried to connect to an Access DB. This time an other error
was occured: "Multiple-step OLE DB operation generated errors. Check
each OLE DB status value, if available. No work was done". As I wrote
below. The application runned once. On all the other machines the code
I'm using is working fine, but it can be improved as I can read in
your answer. So that's the first thing I'm going to do after fixing
this problem. So, when the Access solution too didn't work, I tried to
connect to the MS SQL DB by using ODBC. I created a DSN, but still the
error (ADODB.Command (0x800A0E7D)) occured. Unfortunately I didn't
have the Admin-account password for this machine. They gave me an
account of which they said it was equall to the admin-account. I
cracked the admin-password to be sure that the problem wasn't caused
by userrights. Then I tried to connect the MS SQL DB using the SA
account. I received the error that the IUSER_XXXXXX was not granted
access to the DB. Now it became clear the the problem was caused by
rights used on the machine and in the MS SQL DB. I'm sorry, but I
changed too much on the machine to check wether your suggestions would
solve the problem. I will install everything new, but maybe some other
people who came around this problem will find some answer here.

Thanks for the help

P.S.: The user HKT30USER was created in the DB. I didn't check for
sessionpooling. But all of this I'll keep in mind for the next time.


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message news:<#TUZs4rpDHA.708@TK2MSFTNGP10.phx.gbl>...

> Stephan wrote:

> > Hi all,
> >
> > maybe someone can help me out. I have installed an ASP-App, using a
> > W2K server, IIS 5.0, MDAC 2.8, MS SQL 2000. I got the app running once
> > (first time onley), but after that I keep getting this error message.
> > "ADODB.Command (0x800A0E7D) Requested operation requires an OLE DB
> > Session object, which is not supported by the currentprovider".
> > Whatever I tried, nothing helped. Initialy MDAC 2.5 was installed. So
> > I upgraded to 2.8 but this didn't help. I found some post here about
> > the issue, but none of the suggestions help. Het is the code that I
> > use
> >
> >
> > ConnectString = "Provider=sqloledb;Data Source=127.0.0.1;Initial
> > Catalog=HKT30;UID=HKT30USER;PWD=HKT30USER;Trusted_ Connection=Yes;"

>
> Why are you passing a user id and password while setting trusted connection
> to yes? I suspect you actually have a SQL login created called HKT30USER, so
> you really don't want to be using a trusted connection - remove that part of
> the connection string. Go to [url]www.able-consulting.com/ado_conn.htm[/url] for
> examples of OLEDB connection strings.
>

> >
> > Set objConn = Server.CreateObject("ADODB.Connection")
> > objConn.Open ConnectString
> >
> > Set cmdSP = Server.CreateObject("ADODB.Command")
> > cmdSP.CommandText = strStoredProcedure
> > cmdSP.CommandType = adCmdStoredProc
> > cmdSP.ActiveConnection = objConn <== This is the line on which the
> > error is created

>
> The line should be:
> Set cmdSP.ActiveConnection = objConn
>
> Without the Set keyword, a new Connection is being created using the
> connection string property of the objConn connection.
>
> <snip>
>
> I've never seen this error. Let us know if the above suggestions fix the
> problem. The only other thing I can think of is that OLEDB Session Pooling
> on this particular machine has been turned off. Go to
> [url]http://msdn.microsoft.com/library/techart/pooling2.htm[/url]
> to see an explanation of how to turn session/resource pooling on.
>
> HTH,
> Bob Barrows

[Stephan]

Some extra information:

As I wrote before, to solve te problem using an Access DB, an other
error occurd (multi-step error). There is a way so get the same error
message I got when using the SQL-server. Use the Access DB, make a
form, start the form and then try to connect to the DB using a
webapplication. This wil generate the same error as described in my
first message. The cause is probably different (locking of the table),
but it's a way to reproduce the same error.



"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message news:<#TUZs4rpDHA.708@TK2MSFTNGP10.phx.gbl>...

> Stephan wrote:

> > Hi all,
> >
> > maybe someone can help me out. I have installed an ASP-App, using a
> > W2K server, IIS 5.0, MDAC 2.8, MS SQL 2000. I got the app running once
> > (first time onley), but after that I keep getting this error message.
> > "ADODB.Command (0x800A0E7D) Requested operation requires an OLE DB
> > Session object, which is not supported by the currentprovider".
> > Whatever I tried, nothing helped. Initialy MDAC 2.5 was installed. So
> > I upgraded to 2.8 but this didn't help. I found some post here about
> > the issue, but none of the suggestions help. Het is the code that I
> > use
> >
> >
> > ConnectString = "Provider=sqloledb;Data Source=127.0.0.1;Initial
> > Catalog=HKT30;UID=HKT30USER;PWD=HKT30USER;Trusted_ Connection=Yes;"

>
> Why are you passing a user id and password while setting trusted connection
> to yes? I suspect you actually have a SQL login created called HKT30USER, so
> you really don't want to be using a trusted connection - remove that part of
> the connection string. Go to [url]www.able-consulting.com/ado_conn.htm[/url] for
> examples of OLEDB connection strings.
>

> >
> > Set objConn = Server.CreateObject("ADODB.Connection")
> > objConn.Open ConnectString
> >
> > Set cmdSP = Server.CreateObject("ADODB.Command")
> > cmdSP.CommandText = strStoredProcedure
> > cmdSP.CommandType = adCmdStoredProc
> > cmdSP.ActiveConnection = objConn <== This is the line on which the
> > error is created

>
> The line should be:
> Set cmdSP.ActiveConnection = objConn
>
> Without the Set keyword, a new Connection is being created using the
> connection string property of the objConn connection.
>
> <snip>
>
> I've never seen this error. Let us know if the above suggestions fix the
> problem. The only other thing I can think of is that OLEDB Session Pooling
> on this particular machine has been turned off. Go to
> [url]http://msdn.microsoft.com/library/techart/pooling2.htm[/url]
> to see an explanation of how to turn session/resource pooling on.
>
> HTH,
> Bob Barrows

[Bob Barrows]

Stephan wrote:

> Some extra information:
>
> As I wrote before, to solve te problem using an Access DB, an other
> error occurd (multi-step error). There is a way so get the same error
> message I got when using the SQL-server. Use the Access DB, make a
> form, start the form and then try to connect to the DB using a
> webapplication. This wil generate the same error as described in my
> first message. The cause is probably different (locking of the table),
> but it's a way to reproduce the same error.
>
>

Sorry, but this does not help here. You should post to an Access newsgroup
if you are having a problem with an Access form.

I need a way to reproduce this error when connecting to SQL Server (or
Access) from an ASP page.

The multi-step errors are very tough to troubleshoot. Have you done a Google
search on the error number and/or message? If you do, you will see many
different ways to raise this error, each with its own solution.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Stephan]

Sorry Bob,

I just wanted to point out that this is a way to reproduce the error.
I didn't want to know anything about Access or the Multi-Step error.

I could solve the problem mentioned in the first mail. As said, my
strong feeling is that it had something to do with userrights, but I
changed too much to be sure that that was the only thing. I hope
someone has more experience.

Greetings.

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message news:<u7X#2aEqDHA.2140@TK2MSFTNGP09.phx.gbl>...

> Stephan wrote:

> > Some extra information:
> >
> > As I wrote before, to solve te problem using an Access DB, an other
> > error occurd (multi-step error). There is a way so get the same error
> > message I got when using the SQL-server. Use the Access DB, make a
> > form, start the form and then try to connect to the DB using a
> > webapplication. This wil generate the same error as described in my
> > first message. The cause is probably different (locking of the table),
> > but it's a way to reproduce the same error.
> >
> >

> Sorry, but this does not help here. You should post to an Access newsgroup
> if you are having a problem with an Access form.
>
> I need a way to reproduce this error when connecting to SQL Server (or
> Access) from an ASP page.
>
> The multi-step errors are very tough to troubleshoot. Have you done a Google
> search on the error number and/or message? If you do, you will see many
> different ways to raise this error, each with its own solution.
>
> Bob Barrows