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,

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

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

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.

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,

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.

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