Focal Point
[SOLVED] SQL Server View with ODBC Connection

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/2957000216

November 02, 2011, 11:12 AM
Sayed
[SOLVED] SQL Server View with ODBC Connection
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,


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
November 02, 2011, 04:46 PM
Waz
Does the connections work if you take WebFOCUS out of the picture, and use an ODBC test tool.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

November 02, 2011, 06:12 PM
Sayed
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


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
November 02, 2011, 06:25 PM
Waz
What is the SQL generated/SQL used in the fex ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

November 03, 2011, 11:04 AM
Sayed
I didn't get to writing the report. It bombs when trying to sample data from the server console.

Thanks,
Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
November 03, 2011, 11:21 AM
njsden
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,



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
November 03, 2011, 11:56 AM
dhagen
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.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
November 03, 2011, 06:18 PM
Sayed
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


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
November 03, 2011, 06:25 PM
Sayed
Nevermind,... DHAGEN your suggestion worked!!! Smiler

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,


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE