Results 1 to 7 of 7

Thread: SQL 2000 Named Instance Issue

  1. #1
    Join Date
    Jan 2007
    Posts
    4

    SQL 2000 Named Instance Issue

    Hello, can anyone help with this question? I have a SQL Server 2000 machine with both a default (SQL4) and named instance(SQL4\CONTROL). They are both running in mixed mode, and the named instance is set up with TCP enabled (along with named pipes) on port 1434, while the default is still on 1433. Here's the problem - I can access the named instance perfectly through Enterprise Manager, Query Analyzer, etc, but when I try to connect to it through any .ASP page I get the default instance! For example, if I use a database in the connection string that is also in the default instance, such as a system db, then the page works. If I try to connect with a DB in the connectin string that is only in the named instance, then I get the following error:

    Microsoft OLE DB Provider for SQL Server (0x80004005)
    Cannot open database requested in login 'LogHB'. Login fails.
    /MDF_Prod_Rprts/Test.asp, line 31


    Here's the connection string I'm using, which I know works, because I use it in all my pages:

    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''

    Dim cn

    'Create a connection object
    Set cn = Server.CreateObject("ADODB.Connection")

    'Connect using the OLE DB provider for SQL Server - SQLOLEDB
    cn.ConnectionString = "PROVIDER=SQLOLEDB" & _
    ";SERVER=*******" & _
    ";UID=******" & _
    ";PWD=*******" & _
    ";Trusted_Connection=True" & _
    ";DATABASE=LogHB"
    cn.ConnectionTimeout = 120
    cn.Open

    Set rs = Server.CreateObject("ADODB.Recordset")

    ' Associate the Recordset with the open connection
    rs.ActiveConnection = cn

    ' Associate the Recordset with the open connection
    rs.ActiveConnection = cn

    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''

    Why won't the .ASP page recognize the named instance...???

    Thanks in advance for any help
    Last edited by AJP; 01-24-2007 at 10:06 AM.

  2. #2
    Join Date
    Apr 2006
    Posts
    30
    may need to configure with sql client network utility on web server, since instance isn't listening on port 1433.

    may also want to wipe that password outta yer post there...

  3. #3
    Join Date
    Sep 2002
    Posts
    5,938
    Don't use port 1434, it's sql server management port.

  4. #4
    Join Date
    Jan 2007
    Posts
    4
    Thanks for the suggestions russellb and rmaio. Unfortunately the problem still exists.

  5. #5
    Join Date
    Apr 2006
    Posts
    30
    rmiao is right. did u change port and config on web server?

  6. #6
    Join Date
    Jan 2007
    Posts
    4
    I changed the port on the SQL Server but I don't think that I'm communicating over it - Telnet <ip address> <port> returns an error instead of an open port... SQL Server isn't installed on the web server so I don't have sql client utility on it. The funny thing is that this used to work with this config... Here's the whole background:This used to be part of a cluster. The current default instance was on a virtual server using port 1434. The virtual server had the named instance installed as well. I removed clustering some time back due to problems with my Dell 220s array and created stand alone servers out of it. Ever since the two servers that were clustered have worked fine with the excepetion of the ASP pages only being able to contact the default instance...

  7. #7
    Join Date
    Jan 2007
    Posts
    4
    I've gotten around this problem by adding the port number to my connection string:
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''

    Dim cn

    'Create a connection object
    Set cn = Server.CreateObject("ADODB.Connection")

    'Connect using the OLE DB provider for SQL Server - SQLOLEDB
    cn.ConnectionString = "PROVIDER=SQLOLEDB" & _
    ";SERVER=*******, 1434" & _
    ";UID=******" & _
    ";PWD=*******" & _
    ";Trusted_Connection=True" & _
    ";DATABASE=LogHB"
    cn.ConnectionTimeout = 120
    cn.Open

    Set rs = Server.CreateObject("ADODB.Recordset")

    ' Associate the Recordset with the open connection
    rs.ActiveConnection = cn

    ' Associate the Recordset with the open connection
    rs.ActiveConnection = cn

    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •