Hi all,
I want to fetch data from sql server database and load it in oracle database.
But sql server database has the table with fields having space between.,
like,
Policy No Number
Item code Number
while trying to fetch the data from sql server I am unable to fetch the data and I am not able to view the data also. Its throwing error like, Unexpected syntax near the field.
how to fetch those data from the table. any suggestions are welcome
thanks in advance
odbc error while fetching data
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Ray,ray.wurlod wrote:Table names containing space characters have to be quoted. Is yours? ...
I do not have sql installed in my machine, I have the access to import the table definition from the sql server, when i am doing this I cannot able to view the data, its throwing error like as follows..
DSBrowser..ODBC_0.DSLink2: DSD.BCIOpenR call to SQLExecDirect failed.
SQLSTATE=37000, DBMS.CODE=102
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Code'.
SQLSTATE=37000, DBMS.CODE=8180
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
column name like "Product Code"
when i remove that column and view another column for example
column_name: ProductCategory
record1: 1
record2: 2
.
.
.
I can able to view the data like above.
when I tried give quotes like "Product Code" odbc stage is throwing warning as column name should begin with alphabetic characters, underscores etc..
any suggestions are welcome to resolve this ..
thanks in advance
So where I should do this, at database level or datastage level.ArndW wrote:It sounds like you have embedded spaces as Ray surmised. You will need to quote them in order to have DataStage use them correctly. ...
what I have understood is the quotes should be give in the table in sql database. Is that right?
or can we able to do it in datastage itself? if yes how?
please clarify this and give suggestions.
thanks in advance
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Ray,ray.wurlod wrote:Or just use a double-quoted name in the Table field. ...
double-quoted name in the table field means where do you mean to say, in ODBC stage or SQL table structure?
I tried in ODBC stage its not accepting quotes in column name as well as in the derivation field.
Andrews idea works but I want to know the solution without custom query
any suggestions from your side? are welcome
thanks in advance