Post reply

OPENQUERY Query :p

  • January 12, 2012 at 9:51 am

    #249069

    Hi - I have set up a linked server. Previously when querying I have just used 4 part naming convention however in this case it does not work however OPENQUERY is working. Could anyone explain as to why this is? The code is as follows:

    --Failing Code

    select * from DBTEST.MarshAD.dbo.UserSummary

    --Working Code

    SELECT *

    FROM OPENQUERY(DBTEST, 'select

    [login],

    name,

    div,

    comp,

    pdon,

    dept,

    adtitle,

    costcode

    from MarshAD.dbo._UserSummary')

    Thanks

  • January 12, 2012 at 10:10 am

    #1432737

    What do you mean with "fail"?

    Care to share the error message?

    -- Gianluca Sartori

  • January 12, 2012 at 10:12 am

    #1432738

    i think it has to do with the way you have the linked server set up;

    if you look at it graphically, you want to make sure the radio button that says "be made using the logins current security context " is selected.

    otherwise, like in the screenshot, you need to provide which user should be used as a proxy/instead of whoever calls the linked server.

    the openquery you posted is using the current logins credentials.

    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!

  • January 13, 2012 at 2:11 am

    #1433071

    Hi there thanks for the replies. My linked server is setup such that my account is linked to a remote user that has access to the database in question.

    The error I am getting when using the 4 part naming convention is

    Msg 7314, Level 16, State 1, Line 1

    The OLE DB provider "SQLNCLI10" for linked server "DBTEST" does not contain the table ""MarshAD"."dbo"."UserSummary"". The table either does not exist or the current user does not have permissions on that table.

  • January 13, 2012 at 2:15 am

    #1433074

    Kwisatz78 (1/13/2012)

    Hi there thanks for the replies. My linked server is setup such that my account is linked to a remote user that has access to the database in question.

    Has the account SELECT permission on your table ?

  • January 13, 2012 at 2:20 am

    #1433079

    the openquery says the table starts with an underscore.

    MarshAD.dbo._UserSummary works, but MarshAD.dbo.UserSummary fails;

    looks like a simple issue to fix then.

    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!

  • January 13, 2012 at 2:55 am

    #1433102

    lol omg what a numpty - sorry to bother you with that, but thanks for your time.

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

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