error when connecting to sql server

am 22.03.2007 09:16:53 von 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

Re: error when connecting to sql server

am 22.03.2007 13:52:26 von reb01501

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.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).a spx)
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).a spx)

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

Re: error when connecting to sql server

am 22.03.2007 13:52:26 von reb01501

Re: error when connecting to sql server

am 22.03.2007 16:47:01 von 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]" schreef in bericht
news:uyAOhCIbHHA.4176@TK2MSFTNGP02.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.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).a spx)
> 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).a spx)
>
>
>
>
> --
> 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.
>
>

Re: error when connecting to sql server

am 22.03.2007 16:47:01 von bart

Re: error when connecting to sql server

am 22.03.2007 18:09:22 von reb01501

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

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

Re: error when connecting to sql server

am 22.03.2007 18:09:22 von reb01501

Re: error when connecting to sql server

am 22.03.2007 18:38:27 von 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]" schreef in bericht
news:ORaxFSKbHHA.2552@TK2MSFTNGP06.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\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.
>
>
> --
> 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.
>
>

Re: error when connecting to sql server

am 22.03.2007 18:38:27 von bart

Re: error when connecting to sql server

am 22.03.2007 18:50:22 von reb01501

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

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

Re: error when connecting to sql server

am 22.03.2007 18:50:22 von reb01501

Re: error when connecting to sql server

am 22.03.2007 21:14:10 von 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\resk rosql\App_Data\newres.mdf;Integrated
Security=True;User Instance=True"

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

i'm getting crazy.

"Bob Barrows [MVP]" schreef in bericht
news:eGie$oKbHHA.4872@TK2MSFTNGP03.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..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.
>
>
> --
> 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.
>
>

Re: error when connecting to sql server

am 22.03.2007 21:14:10 von bart

Re: error when connecting to sql server

am 23.03.2007 00:19:31 von reb01501

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..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\resk rosql\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.

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

Re: error when connecting to sql server

am 23.03.2007 00:19:31 von reb01501

Re: error when connecting to sql server

am 23.03.2007 08:37:33 von bart

I tried this:

Application("newres") = "Provider=SQLOLEDB;DATABASE=newres;Data
Source=.\SQLEXPRESS;AttachDbFilename=c:\inetpub\wwwroot\resk rosql\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\resk rosql\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]" schreef in bericht
news:ulu9IiNbHHA.2552@TK2MSFTNGP06.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..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\resk rosql\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.
>
> --
> 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"
>

Re: error when connecting to sql server

am 23.03.2007 08:37:33 von bart

Re: error when connecting to sql server

am 23.03.2007 11:26:48 von reb01501

Bart wrote:
> I tried this:
>
> Application("newres") = "Provider=SQLOLEDB;DATABASE=newres;Data
> Source=.\SQLEXPRESS;AttachDbFilename=c:\inetpub\wwwroot\resk rosql\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\resk rosql\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.

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

Re: error when connecting to sql server

am 23.03.2007 11:26:48 von reb01501

Re: error when connecting to sql server

am 23.03.2007 18:59:40 von bart

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

"Bob Barrows [MVP]" schreef in bericht
news:OF8iAXTbHHA.4140@TK2MSFTNGP06.phx.gbl...
> Bart wrote:
>> I tried this:
>>
>> Application("newres") = "Provider=SQLOLEDB;DATABASE=newres;Data
>> Source=.\SQLEXPRESS;AttachDbFilename=c:\inetpub\wwwroot\resk rosql\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\resk rosql\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.
>
> --
> 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"
>

Re: error when connecting to sql server

am 23.03.2007 18:59:40 von bart