Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] common Table Expression in DMC 8.1

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] common Table Expression in DMC 8.1
 Login/Join
 
Virul Patel
Silver Member
posted
We have Procedure .fex in DMC, It was working fine in DMC 7.*, but not working after upgrade to DMC 8.1

Procedure .fex is using SQLMSS with SQL common Table Expression.

in DMC 8.1 if I remove Common table expression, SQL query is working fine, but it is not working with common table expression.

The same procedure .fex was working fine in DMC 7.*

We get below error in DMC 8.1

Microsoft OLE DB Provider for SQL Server: [] Multiple-step OLE DB operation generated errors.

Any suggestion.

Thanks, Virul

This message has been edited. Last edited by: Virul Patel,


Virul Patel
Product :
iWay Data Migrator (8.1)
WebFocus 8.1
SQL SERVER 2012
 
Posts: 32 | Location: Cambridge, Ontario, Canada | Registered: June 02, 2014Report This Post
Virul Patel
Silver Member
posted Hide Post
So, solution is here:

Error is misguiding to OLEDB. Instead its Java based connection causing issue. another blog found that putting below line before connection to SQLMSS, will resolv issue.

Put below line before SLQMSS connection

ENGINE SQLMSS SET CURSORS CLIENT

Its tested and solved.

Thanks, Virul


Virul Patel
Product :
iWay Data Migrator (8.1)
WebFocus 8.1
SQL SERVER 2012
 
Posts: 32 | Location: Cambridge, Ontario, Canada | Registered: June 02, 2014Report This Post
Virul Patel
Silver Member
posted Hide Post
However, I found that Line
ENGINE SQLMSS SET CURSORS CLIENT

Is already exist in Server Profile edasprof.prf. then what is the reason, I need to put this line in my procedure .fex

Amy advice,

Thanks, virul


Virul Patel
Product :
iWay Data Migrator (8.1)
WebFocus 8.1
SQL SERVER 2012
 
Posts: 32 | Location: Cambridge, Ontario, Canada | Registered: June 02, 2014Report This Post
Tamra
Guru
posted Hide Post
Hi Virul,

I am following up with you regarding your question -" common Table Expression in DMC 8.1"

It looks like you have a resolution but have an additional question about the location of a set of
commands.

ENGINE SQLMSS SET CURSORS CLIENT - this can be in the in the edasprf as a global command and available to everyone or if added in the procedure it is for that specific process.

Here is a link from the IBI Technical support site that might be useful.

Summary: SQL Server gets: Cannot create new connection

If this helps then please update the subject line with [SOLVED]

Thank you for participating in the Focal Point Forum.

Kindest regards,
Tamra Colangelo
Focal Point Moderator
Information Builders Inc.


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Report This Post
Virul Patel
Silver Member
posted Hide Post
Tamara, Thanks for your reply.

ENGINE SQLMSS SET CURSORS CLIENT, is alredy exist in edasprf file. However it does not work for me, and as a resolution, I need to put in procedure, and that is something that I have question.

I tried to open the link that you provided. it says Error 404 document not found

Please provide another link.

Thanks, Virul


Virul Patel
Product :
iWay Data Migrator (8.1)
WebFocus 8.1
SQL SERVER 2012
 
Posts: 32 | Location: Cambridge, Ontario, Canada | Registered: June 02, 2014Report This Post
Tamra
Guru
posted Hide Post
Virul,

Can you please try the link again - sorry for the inconvenience - SQL Server gets: Cannot create new connection

Here is the information within the link:

Review the documentation for the following Data Adapter settings as they are
relevant for the error being received: "Adapter Administration for UNIX,
Windows, OpenVMS, IBM i, and z/OS 7.7.05" (DN4501040.0114) at:
http://documentation.informati...x/html/html_wf_7705/
adapteradmin7705/index.htm
(Please note the above URL spans multiple lines.)

In the SQL Server Data Adapter Chapter, these settings are relevant for the
error being received:
ENGINE SQLMSS SET CURSORS [CLIENT|SERVER]
ENGINE SQLMSS SET TRANSACTIONS {LOCAL|DISTRIBUTED|AUTOCOMMITTED}
ENGINE SQLMSS SET FETCHSIZE n

Specifying the following SQL Server Data Adapter setting in the edasprof.prf
resolved the issue:
ENGINE SQLMSS SET CURSORS SERVER

Thank you for participating in the Focal Point Forum.

Kindest regards,
Tamra Colangelo
Focal Point Moderator
Information Builders Inc.


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Report This Post
Virul Patel
Silver Member
posted Hide Post
Tamara,

I reviewed the link that you provided. and I am agree that we should have below line in edasprof.prf file.

At our setup we already have all below line included in edasprof.prf file.

ENGINE SQLMSS SET CURSORS CLIENT
ENGINE SQLMSS SET CURSORS SERVER

having above line in edasprof.prf, means I really don't need to set
ENGINE SQLMSS SET CURSORS CLIENT
on the procedure code, however procedure required above line inside procedure.

That is the think I am thinking that something is missing in somewhere config.

however the workout, I do is working for me for now.

I am adding "ENGINE SQLMSS SET CURSORS CLIENT" to each procedure and it is working fine, but I know that this line is really not required on procedure level. and it is already setup on edasprof.prf file.

Thanks, Virul


Virul Patel
Product :
iWay Data Migrator (8.1)
WebFocus 8.1
SQL SERVER 2012
 
Posts: 32 | Location: Cambridge, Ontario, Canada | Registered: June 02, 2014Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] common Table Expression in DMC 8.1

Copyright © 1996-2020 Information Builders