x Want to participate? Join the group to interact
Options
  • State Suggested Answer
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 120 subscribers
  • Views 3780 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
91
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
962
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
brc

Dynamically pass new connection string for different SQL database to Crystal Report with a script

SUGGESTED
Posted By

Using Sage 100 Premium 2017

Using Crystal Reports 2011

I have six production SQL databases, all structured identically (for regional purposes).

I have created a Crystal Report using a Connection based on OLEDB  using one of the 6 databases.  It uses integrated security, no data is saved with it, and it works fine.

I have registered Sage100ERP.Reporting DLL using REGASM.

I have a VBscript that creates the required object (Set report = CreateObject("Sage100ERP.Reporting.Report")) and I have a connection string set to the database

connectionString = "Provider=SQLOLEDB;Server=SQL2008;Trusted_Connection=yes;Database=Live1;"

the connection is set:

report.SetConnection connectionString

and the report generates in Preview:

report.Preview()

Using Cscript from c:\Windows\SysWoW64, I can run the VBS and all is good--it accesses the report and generates the Preview page.

NOW, I want to change the Connection string so that the tables in the report look to a different database, say Live2.

If I change the connection string per above, the report generates but uses the database that was set up in the report, not Live2.

Anyone have any idea how to dynamically pass the connection string to the report so that a different Database is used?

  • 0

    Since you are in Premium, why not create SQL Views within your MAS_### databases to point to the correct external database?  (Assuming you have a 1-1 relationship with Sage database and external database... or just create hollow company codes as pointers).

  • 0 in reply to Kevin M

    Sorry for the delay in responding.  I didn't get a notification email to say there had been a response.

    The 6 databases are not Sage 100 companies--they are external systems.

    Sage dynamically passes a new connection string to identify each wrk file when a document/report is printed.  There must be a way to do this.

  • 0
    SUGGESTED

    You need to make sure the database setup in the report does not exist.  Crystal Reports will not override the database if the database in the report is still available.  If you ever look at Sage 500 reports, they are pointed to a database name that should never exists.  Let me know if this solves your issue.

  • 0 in reply to Sage100Reports

    I've come back to this because I didn't happen to see the reply way back then and it would still help to solve this issue.

    Still the same environment..sage 2017 CR2011

    So I made a duplicate database of the SQL database and updated the Crystal Report to use this duplicate database for the report.  It worked as expected.

    I then rename this Duplicate SQL database so that the one referred to in the CR no longer exists .

    I get the same error regardless if the "report.SetConnection ConnectionString" refers to the correct connection or if the ConnectionString is blank.  So, in short, it does not work.  I use the same connection string to access data elsewhere, so I don't think that is the issue.

    Is there something I'm missing in how to make sure the database does not exist ?

*Community Hub is the new name for Sage City