VB6/CR9: Getting "Unknown query engine error"

[sgarv]

Hi all,

I was charged with getting an old report to work when the database was moved to the new database servers. I documented everything in this post:

http://www.vbforums.com/showthread.p...ith-CR9-report

There are three servers: CorpPac, CorpAt and CorpDev. One of the three databases has a different name but is identical to the other two:
CorpPac db name: CorpDataCA
CorpAt db name: CorpData
CorpDev db name: CorpDataCA

I used CorpDev to do all development work and testing. The report was working well. At this time some one else is conducting further tests. The first task that the tester did was to switch from the CorpDev server to the CorpAt server. When he ran the report he got the "Unknown query engine error". I have been looking into this, but this error seems to be somewhat generic and the information that I have found has not been helpful. I performed more tests by tweaking the Set DB location database spec in the RPT file and logging in into my app using the three different servers:

RPT database set location: CorpPac.CorpDataCA

App login: CorpPac, CorpDataCA report is ok
App login: CorpDev, CorpDataCA report is ok
App login: CorpAt, CorpData report generates error
App login: CorpDev, CorpData report is ok (Note: CorpDev has both databases)

RPT database set location: CorpAt.CorpData

App login: CorpPac, CorpDataCA report generates error
App login: CorpDev, CorpDataCA report is ok
App login: CorpAt, CorpData report ok
App login: CorpDev, CorpData report is ok

My conclusions:
The report generates well when run against the dev server, regardless of database or how the RPT report file is configured
The report generates well when run against the same server as the RPT report file is configured
The report consistently generates the error when the RPT file is configured to CorpPac and the application passes logon credential for CorpAt, or viceversa.

I am using the following code to log on to the server:

      Set crReport = crApp.OpenReport(sRptFilePath)
    
      crReport.Database.Tables(1).ConnectionProperties.DeleteAll
      
      'I need to pass DB logon credentials to the report.
      crReport.Database.Tables(1).ConnectionProperties.Add "Provider", "SQLOLEDB"
      crReport.Database.Tables(1).ConnectionProperties.Add "Data Source", sSrv
      crReport.Database.Tables(1).ConnectionProperties.Add "Initial Catalog", sDB
      crReport.Database.Tables(1).ConnectionProperties.Add "User ID", sDBUsr
      crReport.Database.Tables(1).ConnectionProperties.Add "Password", sDBPwd

I use Tables(1) explicitly because the report makes use of only one table.

Thank you for your time. Any ideas on how to resolve this issue will be greatly appreciated, as always. SGarv

[sgarv]

As an update. I was not able to resolve this issue. As best as I could conclude, the problem arises when the report internally uses a table that points to a specific server, but the log on credentials passed to the report specify a different server. The two tables are linked withing the report. To reproduce this error I have the following example:

Let's take two tables. The first contains a list of orders. The second is the Clients table that gives the client name, address and other pertinent information. I want my report to contain and order id, description and client name and address.

I create my report, connect to database ServerA (SQL Server for this example since I did not test with other engines). I include two tables called MainOrders and Clients. I link these two tables using MainOrders.ClientId and Clients.Id. I include the fields that I want from both tables in the report.

I have two servers with identical databases and table structures, but different data; ServerA (mentioned above) and ServerB.

When I run my app I can log into either server, depending which data I want.

I run the app, log into ServerB, open an ADO record set listing the orders (OrderId, Description & ClientId). I pass the record set data source to the report and generate the report. The "Unknown query engine error" presents itself.

I exit the app and run it again, this time logging into ServerA. I generate the report without problems.

Since I did not find a solution to this problem I removed the Clients table from within the report. I inner joined the Clients table with the MainOrders in the main query in the application. I added the client's name and address to the select list of the query, got all the information that I needed for the report from that query and passed the record set to the report. I modified the report to substitute the report's fields referencing the Clients table with the new fields in the sent over record set.

This permitted a workaround to the initial problem, but did not resolve it. The "Unknown query engine error" is a very generic message from CR that does not really indicate what the problem and can be caused by any number of circumstances that CR does not like. At least, given the information in this post, there is another option that can be investigated. Regards.

[jggtz]

See code in post #6
Maybe the part where I set the LogonInfo could be useful to You
http://www.vbforums.com/showthread.p...m-VB6-to-CR-XI

[sgarv]

Thanks JG. This issue has been a challenge. Although the workaround of passing the client data to the report works I would like to find amore proper solution as this will tell me what "invalid" condition is causing this error and will be able to use it as a learning experience for future CR projects. Regards.

[sgarv]

I saw the code snippet that you referenced, specifically

   Dim crDBTab As CRAXDRT.DatabaseTable
   
   For Each crDBTab In crReport.Database.Tables
     crDBTab.SetLogOnInfo strSrv, strDB, strDBUsr, strDBPwd
   Next

I added this to the process, but still with same results. I also tried using the other statement in your code snippet:

  Set crReport = crApp.OpenReport(strRPTFilePath)

  crReport.Database.LogOnServer "p2ssql.dll", strSrv, strDB, strDBUsr, strDBPwd

  'rsRepData is open and ready with data.
  crReport.Database.Tables(1).SetDataSource rsRepData, 3

I was not sure which DLL name to use, so I left it as is. It did not generate any errors. Still the same problem persisted.

Regards, SGarv