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.CreateObject("ADODB.Connection")
objdc.Open(application("newres"))
%>

global.asa:
----------
Sub Application_OnStart
Application("newres") = "Provider=SQLOLEDB; Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\newres.mdf;Integrated
Security=True;User Instance=True"
End Sub

Thanks for hrlp
Bart

 

[Bob Barrows [MVP]]

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.CreateObject("ADODB.Connection")
objdc.Open(application("newres"))
%>

global.asa:
----------
Sub Application_OnStart
Application("newres") = "Provider=SQLOLEDB; Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\newres.mdf;Integrat
ed
Security=True;User Instance=True"
End Sub

1. Why are you using the AttachDbFilename 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/library/aa198298(SQL.80).aspx)
3. You must specify the database name using the DATABASE attribute when
using the AttachDbFilename attribute
(http://msdn2.microsoft.com/en-us/library/aa198298(SQL.80).aspx)

 

[Bart]

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\sqlexpress" with windows
authentification.

So i tried this with attribute Database but doesn't work:
"Provider=SQLOLEDB; Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\newres.mdf;Integrated
Security=True;User Instance=True; Database=mewres.mdf"

 

[Bob Barrows [MVP]]

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\sqlexpress" with
windows authentification.

So i tried this with attribute Database but doesn't work:
"Provider=SQLOLEDB; Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\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 AttachDbFilename. Why not
just use:

"Provider=SQLOLEDB; Data
Source=Servername\SQLEXPRESS;Integrated
Security=True;User 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..sysdatabases" to verify.

 

[Bart]

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..sysdatabases".
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=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\newres.mdf;Integrated
Security=True;User Instance=True"
providerName="System.Data.SqlClient"

That's why i also use AttachDbFilename ...

I tried this now, but still same error:

Application("newres") = "Provider=SQLOLEDB; Data
Source=Servername\SQLEXPRESS;Integrated Security=True;User Instance=True;
Database=mewres.mdf"
....

 

[Bob Barrows [MVP]]

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..sysdatabases".
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 AttachDbFilename.

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=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\newres.mdf;Integrat
ed
Security=True;User Instance=True"
providerName="System.Data.SqlClient"

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
AttachDbFilename 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.

 

[Bart]

Here are my next attempts:

First, i created an aspx to connect to the MDF file and did the select again
(select * from master..sysdatabases) 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\wwwroot\reskrosql\App_Data\newres.mdf
c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf


So the logical name is: c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf
So i tried this connectionstring (in global.asa)
Application("newres") = "Provider=SQLOLEDB; Data
Source=.\SQLEXPRESS;AttachDbFilename=c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf;Integrated
Security=True;User Instance=True"

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

i'm getting crazy.

 

[Bob Barrows [MVP]]

Here are my next attempts:

First, i created an aspx to connect to the MDF file and did the
select again (select * from master..sysdatabases) 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\wwwroot\reskrosql\App_Data\newres.mdf
c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf


So the logical name is:
c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf So i tried this
connectionstring (in global.asa)
Application("newres") = "Provider=SQLOLEDB; Data
Source=.\SQLEXPRESS;AttachDbFilename=c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf;Integrated
Security=True;User Instance=True"

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

You left out the Database attribute. I would try both "DATABASE=newres" and
"DATABASE=c:\inetpub\wwwroot\reskrosql\App_Data\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.programming.

 

[Bart]

I tried this:

Application("newres") = "Provider=SQLOLEDB;DATABASE=newres;Data
Source=.\SQLEXPRESS;AttachDbFilename=c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf;Integrated
Security=True;User Instance=True"

and this:

Application("newres") =
"Provider=SQLOLEDB;DATABASE=c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf;Data
Source=.\SQLEXPRESS;AttachDbFilename=c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf;Integrated
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 ...
Anyway, thanks for your help.


"Bob Barrows [MVP]" <[email protected]> schreef in bericht

 

[Bob Barrows [MVP]]

I tried this:

Application("newres") = "Provider=SQLOLEDB;DATABASE=newres;Data
Source=.\SQLEXPRESS;AttachDbFilename=c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf;Integrated
Security=True;User Instance=True"

and this:

Application("newres") =
"Provider=SQLOLEDB;DATABASE=c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf;Data
Source=.\SQLEXPRESS;AttachDbFilename=c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf;Integrated
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.

 

[Bart]

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

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"