Asp Forum - how to connect to MDF file with classic ASP? - microsoft.public.sqlserver.programming

Bart

3/23/2007 7:46:00 AM

Hi,

i have already posted this problem in an ASP newsgroup without succes, so i
try here.

I try to access within an cliassic ASP page an MDF file (newres.mdf) of sql
server express 2005 database which is located in
c:\inetpub\wwwroot\reskrosql\App_Data.
I get following error :

Provider 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")) 'error line
%>

global.asa:
----------
Sub Application_OnStart
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"

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

End Sub

Thanks for help
Bart

8 Answers

Andrew J. Kelly

3/23/2007 12:47:00 PM

You might want to have a look at some of these:

http://msdn2.microsoft.com/en-us/library/ms3... Express Overview
http://msdn2.microsoft.com/en-us/library/ms1... Comparing Express
with MSDE
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsse/html/sqlexpus...
User Instances
http://www.datamasker.com/SSE2005_Netw... Configuring Express
for Remote Access
https://blogs.msdn.com/sqlexpress/archive/2005/05/05/4...
Configuring Remote Access
http://www.kbalertz.com/Feedback_9... Configuring SQL2005 for
remote access
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/emsqlexc...
Embedding Express in Apps
http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&Disp...
SSMS Express
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/5...
Backing up Express

--
Andrew J. Kelly SQL MVP

"Bart" <b@sdq.dc> wrote in message
news:%23%23w0O9RbHHA.3584@TK2MSFTNGP02.phx.gbl...
> Hi,
>
> i have already posted this problem in an ASP newsgroup without succes, so
> i try here.
>
> I try to access within an cliassic ASP page an MDF file (newres.mdf) of
> sql server express 2005 database which is located in
> c:\inetpub\wwwroot\reskrosql\App_Data.
> I get following error :
>
> Provider 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")) 'error line
> %>
>
> global.asa:
> ----------
> Sub Application_OnStart
> 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"
>
>
> 'i also tried this:
> Sub Application_OnStart
> Application("newres") = "Provider=SQLOLEDB;DATABASE=newres;Data
> Source=.\SQLEXPRESS;AttachDbFilename=c:\inetpub\wwwroot\reskrosql\App_Data\newres.mdf;Integrated
> Security=True;User Instance=True"
>
> End Sub
>
> Thanks for help
> Bart
>
>
>

Bart

3/23/2007 6:25:00 PM

Thanks Andrew,

to be honest, i read all of them but could not find an example of a
connectionstring for ASP using AttachDbFilename

and i'm getting fed up to read and search for 2 days something that is not
even so particular. I get the impression that it's the first time someone
try this ...

What i noticed is that when i change the connectionstring with a false path,
like:
"Provider=SQLOLEDB;Data
Source=.\SQLEXPRESS;AttachDbFilename=c:\WRONGPATH;Integrated
Security=True;User Instance=True"

i still get the same error. Maybe the problem is elsewhere

Andrew J. Kelly

3/23/2007 8:17:00 PM

Well to be honest I am not sure if classic ASP can access SQL Express or
not. I don't mess with express much but the first link did say there was
some issues with earlier versions of MDAC and connecting under certain
conditions. Look here under Data Access section:
http://msdn2.microsoft.com/en-us/library/ms3...

In the below link it goes into great detail about connecting using
AttachDBFileName but not necessarily with classic ASP.
http://msdn2.microsoft.com/en-us/library/bb2...

Have you tried this with .net and or the SNAC driver to see if you can get
it to work at all yet?

--
Andrew J. Kelly SQL MVP

"Bart" <b@sdq.dc> wrote in message
news:epUZNiXbHHA.260@TK2MSFTNGP02.phx.gbl...
> Thanks Andrew,
>
> to be honest, i read all of them but could not find an example of a
> connectionstring for ASP using AttachDbFilename
>
> and i'm getting fed up to read and search for 2 days something that is not
> even so particular. I get the impression that it's the first time someone
> try this ...
>
> What i noticed is that when i change the connectionstring with a false
> path, like:
> "Provider=SQLOLEDB;Data
> Source=.\SQLEXPRESS;AttachDbFilename=c:\WRONGPATH;Integrated
> Security=True;User Instance=True"
>
> i still get the same error. Maybe the problem is elsewhere
>
>
>
>

Roger Wolter[MSFT]

3/23/2007 9:24:00 PM

