Re: SQL CE on Windows 7

[Corion]

You don't tell us what exactly you tried and how exactly it failed for you. This makes it much harder for us to help you efficently.

Personally, I look at http://connectionstrings.com/ whenever I'm in search of parameters to pass on using DBD::ODBC. If you don't have an ODBC driver for your target database, get one.

Also, it seems that "SQL CE" is "Microsoft SQL Server Compact Edition", so I would guess that any connection string for Microsoft SQL Server should work. Traditionally, MS SQL used the same protocol as Sybase did (TDS), so you might get some results using DBD::Sybase.

As Microsoft SQL Server Compact Edition is a Microsoft product, personally, I would look at instantiating an ADO connection using Win32::OLE or DBD::ADO, and then using that. See, again, the connection strings provided by http://connectionstrings.com.

[ig]

Thanks for the suggestions. I don't think what I tried is much worth pursuing, so didn't give details.

I usually have good success with Super Search or Google, but failed to find anything to get me going this time. Maybe I'm just having a bad search day.

I am familiar with accessing SQL Server via DBI and Win32::OLE, but there is no "server" in SQL Server Compact Edition (not that I know about, at least - but maybe that's just my ignorance - I know very little about SQL Server Compact Edition). The connections I make to SQL Server all include specification of the server, and I don't know how they might apply to a SQL CE data file - I suspect they don't but, again, that may just be my ignorance misleading me.

I thought/hoped someone might be able to point me to a working example to get me started in the right direction. If not, I'll certainly look further at Win32::OLE and DBD::ADO, with updates here if/when I get something working.

[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);

[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?