Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177

    Unanswered: T-SQL ODBC connection

    Would someone provide a code snip example of a T-SQL script using an ODCB connection against a non-SQL2K database. I can hit the outside database using DTS (so I know it is possible) but I'm having trouble with the parameters using a script.

    Thanks,
    Fred.
    Fred Prose

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    uhhhh, what do you mean exactly???? You can use a linked server, or OPENROWSET. Both are described really well in Books Online. What kind of database is the other one? We might be able to help you more with that info.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    The ODBC driver in question points to a database on the Tivoli storage manager. It permits "generic" SQL queries into the database and works with 3rd party tools like WinSql to AQT. DTS will use it for export/import - so I know it works.

    I've created a linked server:

    sp_addlinkedserver @server='Tivoli', @srvproduct='', @provider='MSDASQL', @provstr='DSN=Tivoli;UID=xxxx;PWD=yyyy'

    And the stored procedure below works - it returns the valid list of tables:
    sp_tables_ex 'Tivoli'

    However, when I try and run something simple, like:
    select * from openquery(Tivoli,'SELECT * FROM NODES')

    I get errors:
    Server: Msg 7357, Level 16, State 2, Line 6
    Could not process object 'SELECT * FROM NODES'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.
    OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDASQL', Query=SELECT * FROM NODES'].


    But, DTS works. Is there a way to see what type of SQL DTS is generating?
    Fred Prose

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •