Problems extracting data from Lotus Notes using NotesSQL
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 5
- Joined: Mon Jul 12, 2004 6:50 am
Problems extracting data from Lotus Notes using NotesSQL
When setting up an ODBC stage to import Lotus Notes data using Datastage 7.1, I'm able to load the views and field names in my ODBC stage, but I am unable to query the data with the 'view data' button.
The error that I get is:
DSBrowser..DSBrowser1: DSD.BCIConnect connecting to xxxxx, call to SQLConnect failed.
SQLSTATE=IM005, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed
The ODBC driver that I'm using is NotesSQL 3.02.2003.1112.
If anybody could give me any advice on this I'd be grateful.
Many thanks in advance,
RJ
The error that I get is:
DSBrowser..DSBrowser1: DSD.BCIConnect connecting to xxxxx, call to SQLConnect failed.
SQLSTATE=IM005, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed
The ODBC driver that I'm using is NotesSQL 3.02.2003.1112.
If anybody could give me any advice on this I'd be grateful.
Many thanks in advance,
RJ
Welcome. :D
Out of curiousity, what happens when you run the job? Do you get the same error? I've seen times where you could run but not view, due to some of the 'quirks' in the View Data code, hence the question...
Also, might help people to know what version of Notes you are accessing, from what I remember the internal data structures underwent some significant changes 'lately'.
Out of curiousity, what happens when you run the job? Do you get the same error? I've seen times where you could run but not view, due to some of the 'quirks' in the View Data code, hence the question...
Also, might help people to know what version of Notes you are accessing, from what I remember the internal data structures underwent some significant changes 'lately'.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 5
- Joined: Mon Jul 12, 2004 6:50 am
Thanks for the quick reply Craig, and thanks for the welcome.
When I run the job, I get exactly the same error.
I'm probably wrong, but I get the sense that it could be caused by the way that DS passes the requested table names to Notes - maybe I need to qualify them somehow.
What makes me think this is the fact that I can 'Get SQL Info' from the DB and can retrieve the list of tables, but as soon as I try to access actual tables, I get this error.
By the way, the version of Notes is 6.0.1CF1
Cheers,
RJ
When I run the job, I get exactly the same error.
I'm probably wrong, but I get the sense that it could be caused by the way that DS passes the requested table names to Notes - maybe I need to qualify them somehow.
What makes me think this is the fact that I can 'Get SQL Info' from the DB and can retrieve the list of tables, but as soon as I try to access actual tables, I get this error.
By the way, the version of Notes is 6.0.1CF1
Cheers,
RJ
Not sure how many people here have experience with sourcing from Notes or with that particular ODBC driver. I don't. We had Notes push data directly to Oracle staging tables rather than trying to go get it ourselves when I had to do this in a past life.
You may have better luck contacting Ascential support directly and seeing if they can help or are aware of any issues.
You may have better luck contacting Ascential support directly and seeing if they can help or are aware of any issues.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This has always been tricky. The only time I've seen this actually working was on Notes version 4.?, with a user ID that had an empty password. Most Notes administrators won't allow this.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 5
- Joined: Mon Jul 12, 2004 6:50 am
-
- Participant
- Posts: 5
- Joined: Mon Jul 12, 2004 6:50 am
In case there's anybody else having this problem, I've discovered that this is being caused by an incompatibility between NotesSQL v3.02 and the DataStage ODBC manager.
Apparently, this can be resolved by using NotesSQL 3.01, although I haven't tested this yet as I'm having difficulty getting hold of this version - in spite of the fact that it's a free app, IBM don't seem to want to help me out in a hurry.
When I'm finally able to try it out, will let you know how I got on...
RJ
Apparently, this can be resolved by using NotesSQL 3.01, although I haven't tested this yet as I'm having difficulty getting hold of this version - in spite of the fact that it's a free app, IBM don't seem to want to help me out in a hurry.
When I'm finally able to try it out, will let you know how I got on...
RJ
OK, I can also confirm that only NotesSQL 3.01 is working OK. Using older or newer version is cousing lots of troubles.
KR, Bostjan
PS
If someone needs NotesSQL 3.01 version send me email to bostjan.pavle@snt.si
KR, Bostjan
PS
If someone needs NotesSQL 3.01 version send me email to bostjan.pavle@snt.si
KR, Bostjan
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
More and more it is pointing to a bug with DataStage (at least in 8.1). I am still actively working on this. Update: I can see Domino tables and views via the ODBC DSN in 'import table connections'. I get ODBC errors when clicking 'view data' or running the stage. In excel, I am able to import data using the same ODBC DSN that is failing in DataStage.
We decided to purchase LEI from IBM, which is definitely the better solution, but it would be nice to get ODBC working in DataStage for quick and easy transfers.
We decided to purchase LEI from IBM, which is definitely the better solution, but it would be nice to get ODBC working in DataStage for quick and easy transfers.