SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cannot see tables in Linked Server (ProvideX)


Cannot see tables in Linked Server (ProvideX)

Author
Message
dan 91669
dan 91669
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 97

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
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41464 Visits: 11494


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
dan 91669
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 97
Hi Sue, i've set the linked server up following that exact guide.
Sue_H
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41464 Visits: 11494
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
dan 91669
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 97
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
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41464 Visits: 11494


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
dan 91669
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 97
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
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41464 Visits: 11494
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
dan 91669
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 97
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
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41464 Visits: 11494
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search