BrioQuery v6.6.4 convert existing reports from DB2 to MS SQL Server

I am on a project where we are converting BrioQuery v6.6.4 existing reports from DB2 to MS SQL Server.

The reports are pre-existing and working.

HERE IS A LIST OF OBSERVATIONS:

1.
I open report1, and run it as is, it runs fine.  Yet if I open the 'view' -> 'custom SQL',
and run it it fails on 'Not Use11', here is the code:

select srst1staff.empusrid, vstb01be.subusrid
FROM From.Vstb01Be, From.Srst1Staff, From.Cont1Conti WHERE (Cont1Conti.Companyid=Vstb01Be.Companyid AND

Cont1Conti.Conid=Srst1Staff.Conid AND Srst1Staff.Conid=Vstb01Be.Supid)  AND (Vstb01Be.Subdate>='Not Use11' AND

Vstb01Be.Batstcd='O' AND Srst1Staff.Empusrid='AWILEY  ' AND Vstb01Be.Statuscd='A')

The error occurs on the 'Not Use11'.



2.
If I connect to db2 then hit the 'reset' button it updates the custom sql from the GUI:

select srst1staff.empusrid, vstb01be.subusrid
FROM From.Vstb01Be, From.Srst1Staff, From.Cont1Conti WHERE (Cont1Conti.Companyid=Vstb01Be.Companyid AND

Cont1Conti.Conid=Srst1Staff.Conid AND Srst1Staff.Conid=Vstb01Be.Supid)  AND (Vstb01Be.Subdate>='2002-06-11' AND

Vstb01Be.Batstcd='O' AND Srst1Staff.Empusrid='AWILEY  ' AND Vstb01Be.Statuscd='A')

Note: the 'Not Use11' has been replaced by '2002-06-11'



3.
Now I open a connection to MS SQL Server and run the query I get an error saying
"SQLSTATE 22008, Error Msg: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime

from character string."


4.
So now I open the custom SQL and hit the reset button, the code changes to:

select srst1staff.empusrid, vstb01be.subusrid
FROM From.Vstb01Be, From.Srst1Staff, From.Cont1Conti WHERE (Cont1Conti.Companyid=Vstb01Be.Companyid AND

Cont1Conti.Conid=Srst1Staff.Conid AND Srst1Staff.Conid=Vstb01Be.Supid)  
AND (Vstb01Be.Subdate>={d '2002-06-11 00:00:00.000'} AND Vstb01Be.Batstcd='O' AND Srst1Staff.Empusrid='AWILEY  

' AND Vstb01Be.Statuscd='A')

it gives the following error
"SQLExecDirect2: rc = -1, SQLSTATE 22008, Error Msg: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax

error converting datetime from character string."


5.
Now if I remain connected to MS SQL Server, remove the datetime field in the GUI, then add it back, open the

veiw custome SQL and hit the reset button the code is:

select srst1staff.empusrid, vstb01be.subusrid
FROM From.Vstb01Be, From.Srst1Staff, From.Cont1Conti WHERE (Cont1Conti.Companyid=Vstb01Be.Companyid AND

Cont1Conti.Conid=Srst1Staff.Conid AND Srst1Staff.Conid=Vstb01Be.Supid)  
AND (Vstb01Be.Subdate>={ts '2002-06-11 00:00:00.000'} AND Vstb01Be.Batstcd='O' AND Srst1Staff.Empusrid='AWILEY  

' AND Vstb01Be.Statuscd='A')

This run ok with the custom sql window open or closed.


So I have proof that BrioQuery is NOT A WYSIWYG (What you see is what you get).

QUESTION
Is there a bug fix for this from the vendor?



COPY OF FOLLOW UP EMAIL WITH HYPERION TECH SUPPORT 10/27/04
COPY OF FOLLOW UP EMAIL WITH HYPERION TECH SUPPORT 10/27/04
COPY OF FOLLOW UP EMAIL WITH HYPERION TECH SUPPORT 10/27/04
COPY OF FOLLOW UP EMAIL WITH HYPERION TECH SUPPORT 10/27/04


As we discused on the phone.

Here are the identical SQL exports.

What I did.

1. open report
2. run report against db2
3. put in user id, password
4. export sql to query_db2.txt
5. break connection
6. open view custome sql
7. run report against ms
8. put in user id, password
9. export sql to query_ms.txt
10. close custom sql window.
11. run report again.
12. here is the error
SQLExecDirect2: rc = -1, SQLSTATE: 22008, Error Msg: [Microsoft][ODBC SQL Server Driver][SQL Server] Syntax

error converting datetime from character string.

13. export sql to query_ms_error.txt

14. I can reproduce the error by not saving the report and closing it, then opening it againg and following the

same sequence.
THIS IS IMPORTANT, SHOW THIS TO A PROGRAMMER, I CAN REPRODUCE THE ERROR BY FOLLOWING THE SAME STEPS AS ABOVE.


query_db2.txt
SELECT AL1.BRANDNM, AL1.GENNAME, AL1.NDC, AL1.DSGFRMDE, AL1.DRGFRMCD, AL1.DRGSTRDE, AL1.DRGSTRNO, AL1.DRGSTRUN, AL3.ADADT, SUM ( AL3.CQTYSLD ), SUM ( AL3.CONSLSC ), SUM ( AL3.NCQTYSLD ), SUM ( AL3.NCONSLSC )  FROM SRIS.VPTB01NF AL1, VHA.MEMT1PHINQ AL2, SRIS.VPTB01SP AL3 WHERE (AL3.COMPANYID=AL2.COMPANYID AND AL3.HSPID=AL2.HSPID AND AL3.NDC=AL1.NDC)  AND (AL1.NDC IN ('61563020021', '65044999705') AND AL3.ADADT>='2004-01-01') GROUP BY AL1.BRANDNM, AL1.GENNAME, AL1.NDC, AL1.DSGFRMDE, AL1.DRGFRMCD, AL1.DRGSTRDE, AL1.DRGSTRNO, AL1.DRGSTRUN, AL3.ADADT


query_ms.txt
SELECT AL1.BRANDNM, AL1.GENNAME, AL1.NDC, AL1.DSGFRMDE, AL1.DRGFRMCD, AL1.DRGSTRDE, AL1.DRGSTRNO, AL1.DRGSTRUN, AL3.ADADT, SUM ( AL3.CQTYSLD ), SUM ( AL3.CONSLSC ), SUM ( AL3.NCQTYSLD ), SUM ( AL3.NCONSLSC )  FROM SRIS.VPTB01NF AL1, VHA.MEMT1PHINQ AL2, SRIS.VPTB01SP AL3 WHERE (AL3.COMPANYID=AL2.COMPANYID AND AL3.HSPID=AL2.HSPID AND AL3.NDC=AL1.NDC)  AND (AL1.NDC IN ('61563020021', '65044999705') AND AL3.ADADT>='2004-01-01') GROUP BY AL1.BRANDNM, AL1.GENNAME, AL1.NDC, AL1.DSGFRMDE, AL1.DRGFRMCD, AL1.DRGSTRDE, AL1.DRGSTRNO, AL1.DRGSTRUN, AL3.ADADT


query_ms_error.txt
SELECT AL1.BRANDNM, AL1.GENNAME, AL1.NDC, AL1.DSGFRMDE, AL1.DRGFRMCD, AL1.DRGSTRDE, AL1.DRGSTRNO, AL1.DRGSTRUN, AL3.ADADT, SUM ( AL3.CQTYSLD ), SUM ( AL3.CONSLSC ), SUM ( AL3.NCQTYSLD ), SUM ( AL3.NCONSLSC ) FROM SRIS.VPTB01NF AL1, VHA.MEMT1PHINQ AL2, SRIS.VPTB01SP AL3 WHERE (AL3.COMPANYID=AL2.COMPANYID AND AL3.HSPID=AL2.HSPID AND AL3.NDC=AL1.NDC)  AND (AL1.NDC IN ('61563020021', '65044999705') AND AL3.ADADT>={d '2004-01-01 00:00:00.000'}) GROUP BY AL1.BRANDNM, AL1.GENNAME, AL1.NDC, AL1.DSGFRMDE, AL1.DRGFRMCD, AL1.DRGSTRDE, AL1.DRGSTRNO, AL1.DRGSTRUN, AL3.ADADT
LVL 6
Duane LawrenceAsked:
Who is Participating?
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.

Duane LawrenceAuthor Commented:
I answered my own question.

There is a bug in BrioQuery that causes problems with dates.  I have a work around.  I just wanted to show documented proof from the vendor on the known problem.
 
Explanation:
When the dates are sent to DB2:  Cont1Conti.Conenddt>='2000-01-01'
 
When the dates are sent to SQL Server: Cont1Conti.Conenddt>={d '2000-01-01 00:00:00.000'}
         NOTE: The proprietary syntax {d }
 
What should be sent to SQL Server and is sent when a new date is added:  Cont1Conti.Conenddt>={ts '2000-01-01 00:00:00.000'}
         NOTE: The proprietary syntax {ts }
 
Please keep a copy of this email for long term.
Duane
 
 
 
 
-----Original Message-----
From: Timothy Dillingham [mailto:Timothy_Dillingham@hyperion.com]
Sent: Monday, November 01, 2004 12:47 PM
To: Lawrence, Duane
Subject: RE: SR# 2-4099804


Duane, converting from a DB2 to a Microsoft SQL Server database will cause problems with the SQL that is sent to the respective database.  
In your situation the DB2 database expects date/time SQL syntax differently the the Microsoft SQL Server database.  We currently do not automatically
update the SQL syntax when the database changes.  This must be done manually by removing then adding the date/time field.  This will change the SQL
syntax that is sent to the database.  Please let me know if you have any questions.  Thanks
 
Tim
0
ee_ai_constructCommented:
Question answered by asker or dialog valuable.
Closed, 500 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
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
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
DB Reporting Tools

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.