Linked Server - invalid object

  • krypto69

    SSChampion

    Points: 13506

    I have created a linked server connection...but when I run my query I get:

    Invalid object name 'VSMIADBGP01.LPB_GreatPlainsUtils.vwADB_LPB_CONSOLIDATED_ACCOUNT_DIV_DEPT_BRANCH_YEAR_2009'.

  • Lowell

    SSC Guru

    Points: 323463

    does wrapping it in brackets help? plus it needs to be a 4 part name...server.database.owner.object:

    does this work:

    SELECT * FROM [VSMIADBGP01].[LPB_GreatPlainsUtils]..[vwADB_LPB_CONSOLIDATED_ACCOUNT_DIV_DEPT_BRANCH_YEAR_2009]

    --or

    SELECT * FROM [VSMIADBGP01].[LPB_GreatPlainsUtils].[dbo].[vwADB_LPB_CONSOLIDATED_ACCOUNT_DIV_DEPT_BRANCH_YEAR_2009]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Sergey Vavinskiy

    SSCarpal Tunnel

    Points: 4469

    As mentioned, the code below should work, you just missed a schema part (dbo), which is reason for your error message.

    SELECT * FROM [VSMIADBGP01].[LPB_GreatPlainsUtils].[dbo].[vwADB_LPB_CONSOLIDATED_ACCOUNT_DIV_DEPT_BRANCH_YEAR_2009]

  • krypto69

    SSChampion

    Points: 13506

    thanks guys

    when I run it I get -

    The OLE DB provider "SQLNCLI" for linked server "VSMIADBGP01" does not contain the table ""LPB_GreatPlainsUtils"."dbo"."vwADB_LPB_CONSOLIDATED_ACCOUNT_DIV_DEPT_BRANCH_YEAR_2009"". The table either does not exist or the current user does not have permissions on that table.

  • Sergey Vavinskiy

    SSCarpal Tunnel

    Points: 4469

    Hmm..

    If this table actually exists then you have clear permission issue.

  • krypto69

    SSChampion

    Points: 13506

    the view does exist...

    how can I go about checking the permission?

  • Lowell

    SSC Guru

    Points: 323463

    definitely a permissions issue;

    there's several ways to set up how to get to the view, and it depends on how it's set up.

    in my example below, because i decided to use "sa", i don't have a permissions issue, but if i put in "webdev", who is my user who has access to just one specific database, any calls to a database that user does nto have access to would fail with the same permissions error you are receiving.

    go to this spot on your list of linked servers:

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • krypto69

    SSChampion

    Points: 13506

    Same error

    Msg 7314, Level 16, State 1, Line 1

    The OLE DB provider "SQLNCLI" for linked server "VSMIADBGP01" does not contain the table ""LPB_GreatPlainsUtils"."dbo"."vwADB_LPB_CONSOLIDATED_ACCOUNT_DIV_DEPT_BRANCH_YEAR_2009"". The table either does not exist or the current user does not have permissions on that table.

    Under linked servers:

    If I expand catalogs, I don't see my DB

    I only see master, msdb and tempdb

    If I click 'test connection' for linked servers, it tests fine.

  • krypto69

    SSChampion

    Points: 13506

    THANKS GUYS I GOT

    THANKS TO YOU GUYS!!!

    GOOD KARMA!!! 2 YOU ALL!!!!

  • Vishal Singh

    SSCertifiable

    Points: 5830

    krypto69 (9/18/2009)


    THANKS GUYS I GOT

    THANKS TO YOU GUYS!!!

    GOOD KARMA!!! 2 YOU ALL!!!!

    Okay, and what was the problem? Something related to schema?

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • marykdba

    Mr or Mrs. 500

    Points: 574

    Please come back and tell us what you figured out. I am having the same problem.

Viewing 11 posts - 1 through 11 (of 11 total)

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