AttachDBFilename should work from OLEDB or ODBC but the User instance clause
is only available from SQLClient - ADO.Net so it's not clear how much good
AttachDBFilename will do you.

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cp...

"Bart" <b@sdq.dc> wrote in message
news:epUZNiXbHHA.260@TK2MSFTNGP02.phx.gbl...
> Thanks Andrew,
>
> to be honest, i read all of them but could not find an example of a
> connectionstring for ASP using AttachDbFilename
>
> and i'm getting fed up to read and search for 2 days something that is not
> even so particular. I get the impression that it's the first time someone
> try this ...
>
> What i noticed is that when i change the connectionstring with a false
> path, like:
> "Provider=SQLOLEDB;Data
> Source=.\SQLEXPRESS;AttachDbFilename=c:\WRONGPATH;Integrated
> Security=True;User Instance=True"
>
> i still get the same error. Maybe the problem is elsewhere
>
>
>
>

Bart

3/23/2007 10:42:00 PM

Yes it works with asp.net.
Classic ASP works even when connecting a database in sqlexpress (not a mdf).
I use this stringconnection:
Sub Application_OnStart
Application("newresfull") = "Provider=SQLOLEDB;Data
Source=laptop\sqlexpress;Integrated Security=SSPI;Initial Catalog=test"
End Sub

I think it's simply impossible to access a mdf file with asp ....

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> schreef in bericht
news:%23WmHJhYbHHA.4000@TK2MSFTNGP02.phx.gbl...
> Well to be honest I am not sure if classic ASP can access SQL Express or
> not. I don't mess with express much but the first link did say there was
> some issues with earlier versions of MDAC and connecting under certain
> conditions. Look here under Data Access section:
> http://msdn2.microsoft.com/en-us/library/ms3...
>
>
> In the below link it goes into great detail about connecting using
> AttachDBFileName but not necessarily with classic ASP.
> http://msdn2.microsoft.com/en-us/library/bb2...
>
> Have you tried this with .net and or the SNAC driver to see if you can get
> it to work at all yet?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Bart" <b@sdq.dc> wrote in message
> news:epUZNiXbHHA.260@TK2MSFTNGP02.phx.gbl...
>> Thanks Andrew,
>>
>> to be honest, i read all of them but could not find an example of a
>> connectionstring for ASP using AttachDbFilename
>>
>> and i'm getting fed up to read and search for 2 days something that is
>> not even so particular. I get the impression that it's the first time
>> someone try this ...
>>
>> What i noticed is that when i change the connectionstring with a false
>> path, like:
>> "Provider=SQLOLEDB;Data
>> Source=.\SQLEXPRESS;AttachDbFilename=c:\WRONGPATH;Integrated
>> Security=True;User Instance=True"
>>
>> i still get the same error. Maybe the problem is elsewhere
>>
>>
>>
>>
>
>

Bart

3/23/2007 10:43:00 PM

Thanks, so the conclusion is:
it's impossible to access a MDF file with classic asp?

"Roger Wolter[MSFT]" <rwolter@online.microsoft.com> schreef in bericht
news:EF1406A3-C657-44F9-8FF6-3F1D032175E1@microsoft.com...
> AttachDBFilename should work from OLEDB or ODBC but the User instance
> clause is only available from SQLClient - ADO.Net so it's not clear how
> much good AttachDBFilename will do you.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cp...
>
> "Bart" <b@sdq.dc> wrote in message
> news:epUZNiXbHHA.260@TK2MSFTNGP02.phx.gbl...
>> Thanks Andrew,
>>
>> to be honest, i read all of them but could not find an example of a
>> connectionstring for ASP using AttachDbFilename
>>
>> and i'm getting fed up to read and search for 2 days something that is
>> not even so particular. I get the impression that it's the first time
>> someone try this ...
>>
>> What i noticed is that when i change the connectionstring with a false
>> path, like:
>> "Provider=SQLOLEDB;Data
>> Source=.\SQLEXPRESS;AttachDbFilename=c:\WRONGPATH;Integrated
>> Security=True;User Instance=True"
>>
>> i still get the same error. Maybe the problem is elsewhere
>>
>>
>>
>>
>

Roger Wolter[MSFT]

3/23/2007 11:48:00 PM

Depends on how you define impossible. You can't access it using the
connection string you are trying to use because only ADO.Net understands the
Userinstance parameter. You can definitely attach it to SQL Express and
access it the way you would any other database. You can also access it
through the named pipe name but that's not something I would recommend for a
production system. More information on user instances is available here:

