Problem using DTS to copy table to Progress Database (Error: [MERANT][ODBC PROGRESS driver]Function sequence error.)

barksdaleIT
barksdaleIT used Ask the Experts™
on
I am trying to data transform task to copy data from a SQL Server 2000 table to a Progress 8.3E database table. I am connecting to the Progress database using the Merant DataDirect Technologies ODBC drivers v3.7.

When I run the DTS job I get the error:

[MERANT][ODBC PROGRESS driver]Function sequence error.

I turned on logging in the data transform task and I found the following error for every other record: (2, 4, 6, 8...)

@@ErrorRow:  2
Error at Destination for Row number 2. Errors encountered so far in this task: 1.

Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Description:[MERANT][ODBC PROGRESS driver]Function sequence error.
Error Help File:
Error Help Context ID:0
@@SourceRow:  Logged
@@DestRow:  Logging Failed

Error code: 8007000E

I have tried turning off fast load, using another computer, changing lock options, with no luck.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
jrb1senior developer
Top Expert 2005

Commented:
Here is one solution I found:
======
It sounds like the Dsrv parameter is not set correctly.

Confirm that your conneciton string for 9.0 contains the following:
-Dsrv PRGRS_CONNECT,DSN=<dsnName>,ASYNC_ENABLE,0,AUTOCOMMIT,1

For 9.1 you would use:
-Dsrv PRGRS_CONNECT,DSN=<dsnName>,ASYNC_ENABLE,0,TXN_ISOLATION,1

In this case the dsnName is the name of our ODBC Data Source.

Author

Commented:
Does the solution show how to use these lines?
jrb1senior developer
Top Expert 2005

Commented:
barksdaleIT,

Sorry, the link to the message is broken...finding lots of missing pages.  But here are some details on using that parameter:
=================
6.1 Tuning Your Environment with the -Dsrv Startup Parameter
The DataServer (-Dsrv) startup parameter allows you to use special ODBC or DataServer options to tune your environment. You pass these options as arguments to -Dsrv when connecting to an ODBC data source.

There are two versions of the syntax, as follows:


SYNTAX

CONNECT data-source-name -ld logical-name -dt ODBC


-Dsrv arg1,val1 -Dsrv arg2,val2 -Dsrv arg3,val3...

 



SYNTAX

CONNECT data-source-name -ld logical-name -dt ODBC


-Dsrv arg1,val1,arg2,val2,arg3,val3...

 


In this syntax:

The schema holder is already connected.

The data-source-name argument is the name of the data source and the logical-name argument is its logical name, which is defined when you created your schema image.

You pass the options as the argn,valn pairs.

Here is an example of how to use the CONNECT statement with the -Dsrv parameter:



CONNECT mssql_db -ld mydb -Ux -Py


   -Dsrv qt_debug,EXTENDED


   -Dsrv PRGRS_IDBUF,100


   -Dsrv PRGRS_MINBUF,10000


   -Dsrv MAX_R,10000.

 


Note that MAX_R is an abbreviation of MAX_ROWS. You can abbreviate option names as long as they identify parameters uniquely.

Both the syntax statements and the example show the use of the -Dsrv startup parameter in CONNECT statements. You can also specify -Dsrv options in a parameter file, on a program item command line, or in the Connection Parameters field in the Database Connect dialog box.

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I tried adding these options to the startup parameters for the ODBC broker, but it didn't seem to fix the problem.

Some of the variations that were tried:

_prooibk -SV -S prod-oib -Dsrv PRGRS_CONNECT,ASYNC_ENABLE,0,AUTOCOMMIT,1
_prooibk -SV -S prod-oib -Dsrv ASYNC_ENABLE,0,AUTOCOMMIT,1
_prooibk -SV -S prod-oib -Dsrv AUTOCOMMIT,1

Any ideas?
senior developer
Top Expert 2005
Commented:
Another possibility:

http://knowledgebase.datadirect.com/kbase.nsf/SupportLink+Online/2465103DH?OpenDocument

When going to a Progress Database using the SQL89 engine, you may get the following errors if you are in autocommit mode and you have multiple resultsets open:

DIAG [01S01] [MERANT][ODBC PROGRESS driver]Error in row. (0)

DIAG [S1010] [MERANT][ODBC PROGRESS driver]Function sequence error. (0)

When using the SQL89 engine, one should fetch resultset per resultset when in autocommit mode. Don't have multiple open resultsets at the same time when in autocommit mode.  Also, your application may allow you to specify that you do not want autocommit mode to be turned on.
 
Go here for more information on AutoCommit mode: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odch21dpr_5.asp 
jrb1senior developer
Top Expert 2005

Commented:
Another possibility, depending on your version:

09-34023 Function Sequence Error During Browse

You might see the following function sequence error when you try to do a delete during a browse in Version 9.1B11:

S1010: [Microsoft] [ODBC SQL Server Driver] Function sequence error.

The session might hang and die on another attempt following a restart.

Earliest Version Detected: 9.1B
Earliest Version Fixed: 9.1C

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial