none
Is it possible to connect to ODBC database through SQL Management Studio

    Question

  • Hi,

    I am trying to connect to ODBC database in SQL 2008 Management studio, but it does not let me.

    Is it possible to connect? I use below statement:

    connect to oracle(user=ms password=ssd path='qsd');
    create table ds.E1_CRPP as
      select * from connection to oracle (
    SELECT A.J_ID, A.J_REF, A.J_T_CODE, A.CREATED_DATE
    FROM J A, J_CHARACTERISTIC B, J_CHARACTERISTIC C
    WHERE A.J_ID=B.J_ID
    AND B.J_ID=C.J_ID
    AND A.J_T_CODE IN ('E4-1','E4-2','E4-3','E4-4','E4-5','E4-6','E4-8','E4-9','E4-10','E4-11');
    disconnect from oracle;

    Thank you

    Tuesday, August 17, 2010 10:37 AM

Answers

  • Hello,

    SSMS is a dedicated tool to connect & work on MS SQL Server + addtional Services like SSAS, SSIS, SSRS.

    If you want to query data from a Oracle database then you have to us Oracle tool like the Sql Developer.

    Or you could add a linked server for Oracle to a SQL Server. Or use OpenQuery.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    • Marked as answer by nv1950 Tuesday, August 17, 2010 11:08 AM
    Tuesday, August 17, 2010 10:54 AM

All replies

  • For this you should be using linkedservers. You will not be able to connect to ODBC databases using SSMS.

    See below link for more details

    http://technet.microsoft.com/en-us/library/ms190479.aspx

    Tuesday, August 17, 2010 10:52 AM
  • Hello,

    SSMS is a dedicated tool to connect & work on MS SQL Server + addtional Services like SSAS, SSIS, SSRS.

    If you want to query data from a Oracle database then you have to us Oracle tool like the Sql Developer.

    Or you could add a linked server for Oracle to a SQL Server. Or use OpenQuery.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    • Marked as answer by nv1950 Tuesday, August 17, 2010 11:08 AM
    Tuesday, August 17, 2010 10:54 AM
  • Your syntax looks very strange to me. Does that syntax work on Oracle? It is definitely not valid syntax on SQL Server.

    To query a remote data source, you - or someone with sufficient permissions - must first create a linked server with sp_addlinkedserver, and you may also need to define login-mapping with sp_addlinkedsrvlogin.

    Once this is done, you can access the linked server is several way:

    {sql]
    -- Four-part notation:
    SELECT ... FROM SERVER.database.sch.tbl

    -- Passthrough query:
    SELECT ... FROM OPENQUERY(SERVER, 'SELECT ... FROM database.sch.tbl')

    -- Run a command remotely
    EXEC('SELECT ... FROM database.sch.tbl WHERE col = ?', @myvalue) AT SERVER
    [/sql]

    By the way, the access to remote data sources, is over OLE DB, not ODBC.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Tuesday, August 17, 2010 11:05 AM