INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to use a Synonym as a data source.

How to use a Synonym as a data source.

(OP)
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,








RE: How to use a Synonym as a data source.

(OP)
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.

RE: How to use a Synonym as a data source.

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
www.decisionfirst.com

RE: How to use a Synonym as a data source.

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close