ODBC in Windows Vista

[ashly]

Hi All,

I encounter a problem when running vb program at Windows Vista.

I have an application which allow user to view reports.

I assume ODBC is OK because when I press "TEST DATA SOURCE" button and it returns "TESTS COMPLETED SUCCESSFULLY!".

But yet when I want to display report that time, an error "Cannot open SQL Server" error came out

Please help

[randem]

Who's reports? Crystal Reports? DataEnvironment?

[ashly]

Who's reports? Crystal Reports? DataEnvironment?

crystal reports

[randem]

Did you deploy the proper merge modules for CR?

[ashly]

Did you deploy the proper merge modules for CR?

umm... what do u mean by "proper merge modules" ?

created a new deployment for the program, CR are copy & paste into the pc

[randem]

That rarely works...

[RobDog888]

Thread Moved

If you dont distribute the required dependancy files for Crystal Reports, it will fail on other computers.

[randem]

BTW: You need to create an installation package for your app...

[RobDog888]

created a new deployment for the program

I think a package was created, but using which and its obvious that the CR dependancies/merge modules were not included.

What version of CR?

[ashly]

I think a package was created, but using which and its obvious that the CR dependancies/merge modules were not included.

What version of CR?

CR version is 8...

If not mistaken, all CR dependencies are included. If not, I think should be another error message.

It just when want to open the report, it shows "cannot open sql server".

[randem]

You are probably using Data Controls eh?

And you are mistaken...

[ashly]

You are probably using Data Controls eh?

And you are mistaken...

how can I find out whether I'm using Data Controls?

[randem]

Open your VBP file with notepad and post it's contents.

[ashly]

Open your VBP file with notepad and post it's contents.

