SQL Server Performance

Connection string woes

Discussion in 'SQL Server 2005 General Developer Questions' started by kreplech, Oct 28, 2009.

  1. kreplech New Member

    hello all,
    i'm having an issue with connection strings (sql server 2005 on windows server 2003). i should mention that, since I am currently mid-migration, there are two instances of sql on the sever: 2000 (in production) and 2005 (currently setting up). the tech before me named the 2000 sql instance the same as the windows server... maybe that's good... just seems weird to me. anyway....
    connecting from ASP and ASP.NET works just fine using:Data Source=serversqlinstance;Initial Catalog=database;Persist Security Info=True;User ID=user;Password=password
    " providerName="System.Data.SqlClient
    However, I have to use tcp to connect from Excel 2003 like this:
    Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=user;Initial Catalog=database;Data Source=tcp:serversqlinstance,1433;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=blahblah;Use Encryption for Data=False;Tag with column collation when possible=False
    shared memory is enabled - i can't figure out why i can't use it.
    just looking for an explanation. any information you can provide will be... informative!
    thanks very much,
    k
  2. atulmar New Member

    1. It doesn't matter whether the shared memory is enabled. You are using TCP for connection. Is TCP enabled?
    2. What error do you see?
  3. Adriaan New Member

    If the instance name is the same as the server name, you can drop the "sqlinstance" part of the data source:
    Data Source=serversqlinstance
  4. kreplech New Member

    Thanks Atulmar and Adriaan.
    Let me clarify.
    I can connect using tcp. I just find it odd that I have to use:
    Data Source=tcp:serversqlinstance,1433
    Instead of:
    Data Source=serversqlinstance
    The latter works perfectly fine when connecting from web applications (.NET, classic ASP). The former is necessary when connecting from Microsoft applications Excel and Access only since I've moved to SQL 2005.
    The error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
    Also: I cannot connect to the database remotely using Management Studio. I receive the error:
    Cannot connect to SERVERSQLINSTANCE... a network-related or instance-specific error occurred while establishing a connection to SQL server. the server was not found or was not accessible. verify that the instance name is correct and that sql server is configured to allow remote connections.
    sql server IS configured to allow remote connections.
    any ideas?
  5. kreplech New Member

    I CAN connect using Management Studio if I specify the port as so:
    SERVER/SQLINSTANCE,1433
    Is this normal?
  6. kreplech New Member

    nevermind.
    management studio AND microsoft applications were actually connecting to the 2000 instance when using
    tcp:SERVERSQLINSTANCE,1433 - despite having specified the sql instance.
    this is all VERY strange. i CANNOT connect to sql 2005 remotely from either management studio or microsoft applications.
    i'm confused.
  7. bseefelt New Member

    The default instance listens on port 1433. If connecting to port 1433 gets you into SQL 2000, then SQL 2000 was installed as the default (unnamed) instance.
    Then SQL 2005 must be a named instance. So your data source string should be just serverinstancename
    Try connecting with management studio to just serverinstance and verify you connect to the 2005 instance. You will be able to tell by the version number next to the server name if it connects, it will 9.x.x instead of 8.x.x
  8. kreplech New Member

    thanks for the quick reply Beth.
    unfortunately serverinstance produces the error:
    Cannot connect to SERVERSQLINSTANCE... a network-related or instance-specific error occurred while establishing a connection to SQL server. the server was not found or was not accessible. verify that the instance name is correct and that sql server is configured to allow remote connections.

Share This Page