August 18, 2010 at 5:21 am
We have an integration services package which collects data from an Oracle 7 database. At the moment it collects quite a lot of data but we need to add just one field.
However when the field is added we get this error (please see attached). I do not know if this is an Oracle error or a Microsoft error. The SQL server version is
9.00.1399.06RTMStandard Edition
Is this a fault fixed by a service pack or is this an Oracle error?
Can someone point me in the right direction?
Thanks
Madame Artois
August 18, 2010 at 9:38 am
S Hodkinson (8/18/2010)
We have an integration services package which collects data from an Oracle 7 database. At the moment it collects quite a lot of data but we need to add just one field.However when the field is added we get this error (please see attached). I do not know if this is an Oracle error or a Microsoft error. The SQL server version is
9.00.1399.06RTMStandard Edition
Is this a fault fixed by a service pack or is this an Oracle error?
Can someone point me in the right direction?
Check generated query. ORA- error suggests query is malformed, like a missing "," in the select's column list or something of the sort.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 19, 2010 at 12:45 am
We have tried running the scripts attached but still get the error reported on the ODBC connection.
Madame Artois
August 19, 2010 at 6:42 am
S Hodkinson (8/19/2010)
We have tried running the scripts attached but still get the error reported on the ODBC connection.
Please do this...
Log into Oracle database via sqlplus then execute query - post session log.
If query fails take out newly added column then execute again on sqlplus - post session log.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 20, 2010 at 12:57 am
I do not have access currently to the Oracle database but I am trying to contact the people who have. Is there any way to check from SQL Server? Could it be the lack of service packs?
Madame Artois
August 21, 2010 at 6:01 pm
S Hodkinson (8/20/2010)
I do not have access currently to the Oracle database but I am trying to contact the people who have. Is there any way to check from SQL Server? Could it be the lack of service packs?
We don't know yet where the problem is, that's why we want to test queries running directly in Oracle.
Once we have certified our queries run in Oracle with no problems then we try from SQL Server... if they fail then the issue is on SQL Server side.
Usually I test and finetune every single query in Oracle side then I go to SQL Server side.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 26, 2010 at 3:34 am
Sorry for the delay; I was waiting for a colleague to return from leave.
With the help of my colleague, we have got to the bottom of this.
When you add the additional field, SQL server changed the ‘From’ clause to one with inner joins. However Oracle 7, which is what the database sits on, does not support inner joins. My colleague has now changed the sql to one which Oracle 7 supports and it does return data.
I am now waiting for another colleague to return from leave (so that he can understand what happened) before applying it.
Thanks for your help
Madame Artois
August 26, 2010 at 3:33 pm
S Hodkinson (8/26/2010)
Sorry for the delay; I was waiting for a colleague to return from leave.With the help of my colleague, we have got to the bottom of this.
When you add the additional field, SQL server changed the ‘From’ clause to one with inner joins. However Oracle 7, which is what the database sits on, does not support inner joins. My colleague has now changed the sql to one which Oracle 7 supports and it does return data.
I am now waiting for another colleague to return from leave (so that he can understand what happened) before applying it.
Thanks for your help
Thank you for the feedback.
That's why I was asking to run generated query on Oracle side, to be sure it will run there.
Great job!!!
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 1, 2010 at 3:39 am
My colleague has now returned from leave so we have sat down together and worked through it; he was as surprised as me. We have updated the original package (carefully keeping a copy!) so we should be OK now.
Thanks for all your help
Madame Artois
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply