Post reply

Can''t access views from linked servers

  • July 12, 2006 at 12:52 pm

    #173460

    Hi!

    I'm having problems when trying to use a view from database A, that takes data from a table in database B.

    Actually, if I run a query from Query Analyzer, it works fine. But if I want to run it from my application, that uses ODBC, it doesn't work.

     

    Summarizing, the situation is this:

    SQL 2000 A. Has some views that take information from Server B. It has a "Linkd Server" configured, that points to B.

    SQL 2000 B. Has the databases used.

    If I use the linked views from A using ODBC, I get a timeout error (as if B were turned off).  From Query Analyzer, works fine.

     

    Any Idea???

    Thanks!

  • July 13, 2006 at 4:19 am

    #648907

    Maybe you can provide us with some additional information such as the real error message you get, and the real query?

     

    N 56°04'39.16"
    E 12°55'05.25"

  • July 13, 2006 at 9:33 am

    #649008

    I'm sorry, I don't get error codes, just a timeout error.

    Even in the SQL logs...

  • July 13, 2006 at 10:49 am

    #649041

    odbc is probably timing out.

    how long does the query take?

    increase odbc timeout to larger value or consider revising query so it is faster.

    Cheers,

    Todd

  • July 13, 2006 at 10:54 am

    #649042

    I think I was missing the point.

    Trying over and over again, I found that this happens when the link is inside a transaction. If I only run a query, it works fine!

    Is this known to be a security restriction ... ? An issue?

    Thanks !!!

  • July 13, 2006 at 1:13 pm

    #649085

    This may have something to do with the way your linked server is configured (there are 4 security options for linked servers) since the security for an execution from Query analyzer and an ODBC connection may act differently based on how it is configured and how your application establishes it's connection.  Also if you are including this as part of a transaction I believe you need to enable MSDTC and start the service.  We ran into a problem with this on new servers running 2003 since the service is not turned on by default.

    Hope this helps.....

    Judie

  • July 13, 2006 at 11:30 pm

    #649134

    I had trouble with transactions and linked servers once before - I had SQL Server querying Oracle twice.  If done in a single transaction, the 2nd query would freeze.  If done in two separate transactions, all was swell but I had to use some staging tables to store the data... yuk

    I would agree with Judith - linked server security settings or even the SET options that you have enabled in query analzer may be different from those set via ODBC.  Does your code run when executed via the osql.exe command-line tool?  How about the older isql.exe command-line tool?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply