TOPIC: No records when specifying database account in SB

No records when specifying database account in SB 22 Sep 2011 21:30 #878

  • acouron@crockerglobal.com
  • acouron@crockerglobal.com's Avatar
  • Offline
Anyone know why my reports (whether saving or viewing) have no records when I specify a database user account and pw?

If I promt for ccount and pw it works but if I specify it returns zero records.

Greg
The administrator has disabled public write access.

Re:No records when specifying database account in SB 26 Sep 2011 20:05 #881

  • acouron@crockerglobal.com
  • acouron@crockerglobal.com's Avatar
  • Offline
Through troubleshooting I have narrowed this down to SB not recognizing the lastfullmonth date range function.

If I change to last7days, it works fine.

The report works in Crystal, Logicity Desktop, but when using SB, no records are being displayed.

So in the reord selection criteria, If I enter hard dates that would represent first and last date of last month or use any of the other date range functions, the report can be viewed in SB just fine. Switch back to lastfullmonth and it pulls zero records.

Is it a matter of the lastfullmonth not being supported by version 1.6.24.0?

Greg
The administrator has disabled public write access.

Re:No records when specifying database account in SB 26 Sep 2011 20:10 #882

  • acouron@crockerglobal.com
  • acouron@crockerglobal.com's Avatar
  • Offline
Sorry, Last7Days does not work either.

However yeartodate and mothtodate do work in SB.

Greg
The administrator has disabled public write access.

Re:No records when specifying database account in SB 26 Sep 2011 20:11 #883

  • sduranceau
  • sduranceau's Avatar
  • Offline
Hello!
Can you post a copy of your report, and your Logicity Solution File?

Thanks,
-Devin
The administrator has disabled public write access.

Re:No records when specifying database account in SB 26 Sep 2011 20:57 #884

  • acouron@crockerglobal.com
  • acouron@crockerglobal.com's Avatar
  • Offline
Will do, although I think it goes beyond what I was just explaining. I also just found out that the same table name exists under multiple oracle shemas in the database.

Even though the report is pulling zero records, I think it is pulling from wrong table as there is no data in that other table.

I am having a DBA grant my user right to only one table (with data in it) and then retesting in SB.

Will reply as soon as I test.

Greg
The administrator has disabled public write access.

Re:No records when specifying database account in SB 26 Sep 2011 21:07 #885

  • acouron@crockerglobal.com
  • acouron@crockerglobal.com's Avatar
  • Offline
Do you happen to know if SB keeps any temp files or recent connection history anywhere that it might be referencing?
The administrator has disabled public write access.

Re:No records when specifying database account in SB 03 Oct 2011 17:39 #886

  • acouron@crockerglobal.com
  • acouron@crockerglobal.com's Avatar
  • Offline
The resoultion I found was that there was an issue with the same table name under two schemas. One happened to also be a public synonym but was not the table I wanted to base report off of. I then created a private synonym for the one I wanted however there apparently was an issue having the private synonym named exactly the same as the table. Since I could removed the public synonym for other reasons, I had our DBAs create a new private synonym giving it a unique name, now it all works fine.
The administrator has disabled public write access.

Re:No records when specifying database account in SB 04 Oct 2011 21:10 #889

  • acouron@crockerglobal.com
  • acouron@crockerglobal.com's Avatar
  • Offline
I found the issue was due to two issues.

First issue was that the ODBC driver was somehow not refreshing connection credentials. Therefore the CR viewer was finding records under the tale name of the second schema which had a public synonym with the same name as the table name.

After we created a private synonym to the correct table, updated database location, the issue was partly resolved. However there can be issues with CR where synonyms have the same name as the table in one particular schema.

I simply gave the private synonym a different name, updated database location, and now all is working.

By giving the private synonym a unique name, yu also ensure connection to correct location as it no longer uses a name of a table that exists in multiple schemas.

Thought I would share my finding.

Greg
The administrator has disabled public write access.