Function Sequence Error -- After upgrading to Crystal Reports 2008

[Former Member]

Since we intergrated Crystal 2008 in our application, We are having the ODBC DRIVER ERROR "S1010, Function Sequence Error"

The following steps reproduces the error.

I open any crystal report(using my application) and close it.

And then I try to close another dialog in my application.

The destructor of that dialog has the DELETE FROM TMPRPT WHERE etc... But actually the TMPRPT table is empty.

But This Scenario in general, does not produce any error. Only after I open and close Crystal report, and when the TMPRPT table doesn't have any records, executing the above DELETE sql throws CDBException, Funciton Sequence Error.

Is it because of the upgrade/mismatch of dlls? Can any one help how to work around this?

Thanks.

[Former Member · Accepted solution]

Is this the right forum for this or should i ask somewhere else?

Can any one pls help me?

Thanks.

[former_member183750 · Accepted solution]

Please see the [Rules of Engagement|https://www.sdn.sap.com/irj/sdn/wiki?path=/display/home/rulesofEngagement].

- Why is Nobody Answering my Question?

There may be a variety of reasons:

1) Never assume that you are entitled to an answer. Remember: this is a community forum; people have jobs just like you and respond voluntarily. Perhaps nobody knows the answer.

2) Did you give the other users enough time?

3) Is your subject misleading? your post in the right forum? your problem description understandable?

This is only a forum and answers may take over 24 hours to post. If you nede immediate attention, I'd recommend obtaining phone support here:

http://store.businessobjects.com/store/bobjamer/DisplayProductByTypePage&parentCategoryID=&categoryI...

Ludek

Edited by: Ludek Uher on Jan 27, 2009 9:43 AM

[Former Member · Accepted solution]

Thanks for the reply. I just was not sure, if i should ask in crystal reports design forum or here.

I understand very well that help here is voluntary and I appreciate that.

[former_member11696 · Accepted solution]

You are in the right forum.

Are you disposing of the report object and your data connection info? Then use GC.Collect to force Windows to clean up the close method.

It may be the report is still busy waiting for something or processing the data.

Thank you

Don

[Former Member · Accepted solution]

My form closed is like below.

private void CrystalReportsForm_FormClosed(object sender, FormClosedEventArgs e)

{

try

{

CleanupRptSql();

Tables tables = _reportDocument.Database.Tables;

foreach (Table table in tables)

table.Dispose();

// Clean up by closing and disposing of the ReportDocument object

if (_reportDocument != null)

{

_reportDocument.Database.Dispose();

_reportDocument.Close();

_reportDocument.Dispose();

}

crystalReportViewer.Dispose();

GC.Collect();

GC.WaitForPendingFinalizers();

}

catch (Exception Ex)

{

MessageBox.Show(Ex.Message, "CrystalReports Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

}

}

I connect to the database like below

private void SetDBLogonForReport(ConnectionInfo connectionInfo, ReportDocument reportDocument)

{

try

{

Tables tables = reportDocument.Database.Tables;

foreach (Table table in tables)

{

TableLogOnInfo tableLogonInfo = table.LogOnInfo;

tableLogonInfo.ConnectionInfo = connectionInfo;

table.ApplyLogOnInfo(tableLogonInfo);

}

}

catch (Exception)

{

throw;

}

}

somehow, iam not able to release database properly. That is the reason my "ExecuteSql" func throwing the "Function Sequence Error".

Is there some thing that I am missing while closing database connection? Any help would be greatly appreciated

[Former Member · Accepted solution]

I noticed in ProcessExplorer that my CrystalLibrary(C#) is still holding database.

That is the reason my VC++ code gets function Sequence error in ExecuteSql function.

Is there any thing else I should be doing to release all crystal library dlls once i close the report?

Please help.

[former_member11696 · Accepted solution]

Hello,

That seems odd. When the report object is closed and disposed of CR will release any DB connections we have open. Our core dll's will stay in your app memory space until your application is closed but if no report objects are open we should be releasing all DB connections.

Only reason we are not would be because your app is keeping the connection info active. Is there some other place you are making the DB connection and setting it to the report object? Or simply making the connection using Cr API's?

Also, you do not need to dispose of the viewer:

crystalReportViewer.Dispose();

When your form closes the viewer child process will close itself. And we actually prefer you let us clean up after ourselves, we have unload methods within the dll's and we need to follow our steps as defined in the unload methods.

Not sure if you are doing anything in your app to make a DB connection so try removing all access to the report object to see if any connections are still active.

Thanks again

Don

[Former Member · Accepted solution]

Thanks for your reponse.

I will remove crystalReportViewer.Dispose(); from my code.

We have some legacy code in our application that connects to sybase database using odbc(it is vC++).

After I open my crystal library and opens any crystal report (using the above connectionInfo) and close that form(C# module) and goes back to my legacy code, the ExecuteSql function(which was executing fine previous to my crystal api) is now throwing the DBException.

I am not sure what is happening here. I feel like, crystal uses some sort of latest odbc drivers or files which are contradicting my previous odbc connection(vc++ code)

I found an article in your site at https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/c0ffb38b-fb1f-2b10-b699-fac3b31a...

but none of the links work.

Can you please give your insight on this one?

[former_member11696 · Accepted solution]

Hi Lavanya,

That doc won't help here. Can you turn on ODBC tracing and single step through your code to see if Cr is disconnecting or leaving any connection open?

This issue may be to deep to use forum to handle it. Can you purchase a case on line and make a note to notify me to take your message or simply assign it to me.

I have some logging dll's that we can use to see what CR is doing but we can't pass files back and forth in forums.

Thank you

Don

[Former Member · Accepted solution]

Thanks for your help.

I ran odbc tracer. looks like all the disconnection and Freehandle went smooth until it came to c++ code and executed the delete statement which return empty result set.

I wanted to copy that output to you. but for some reason i am not able to run that odbc tracer again. even tried through registery, but of no use.

I haven't got any approval for purchasing a case.

[former_member11696 · Accepted solution]

Hi Lavanya,

In your legacy C++ code what API's are you using? It's possible in the old version we did not do a FreeSQLConnect ( something like that to release the connection ) which means the connection was still active and allowed the code to send the delete statement. It may be that you have lost connection which explains why it fails, no DB connection to send the delete to. In later versions of CR we now make the HDBC handle and then open a thread on the handle. When CR closes it then closes the HDBC handle, this may be the problem for you now.

We you using LogonSQLServerWithPrivateInfo in the C++ app?

You may need to re-do your legacy C++ code, open a new connection and then delete the temp table.

MS ODBC trace can be a pain at times. I find what works for me is Open ODBC Admin, click on the Trace tab, create the log file name and close the Admin. Re-open Admin and then start tracing, run your app, close your app to release the log file, stop tracing, close Admin, re-open your app and log file...

Thanks again

Don

[Former Member · Accepted solution]

Hi Don,

I would like to thank you for your helpful advice. your tips for odbc tracing really works.

Just to simplify things, I have created a simple mfc dialog based application that opens a connection using CDatabase in the initdialog and closes the connection in the destructor(because that is how we do in our main large application). On the dialog i put a "Print" button and when i click it, I open a crystalreportform and fill the connectioninfo structure and then call SetDBLogonForReport(ConnectionInfo connectionInfo, ReportDocument reportDocument). on the Form_closed function, I close database connections and close the report document.

When I come back to mfc application I execute a Delete from table where 1 =0, basically any delete/update that return empty recordset and it throws function sequence error.

BOOL CCrystalDemoDlgDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	ConnectDatabase() ;

	return TRUE;  // return TRUE  unless you set the focus to a control
}

BOOL CCrystalDemoDlgDlg::ConnectDatabase() 
{
	if ( m_Database.IsOpen() )
		m_Database.Close();

	// Process database open request.
	CString szConnection("DSN=CRYSTALTEST32;UID=DBA;PWD=picture");
                 !m_Database.OpenEx( szConnection, CDatabase::noOdbcDialog ) )

	return TRUE;
}


BOOL CCrystalDemoDlgDlg::bExecuteSQL( CString SqlString )
{
                if(m_Database.IsOpen())
	m_Database.ExecuteSQL( (LPCTSTR)SqlString );
	return TRUE;
}

void CCrystalDemoDlgDlg::OnBnClickedBtnPrint()
{
	TRY
	{
		bExecuteSQL(_T("DELETE FROM TMPRPT WHERE 1=0"));
		
		CrystalReportsForm ^ CRForm = gcnew CrystalReportsForm(gcnew System::String("ActvSumm1.rpt"));
		CRForm->ShowDialog();
		//CRForm->RunCrystalReports();

		delete CRForm;
		CRForm = nullptr;

		bExecuteSQL(_T("DELETE FROM TMPRPT WHERE 1=0"));
	}
	CATCH(CDBException, e)
	{
		AfxMessageBox( e->m_strError );
		return ;
	}
	END_CATCH	
}

the following is the code in crystalreports library

namespace CR2008Library
{
    public partial class CrystalReportsForm : Form
    {
        private ReportDocument _reportDocument;
        private string _reportFile = "C:\\Nomadic\\Report\\";

        public CrystalReportsForm(string reportFile)
        {
            InitializeComponent();
  			_reportDocument = CreateReportDocument(reportFile);
        }

        private ReportDocument CreateReportDocument(string reportFile)
        {
            ReportDocument newDocument = new ReportDocument();

            _reportFile += reportFile;
            newDocument.Load(_reportFile);

            return newDocument;
        }
        
        public void ConfigureCrystalReports()
        {

            ConnectionInfo connectionInfo = new ConnectionInfo();
            connectionInfo.DatabaseName = "CRYSTALTEST";
            connectionInfo.UserID = "DBA";
            connectionInfo.Password = "picture";
            connectionInfo.ServerName = "CRYSTALTEST32";
            SetDBLogonForReport(connectionInfo, _reportDocument);

            crystalReportViewer.ReportSource = _reportDocument;

        }
        private void SetDBLogonForReport(ConnectionInfo connectionInfo, ReportDocument reportDocument)
        {
            Tables tables = reportDocument.Database.Tables;
            foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
            {
                TableLogOnInfo tableLogonInfo = table.LogOnInfo;
                tableLogonInfo.ConnectionInfo = connectionInfo;
                table.ApplyLogOnInfo(tableLogonInfo);
            }
        }

        public void CrystalReportForm_Load(object sender, EventArgs e)
        {
            ConfigureCrystalReports();
        }

        private void CrystalReportsForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            DisposeCR();
        }
        private void DisposeCR()
        {
            // Clean up by closing and disposing of the ReportDocument object
            if (_reportDocument != null)
            {
                if (_reportDocument.Database.Tables.Count > 0)
                {
                    Tables tables = _reportDocument.Database.Tables;
                    foreach (Table table in tables)
                        table.Dispose();
                }
                _reportDocument.Database.Dispose();
                _reportDocument.Close();
                _reportDocument.Dispose();
            }
            _reportDocument = null;
        }
    }

I have the log file which doesn't show any error in crystalreports library. I am giving some of the log file for your reference to see what's happening while exiting from crystal and executing the problem sql

CrystalDemoDlg 16c8-e8c EXIT SQLFetch with return code 0 (SQL_SUCCESS)

HSTMT 00F41CC0

CrystalDemoDlg 16c8-e8c ENTER SQLFetch

HSTMT 00F41CC0

CrystalDemoDlg 16c8-e8c EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND)

HSTMT 00F41CC0

CrystalDemoDlg 16c8-e8c ENTER SQLCloseCursor

SQLHSTMT 00F41CC0

CrystalDemoDlg 16c8-e8c EXIT SQLCloseCursor with return code 0 (SQL_SUCCESS)

SQLHSTMT 00F41CC0

CrystalDemoDlg 16c8-e8c ENTER SQLFreeHandle

SQLSMALLINT 3 <SQL_HANDLE_STMT>

SQLHANDLE 00F41CC0

CrystalDemoDlg 16c8-e8c EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)

SQLSMALLINT 3 <SQL_HANDLE_STMT>

SQLHANDLE 00F41CC0

CrystalDemoDlg 16c8-e8c ENTER SQLDisconnect

HDBC 00F427A0

CrystalDemoDlg 16c8-e8c EXIT SQLDisconnect with return code 0 (SQL_SUCCESS)

HDBC 00F427A0

CrystalDemoDlg 16c8-e8c ENTER SQLFreeHandle

SQLSMALLINT 2 <SQL_HANDLE_DBC>

SQLHANDLE 00F427A0

CrystalDemoDlg 16c8-e8c EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)

SQLSMALLINT 2 <SQL_HANDLE_DBC>

SQLHANDLE 00F427A0

CrystalDemoDlg 16c8-e8c ENTER SQLFreeHandle

SQLSMALLINT 1 <SQL_HANDLE_ENV>

SQLHANDLE 00F42718

CrystalDemoDlg 16c8-e8c EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)

SQLSMALLINT 1 <SQL_HANDLE_ENV>

SQLHANDLE 00F42718

CrystalDemoDlg 16c8-a34 ENTER SQLAllocStmt

HDBC 00F419A0

HSTMT * 0012E2C4

CrystalDemoDlg 16c8-a34 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)

HDBC 00F419A0

HSTMT * 0x0012E2C4 ( 0x00f41cc0)

CrystalDemoDlg 16c8-a34 ENTER SQLSetStmtOption

HSTMT 00F41CC0

UWORD 0 <SQL_QUERY_TIMEOUT>

SQLPOINTER 0x0000000F

CrystalDemoDlg 16c8-a34 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)

HSTMT 00F41CC0

