none
Connecting to SQL Server 2008 using ADO/ODBC

    Question

  • Trying to connect to New Sql Server 2008 with VB6 using ADO/OBDC  obj.open command  worked ok with Sql2000 & Sql2005 express.
    Retruns error -2147467259.   which says unknown error ... running Microsoft SQL Server ODBC Driver Version 03.85.1132

    Found this same issue when running ODBC [System DSN]  if using the option Dynamic Determine Port.
    When i turn this off and enter the port address it connects ok.

    Is this a MDAC issue that i have to upgrade or is it something set on the Sqlserver2008
    Thursday, September 10, 2009 10:29 PM

Answers

  • Hi Barray,
    By default, SQL Client always connect to default instance of SQL Server with port number 1433. It is why you client can work well with SQL2000/2005. But if you config the default instance of SQL Server to use dynamic port, SQL Client must retriever right port number by quering SQL Server  Browser(port number 1434).

    So, I believe your SQL Server doesn't start up SQL Server Browser Service(http://msdn.microsoft.com/en-us/library/ms181087.aspx), please enable it with SQL Server Configuration Manager.
    • Marked as answer by Chao SUEditor Saturday, September 12, 2009 4:12 AM
    • Unmarked as answer by Chao SUEditor Saturday, September 12, 2009 4:12 AM
    • Proposed as answer by Chao SUEditor Saturday, September 12, 2009 4:12 AM
    • Marked as answer by Chao SUEditor Saturday, September 12, 2009 4:13 AM
    Saturday, September 12, 2009 4:11 AM
    Answerer

All replies

  • If it is simple connect that worked in Sql2000 and Sql2005 it should work with Sql2008 also. Can you please share some sample code and how you created the DSN (also please specify the OS you are using)


    This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, September 11, 2009 3:58 PM
    Moderator
  • Can you try creating a DSN using the "SQL Native Client" ODBC driver? I expect it will fail to connect as well, and if it does, can you give us the full error message? Also, check the SQL Server errorlog for anything that might indicate connectivity failure.
    This post is provided 'as is' and confers no express or implied warranties or rights.
    Friday, September 11, 2009 4:36 PM
    Moderator
  • Hi Barray,
    By default, SQL Client always connect to default instance of SQL Server with port number 1433. It is why you client can work well with SQL2000/2005. But if you config the default instance of SQL Server to use dynamic port, SQL Client must retriever right port number by quering SQL Server  Browser(port number 1434).

    So, I believe your SQL Server doesn't start up SQL Server Browser Service(http://msdn.microsoft.com/en-us/library/ms181087.aspx), please enable it with SQL Server Configuration Manager.
    • Marked as answer by Chao SUEditor Saturday, September 12, 2009 4:12 AM
    • Unmarked as answer by Chao SUEditor Saturday, September 12, 2009 4:12 AM
    • Proposed as answer by Chao SUEditor Saturday, September 12, 2009 4:12 AM
    • Marked as answer by Chao SUEditor Saturday, September 12, 2009 4:13 AM
    Saturday, September 12, 2009 4:11 AM
    Answerer