July 12, 2017 at 2:03 pm
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
July 17, 2017 at 4:22 pm
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
July 21, 2017 at 9:48 am
Hi Sue, i've set the linked server up following that exact guide.
July 21, 2017 at 1:48 pm
dan 91669 - Friday, July 21, 2017 9:48 AMHi 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
August 1, 2017 at 9:15 am
Sue_H - Friday, July 21, 2017 1:48 PMdan 91669 - Friday, July 21, 2017 9:48 AMHi 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.
August 1, 2017 at 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
August 1, 2017 at 12:37 pm
Sue_H - Tuesday, August 1, 2017 11:58 AMMost 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.
August 1, 2017 at 12:48 pm
dan 91669 - Tuesday, August 1, 2017 12:37 PMYep, 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
August 10, 2017 at 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.
August 10, 2017 at 3:54 pm
dan 91669 - Thursday, August 10, 2017 9:34 AMSo 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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply