Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL Server View with ODBC Connection
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] SQL Server View with ODBC Connection
 Login/Join
 
Guru
posted
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
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6272 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
What is the SQL generated/SQL used in the fex ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6272 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL Server View with ODBC Connection

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.