error when connecting to sql server

Discussion in 'ASP General' started by Bart, Mar 22, 2007.

  1. Bart

    Bart Guest

    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
     
    Bart, Mar 22, 2007
    #1
    1. Advertising

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




    --
    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], Mar 22, 2007
    #2
    1. Advertising

  3. Bart

    Bart Guest

    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:...
    > 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).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)
    >
    >
    >
    >
    > --
    > 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, Mar 22, 2007
    #3
  4. 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.
     
    Bob Barrows [MVP], Mar 22, 2007
    #4
  5. Bart

    Bart Guest

    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:...
    > 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.
    >
    >
     
    Bart, Mar 22, 2007
    #5
  6. 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.
     
    Bob Barrows [MVP], Mar 22, 2007
    #6
  7. Bart

    Bart Guest

    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]" <> schreef in bericht
    news:eGie$...
    > 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.
    >
    >
     
    Bart, Mar 22, 2007
    #7
  8. 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\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.

    --
    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], Mar 22, 2007
    #8
  9. Bart

    Bart Guest

    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]" <> schreef in bericht
    news:...


    > 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\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.
    >
    > --
    > 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, Mar 23, 2007
    #9
  10. Bart wrote:
    > 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.

    --
    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], Mar 23, 2007
    #10
  11. Bart

    Bart Guest

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

    "Bob Barrows [MVP]" <> schreef in bericht
    news:...
    > Bart wrote:
    >> 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.
    >
    > --
    > 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, Mar 23, 2007
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?YWJiYXMgYWRhbmFsxLE=?=

    RE: Error connecting to sql server thro .net

    =?Utf-8?B?YWJiYXMgYWRhbmFsxLE=?=, Aug 6, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    361
    =?Utf-8?B?YWJiYXMgYWRhbmFsxLE=?=
    Aug 6, 2004
  2. Ann Marinas
    Replies:
    12
    Views:
    749
    Guest
    May 5, 2005
  3. hfk0
    Replies:
    2
    Views:
    21,683
  4. Don
    Replies:
    0
    Views:
    444
  5. =?Utf-8?B?TmVpbCBQYWRkb2Nr?=

    Connecting to SQL 2000 database with SQL 2005 tools installed

    =?Utf-8?B?TmVpbCBQYWRkb2Nr?=, May 2, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    408
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
    May 2, 2007
Loading...

Share This Page