http://msdn2.microsoft.com/en-us/library/bb2...

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cp...

"Bart" <b@sdq.dc> wrote in message
news:%23kiVRyZbHHA.4832@TK2MSFTNGP02.phx.gbl...
> Thanks, so the conclusion is:
> it's impossible to access a MDF file with classic asp?
>
> "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> schreef in bericht
> news:EF1406A3-C657-44F9-8FF6-3F1D032175E1@microsoft.com...
>> AttachDBFilename should work from OLEDB or ODBC but the User instance
>> clause is only available from SQLClient - ADO.Net so it's not clear how
>> much good AttachDBFilename will do you.
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cp...
>>
>> "Bart" <b@sdq.dc> wrote in message
>> news:epUZNiXbHHA.260@TK2MSFTNGP02.phx.gbl...
>>> Thanks Andrew,
>>>
>>> to be honest, i read all of them but could not find an example of a
>>> connectionstring for ASP using AttachDbFilename
>>>
>>> and i'm getting fed up to read and search for 2 days something that is
>>> not even so particular. I get the impression that it's the first time
>>> someone try this ...
>>>
>>> What i noticed is that when i change the connectionstring with a false
>>> path, like:
>>> "Provider=SQLOLEDB;Data
>>> Source=.\SQLEXPRESS;AttachDbFilename=c:\WRONGPATH;Integrated
>>> Security=True;User Instance=True"
>>>
>>> i still get the same error. Maybe the problem is elsewhere
>>>
>>>
>>>
>>>
>>
>
>

Bart

3/24/2007 9:46:00 AM

"You can definitely attach it to SQL Express and
access it the way you would any other database."

Do you mean: starting sql server management studio and there attaching the
MDF file?
But then i loose the benefit of having a MDF file which can be
administratted independantly of sql server ...

About named pipe name, i installed sqlexpress as "default" (not named
instance).

"Roger Wolter[MSFT]" <rwolter@online.microsoft.com> schreef in bericht
news:O16huWabHHA.596@TK2MSFTNGP06.phx.gbl...
> Depends on how you define impossible. You can't access it using the
> connection string you are trying to use because only ADO.Net understands
> the Userinstance parameter. You can definitely attach it to SQL Express
> and access it the way you would any other database. You can also access
> it through the named pipe name but that's not something I would recommend
> for a production system. More information on user instances is available
> here:
>
> http://msdn2.microsoft.com/en-us/library/bb2...
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cp...
>
> "Bart" <b@sdq.dc> wrote in message
> news:%23kiVRyZbHHA.4832@TK2MSFTNGP02.phx.gbl...
>> Thanks, so the conclusion is:
>> it's impossible to access a MDF file with classic asp?
>>
>> "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> schreef in bericht
>> news:EF1406A3-C657-44F9-8FF6-3F1D032175E1@microsoft.com...
>>> AttachDBFilename should work from OLEDB or ODBC but the User instance
>>> clause is only available from SQLClient - ADO.Net so it's not clear how
>>> much good AttachDBFilename will do you.
>>>
>>> --
>>> This posting is provided "AS IS" with no warranties, and confers no
>>> rights.
>>> Use of included script samples are subject to the terms specified at
>>> http://www.microsoft.com/info/cp...
>>>
>>> "Bart" <b@sdq.dc> wrote in message
>>> news:epUZNiXbHHA.260@TK2MSFTNGP02.phx.gbl...
>>>> Thanks Andrew,
>>>>
>>>> to be honest, i read all of them but could not find an example of a
>>>> connectionstring for ASP using AttachDbFilename
>>>>
>>>> and i'm getting fed up to read and search for 2 days something that is
>>>> not even so particular. I get the impression that it's the first time
>>>> someone try this ...
>>>>
>>>> What i noticed is that when i change the connectionstring with a false
>>>> path, like:
>>>> "Provider=SQLOLEDB;Data
>>>> Source=.\SQLEXPRESS;AttachDbFilename=c:\WRONGPATH;Integrated
>>>> Security=True;User Instance=True"
>>>>
>>>> i still get the same error. Maybe the problem is elsewhere
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>

microsoft.public.sqlserver.programming

how to connect to MDF file with classic ASP?

Bart

Andrew J. Kelly

Bart

Andrew J. Kelly

Roger Wolter[MSFT]

Bart

Bart

Roger Wolter[MSFT]

Bart

x Login to ForumsZone