DSXchange

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

Hello pxraja, could you give an example of the data and also post your table's DDL?

Table names containing space characters have to be quoted. Is yours?

ray.wurlod wrote:Table names containing space characters have to be quoted. Is yours? ...

Hi Ray,

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

It sounds like you have embedded spaces as Ray surmised. You will need to quote them in order to have DataStage use them correctly.

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. ...

So where I should do this, at database level or datastage level.

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

I would prefer to use table and column names without embedded blanks, but if you do custom SQL in your job and use double-quotes around the column names with blanks that should solve your problem.

Or just use a double-quoted name in the Table field.

ray.wurlod wrote:Or just use a double-quoted name in the Table field. ...

Hi Ray,

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