Type=Exe
Reference=*\G{00020430-0000-0000-C000-000000000046}#2.0#0#C:\WINDOWS\system32\stdole2.tlb#OLE Automation
Reference=*\G{00000205-0000-0010-8000-00AA006D2EA4}#2.5#0#C:\Program Files\Common Files\system\ado\msado25.tlb#Microsoft ActiveX Data Objects 2.5 Library
Object={831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.0#0; MSCOMCTL.OCX
Object={BDC217C8-ED16-11CD-956C-0000C04E4C0A}#1.1#0; TABCTL32.OCX
Object={0ECD9B60-23AA-11D0-B351-00A0C9055D8E}#6.0#0; MSHFLXGD.OCX
Object={F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0; COMDLG32.OCX
Object={C932BA88-4374-101B-A56C-00AA003668DC}#1.1#0; MSMASK32.OCX
Object={86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0; MSCOMCT2.OCX
Object={648A5603-2C6E-101B-82B6-000000000014}#1.1#0; MSCOMM32.OCX
Object={67397AA1-7FB1-11D0-B148-00A0C922E820}#6.0#0; MSADODC.OCX
Object={CDE57A40-8B86-11D0-B3C6-00A0C90AEA82}#1.0#0; MSDATGRD.OCX
Reference=*\G{56BF9020-7A2F-11D0-9482-00A0C91110ED}#1.0#0#C:\WINDOWS\system32\MSBIND.DLL#Microsoft Data Binding Collection VB 6.0 (SP4)
Object={00025600-0000-0000-C000-000000000046}#5.2#0; Crystl32.OCX
Object={248DD890-BB45-11CF-9ABC-0080C7E7B78D}#1.0#0; MSWINSCK.OCX
Form=frmLogin.frm
Form=MDIMain.frm
Form=frmAbout.frm
Module=SubMain; SubMain.bas
Module=Global; Global.bas
Form=frmTest.frm
Form=frmReport.frm
Reference=*\G{6B263850-900B-11D0-9484-00A0C91110ED}#1.0#0#C:\WINDOWS\system32\MSSTDFMT.DLL#Microsoft Data Formatting Object Library 6.0 (SP4)
Form=frmWait.frm
Module=Lib; Lib.bas
ResFile32="Test.RES"
IconForm="frmAbout"
Startup="MDIMain"
HelpFile=""
Title="Test"
ExeName32="Test.exe"
Command32=""
Name="Test"
HelpContextID="0"
CompatibleMode="0"
MajorVer=1
MinorVer=0
RevisionVer=0
AutoIncrementVer=0
ServerSupportFiles=0
VersionCompanyName=""
VersionProductName="Test"
CompilationType=0
OptimizationType=0
FavorPentiumPro(tm)=0
CodeViewDebugInfo=0
NoAliasing=0
BoundsCheck=0
OverflowCheck=0
FlPointCheck=0
FDIVCheck=0
UnroundedFP=0
StartMode=0
Unattended=0
Retained=0
ThreadPerObject=0
MaxNumberOfThreads=1

[MS Transaction Server]
AutoRefresh=1

[randem]

Yes, you are using Data Controls

DataGrid
ADO Data Control

[ashly]

Yes, you are using Data Controls

DataGrid
ADO Data Control

sorry... I still cannot get what you mean

If I'm using Data controls, then I'll get the "cannot open sql server" error?

[randem]

Yes, unless you deployed vb5db.dll and you coded your DC's correctly. They are a pain with unexplained errors and procedures.

Most people here use ADO code to connect to the database for data. You have more control. Data Controls are Evil...

[dilettante]

I'm not a fan of the data controls myself, but they do work.

I just created a minimal VB6 program with one form, an ADODC, a DataGrid, and a one table MDB file accessed through a DSN-less connection string. I packaged it with PWD including the following:

MSSTDFMT.DLL
MSBIND.DLL
MSDATGRD.OCX
MSADODC.OCX
Project1.exe
sample.mdb

Then I deployed it to both XP SP2 and Vista, choosing a folder outside Program Files to eliminate complications as a test. It ran fine in both locations.

There is no vb5db.dll present on either machine, I did a complete search of the hard drive. No suprise, that's a DAO DLL anyway and not used here. It's also not listed as a supported library on Vista at Support Statement for Visual Basic 6.0 on Windows®Vista™and Windows®Server 2008 ™ (unlike everything else here, which are all legit).


I see ODBC mentioned above. Hopefully that was a misstatement, surely you're using the SQL Server OLE DB provider, right? Not a big deal really though. Can we see your connection string? I suspect that's the problem. Or are you using (shudder) a DSN or .UDL file? Yes, we all have our phobias.

I'm leaning toward your problem being that you're using a DSN. ADO only recognizes System DSNs, which are stored in the registry. You might be running afoul of registry virtualization in Vista. Just a wild guess though.

[ashly]

I think a package was created, but using which and its obvious that the CR dependancies/merge modules were not included.

What version of CR?

May I know what are CR dependencies that need to be included while deploying?

I think some dll or ocx files not included

If some of the CR dependencies not included, it will returns "Cannot open SQL server" error message when I try to open the report?

[ashly]

I'm not a fan of the data controls myself, but they do work.

I just created a minimal VB6 program with one form, an ADODC, a DataGrid, and a one table MDB file accessed through a DSN-less connection string. I packaged it with PWD including the following:

MSSTDFMT.DLL
MSBIND.DLL
MSDATGRD.OCX
MSADODC.OCX
Project1.exe
sample.mdb

Then I deployed it to both XP SP2 and Vista, choosing a folder outside Program Files to eliminate complications as a test. It ran fine in both locations.

There is no vb5db.dll present on either machine, I did a complete search of the hard drive. No suprise, that's a DAO DLL anyway and not used here. It's also not listed as a supported library on Vista at Support Statement for Visual Basic 6.0 on Windows®Vista™and Windows®Server 2008 ™ (unlike everything else here, which are all legit).


I see ODBC mentioned above. Hopefully that was a misstatement, surely you're using the SQL Server OLE DB provider, right? Not a big deal really though. Can we see your connection string? I suspect that's the problem. Or are you using (shudder) a DSN or .UDL file? Yes, we all have our phobias.

I'm leaning toward your problem being that you're using a DSN. ADO only recognizes System DSNs, which are stored in the registry. You might be running afoul of registry virtualization in Vista. Just a wild guess though.

yes, you are rite. I'm using System DSN.

what do you mean by "running a foul of registry virtualization in Vista"? anyway to fix it?

[dilettante]

User DSNs are not recognized by ADO at all. See: How To Create a System Data Source Name (DSN) in Windows 2000.

A true System DSN turns out to be a special case in Vista: Creating System DSNs on Vista.

So while my virtualization comment turns out to be a red herring, it does mean System DSNs must be created by an elevated process or a runtime error results (and you get no System DSN).

Assuming you create the DSN elevated (the Vista ODBC Administrator control panel applet always requests elevation) you should be fine on that point.

Bottom line: I may have sent you on a wild goose chase.


In my mind though the real question is why you're using ODBC and DSNs at all though. This is a relic from olden times and adds a layer of inefficiency to boot! Most people tend to use the OLE DB Providers and DSN-less connections today, storing connection string parameters in INI files or the equivalent (app.config XML files) instead of the registry.


So this (System DSNs) is what led me to the registry as a source of your problem. Its use for application settings is deprecated in Vista and there is a maze of rules to navigate about when a registry key is written to (or diverted to a per-user virtual location) and what you get when you try to read it back (i.e. from where, a system location or a per-user location).

However all of this may be irrelevant depending on how you are setting your DSN. Yet we still have the case where the test succeeds while your application fails. Is there any more detail to this error message, like an exception number or hResult value?

[randem]

May I know what are CR dependencies that need to be included while deploying?

I think some dll or ocx files not included

If some of the CR dependencies not included, it will returns "Cannot open SQL server" error message when I try to open the report?

Just deploy the CR Merge Modules with your app.