Synonym table has no fields (Crystal Reports 2011 ...

I am running Crystal Reports 2011. I'm using OLE DB to connect to a sql server database that has tables and synonyms. I choose the table (cust_ord) and the synonym table (customers) and then I go to the next screen. At the next screen it says "Link together the tables". It shows the cust_ord table with its fields, and it also shows the customers synonym table, but the customers synonym table is just a blank box with no fields in it. Therefore I am not able to link together the tables. Please advise. Thank you.

SAP Managed Tags:
SQL,
SAP Crystal Reports

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (2)

Thank you Don. We're using SQL Server 2008. You mentioned that I can install the SQL 2008 client from the Tools menu option on install. Does that mean I should re-run the Crystal Reports 2011 install process?

-David

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

David,

Well in Crystal Reports i had seen a similar kind of issue, where if we are creating Crystal Report based of SQL Server and if we are using ODBC connection synonyms doesnt get showed up in the hirarchy structure.

Try and create a command object which will try and fetch the data from a synonym. See if this works for you or not.

Regards,

Kuldeep G

What version of SQL Server are you using?

If it's 2005 then use the SQL 2008 Client and select the Native 10 driver. If it's 2008 then you must use the SQL 2008 client, you can install it from the Tools menu option on install. MS does not support MDAC version of their DB client in SQL 2008.

Also, in the next version of SQL it's going to be the last version they support OLE DB, you may want to convert to ODBC now.

Thank you

Don

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Don, I tried running the Crystal Reports install process again (both modify and repair), but I didnu2019t see a Tools menu option. Can you be more specific? Thanks again.

Hi David,

Sorry I should have been clearer, It's MS SQL Server Client Tools and not CR Tools.

Don

Don,

I checked, and I already have MS Sql Server 2008 Native Client on my machine.

When I go into the Crystal Reports 2011 Reports Wizard, I do the following:

1. I choose u201CCreate New Connectionu201D

2. I choose u201COLE DB (ADO)u201D

3. I choose u201CMicrosoft OLE DB Provider for SQL Serveru201D (the only other reasonable option I see is u201CSQL Server Native Client 10.0u201D)

4. On the next screen I enter the information for Sever, UserID, Password, and Database.

5. On the next screen I click u201Cfinishu201D

6. On the next screen under DBO, I choose a table (cust_ord) and a synonym (customers)

7. Then I click Next

8. On the following screen I see 2 boxes: The cust_ord box has the fields in it and the Customers box is empty (no fields showing)

Please advise.

Thanks, David

Hi David,

That's the problem....

3. I choose u201CMicrosoft OLE DB Provider for SQL Serveru201D (the only other reasonable option I see is u201CSQL Server Native Client 10.0u201D)

You MUST select "SQL Server Native Client 10.0".

OLE Db Provider is the MDAC version which MS doesn't support.

Thanks again

Don

Hi Don,

When I choose SQL Server Native Client 10.0, the same thing happens (The cust_ord box has the fields in it and the Customers box is empty).

Thanks,

David

Hi David,

This usually doesn't happen with SQL Server but I heard on another thread this did so try this...

After you make your Connection right click on the Table Name and select Options and check off the all of the other options and if that doesn't work then try adding the Owner info etc.

There is a limited amount of internal memory space to hold all of the database info and if it runs out you won't see everything.

If that doesn't resolve the issue then purchase a case if you don't have a support contract and a Rep can work with you to resolve the issue. If it is anew bug you'll get a refund.

Thanks

Don

Hi Don,

Thank you again for your efforts. I tried your two latest suggestions, but unfortunately they don't resolve the problem.

I did a little more searching and found the following thread:

Towards the end of the thread Jeremy Isikoff writes:

"I am having a similar problem with synonyms in crystal 2008 designer connecting to a sql server 2005 database using OLE DB provider. I can connect and the synonym shows in the field explorer with a plus sign but it doesn't allow you to expand the plus sign to show the fields and use them in a report!"

Then Ron Vervenne responds:

I have reported this behavior to SAP as a bug in November.

I finally got today the message:

"The issue is planned to be fixed in CR 2008 SP3 and Release of SP3 would be(tentative) in May or June."

So we have no choice than to wait until SP3 is released.

Before I spend the $195 on the tech support single instance, could you find out if the fix mentioned above was ever implemented?

Thanks again,

David

Hi David,

I did a bunch of testing and I can't see them either. I tried using SQL 2005 and SQL 2008 and CR 2008 and 2011 no go... In ODBC I didn't even have the option to see them in 2011.

I've sent an e-mail off to the Developer asking to verify if we support them or not and more info.

Update - PO has confirmed this should work. It has now bee tracked to be fixed - ADAPT01599227

Thanks

Don

Edited by: Don Williams on Nov 28, 2011 6:49 AM

That's good news Don. I appreciate it.

In your experience does 'tracked to be fixed' mean it will be a matter of weeks or a matter of months?

-David

Hi David,

It depends on who you are.... If you are an OEM or Corporate Partner then there is an option to get an Fix Pack done and it could be resolved in a few weeks depending on the business case. In this case it appears to be a regression issue ( meaning it used to work ) so it gets higher priority. So if you have a Support Contract please log a case in SMP and let me know the case number and I'll grab it and escalate to a higher level.

If you are not one of the above then it's been set for Support Pack 4.0 which I believe are scheduled for 1/4'ly releases. I don't know the date for 4.0 at this time but it looks to be July 2012 but nothing set at this time.

Thanks

Don

NOTE: Corrections to the patch release. Fix packs are served for Production down systems. This has been moved to Support Pack 4.0

Edited by: Don Williams on Dec 3, 2011 3:50 PM

Hi David,

Developers have had a chance to look at this issue and it appears to be a limitation in the Report Designer Database Wizard.

What we have discovered is the API the DB Wizard uses to query the DB use a Client API called SQLTables(). We have discovered that it does work through a Command Object which uses SQLExecuteDirect() to get the table info from a Synonym.

So for now the work around is to use a Command Object, then you can use Synonyms.

I created a Synonym in SQL 2008:

USE Northwind;

IF OBJECT_ID('dbo.Orders', 'SN') IS NOT NULL

DROP SYNONYM dbo.Orders;

CREATE SYNONYM dbo.MyOrders FOR Northwind.dbo.Orders;

and then in the Designer I added a Command object:

select * from dbo.MyOrders

Because it's always been this way to change it now would be a major rewrite in CR Designer and has the potential to break all legacy reports. This means we will not add/fix the ability in CR diesign until either a Service Pack or likely some future release.

Please use the Work around and add your request to Idea Place. If enough votes they will seriously look at adding the ability.

I should mention also that using ODBCTest ( from Microsoft ) doesn't work either, so it's a limitation in the ODBC driver....

Thank you

Don

Edited by: Don Williams on Dec 30, 2011 7:26 AM

Hi Don,

I tried the workaround you mentioned. When I try to run a report using the workaround, the processing is painfully slow and totally unusable. Please go up the chain and tell them that the workaround is not acceptable.

Thanks,

David

Hi David,

Are you an OEM Partner of ours?

We've dug into this a lot more since and discovered that Microsoft did not support synonyms in 2005 and it was tracked at that time and rejected because it was a bug in their ODBC driver. MS has since added support for them in their driver but very few CR users asked for it so we never added the ability in MS, DB2 and Oracle Synonyms do work.

Problem now is there is a potential to cause serious problems for current users if we add new functionality so they don't want to until the next version or unless we have a Good business Case to do so in the next Service Pack ( major Release )

So your only work around is to Purchase or Log a case in SMP if you have a support contract. We need a case to create the TE and Adapt ( Enhancement Request ) and we especially need your business case why you need this since it never worked in previous versions so it's not a regression issue.

And add your request to [Idea Place|http://www.sdn.sap.com/irj/scn/idea-place], if enough people vote for it they will seriously look at adding the feature.

Or don't use Synonyms, use Stored Procedures or Views which do work fine in CR.

Thanks again

Don

Hi Don,

Per your suggestion I have just now added my request to Idea Place.

Iu2019m not real keen on spending $195 to purchase a case. From your post I get the feeling that my $195 investment might not bear fruit for quite some time. It doesnu2019t seem quite fair in this instance that I would be required to spend $195.

By the way, you mentioned in your post that Synonyms for Microsoft databases never worked in previous versions of Crystal Reports. Actually, Synonyms for Microsoft databases worked perfectly well in the previous version of Crystal Reports that I most recently used, which was version 8.5.

You asked for my business case. My business case is that I canu2019t get work done in Crystal Reports 2011 that my company needs me to do without the synonyms issue being fixed.

Best regards,

David

Hi David,

Interesting that it worked in 8.5.... that would have been SQL 6.5 days and MDAC 2.x or above ( I don't recall what version MDAC was in those days )....

Our Developers did work with MS in 2005 and MS confirmed it was a problem so it was something MS broke. I have not let this Adapt die either and convinced R&D to add the function back in so they have now committed to doing so.

One problem is in the Native 10 Client we use SQLTables() API to get the synonym list which we do, the problem now is that MS API doesn't return the field info. I works in Commands because we use SQLExecute to get the Synonyms which does return the fields but as you have discovered the performance is not very good. R&D verified this, it's due to having to process the Query and return all of the tables and fields in the Synonym. They are working on improving that part of or our code and as long as MS "fixes" the API to get the Field info then we should be able to use the Database Wizard to connect.

Bottom line is it's still a MS issue but our Developers are working with MS to resolve the problem.... If we can fix it we will, otherwise we have to wait for MS to do the needful....

Thanks again

Don

Hi Don,

That is great news. I do appreciate your help.

Just in case it's useful for your R&D people, here are the steps I took in Crystal Reports 8.5:

Database/More Data Sources/OLE DB/Make New Connection/Microsoft OLE DB Provider for SQL Server

Thanks again,

David

Thanks David,

Back then if you were using OLE DB then using the OLE DB Provider was the only one we supported. I don't know if at the time is did use the MSSQL Native Client ( i don't recall now what MS labelled the dll as ) or an ODBC layer to connect...

They are working with MS through TSANet, do you know if you or your company has a TSANet account? If so they would like to get you involved also, it's how TSANet works...

Thanks

Don

Don,

We do not have a TSANet account.

FYI, using OLE on 8.5 one can see the Synonym Tables, but using ODBC on 8.5 one cannot see the Synonym Tables. Also if I try using the "Microsoft SQL Server" option under "More Data Sources" in 8.5 I get an error message that says "General SQL Server Error"

Thanks,

David

Hi David,

Thanks for the info, we found another access point into MS to get more info.

Update for you.... Microsoft has confirmed that ODBC will not return the Synonyms using the API we use SQLTable().

From MS Thread:

1) Is this the limitation in SQL Server SQLTables ODBC API to return synonyms? u2013 Yes , this is by design limitation.

2) Is there any workaround to have SQLTables to return synonyms? or any other replaceable API to return synonyms. u2013 There is no workaround when using SQLTables. But the user is recommended to use sys.synonyms.

3) If this is a bug, when we can see the fix? u2013 The product team has confirmed that there will be no fix released for the issue with SQLTables. However the change to be done in our Microsoft Documentation ( both MSDN and BooksOnline ) is still pending.

(The result of the bug research by the product team was to modify Books On-Line documentation to state that SYNONYMS are not returned/queried by SP_TABLES.)

So next step is for our Developers to see if they want to implement the suggested API to use in our DB drivers to support MS Synonyms.... The issue is performance as you discovered, we have to optimize the query sent to get the tables, SP's, Views, Synonyms etc....

When they decide I'll let you know.

In the mean time if this is a show stopper for you you could use Views or SP's.

Don

Ask a Question

Top Q&A Solution Author

User

Count