error when connecting to sql server

[Bart]

Hi,

I get following error when trying to connect to a MDF file of sql server
express 2005 database:

"Microsoft OLE DB Service Components error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done"

The code in ASP file:
--------------------
<%
set objdc = Server.CreateOb ject("ADODB.Con nection")
objdc.Open(appl ication("newres "))
%>

global.asa:
----------
Sub Application_OnS tart
Application("ne wres") = "Provider=SQLOL EDB; Data
Source=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|\newres. mdf;Integrated
Security=True;U ser Instance=True"
End Sub

Thanks for hrlp
Bart

[Bob Barrows [MVP]]

Re: error when connecting to sql server

Bart wrote:

Hi,
>
I get following error when trying to connect to a MDF file of sql
server express 2005 database:
>
"Microsoft OLE DB Service Components error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done"
>
The code in ASP file:
--------------------
<%
set objdc = Server.CreateOb ject("ADODB.Con nection")
objdc.Open(appl ication("newres "))
%>
>
global.asa:
----------
Sub Application_OnS tart
Application("ne wres") = "Provider=SQLOL EDB; Data
>

Source=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|\newres. mdf;Integrat
ed

Security=True;U ser Instance=True"
End Sub
>

1. Why are you using the AttachDbFilenam e attribute? Do you have a
detached mdf file which you want to attach?
2. I assume you are substituting the real path to the data directory for
"|DataDirectory |"
(http://msdn2.microsoft.com/en-us/lib...8(SQL.80).aspx)
3. You must specify the database name using the DATABASE attribute when
using the AttachDbFilenam e attribute
(http://msdn2.microsoft.com/en-us/lib...8(SQL.80).aspx)




--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bart]

Re: error when connecting to sql server

Hi Bob,

thanks for replying;
In fact, we have a webapplication written mainly in ASP.NET, but there are
still old ASP pages which must be converted later.
Meanwhile i have to work with them. The asp.net pages are not a problem
(provider sqlclient).

We use a mdf file which is located in the App_Data directory of the
application (sql server express 2005).
Sql server express 2005 is installed as "Servername\sql express" with windows
authentificatio n.

So i tried this with attribute Database but doesn't work:
"Provider=SQLOL EDB; Data
Source=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|\newres. mdf;Integrated
Security=True;U ser Instance=True; Database=mewres .mdf"



"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcomschre ef in bericht
news:uyAOhCIbHH A.4176@TK2MSFTN GP02.phx.gbl...

Bart wrote:

>Hi,
>>
>I get following error when trying to connect to a MDF file of sql
>server express 2005 database:
>>
>"Microsoft OLE DB Service Components error '80040e21'
>Multiple-step OLE DB operation generated errors. Check each OLE DB
>status value, if available. No work was done"
>>
>The code in ASP file:
>--------------------
><%
>set objdc = Server.CreateOb ject("ADODB.Con nection")
>objdc.Open(app lication("newre s"))
>%>
>>
>global.asa:
>----------
>Sub Application_OnS tart
>Application("n ewres") = "Provider=SQLOL EDB; Data
>>

Source=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|\newres. mdf;Integrat
ed

>Security=True; User Instance=True"
>End Sub
>>

1. Why are you using the AttachDbFilenam e attribute? Do you have a
detached mdf file which you want to attach?
2. I assume you are substituting the real path to the data directory for
"|DataDirectory |"
(http://msdn2.microsoft.com/en-us/lib...8(SQL.80).aspx)
3. You must specify the database name using the DATABASE attribute when
using the AttachDbFilenam e attribute
(http://msdn2.microsoft.com/en-us/lib...8(SQL.80).aspx)
>
>
>
>
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
>
>

[Bob Barrows [MVP]]

Re: error when connecting to sql server

Bart wrote:

Hi Bob,
>
thanks for replying;
In fact, we have a webapplication written mainly in ASP.NET, but
there are still old ASP pages which must be converted later.
Meanwhile i have to work with them. The asp.net pages are not a
problem (provider sqlclient).
>
We use a mdf file which is located in the App_Data directory of the
application (sql server express 2005).
Sql server express 2005 is installed as "Servername\sql express" with
windows authentificatio n.
>
So i tried this with attribute Database but doesn't work:
"Provider=SQLOL EDB; Data
>

Source=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|\newres. mdf;Integrat
ed

Security=True;U ser Instance=True; Database=mewres .mdf"
>

Again - are you substituting the true path to the file for
"|DataDirectory |"? If "|DataDirectory |" is a .Net keyword, classic ASP
has no concept of what it is.
I still don't understand why you are using AttachDbFilenam e. Why not
just use:

"Provider=SQLOL EDB; Data
Source=Serverna me\SQLEXPRESS;I ntegrated
Security=True;U ser Instance=True; Database=mewres "

Oh! I just noticed. The database name is probably not "mewres.mdf ". It
would be very rare if the database had the same name as the file. More
likely, the name is as i showed in my example. Look at the result of
"select * from master..sysdata bases" to verify.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bart]

Re: error when connecting to sql server

Bob,

Sorry if i don't understand but I'm a little bit confused ...
I went to Sql server Management Studio express and did "select * from
master..sysdata bases".
I get a list of databases (master, model, tempdb, msdb) all located in
\program files\sql server .... Nothing else.

My MDF file is independant of sql server and located in App_Data of the
application.

To show you as example, for the aspx files, i use this string for the same
MDF file:
"Data
Source=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|\newres. mdf;Integrated
Security=True;U ser Instance=True"
providerName="S ystem.Data.SqlC lient"

That's why i also use AttachDbFilenam e ...

I tried this now, but still same error:

Application("ne wres") = "Provider=SQLOL EDB; Data
Source=Serverna me\SQLEXPRESS;I ntegrated Security=True;U ser Instance=True;
Database=mewres .mdf"
....



"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcomschre ef in bericht
news:ORaxFSKbHH A.2552@TK2MSFTN GP06.phx.gbl...

Bart wrote:

>Hi Bob,
>>
>thanks for replying;
>In fact, we have a webapplication written mainly in ASP.NET, but
>there are still old ASP pages which must be converted later.
>Meanwhile i have to work with them. The asp.net pages are not a
>problem (provider sqlclient).
>>
>We use a mdf file which is located in the App_Data directory of the
>application (sql server express 2005).
>Sql server express 2005 is installed as "Servername\sql express" with
>windows authentificatio n.
>>
>So i tried this with attribute Database but doesn't work:
>"Provider=SQLO LEDB; Data
>>

Source=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|\newres. mdf;Integrat
ed

>Security=True; User Instance=True; Database=mewres .mdf"
>>

>
Again - are you substituting the true path to the file for
"|DataDirectory |"? If "|DataDirectory |" is a .Net keyword, classic ASP
has no concept of what it is.
I still don't understand why you are using AttachDbFilenam e. Why not
just use:
>
"Provider=SQLOL EDB; Data
Source=Serverna me\SQLEXPRESS;I ntegrated
Security=True;U ser Instance=True; Database=mewres "
>
Oh! I just noticed. The database name is probably not "mewres.mdf ". It
would be very rare if the database had the same name as the file. More
likely, the name is as i showed in my example. Look at the result of
"select * from master..sysdata bases" to verify.
>
>
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
>
>

[Bob Barrows [MVP]]

Re: error when connecting to sql server

Bart wrote:

Bob,
>
Sorry if i don't understand but I'm a little bit confused ...
I went to Sql server Management Studio express and did "select * from
master..sysdata bases".
I get a list of databases (master, model, tempdb, msdb) all located in
\program files\sql server .... Nothing else.

OK, that does explain the need to use AttachDbFilenam e.

>
My MDF file is independant of sql server and located in App_Data of
the application.
>
To show you as example, for the aspx files, i use this string for
the same MDF file:
"Data
>

Source=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|\newres. mdf;Integrat
ed

Security=True;U ser Instance=True"
providerName="S ystem.Data.SqlC lient"

A classic ASP page will have absolutely NO concept of an App_Data
directory. It will have no idea of what to do with |DataDirectory| .
You MUST provide the actual physical path to the mdf file in the
AttachDbFilenam e attribute.
Again, the name of the database as seen by the server will never be
"mewres.mdf ". What needs to be used is the logical name of the database,
not its physical name.

Create an aspx page which connects to the database and query that
sysdatabases table again to get the database's logical name.

Another approach you might want to consider is creating a .Net web
service to provide data, etc. for your classic asp pages ... nah, this
will require rewriting the asp pages and if you're going to do that, you
might as well convert them to .Net.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bart]

Re: error when connecting to sql server

Here are my next attempts:

First, i created an aspx to connect to the MDF file and did the select again
(select * from master..sysdata bases) to gain its logical name:
what i could see in a gridview was:
name
filename
----
------
master
c:\program files\sql server ...
model
.....
tempdb
.....
msdb
.....
c:\inetpub\wwwr oot\reskrosql\A pp_Data\newres. mdf
c:\inetpub\wwwr oot\reskrosql\A pp_Data\newres. mdf


So the logical name is: c:\inetpub\wwwr oot\reskrosql\A pp_Data\newres. mdf
So i tried this connectionstrin g (in global.asa)
Application("ne wres") = "Provider=SQLOL EDB; Data
Source=.\SQLEXP RESS;AttachDbFi lename=c:\inetp ub\wwwroot\resk rosql\App_Data\ newres.mdf;Inte grated
Security=True;U ser Instance=True"

and ... a new error: Provider error '80040e21'

i'm getting crazy.




"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcomschre ef in bericht
news:eGie$oKbHH A.4872@TK2MSFTN GP03.phx.gbl...

Bart wrote:

>Bob,
>>
>Sorry if i don't understand but I'm a little bit confused ...
>I went to Sql server Management Studio express and did "select * from
>master..sysdat abases".
>I get a list of databases (master, model, tempdb, msdb) all located in
>\program files\sql server .... Nothing else.

>
OK, that does explain the need to use AttachDbFilenam e.
>

>>
>My MDF file is independant of sql server and located in App_Data of
>the application.
>>
>To show you as example, for the aspx files, i use this string for
>the same MDF file:
>"Data
>>

Source=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|\newres. mdf;Integrat
ed

>Security=True; User Instance=True"
> providerName="S ystem.Data.SqlC lient"

>
A classic ASP page will have absolutely NO concept of an App_Data
directory. It will have no idea of what to do with |DataDirectory| .
You MUST provide the actual physical path to the mdf file in the
AttachDbFilenam e attribute.
Again, the name of the database as seen by the server will never be
"mewres.mdf ". What needs to be used is the logical name of the database,
not its physical name.
>
Create an aspx page which connects to the database and query that
sysdatabases table again to get the database's logical name.
>
Another approach you might want to consider is creating a .Net web
service to provide data, etc. for your classic asp pages ... nah, this
will require rewriting the asp pages and if you're going to do that, you
might as well convert them to .Net.
>
>
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
>
>

[Bob Barrows [MVP]]

Re: error when connecting to sql server

Bart wrote:

Here are my next attempts:
>
First, i created an aspx to connect to the MDF file and did the
select again (select * from master..sysdata bases) to gain its logical
name: what i could see in a gridview was:
name
filename
----
------
master
c:\program files\sql server ...
model
....
tempdb
....
msdb
....
c:\inetpub\wwwr oot\reskrosql\A pp_Data\newres. mdf
c:\inetpub\wwwr oot\reskrosql\A pp_Data\newres. mdf
>
>
So the logical name is:
c:\inetpub\wwwr oot\reskrosql\A pp_Data\newres. mdf So i tried this
connectionstrin g (in global.asa)
Application("ne wres") = "Provider=SQLOL EDB; Data
Source=.\SQLEXP RESS;AttachDbFi lename=c:\inetp ub\wwwroot\resk rosql\App_Data\ newres.mdf;Inte grated
Security=True;U ser Instance=True"
>
and ... a new error: Provider error '80040e21'
>

You left out the Database attribute. I would try both "DATABASE=newre s" and
"DATABASE=c:\in etpub\wwwroot\r eskrosql\App_Da ta\newres.mdf"

If those don't work, then it's time for me to admit defeat and send you to a
SQL Server group. Try m.p.sqlserver.p rogramming.

--
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"

[Bart]

Re: error when connecting to sql server

I tried this:

Application("ne wres") = "Provider=SQLOL EDB;DATABASE=ne wres;Data
Source=.\SQLEXP RESS;AttachDbFi lename=c:\inetp ub\wwwroot\resk rosql\App_Data\ newres.mdf;Inte grated
Security=True;U ser Instance=True"

and this:

Application("ne wres") =
"Provider=SQLOL EDB;DATABASE=c: \inetpub\wwwroo t\reskrosql\App _Data\newres.md f;Data
Source=.\SQLEXP RESS;AttachDbFi lename=c:\inetp ub\wwwroot\resk rosql\App_Data\ newres.mdf;Inte grated
Security=True;U ser Instance=True"

but unfortuantely, same error.
I can't imagine i'm the first who want to access an MDF file with an asp
page ...
Anyway, thanks for your help.


"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcomschre ef in bericht
news:ulu9IiNbHH A.2552@TK2MSFTN GP06.phx.gbl...

Bart wrote:

>Here are my next attempts:
>>
>First, i created an aspx to connect to the MDF file and did the
>select again (select * from master..sysdata bases) to gain its logical
>name: what i could see in a gridview was:
>name
>filename
>----
> ------
>master
>c:\program files\sql server ...
>model
>....
>tempdb
>....
>msdb
>....
>c:\inetpub\www root\reskrosql\ App_Data\newres .mdf
>c:\inetpub\www root\reskrosql\ App_Data\newres .mdf
>>
>>
>So the logical name is:
>c:\inetpub\www root\reskrosql\ App_Data\newres .mdf So i tried this
>connectionstri ng (in global.asa)
>Application("n ewres") = "Provider=SQLOL EDB; Data
>Source=.\SQLEX PRESS;AttachDbF ilename=c:\inet pub\wwwroot\res krosql\App_Data \newres.mdf;Int egrated
>Security=True; User Instance=True"
>>
>and ... a new error: Provider error '80040e21'
>>

You left out the Database attribute. I would try both "DATABASE=newre s"
and "DATABASE=c:\in etpub\wwwroot\r eskrosql\App_Da ta\newres.mdf"
>
If those don't work, then it's time for me to admit defeat and send you to
a SQL Server group. Try m.p.sqlserver.p rogramming.
>
--
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"
>

[Bob Barrows [MVP]]

Re: error when connecting to sql server

Bart wrote:

I tried this:
>
Application("ne wres") = "Provider=SQLOL EDB;DATABASE=ne wres;Data
Source=.\SQLEXP RESS;AttachDbFi lename=c:\inetp ub\wwwroot\resk rosql\App_Data\ newres.mdf;Inte grated
Security=True;U ser Instance=True"
>
and this:
>
Application("ne wres") =
"Provider=SQLOL EDB;DATABASE=c: \inetpub\wwwroo t\reskrosql\App _Data\newres.md f;Data
Source=.\SQLEXP RESS;AttachDbFi lename=c:\inetp ub\wwwroot\resk rosql\App_Data\ newres.mdf;Inte grated
Security=True;U ser Instance=True"
>
but unfortuantely, same error.
I can't imagine i'm the first who want to access an MDF file with an
asp page ...

:-)
Let your imagination soar!