UWORD 0 <SQL_QUERY_TIMEOUT>

SQLPOINTER 0x0000000F (BADMEM)

CrystalDemoDlg 16c8-a34 ENTER SQLExecDirectW

HSTMT 00F41CC0

WCHAR * 0x03A30458 [ -3] "DELETE FROM TMPRPT WHERE 1=0\ 0"

SDWORD -3

CrystalDemoDlg 16c8-a34 EXIT SQLExecDirectW with return code 100 (SQL_NO_DATA_FOUND)

HSTMT 00F41CC0

WCHAR * 0x03A30458 [ -3] "DELETE FROM TMPRPT WHERE 1=0\ 0"

SDWORD -3

CrystalDemoDlg 16c8-a34 ENTER SQLNumResultCols

HSTMT 00F41CC0

SWORD * 0x0012E2B8

CrystalDemoDlg 16c8-a34 EXIT SQLNumResultCols with return code -1 (SQL_ERROR)

HSTMT 00F41CC0

SWORD * 0x0012E2B8

DIAG [S1010] [Microsoft][ODBC Driver Manager] Function sequence error (0)

CrystalDemoDlg 16c8-a34 ENTER SQLErrorW

HENV 00F418D8

HDBC 00F419A0

HSTMT 00F41CC0

WCHAR * 0x0012DE00 (NYI)

SDWORD * 0x0012E224

WCHAR * 0x0012DE20

SWORD 511

SWORD * 0x0012DE14

CrystalDemoDlg 16c8-a34 EXIT SQLErrorW with return code 0 (SQL_SUCCESS)

HENV 00F418D8

HDBC 00F419A0

HSTMT 00F41CC0

WCHAR * 0x0012DE00 (NYI)

SDWORD * 0x0012E224 (0)

WCHAR * 0x0012DE20 [ 56] "[Microsoft][ODBC Driver Manager] Function sequence error"

SWORD 511

SWORD * 0x0012DE14 (56)

CrystalDemoDlg 16c8-a34 ENTER SQLErrorW

HENV 00F418D8

HDBC 00F419A0

HSTMT 00F41CC0

WCHAR * 0x0012DE00 (NYI)

SDWORD * 0x0012E224

WCHAR * 0x0012DE20

SWORD 511

SWORD * 0x0012DE14

I know that my post is too long, but i would like to give enough information for you to see what's happening. I use visual studio 2008 with crystal library 2008.

Thanks,

Lavanya.

[Former Member]

There is an update after the above post. That is the reason I am posting new one instead of edit.

1) When I tried the above sample code connecting with SQLServer database, it works fine.(no function sequence error)

2) My old version 7 crystal report files are connected using ODBC(RDO). I upgraded them to version 2008 using save as button.

This time, I connected to the sybase database(we use sybase here) using OLE DB(ADO), the application does not throw any function sequence error.

why is it happening? when i go ahead and change the data source all my report fields are losing their bindings. Do I have to rewrite all my crystal reports using this method? or is there any work around?

Thanks again.

[former_member11696]

Hi Lavanya,

Are you doing a set location in code or in CR Designer to make the change from ODBC to OLE DB? If not then the auto mapping will simply remove all fields because we can map them.

What happens if you create a new report off of sybase and test it, does that work?

What ever you do in the DEsigner to make this work you need to duplicate the steps in code.

Thank you

Don

[Former Member]

Hi Don

I wasn't using the SetLocation in designer in the beginning. But once I did that, all my reports work fine using my application. But they are slow.

Is there any way I can find out which odbc driver is crystal using? how i do i tell crystalreport to use latest driver instead of the old one?

Thanks.

[former_member11696]

Hi Lavanya,

Cool, we are getting somewhere now. You specify the name of the DSN which when created you select the ODBC driver. ODBC Admin should list all versions available, simply create a new DSN that uses the new ODBC driver and then set the report connection info to the new DSN.

If you ahve a new folder for the new client and ODBC driver then make sure that folder is specified in the PATH statement. CR search the PATH for the client install.

Thanks again

Don

[Former Member]

Hi Don,

I created a new connection to the same data source(and same database) using OLE DB(ADO). i updated the old ODBC connection with the new connection.

I removed all the old odbc connections. I only have one new OLE DB(ADO) connection, AsaDataSource.

But on my production build, it is prompting a log on screen, with correct server name, login and password. but the database name field is empty.

Everything is smooth and running fine on my development laptop.

Please help. I am really getting tired of this.

Thanks.

[Former Member]

Hi Don,

Found the issue. Sybase OLe db provider drivers are not installed properly(or registered) on the client machine.

THANKS for your support through this crisis.

Lavanya.