Cannot see tables in Linked Server (ProvideX)

  • dan 91669

    Mr or Mrs. 500

    Points: 540

    System: W7, SSMS 2017, SQL server express 2014
    Server: 2012 R2, MAS100 2017 provideX

    I can run run queries just fine (ish), but no tables/catalogs show up under my linked server. Additionally, i cannot see any tables in the query design view.
    I can see the tables in Crystal Reports
    I understand at this point that it appears to be a permission thing, something with the AD, but i don't know where/how to fix it. 
    SQL exists on my local machine and i am connecting through my windows login.

    thanks

  • Sue_H

    SSC Guru

    Points: 89891

    It could just be the driver and configurations. You may want to try following Sage's article (36360) on setting this up:
    How to set up a Linked Server in SQL Server Management Studio

    Sue

  • dan 91669

    Mr or Mrs. 500

    Points: 540

    Hi Sue, i've set the linked server up following that exact guide.

  • Sue_H

    SSC Guru

    Points: 89891

    dan 91669 - Friday, July 21, 2017 9:48 AM

    Hi Sue, i've set the linked server up following that exact guide.

    It's more likely to be something on the MAS side of things then SQL Server - especially with queries mostly working. One thing you could try playing with is some of the linked server specific stored procedures. They just return meta data related to the linked server source. I think the tables view is actually populated from one of those: 
    sp_tables_ex 'YourLinkedServer'
    You may also want to try sp_catalogs 'YourLinkedServer'
    Limited results coming back from those is usually related to permissions on the source server or if you have a default catalog, database on the source server it can limit what is returned even if you do have permissions in other areas. You may want to check the login used for the connect with the linked server vs Crystal Reports.

    Sue

  • dan 91669

    Mr or Mrs. 500

    Points: 540

    Sue_H - Friday, July 21, 2017 1:48 PM

    dan 91669 - Friday, July 21, 2017 9:48 AM

    Hi Sue, i've set the linked server up following that exact guide.

    It's more likely to be something on the MAS side of things then SQL Server - especially with queries mostly working. One thing you could try playing with is some of the linked server specific stored procedures. They just return meta data related to the linked server source. I think the tables view is actually populated from one of those: 
    sp_tables_ex 'YourLinkedServer'
    You may also want to try sp_catalogs 'YourLinkedServer'
    Limited results coming back from those is usually related to permissions on the source server or if you have a default catalog, database on the source server it can limit what is returned even if you do have permissions in other areas. You may want to check the login used for the connect with the linked server vs Crystal Reports.

    Sue

    Hi Sue, thanks for the help thus far. 
    Running those queries returns headers, but no rows. I am using the same login between CR and SSMS. When you say it has to do with permissions, do you think it is a problem with Active Directory/windows user permission, or a MAS user permission problem?

    thanks!
    dan

    edit: i can see the tables in Excel ODBC connection/query maker.

  • Sue_H

    SSC Guru

    Points: 89891

    Most queries work and the linked server stored procedures work so it's not likely to be on the SQL Server side.
    Are you using the same drivers and same configurations with Crystal as you are with the Linked Server?
    You could have limited permissions on MAS or if you have a default catalog, database on MAS - can't remember how that one works - which can limit what is returned even if you do have permissions in other areas.

    Sue

  • dan 91669

    Mr or Mrs. 500

    Points: 540

    Sue_H - Tuesday, August 1, 2017 11:58 AM

    Most queries work and the linked server stored procedures work so it's not likely to be on the SQL Server side.
    Are you using the same drivers and same configurations with Crystal as you are with the Linked Server?
    You could have limited permissions on MAS or if you have a default catalog, database on MAS - can't remember how that one works - which can limit what is returned even if you do have permissions in other areas.

    Sue

    Yep, they are both using the same DSN. I guess i should call Sage and ask them about the default catalog/database permissions? The last time i talked with them about something unrelated they didn't really get me anywhere as far as being able to see the tables.

  • Sue_H

    SSC Guru

    Points: 89891

    dan 91669 - Tuesday, August 1, 2017 12:37 PM

    Yep, they are both using the same DSN. I guess i should call Sage and ask them about the default catalog/database permissions? The last time i talked with them about something unrelated they didn't really get me anywhere as far as being able to see the tables.

    It used to be that way years ago...I guess some things never change. Try their forums - I used to have better luck searching those.

    Sue

  • dan 91669

    Mr or Mrs. 500

    Points: 540

    So just to close this thread out... 
    I gave up on linked server under the premise that SSIS would probably be a better way to go about doing what i need to do. So long as i manually build the query i can get mappings to set column types etc just fine. Go figure.

  • Sue_H

    SSC Guru

    Points: 89891

    dan 91669 - Thursday, August 10, 2017 9:34 AM

    So just to close this thread out... 
    I gave up on linked server under the premise that SSIS would probably be a better way to go about doing what i need to do. So long as i manually build the query i can get mappings to set column types etc just fine. Go figure.

    Thanks for posting back. Don't think it's you - their drivers really are flaky. I've only had to use them on rare occasions and that was bad enough. You have my sympathies.

    Sue

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

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