Sorry i could not help. If you get an answer form the sql server group, we
would really appreciate if you came back and posted the resolution here.

--
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"

[Bart]

Re: error when connecting to sql server

.... if i find the solution ...

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcomschre ef in bericht
news:OF8iAXTbHH A.4140@TK2MSFTN GP06.phx.gbl...

Bart wrote:

>I tried this:
>>
>Application("n ewres") = "Provider=SQLOL EDB;DATABASE=ne wres;Data
>Source=.\SQLEX PRESS;AttachDbF ilename=c:\inet pub\wwwroot\res krosql\App_Data \newres.mdf;Int egrated
>Security=True; User Instance=True"
>>
>and this:
>>
>Application("n ewres") =
>"Provider=SQLO LEDB;DATABASE=c :\inetpub\wwwro ot\reskrosql\Ap p_Data\newres.m df;Data
>Source=.\SQLEX PRESS;AttachDbF ilename=c:\inet pub\wwwroot\res krosql\App_Data \newres.mdf;Int egrated
>Security=True; User Instance=True"
>>
>but unfortuantely, same error.
>I can't imagine i'm the first who want to access an MDF file with an
>asp page ...

>
:-)
Let your imagination soar!
>
Sorry i could not help. If you get an answer form the sql server group, we
would really appreciate if you came back and posted the resolution here.
>
--
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"
>