[RESOLVED] VB6 and Crystal Reports 8 / 8.5 - Error 20599 - Cannot Open SQL Server.

[guht]

I don't know where else to look or turn at this point... I have googled and read all the articles I could find on this error, followed links and threads from posts I have found, and I am officially stumped!

I know something like this would normally be asked on a crystal report or reporting specific forum, but SAPs response is always upgrade, and the only active people that seem to still have this knowledge live here in the VB6 forum.

So move the thread if you must, but I feel it is best served here.

On to the problem...

• I have a development machine where the forthcoming code works as expected, but when installing and running on the client machine, it throws the error "20599 - Cannot Open SQL Server".
• Obviously, this is an environmental issue, and something is different about the client machine, versus the development machine.
• However, I am unable to determine what that is!

What I know:

• I have looked at the runtime.hlp file
• The report connection configuration has not changed.
• The development machine has both Crystal Reports 8 and 8.5 installed on it, dont ask me why, but it DOES WORK in development, important to remember.
• The ODBC System DSN Connections exist as 32bit connections on both machines, are configured the same, are using the same SQL driver and version, and both test successfully.
• When opening the report in crystal designer, and doing "Convert Database Driver", it lists the PDSODBC.DLL (p2sodbc.dll) as the driver.
• When doing "Verify Database" it comes back with the database is up to date.
• The p2sodbc.dll exists on both machines. I have tried putting all relevant files on the client machine in "C:\windows\crystal", "C:\Windows\System32\"; and "C:\Windows\syswow64"
• Both machines are Windows 10 64bit.
• Crystal reports viewer inside of vb6 code seems to work from what I have tested (This tells me some parts of Crystal reports are registered correctly and working)
• Most of the 200,000+ lines of code of this application seem to be working, including older Adobe 5 functions, and tons of other stuff I have had to finagle to get working in Windows 10 environment.

Here is the code - It is erroring on the PrintReport part:

Private Sub PrintReport()

          Dim crApp As New CRAXDRT.Application
          Dim crRpt As Report
          
          Dim objDBSettings As AppSettings
          Set objDBSettings = New AppSettings

          frmMain.CrystlRep.Reset
          frmMain.CrystlRep.LastErrorNumber = 0
          frmMain.CrystlRep.LastErrorString = ""
          frmMain.CrystlRep.Destination = crptToPrinter
         'I have tried using both these connection strings, with and without the DSN specified 
         'frmMain.CrystlRep.Connect = "DSN=" & objDBSettings.GetINIValue("Crystal Reports", "DSN") & ";UID=" & objDBSettings.GetDBConnectionVal("Admin_Id") & ";PWD=" & objDBSettings.GetDBAdminPwd & ""
          frmMain.CrystlRep.Connect = "UID=" & objDBSettings.GetDBConnectionVal("Admin_Id") & ";PWD=" & objDBSettings.GetDBAdminPwd & ""
          
          frmMain.CrystlRep.ReportFileName = Dir$ + "test.rpt"
          frmMain.CrystlRep.StoredProcParam(0) = "Test"
          
          Set crRpt = crApp.OpenReport(Dir$ + "test.rpt")
         
          crRpt.EnableParameterPrompting = False
          crRpt.ParameterFields(1).AddCurrentValue "Test"
          crRpt.SelectPrinter Printer.DriverName, Printer.DeviceName, Printer.Port
          frmMain.CrystlRep.PrinterSelect
          frmMain.CrystlRep.PrintReport

          If frmMain.CrystlRep.LastErrorNumber Then
            MsgBox "Crystal Report Error #" + CStr(frmMain.CrystlRep.LastErrorNumber) + " --- " + frmMain.CrystlRep.LastErrorString
          End If

End Sub

EDITED:

• The "p2sodbc.dll" appears in the development machines registry.
• The "p2sodbc.dll" DOES NOT appear in the client machines registry.
• The directory the "p2sodbc.dll" is listed as "C:\windows\system32\" in the development machines registry.
• However, "p2sodbc.dll" DOES NOT exist in this directory, it exists under "C:\Windows\Crystal".
• Trying to use "C:\windows\system32\regsvr32.exe" and "c:\windows\syswow64\regsvr32.exe" to register "p2sodbc.dll" results in error saying :it was loaded but entry point dllregisterserver was not found.
• This error trying to register happens on both dev and client machines.

Here are some relevant screenshots:






ANY help is much appreciated. I have exhausted my searches, and need one of those AHA moments desperately!

[DllHell]

seems like this is related to p2sodbc.dll. I'd look for duplicates on the system and then try and figure out why it cannot be registered.

[guht]

