Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Linked Servers

  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: Linked Servers

    I have a linked server connection on a SQL 2k server connected to a Sybase ASE 12.5 server via Sybase OLE DB connection. To this point everything has been working great for months. Now we're encountering a problem where new tables created on the Sybase db won't show up in the linked server connection. I've checked permissions and the object owners and everything is exactly the same as the pre-existing tables except the table's new.

    Anyone have any ideas wht's up???

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Describe "won't show up" in a bit more detail. What exactly happens when you try to issue a SELECT statement against a new table?

    -PatP

  3. #3
    Join Date
    Sep 2003
    Posts
    364
    When I say "won't show up" I mean you can't see the table in the table list in enterprise manager when I open the linked server. Here's the SQL and error message:

    select i_con_contract from openquery([32tlsql2-dreamdb],
    'Select i_con_contract From dbo.temp_client_3' )

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.
    [OLE/DB provider returned message: [Native Error code: 208]
    [DataDirect ADO Sybase Provider] dbo.temp_client_3 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
    ]
    OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What happens in Query Analyzer?

    I suspect that this is another example of EM (SQL Enterprise Mangler) caching information, but never refreshing the cache.

    -PatP

  5. #5
    Join Date
    Sep 2003
    Posts
    364
    The SQL and error message posted are from Query Analyzer.

    I've even created a new linked server connection and the tables still won't show up. So I don't think it's a caching issue in EM.

    Any other ideas??? I appreciate the help.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I was a bit corn-fused when you were talking about EM and posting SQL in the same message. I figured that somehow I must have "missed a meeting" in there somewhere.

    Is there any chance that the Sybase objects are owned by a non-dbo user? Does the user being used by OPENQUERY have access to those objects when you use Sybase tools (like ISQL) to try to access them?

    -PatP

  7. #7
    Join Date
    Sep 2003
    Posts
    364
    Nope, they're owned by dbo and every user and group in the Sybase db have select, insert, update, and delete permissions on the tables.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Well, at least for now I'm stumped. I'm sure that come 03:30 I'll have a bright idea, but right now I'm fresh out. Sorry.

    -PatP

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by peterlemonjello
    Nope, they're owned by dbo and every user and group in the Sybase db have select, insert, update, and delete permissions on the tables.
    What's the linked server login?

    Did you grant right to it on the new tables?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Sep 2003
    Posts
    364
    Yep, the login the linked server is using has explicit permissions set on every table in the db. I've even changed to login to sa and still can't see the tables.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm stuck...did you stop and restart EM?

    Can you query them in QA?

    Hey Pat, it's past 3:30...EST

    Is there a way to see the linked server catalogs?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Sep 2003
    Posts
    364
    Yep, I've rebooted the SQL Server. I can see the catalog using the sp_tables_ex proc and by looking in the sysremote_tables table in the master db. They're missing the new tables too. I have no freakin idea what's going on. I've turned the trace on in the OLEDB properties to try and isolate how SQL Server gets the table list from Sybase. Unfortuantely, when I refresh the tables in EM nothing appears in the OLEDB trace output.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, let's get stupid (since I'm already there)

    Can you create a new linked server with the same code?

    Did you do it with code or through EM?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Sep 2003
    Posts
    364
    I did it through EM. I have two SQL Server instances on seperate servers that are having the same problem. The only common denominator is the target Sybase server. I can't duplicate the problem against any other Sybase servers. I'm going to reboot the Sybase server tonight to see if that clears anything up. I'll let you know how it goes.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Good Luck...

    Time for a 'rita....

    Later...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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