Skip to Content
author's profile photo Former Member
Former Member

Synonym table has no fields (Crystal Reports 2011 OLE DB sql server)

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Nov 22, 2011 at 02:34 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2011 at 03:54 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.