Re^3: SQL CE on Windows 7

[Corion]

sql server compact edition odbc brings me to this "social.msdn.microsoft.com" post, which links, among others, to how to add data to an SDF file from Excel (etc.) via ODBC resp. ADO.

Looking at DBD::ADO, you can supply it an ODBC connection string, so you should be able to use the same connection string as the VBA code, mutatis mutandis. If it still "doesn't work", you will have to post code. Maybe you are using relative path names instead of absolute path names, or you are not doubling the backslashes where it matters, or some other things. It's hard to tell without seeing the failing code.

Update: The DBD::ADO documentation says to use

dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\test.mdb

as the connection string, so likely the following should work for the SQL CE server:

dbi:ADO:PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\North
+wind.sdf

[ig]

That's one of the connection strings I tried:

DBI Connect('Provider=Microsoft.SQLSERVER.CE.OLDEDB.3.5;Data Source=C:
+\temp\MFDB.sdf','',...) failed: Can't Open Connection 'Provider=Micro
+soft.SQLSERVER.CE.OLDEDB.3.5;Data Source=C:\temp\MFDB.sdf'

  Package    : DBD::ADO::dr
  Filename   : C:/strawberry/perl/site/lib/DBD/ADO.pm
  Line       : 158
  Last error : -2146824582

OLE exception from "ADODB.Connection":

Provider cannot be found. It may not be properly installed.

Win32::OLE(0.1709) error 0x800a0e7a
    in METHOD/PROPERTYGET "Open" at test.pl line 7

Comments I read about other providers makes me suspicious that only a 32bit OLEDB provider is available and it doesn't work or isn't available on 64bit Windows 7.

update: I have both 32bit and 64bit versions of SQL Server Compact Edition 3.5 SP2 installed on the system.

update: and the code that produced the above error is:

#!/usr/bin/perl
#
use strict;
use warnings;
use DBI;


my $dbh = DBI->connect("dbi:ADO:Provider=Microsoft.SQLSERVER.CE.OLDEDB
+.3.5;Data Source=C:\\temp\\MFDB.sdf",undef,undef);

[Corion]

Provider cannot be found. It may not be properly installed.

Have you tried the VBA code from Excel and/or Microsoft Access or any other "known good" application? Have you looked at what odbcad32.exe (or the 64-bit variant) and/or whatever method ADO has for listing providers list? Maybe the name is different, or maybe the user account you have does not have the correct permissions to use the provider? What does the manual for SQL Server CE say about connecting to it via ADO? Are you using a 64-bit Perl or a 32-bit Perl?

Try to eliminate as many variables from the problem. If ADO works, then try to elminate the bitness - check whether connecting via ADO 32-bit works, or whether it is limited to 64-bit ADO, by using 32-bit resp. 64-bit applications. Check that the .sdf file exists and is accessible for the user running your program.

[talk2chakra]

Before anything, I need to tell my Software Environment as follows.

Installed Software :- SQL Server Compact 3.5 Books Online ENU SQL Server Compact SP2 ENU SQL Server Compact 3.5 SP2 x64 ENU MS Office Enterprise 2007 MS .NET Framework 4.5.2 CompactView 1.4.12.0 ActivePerl Ver 5.24.0.2400 (x64) OS : Windows 7 Pro x64

I installed IIS 7.5.7600.16385 and configured it properly for use with ActivePerl. Apart from that, I changed Physical Path of Site to E:\PUCC. The PUCC Folder has been assigned proper security for IUSR and IIS_IUSRS as FULL CONTROL. I tested IIS for use with PERL and succeeded.

I've 2 .SDF Files (Password Protected) stored in PUCC\DB Folder. As I know the Password, I can access both the Files through CompactView. However I cannot access it through ActivePerl. Whenever, I run PERL SCRIPT from Command Prompt, following errors popped up. My ultimate goal is to furnish the Tables with specific Rows, Columns in Web Browser through IIS.

PERL Code:

01 use strict;
02 use warnings;
03 use DBI;
04
05 my $user="";
06 my $pass="myPass";
07
08 my $dbh = DBI->connect("dbi:ADO:Provider=Microsoft.SQLSERVER.CE.OLE
+DB.3.5;Data Source=E:\\PUCC\\DB\\D_stud.sdf", $user, $pass);
09
10
11 my $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.TABLES WH
+ERE TABLE_TYPE = 'TABLE'");
12
13 $sth->execute();
14
15 while (my $href = $sth->fetchrow_hashref()) {
16    print $href->{TABLE_NAME} . "\n";
17 }

ERROR Details: DBI connect('Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=E:\PUCC\DB\D_stud.sdf','',...) failed: Can't Open Connection 'Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=E:\PUCC\DB\D_stud.sdf' Package : DBD::ADO::dr Filename : C:/Perl64/site/lib/DBD/ADO.pm Line : 158 Last error : -2147217887 OLE exception from "ADODB.Connection": Provider could not set DATASOURCE, USERID, or PASSWORD property. Win32::OLE(0.1712) error 0x80040e21 in METHOD/PROPERTYGET "Open" at test.pl line 8. Can't call method "prepare" on an undefined value at test.pl line 11. Can someone help me out of the Problem?

[Corion]

At least one of the three ($user, $pass and the DSN itself) seems to be wrong or not to ADOs liking. I assume that you set both your $user and $pass to a non-empty value, and that they do both print out to what you expect if you print them?

I'm no expert with ODBC connection strings - to check that the correct drivers are available, have you tried configuring a named ODBC data source and connecting to that through DBD::ODBC?