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  iWay Software Product Forum on Focal Point    [SOLVED] common Table Expression in DMC 8.1
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] common Table Expression in DMC 8.1
 Login/Join
 
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, 2014Reply With QuoteReport This Post
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, 2014Reply With QuoteReport This Post
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, 2014Reply With QuoteReport This Post
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, 2009Reply With QuoteReport This Post
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, 2014Reply With QuoteReport This Post
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, 2009Reply With QuoteReport This Post
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, 2014Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.