How to use a Synonym as a data source.

[AndersonCJA]

I want to be able to select database location on an existing report where the Synonym is replacing another data source.
Or, just create a simple report using the Synonym.
However, the DBO schema is not visible.

Using Crystal Reports XI version 11.0.0.1282 on Windows 7 Professional, and sql server.
Normally to create a report, I open Crystal Reports, Select New, Blank report.
Then, from the available data sources, I select ODBC(RDO).
From there I select my data source and sign on to the data source.
I select the Database and finish.
Normally, on the left hand side, the data source is displayed in
an explorer window with a variety of schema names underneath it.
Examples are, Dbo, Information_Schema and sys.
When using the Synonym as the data source, the schema item, DBO is not available as usual where I
normally would select the tables from.

With the CMD feature, this query worked to simply select records from the table.
SELECT *
FROM
Synonym.dbo.table1 table1

I want to be able to select database location on an existing report where the Synonym is replacing another data source.

This is the first time I have worked with a Synonym data source and would like understand why I can not use it the same way as
other data sources in my Crystal Report.

Thank you,

 

[AndersonCJA]

To resolve the issue, the sql was created using the Non synonym table and tested in another database tool. Once it was working correctly, using notepad, the non synonym table name was changed to the synonym name. That query was then copied into a CMD field in the Crystal report.

 

[hilfy]

It's great that you found a way to do this. There are some things you need to be aware of when using commands, though. Please take a look at my blog post (

http://bit.ly/1LcUc1h)

for information about the best practices for using commands.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[AndersonCJA]

hiffy,

Thank you for your reply. Your blog post is informative and I will revisit my report commands and update them accordingly. At the moment, all seems great with the current set up, with a test database with less than 1k records. Thank you for the eye opener.