odbc error while fetching data

pxraja · Post by **pxraja** » Sun Nov 23, 2008 8:33 am

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

ArndW · Post by **ArndW** » Sun Nov 23, 2008 8:36 am

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

ray.wurlod · Post by **ray.wurlod** » Sun Nov 23, 2008 1:50 pm

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

pxraja · Post by **pxraja** » Sun Nov 23, 2008 10:34 pm

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

ArndW · Post by **ArndW** » Mon Nov 24, 2008 2:41 am

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

pxraja · Post by **pxraja** » Mon Nov 24, 2008 3:34 am

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

ArndW · Post by **ArndW** » Mon Nov 24, 2008 4:45 am

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.

ray.wurlod · Post by **ray.wurlod** » Mon Nov 24, 2008 2:38 pm

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

pxraja · Post by **pxraja** » Mon Nov 24, 2008 9:21 pm

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