Hi,

Has anyone tried to connect to a SQL Server view through ODBC Connection?

1 - I can generate metadata for SQL Server tables and Views through the console.

2 - I can sample data for the tables but not for the view using ODBC. The view gives the following error

"(FOC1400) SQLCODE IS 1 (HEX: 00000001) : [22005] [Microsoft][ODBC SQL Server Driver]Invalid character value for c : ast specification
(FOC1407) SQL FETCH CURSOR ERROR. : COMPONENT
"

3 - Above works for both tables and views if I use the iWay SQL Server adaptor.

Seems like SQL Server views are not supported using ODBC.

Thanks,
Sayed

This message has been edited. Last edited by: Kerry,

Does the connections work if you take WebFOCUS out of the picture, and use an ODBC test tool.

Waz,

Thanks for the suggestion. I tried with the software from Data Direct and it works for the views. Must be WebFOCUS that's causing the problems. I'm on WF 768 here.

Thanks,
Sayed

What is the SQL generated/SQL used in the fex ?

I didn't get to writing the report. It bombs when trying to sample data from the server console.

Thanks,
Sayed

The question may be silly but why aren't you using the iWay adapter to SQL server instead of forcing yourself to go via ODBC? I'm sure iWay can make a better SQL translation if you use the right adapter which may lead to better optimization anyway.

It is obviously interesting to know why you're getting the errors you mention as whatever happens "behind" ODBC should be transparent to WebFOCUS, shouldn't it?

quote:

It bombs when trying to sample data from the server console

Would you mind posting the whole stack of FOC- and/or SQL- errors you're getting?

This message has been edited. Last edited by: njsden,

I've seen and solved this before. There are some formats that have to be tweaked when using ODBC and MSSQL. What is the usage and actual for the COMPONENT column? Or just change the actual to something more generic then what it has been set to and you will be good to go.

Here is the master generated by ODBC. Tried changing values for Actual.. but no luck when i try to sample data.

 FILENAME=COMPONENT_ODBC, SUFFIX=SQLODBC , $
  SEGMENT=COMPONENT_ODBC, SEGTYPE=S0, $
    FIELDNAME=PNUM_CHI, ALIAS=PNUM_CHI, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=EFFECTIVE_DATE_CHILD, ALIAS=EFFECTIVE_DATE_CHILD, USAGE=HYYMDs, ACTUAL=HYYMDs, $
    FIELDNAME=END_DATE_CHILD, ALIAS=END_DATE_CHILD, USAGE=HYYMDs, ACTUAL=HYYMDs,
      MISSING=ON, $
    FIELDNAME=REVISION_VERSION_CHILD, ALIAS=REVISION_VERSION_CHILD, USAGE=A10V, ACTUAL=A10V,
      MISSING=ON, $
    FIELDNAME=INGREDIENT, ALIAS=INGREDIENT, USAGE=A100V, ACTUAL=A100V,
      MISSING=ON, $
    FIELDNAME=CAS_NUM, ALIAS=CAS_NUM, USAGE=A20V, ACTUAL=A20V,
      MISSING=ON, $
    FIELDNAME=INGREDIENT_PERCENT, ALIAS=INGREDIENT_PERCENT, USAGE=P9.2, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=STATE_REQ, ALIAS=STATE_REQ, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
 

Thanks,
Sayed

Nevermind,... DHAGEN your suggestion worked!!!

I changed the following on my master and now I can sample data.

     FIELDNAME=PNUM_CHI, ALIAS=PNUM_CHI, USAGE=A50V, ACTUAL=A50V,
      MISSING=ON, $
 

In SQL Server the field was defined as follows.. PNUM_CHI(int,null).

Thanks,
Sayed

This message has been edited. Last edited by: Sayed,