Using stored procedure to read from ODBC ?

Using stored procedure to read from ODBC ?

• OLE72

  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

   

  
  

• BarataMota

  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.
  
  

• aybe

  Dear all,

  Do you have any update

  Best regards,

  CJira

  
  

• DINESH CHAUDHARI

  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
  ---
  
  

• RafaDom

  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
  ---
  
• Using stored procedure to read from ODBC ?