Trouble connecting to SQL 2008 DB with ASP

Discussion in 'Site Programming, Development and Design' started by kronos, Jan 11, 2010.

  1. I'm having trouble connecting to my database using the connection string. I can connect using the sql 2008 express management studio just fine, and have verified that the DB is there an functioning. However when I try to connect with an ASP page I get the following error "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." Here is the connection string I'm using with surrounding code
    <% Response.Expires = 0
    Dim objConn, objRS, strQ
    Dim strConnection
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "Data Source=tcp:s01.Winhost.com;Initial Catalog=DB_3086_products;User ID=DB_3086_products_user;Password=*****;Integrated Security=False;"
    Set objRS = Server.CreateObject("ADODB.Recordset")
    Set objRS.ActiveConnection = objConn %>
    The **** above is the actual password.
    Everything works fine when connecting to an access DB, but changing the connection string to attempt to connect to the SQL DB just keeps giving me this error. Please help I've been trying to fix this for many hours with no luck. What can I do?
    Thanks
     
    Last edited by a moderator: Oct 14, 2015
  2. Ray

    Ray

    Try removing tcp: from tcp:s01.Winhost.com. So it should only have

    s01.Winhost.com
     
    Last edited by a moderator: Oct 14, 2015
  3. Still no luck. Same error.:(
     
  4. Ray

    Ray

    Whats the full and exact error message? The error message should also have some kind of stack trace attached to it.
     
  5. 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.

    /conn.inc, line 5


    that is the full error i'm getting. Line 5 in the code is the one with the connection string.

    <% Response.Expires = 0
    Dim objConn, objRS, strQ
    Dim strConnection
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "Data Source=tcp:s01.Winhost.com;Initial Catalog=DB_3086_products;User ID=DB_3086_products_user;Password=*****;Integrated Security=False;"
    Set objRS = Server.CreateObject("ADODB.Recordset")
    Set objRS.ActiveConnection = objConn %>
     
    Last edited by a moderator: Oct 14, 2015
  6. Ray

    Ray

    I don't see a provider defined in your connection string. Try using this line instead.

    objConn.Open "Provider=SQLDB; Data Source=tcp:s01.Winhost.com;Initial Catalog=DB_3086_products;User ID=DB_3086_products_user;Password=*****;Integrated Security=False;"

    If this doesn't work modify the connection string without the tcp: just like this...

    objConn.Open "Provider=SQLDB; Data Source=s01.Winhost.com;Initial Catalog=DB_3086_products;User ID=DB_3086_products_user;Password=*****;Integrated Security=False;"

    Just to make sure, make sure you replace Password=****** with the actual password of the database.
     
    Last edited by a moderator: Oct 14, 2015
  7. FredC

    FredC Winhost Staff

  8. This gives me this error both ways

    ADODB.Connection error '800a0e7a'

    Provider cannot be found. It may not be properly installed.

    /conn.inc, line 5

    Is it possible my DB is not set up right? This is my first attempt to connect to it. I can see it with the sql 2008 express management studio connecting to your server, but no luck with the ASP pages.
     
  9. Ray

    Ray

    Show me a new updated version of your connection string.
     
  10. Tried both

    <% Response.Expires = 0
    Dim objConn, objRS, strQ
    Dim strConnection
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "Provider=SQLDB; Data Source=s01.Winhost.com;Initial Catalog=DB_3086_products;User ID=DB_3086_products_user;Password=****;Integrated Security=False;"
    Set objRS = Server.CreateObject("ADODB.Recordset")
    Set objRS.ActiveConnection = objConn %>

    And

    <% Response.Expires = 0
    Dim objConn, objRS, strQ
    Dim strConnection
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "Provider=SQLDB; Data Source=tcp:s01.Winhost.com;Initial Catalog=DB_3086_products;User ID=DB_3086_products_user;Password=****;Integrated Security=False;"
    Set objRS = Server.CreateObject("ADODB.Recordset")
    Set objRS.ActiveConnection = objConn %>

    As suggested.

    Password is filled in for the actual code.
     
    Last edited by a moderator: Oct 14, 2015
  11. Ray

    Ray

    Try this I tested this with my database and it worked.

    objConn.Open "Provider=SQLDB; Data Source=s01.Winhost.com;Initial Catalog=DB_3086_products;User ID=DB_3086_products_user;Password=****;Connect Timeout=15;Network Library=dbmssocn;"
     
    Last edited by a moderator: Oct 14, 2015
  12. OK I tried that and get the same error

    ADODB.Connection error '800a0e7a'

    Provider cannot be found. It may not be properly installed.

    /conn-test.asp, line 7


    I've even tried a stripped down ASP page that does nothing but connect to the DB

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <% Response.Expires = 0
    Dim objConn, objRS, strQ
    Dim strConnection
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "Provider=SQLDB; Data Source=s01.Winhost.com;Initial Catalog=DB_3086_products;User ID=DB_3086_products_user;Password=******;Connect Timeout=15;Network Library=dbmssocn;"
    Set objRS = Server.CreateObject("ADODB.Recordset")
    Set objRS.ActiveConnection = objConn %>
    <head>
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>

    <body>
    <%

    objRS.Close
    Set objRS = Nothing
    objConn.Close
    Set objConn = Nothing
    %>
    </body>
    </html>

    Same error. Could it be something else in the code causing it? Could something be wrong with my DB? How can I check? This all worked great on a previous server with MYSQL, but I can not get it to work with SQL 2008 for the life of me.
     
    Last edited by a moderator: Oct 14, 2015
  13. Ray

    Ray

    I think you still have some coding issue. When I looked at your connection string more closely you have "Provider=SQLDB" it should be "Provider=SQLOLEDB". If you can connect to our SQL server with SSMS, then that means that the database in our SQL server is setup correctly. You should also be able to connect with your application.
     
  14. Success! That did it. Thanks a lot!! :)
     
  15. Cool, glad you got it sorted out.
     
  16. Sorry to bump an ancient thread, but I have to let it be known that the above solution STILL works after all these years for my SQL Server 2014 database: i.e. adding "Provider=SQLOLEDB", removing "tcp:", removing "Integrated Security=False;", and adding "Connect Timeout=15;Network Library=dbmssocn;". So my questions are (1) is there a reason the default string provided by Winhost doesn't work? And (2) does the default string work for anybody, and how different are their database configurations?
     
  17. Elshadriel

    Elshadriel Winhost Staff

    It depends on the provider your code is using (e.g. OLEDB or ODBC). The connection string we provide in the Control Panel is for an ODBC connection/.NET. A list can be found here.
     

Share This Page