none
Using stored procedure to read from ODBC ?

    Question

  • Dear all,

    I am using SQL 2000 and would like to dynamically assign ODBC data source to transform data task. Do you have a stored procedure to perform read/write from/to ODBC data source? I would like to input data source and table name.

    Thank you and Best regards,

    Chaivat

    Monday, September 25, 2006 2:58 PM

All replies

  • You can use the OPENQUERY for using a linked server defined in the server or the adhoc version with defining a DSN or a connection string along with OPENDATASOURCE. Use can issue any Select query that is supported by the used driver.

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Monday, September 25, 2006 5:19 PM
    Moderator
  • Hello Jens,

    I tried to define link server using both sp_linkedserver and enterprise manager approach. It seems that I cannot add the ODBC datasource to Microsoft OLE DB provider for ODBC.

    My datasource is SAS and I have SAS ODBC driver which able to read/write in DTS.

    I also can read data from SAS to Excel using the same DSN.

    Do we have another way to code stored procedure that works like adding ODBC datasource and data transformation task in DTS ?

    Here is the code and error I found.

    USE ncbbiu
    GO
    -- To use named parameters:
    EXEC sp_addlinkedserver
       'SAS',
       'SAS',
       'MSDASQL',
       'NCBSAS'
    GO
     
    SELECT *
    FROM OPENQUERY(SAS, 'SELECT * FROM x')
    GO
     
     
    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'MSDASQL' reported an error. 
    [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ].

    Best regards,

    CJira

     

    Tuesday, September 26, 2006 7:42 AM
  • Dear all,

    Do you have any update ?

    Best regards,

    CJira

     

    Wednesday, September 27, 2006 3:45 PM
  • If you use a linked server you can use something like this here:

    SELECT * FROM linkedServer.Database.Schema,Objectname

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Wednesday, September 27, 2006 5:26 PM
    Moderator
  • Did you create the system DSN for SAS on the SQL Server machine? What about the SAS driver themselves? TSQL code executes on the server so you need to perform all necessary configuration / install for SAS driver on the server. First, start with just trying a simple ODBC test (using SDK app) on the server using the SAS driver. If this works then the SAS driver install on server is fine and it should work fine using SQL Server. Lastly, you should also verify the capabilities of the driver because SQL Server expects some level of conformance/support in terms of the driver implementation. So depending on this you may or may not be able to use all of the features of distributed queries / DMLs against the SAS driver. See Books Online for more details on this topic.
    Wednesday, September 27, 2006 10:25 PM