error when connecting to sql server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bart - 22 Mar 2007 09:16 GMT

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

Reply to this Message

Bob Barrows [MVP] - 22 Mar 2007 13:52 GMT

> Hi,
>
[quoted text clipped - 16 lines]
> 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)

Signature

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.

Reply to this Message

Bart - 22 Mar 2007 16:47 GMT

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"

>> Hi,
>>
[quoted text clipped - 30 lines]
> using the AttachDbFilename attribute
> (http://msdn2.microsoft.com/en-us/library/aa198298(SQL.80).aspx)

Reply to this Message

Bob Barrows [MVP] - 22 Mar 2007 18:09 GMT

> Hi Bob,
>
[quoted text clipped - 11 lines]
> 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.

Signature

Reply to this Message

Bart - 22 Mar 2007 18:38 GMT

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

>> Hi Bob,
>>
[quoted text clipped - 30 lines]
> likely, the name is as i showed in my example. Look at the result of
> "select * from master..sysdatabases" to verify.

Reply to this Message

Bob Barrows [MVP] - 22 Mar 2007 18:50 GMT

> Bob,
>
[quoted text clipped - 3 lines]
> 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.

Signature

Reply to this Message

Bart - 22 Mar 2007 21:14 GMT

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,
>>
[quoted text clipped - 33 lines]
> will require rewriting the asp pages and if you're going to do that, you
> might as well convert them to .Net.

Reply to this Message

Bob Barrows [MVP] - 23 Mar 2007 00:19 GMT

> Here are my next attempts:
>
[quoted text clipped - 24 lines]
>
> 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.

Signature

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"

Reply to this Message

Bart - 23 Mar 2007 08:37 GMT

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.

>> Here are my next attempts:
>>
[quoted text clipped - 30 lines]
> 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.

Reply to this Message

Bob Barrows [MVP] - 23 Mar 2007 11:26 GMT

> I tried this:
>
[quoted text clipped - 12 lines]
> 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.

Signature

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"

Reply to this Message

Bart - 23 Mar 2007 18:59 GMT

... if i find the solution ...

>> I tried this:
>>
[quoted text clipped - 18 lines]
> 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.

Reply to this Message

Webmaster Forum / ASP / Database Access / March 2007

error when connecting to sql server