Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2009
    Posts
    6

    Question Unanswered: OpenQuery vs dotted

    I have a linked server named AapNootMies on a ODBC datasource on a text file.

    When I do:

    select * from openquery(aapnootmies, 'select * from [mjh.txt]')

    it works fine.
    When I try the dotted notation:

    select * from AAPNOOTMIES.[C:\RABO\CMS\DATA]..[mjh.txt]

    then I get an error:

    OLE DB provider "MSDASQL" for linked server "AAPNOOTMIES" returned message "[Microsoft][ODBC Text Driver] Syntax error (missing operator) in query expression '`Tbl1002`.`AAP` `Col1004`'.".
    Msg 7321, Level 16, State 2, Line 1
    An error occurred while preparing the query "SELECT `Tbl1002`.`AAP` `Col1004`,`Tbl1002`.`NOOT` `Col1005`,`Tbl1002`.`MIES` `Col1006` FROM `C:\RABO\CMS\DATA`\`mjh.txt` `Tbl1002`" for execution against OLE DB provider "MSDASQL" for linked server "AAPNOOTMIES".

    What am I doing wrong?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Your "dotted" statement needs to look like this:

    select * from AAPNOOTMIES...mjh#txt
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Oct 2009
    Posts
    6
    Does not work:

    select * from AAPNOOTMIES...mjh#txt

    Msg 7313, Level 16, State 1, Line 1
    An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "AAPNOOTMIES".

    select * from AAPNOOTMIES.[C:\RABO\CMS\DATA]..mjh#txt

    OLE DB provider "MSDASQL" for linked server "AAPNOOTMIES" returned message "[Microsoft][ODBC Text Driver] Syntax error (missing operator) in query expression '`Tbl1002`.`AAP` `Col1004`'.".
    Msg 7321, Level 16, State 2, Line 1
    An error occurred while preparing the query "SELECT `Tbl1002`.`AAP` `Col1004`,`Tbl1002`.`NOOT` `Col1005`,`Tbl1002`.`MIES` `Col1006` FROM `C:\RABO\CMS\DATA`\`mjh#txt` `Tbl1002`" for execution against OLE DB provider "MSDASQL" for linked server "AAPNOOTMIES".

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Execute the following:

    exec sp_tables_ex 'AAPNOOTMIES'
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Oct 2009
    Posts
    6
    Result is:

    TABLE_CAT: C:\RABO\CMS\DATA
    TABLE_SCHEM: NULL
    TABLE_NAME: mjh.txt
    TABLE_TYPE: TABLE
    REMARKS: NULL

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    hm, my TABLE_CAT column is empty. What's the command you used to create your linked server? Here's mine:

    exec sp_addlinkedserver
    @server = 'TextFile'
    ,@srvproduct = 'OLE DB Provider for Jet (Text IISAM)'
    ,@provider = 'Microsoft.Jet.OLEDB.4.0'
    ,@datasrc = 'C:\temp'
    ,@location = null
    ,@provstr = 'Text'
    go
    -- Add mapping login for the file to be accessible
    exec sp_addlinkedsrvlogin
    @rmtsrvname = 'TextFile'
    ,@useself = false
    ,@locallogin = null
    ,@rmtuser = 'admin'
    ,@rmtpassword = null
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Oct 2009
    Posts
    6
    EXEC master.dbo.sp_addlinkedserver @server = N'AAPNOOTMIES', @srvproduct=N'ODBC', @provider=N'MSDASQL', @datasrc=N'AapNootMies'
    /* For security reasons the linked server remote logins password is changed with ######## */
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AAPNOOTMIES',@useself=N'True',@local login=NULL,@rmtuser=NULL,@rmtpassword=NULL

  8. #8
    Join Date
    Oct 2009
    Posts
    6
    I tried your linked server with the OLE DB provider, and that one works (with a # instead of the dot)

    I tried to use a odbc data source, but with this provider it is even better!

Posting Permissions

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