Database problem when generating reports using SQL Server 2008 R2

I have reports that are running perfectly in one PB application that, for some reason, will not produce the same results in a "related" PB application.  Both applications are using the exact same set of databases in a single instance of SQL Server 2008 R2 -and- both applications are using the EXACT same PowerBuilder logic.  PowerBuilder allows the programmer to "partition" logic into libraries in order to be able to keep reports separate from tables, system specific code separate from specific application code, etc.  Point is, that I'm using the same library in each of these two applications.  Everything works fine in the first application, certain reports are not working in the second application.  Further, this problem only occurs when running these applications on my client's site, while both applications work on my own network!?  I've tried everything I could think of and have spent an entire week working on this problem which I, frankly, never imagined would ever be any sort of issue.  Any ideas would be greatly appreciated.
Jim KlocksinOwner, Data ArchitectsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Use the table diff utility to confirm that the data is the same.

What does "certain reports are not working" mean exactly?
0
Jim KlocksinOwner, Data ArchitectsAuthor Commented:
The data is ABSOLUTELY the same since the exact same databases are used in both cases.  Specifically, what I'm doing is creating a "management" program, whereby certain higher level managers can look at certain reports for ALL departments within the company (there are currently about 35 separate departments that each have their own database, but are running the exact same version of the "original" program).  This "new" program allows the managers to select any department (from a list of all departments using the "first" program) and run the same report as if they were logged into that department WITHOUT having to log in to each department separately.  The reason I said "certain reports" is that some of the reports in the "new" program are working just fine.  3 of the reports in the "new" program are the issue here and I just can not figure out why these specific reports are not working.  Note also that ALL of the reports are contained in the same PB library, the ones that are working fine and the ones that are not!?
0
Mark ElySenior Coldfusion DeveloperCommented:
Check to see the what the default TIME/DATE settings are for each server?  Make sure they are both using the same format and region.    


Go to Control Panel -> Clock, Language, and Region  to verify the settings.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Jim KlocksinOwner, Data ArchitectsAuthor Commented:
Thanks for the thought, but all the date and time settings are EXACTLY the same.  One other thing I haven't mentioned is just something that I'm thinking.  I update certain database fields (for the reports that are NOT working) in stored procedures and it seems that these updates are NOT being saved/committed....this still doesn't make much sense to me since when I run the same reports within each individual department's database, the same stored procedures are being invoked and the updates ARE being saved/committed!?  I'm just at a total loss right now with this one....
0
Mark ElySenior Coldfusion DeveloperCommented:
Run a SQL Server Profiler on both databases.  
Name the Trace Name whatever you want... example DebugRun1
If you want to save the results to a select "Save to file" and name the file (Remember the path) NOTE this is optional.  You can run the trace without this.

Use the Standard (Default) template.  If you want to isolate a single database select the "Events Selection" tab then check the box for "Show all columns".   Slide the viewer to the right and check all the boxes in the second gray column will have a column named "DatabaseName".  

Then click on Column Filters and Click on DatabaseName ... click on the plus sign (expanding) next to "Like" and enter exact name of the database you want to focus on.  

Then run the trace... while the trace is running execute the stored procedures and queries you are having problems with.  

Do this for both servers then compare the results.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim KlocksinOwner, Data ArchitectsAuthor Commented:
Done for today, but I will run the traces early tomorrow and get back into this....at this point I'm exhausted (mentally)!
0
Jim KlocksinOwner, Data ArchitectsAuthor Commented:
I've run the traces, and the primary difference is obvious (data values are being calculated and updated on "My Network" while 0.00 is being updated for all values on my "Clients" server).  I've looked thru the files but, quite frankly, I'm not a SQL Server "guru", so I don't really know what I'm looking for.  I attempted to upload the files but EE doesn't allow .TRC files, so you can download both files, in a .ZIP file from http://www.data-architects.net/Download.asp  and review them to see if you can see what the problem is!?
0
Jim KlocksinOwner, Data ArchitectsAuthor Commented:
After comparing the 2 traces, I realized that the parameters being passed to my stored procedures were correct in the version that was working and wrong in the version that was not working.  Tracking down the exact source of this discrepancy took some time, but, ultimately, I was able to track down the EXACT problem (inserting debugging code into the program since I have no other way to debug in my client's environment).  Point is, your suggestion to run the trace reports using the SQL Server Profiler not only led me to the solution to my problem, but also taught me something as well (since I had never used the Profiler tool...).  Thanks for you solution and your "step-by-step" explanation on how to use the SQL Server Profiler tool!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.