seems like this is related to p2sodbc.dll. I'd look for duplicates on the system and then try and figure out why it cannot be registered.

Thank you for the reply... The problem actually got more strange.

In an attempt, and as simply a test, I installed the full versions of crystal report 8 and 8.5 on the test client machine. Just like they are installed on my development machines.

I then tried to run the portion of code that was giving me the error, and I received the same error.

I then loaded the questionable report in crystal reports designer on the client test machine. I verified the database, which said it was up to date, so I ran a print preview, and it displayed the data I was expecting.

The crystal report is using the same ODBC connection I am trying to use in the VB6 code, so I am even more at a loss at this point!

So basically it works in all scenarions on development machine, it works in crystal reports designer on client test machine, it does not work on client test machine in VB6 application.

I have even tried creating a very simple report from scratch, which works in development, but does not work on client test machine.

[LaVolpe]

If Crystal Reports is not looking first in some hardcoded folder, i.e., C:\Windows\Crystal, then Windows DLL search priorities are used for standard DLLs.

You mentioned that attempting to register the DLL, you received "it was loaded but entry point dllregisterserver was not found" error. I'm not familiar with CR, but do you know for a fact that the dll is an ActiveX DLL? If it isn't, then running regsvr on it should have no effect.

You mentioned following a lot of links to no avail. Have you seen this one? Don't know if it applies to your situation.

Sounds like there are at least 2 p2sodbc.dll versions in play, same file name, different locations?

Edited: If it's a .Net DLL, see this link here in this forum.

[guht]

If Crystal Reports is not looking first in some hardcoded folder, i.e., C:\Windows\Crystal, then Windows DLL search priorities are used for standard DLLs.

You mentioned that attempting to register the DLL, you received "it was loaded but entry point dllregisterserver was not found" error. I'm not familiar with CR, but do you know for a fact that the dll is an ActiveX DLL? If it isn't, then running regsvr on it should have no effect.

You mentioned following a lot of links to no avail. Have you seen this one? Don't know if it applies to your situation.

Sounds like there are at least 2 p2sodbc.dll versions in play, same file name, different locations?

Edited: If it's a .Net DLL, see this link here in this forum.

First, thank you for the detailed reply. I wasnt aware of the DLL search priorities, so always nice to learn something new! From everything I have read it seems like crystal is searching for these files in particular directories.

I do not know what kind of DLL it is, but when I used .net v2.xxxx regasm.exe to try and register it, it said it wasnt a activex dll!

I did actually read that other link you had posted previously, and as a result i added the directories that contained the "p2sodbc.dll" to the windows 10 environment PATH, rebooted, and tried again with the same result.

There were 2 different versions of the DLL and I tried to use both of them exclusively, meaning I removed one version and used the other version, and vice versa. Same result.

The crystal reports runtime.hlp file lists dependencies, and the only dependency listed for this "p2sodbc.dll" is MDAC 2.1... My understanding of MDACs current state is that on newer machines it isnt needed, and 2.1 is so old it wont even install on Windows 10, for the hell of it, I tried installing MDAC 2.8, but still the same result.

I even tried installing VB6 and VB6 SP6, and app still craps out. I need to setup the project on that machine, and see if it runs through VB6 IDE, but not in the app. Im not sure what that will tell me, but I am running out of ideas.

I am still puzzled that it loads the report and data just fine in crystal designer using the same damn odbc connection!

[guht]

Ok so adding a USER DSN made the client application work, but why would that matter when there is a working System DSN with exactly the same information?! I am confused!

Creating a USER specific DSN for every single user sounds like a huge PIA, would much rather figure out why the application cares that there is a USER DSN vs System DSN!

Both DSNs created using \windows\syswow64\odbcad32, the system dsn is listed as 32bit, but user dsn is listed as 32/64bit.... Additionally UAC is turned off, the user logged in is part of the adminstrator group, and the application is being run as adminsitrator.

[guht]

SOOOOO... It turns out it didnt have to be a USER DSN, its just that the System DSN is tweaked in some small little way, even though it tested fine, worked in crystal designer, appeared as a 32bit DSN, etc, etc, etc...

I originally setup the System DSN using Inno Setup script with the following:

[Run]
Filename: "{sys}\ODBCCONF.EXE"; Parameters: "/A {{CONFIGSYSDSN ""SQL Server"" ""DSN=dsnname | Server=servername""}"; StatusMsg: "Setting Up ODBC Connections..."; Flags: runascurrentuser 32bit

I would have assumed that specifying 32bit, would set it up correctly, and it did appear as a 32bit system DSN, and could only be removed by using syswow64\odbcad32, so I am still stumped why this didnt work.