locked
Linked Server not accessible from a database when it is from a different database RRS feed

  • Question

  • Receive an error when trying to use OPENQUERY with a Linked server.  Though if I change the database in which I run the query it works fine.

     

     

    Monday, July 26, 2010 8:42 PM

Answers

  • Make sure you use 3-part naming: AdventureWorks2008.Production.Product

    Can you post the query? Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, July 27, 2010 12:56 AM
  • EXEC sp_serveroption server_name, 'data access' , 'true'
    go
    use pubs
    go
    create procedure proc_

    as
    select * from dbo.authors
    go
    select *
    from OPENQUERY(OPUSPENGUIN,'exec pubs.dbo.proc_')
    where au_lname = 'Ringer'
    go
    -- clean-up
    drop procedure proc_

    go


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, July 27, 2010 5:15 AM

All replies

  • Make sure you use 3-part naming: AdventureWorks2008.Production.Product

    Can you post the query? Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, July 27, 2010 12:56 AM
  • EXEC sp_serveroption server_name, 'data access' , 'true'
    go
    use pubs
    go
    create procedure proc_

    as
    select * from dbo.authors
    go
    select *
    from OPENQUERY(OPUSPENGUIN,'exec pubs.dbo.proc_')
    where au_lname = 'Ringer'
    go
    -- clean-up
    drop procedure proc_

    go


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, July 27, 2010 5:15 AM
  • Are the Linked servers in same network or in a different network?

    Please check the Linked Server configuration again..

    Thanks,

    Sandeep

     

    Wednesday, July 28, 2010 5:14 AM