Sage 100

I have a crystal report querying my Sage company 400. I decided to switch over to another company to start working on some testing. I changed the source of my report and checked my SQL query. It all says it is pointed to the new company. The data on the report is still the original company. I have closed crystal, made sure I am not saving data with report. I have verified database after changing data source location. I mapped each table separately. I checked the table properties which is also showing the correct company. I was querying the table to get index numbers to match with a table I am importing. I have been waiting for VI to load each time. I am days into this thing to find out my data is trashed. It may just be me but thought I would mention for others well being and also see if anyone had insight. Crystal 2011 on Sage 100 Premium 2016.

Is Crystal using a SQL stored procedure for your query? If so, is the stored procedure also in the new company database, the company you decided to switch over to for testing?

I have not intentionally created or used a stored procedure. It seems to be that Crystal is creating new OLE connections for each report and this is part of my issue. I cannot seem to determine the cause of the issue which makes it difficult to solve but now I appear to have one report to one company and another to the other.

Maybe for testing and troubleshooting you could set up an ODBC data source and see if you get the same results as the OLE DB connection.

At the moment I decided to go back to the original company. After I do this load I am going to copy it to a the second. That way both have the same data and same Sage index numbers so I can get this one time project done. I would like to solve it long term at some point. Good idea to try the ODBC. I am curious if you can change source from one type to another.

You can definitely change the data source from one type to another. Create the new data source. Then, in Crystal, click Database, Set Data Source Location. Highlight the current data source at the top of the screen. Click the new data source in the bottom half of the screen. Click Update.

Were you running the report from Sage 100 as a custom report? With SQL that's the easiest way to switch company codes. Otherwise I'd create a DSN for each, and use Doug's Set Data Source Location method to make the change.

I am directly in Crystal and was creating an OLE DB to do the job. The name was the server name SA permissions with a database being assigned. Crystal seems to be ignoring my suggestions of changing the data source.