none
Problem while connecting with SSMS

    Question

  • Hi

    I m getting following error while try to connect with Sql Server Management Studio(ver 2008)

    "Unable to apply connection settings. the detail error message is: 'Ansi_Nulls' is not a recgnized SET"

    but i am able to see database dropdown, but not able to make query on this database.

    Please help me

    Kapil
    Thursday, August 20, 2009 6:04 PM

Answers

  • Hmm, I'm not sure what's going on.  I was able to connect to both master and testdb1 using SSMS.  Here's what I did:

    1. cancel out of the first connection dialog that pops up.
    2. click on the New Query button from the toolbar (upper left)
    3. entered
           Server name: full servername
           Login: just the username (no @...)
    4. Under Options, Connect to Database, I typed TestDb1
    5. Hit connect.

    Let's give it a try.  Hopefully it will work.  If not I'll have to take a look at it tomorrow morning (it's very late here).

    Stan
    Program Manager, SQL Azure
    • Marked as answer by Kaps Pune Saturday, August 22, 2009 6:46 PM
    Friday, August 21, 2009 9:55 AM

All replies

  • Hi Kapil,
      Please be aware that SSMS is not supported with SQL Azure yet. However, you can use the following workaround for the time being:

    1. Click Cancel in Connect to Server Dialog
    2. Click New Query which will bring up the new connection dialog.
    3. Enter your credentials. By default, you will connect to Master database. If you want to connect to specific database, click options and enter the database name. Please be aware that USE <database> is not supported.
    4. Now click Connect and you will get an error "Unable to apply connection settings. The detailed error message is: ‘ANSI_NULLS’ is not a recognized SET option. "
    5. Click Ok.

    After this step, you should be connected to the desired database and you can start running queries.

    Thanks,
    Abi

    Thursday, August 20, 2009 6:25 PM
  • T-SQL Queries from SSMS[X] are working fine for me, disregarding the spurious error message. A fixed up InstNwind.sql script creates and populates the Northwind sample database in 35 seconds over a relatively slow DSL connection.

    --rj
    OakLeaf Blog
    Thursday, August 20, 2009 11:45 PM
  • Hi AbiIyer,

    I am following this step only. but while running the query i got the error like object not found.  i am able to see the database in available database, but not able to select the data base from available database.

    I have created to database, Is only one database is allowed?

    Kapil
    Friday, August 21, 2009 5:18 AM
  • I think the limit on the CTP cluster is 5 databases (not counting master) per server.  What query are you running and what do you mean by "not able to select the data base"?

    Stan
    Program Manager, SQL Azure
    Friday, August 21, 2009 7:48 AM
  • Hi Abi Ayer i follow the same i made a Db called TestDb1 but when i try to coonect to that testdb1 this give me error like "Database is not accesible" i don't know what i am doing wrong
    Friday, August 21, 2009 8:03 AM
  • Ashish,

    Can you access testdb1 using a command line tool (like sqlcmd)?  Do you see testdb1 when you do select * from sys.databases (from master)?

    Stan
    Program Manager, SQL Azure
    Friday, August 21, 2009 8:13 AM
  • hi Thanks for reply yes am able to see ..
    testDb1 from select * from sys.databases

    here is the output..

    TestDb1    4    NULL    0x01020000000000648000000000001522042727F618775749A5968914ABB689AD    2009-08-21 06:15:46.153    100    SQL_Latin1_General_CP1_CI_AS    0    MULTI_USER    0    0    0    0    ONLINE    0    0    0    1    ON    1    1    FULL    2    CHECKSUM    1    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    00000000-0000-0000-0000-000000000000    0    10    SE_REPLICA_CATCHUP_QUEUE    0    0    0    0

    looking forward for reply
    Friday, August 21, 2009 8:17 AM
  • Are you using SSMS, have you tried sqlcmd?  If you did, can you post the command you issued that resulted in the error?

    Stan
    Program Manager, SQL Azure
    Friday, August 21, 2009 8:32 AM
  • hi yes i tried SQLCMD here what i tried and here what i get from this.....
    take a look at that...


    C:\Documents and Settings\ashish>sqlcmd -U *******@uu8199pxvs.ctp.databas
    e.windows.net -P ****** -S uu8199pxvs -d TestDb1
    HResult 0x35, Level 16, State 1
    Named Pipes Provider: Could not open a connection to SQL Server [53].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
    shing a connection to the server. When connecting to SQL Server 2005, this failu
    re may be caused by the fact that under the default settings SQL Server does not
     allow remote connections..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
    Friday, August 21, 2009 8:44 AM
  • Ashish,

    You are getting error 53, which means that the network path wasn't found.  It also looks like the connection is attempted using Named Pipes.  Try forcing it to use tcp/ip protocol instead of named pipes by prefixing the server name with "tcp:"

    Stan
    Program Manager, SQL Azure
    Friday, August 21, 2009 8:57 AM
  • still getting the same error i used tcp: prefix
    Friday, August 21, 2009 9:13 AM
  • You also need to specify the full server name: -S tcp:uu8199pxvs.ctp.database.windows.net
    Sorry, missed it when I looked at the command earlier.
    Program Manager, SQL Azure
    Friday, August 21, 2009 9:20 AM
  • great!!!!!!
    this time some thing new
    here what i get ......

    HResult 0x2746, Level 16, State 1
    TCP Provider: An existing connection was forcibly closed by the remote host.
    Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

    well sorry for bother you again n again... but i like to test that so..
    Friday, August 21, 2009 9:29 AM
  • Hmm, I'm not sure what's going on.  I was able to connect to both master and testdb1 using SSMS.  Here's what I did:

    1. cancel out of the first connection dialog that pops up.
    2. click on the New Query button from the toolbar (upper left)
    3. entered
           Server name: full servername
           Login: just the username (no @...)
    4. Under Options, Connect to Database, I typed TestDb1
    5. Hit connect.

    Let's give it a try.  Hopefully it will work.  If not I'll have to take a look at it tomorrow morning (it's very late here).

    Stan
    Program Manager, SQL Azure
    • Marked as answer by Kaps Pune Saturday, August 22, 2009 6:46 PM
    Friday, August 21, 2009 9:55 AM
  • Thanks many many Thanks..
    worked like a charm...i don't know what i am missing but it works perfectly now...
    Friday, August 21, 2009 10:05 AM
  • Great!
    Program Manager, SQL Azure
    Friday, August 21, 2009 10:06 AM
  • Hi,
    I am getting the same error as Ashish. I tried out the solution provided here. But still getting the same error. The only change is that after prefixing tcp: the error code changes from 53 to 100600.

    Please help

    Wednesday, August 26, 2009 10:51 AM
  • well my error if fixed...
    in which version of SSMS you are trying to connect in SQL Server 2005 SSMS or SQL Server 2008 SSMS...
    here is my version in which i use this..

    Microsoft SQL Server 2008 R2 (CTP) - 10.50.1092.20 (Intel X86)   Jul 22 2009 21:29:56   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) 
    Wednesday, August 26, 2009 11:41 AM