Sunday, February 27, 2011

Access SQL Server Compact 4 with ASP Classic and VbScript

Now that you can use SQL Server Compact 4 with ASP.NET, some developers wonder if you can also use it with ASP Classic. To access with ASP Classic, the SQL Server Compact 4.0 OLEDB provider must be installed on the system, so the 4.0 MSI must be installed by an administrator – no private deployment.

But YES, you can access a SQL Server Compact 4 database file from ASP Classic:

 image

(The url is: http://localhost/aspclassic/default.asp)

Of course the required read/write permissions must be given the the relevant process user to the folder where the database file is located.

Here is the code to do it:

<html>
<head>
    <title>Test SQL Compact 4 and ASP Classic + ADO</title>
</head>
<body>

<%

set conn = Server.CreateObject("ADODB.Connection")

strCnxn = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;" & _
    "Data Source=C:\inetpub\wwwroot\AspClassic\App_Data\nw40.sdf;"

conn.Open strCnxn

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES", conn

do until rs.EOF
    for each x in rs.Fields
       Response.Write(x.name)
       Response.Write(" = ")
       Response.Write(x.value & "<br />")
    next
    Response.Write("<br />")
    rs.MoveNext
loop
'
rs.close
conn.close
%>

</body>
</html>

24 comments:

Rod Mac said...

Excellent article and Compact's reach just grows. I was thinking back to my Access days and how we always wrote two Access DB's as part of a system - one for the UI/front end with all the VBA code then one solely to hold data. If SQL Compact solely held data as one file and a SQL Server database file held SP's etc (the code side), that would give portability to the data. I was thinking that would be a great way to make the task of backing up from shared servers easy, particularly as the SQL DPW now runs from Management Studio only i.e. it's not a standalone anymore and is too complex for non DBA's/programmers. In the UK at least where we have a shared platform with ISP's like Fasthosts, backing up SQL has become unmanageable. Could a data only version of SQL Compact in conjunction with a SQL Server (service) resolve this?

ErikEJ said...

Thanks Rod. I think that Entity Framework + SQL Server Compact solves exactly that problem without involving SQL Server.

Rod Mac said...

I was thinking in the context that one had two SQL Server files which equated to a SQL Server database (ignoring the log file) and the 'data only' file and not the 'code only' SQL Server file was synonymous with a SQL Compact file. When I want to migrate to SQL Server, all I do is create a new SQL Server code file (i.e. with SP's) and copy over my SQL Compact (data) file to become the other half of my SQL Server database i.e. we're talking purely SQL/DB and nothing to do with EF. It gets round the fundamental problem that a SQL Server database file is not just data which poses a current problem of portability. It seems a little crazy that upscaling from Compact to SQL Server requires special sync tools when it could be part of the same thing!

ErikEJ said...

MS is woking on making a SQL Server database very portable/independent/movable in the next release (Denali)

Rod Mac said...

...like a 'contained database' perhaps (that can be extended)?!

ErikEJ said...

Yes: http://msdn.microsoft.com/en-us/library/ff929071(v=sql.110).aspx

Hans said...

Your site is very helpfull. I managed to connect to a Compact 4.0 Database using this connectionstring. But is it also possivle to make a connection from an ASP.NET 3.5 website?

And is it possible to link the tables to an Access database?

ErikEJ said...

Hans: ASP.NET 3.5 -Yes, see other posts in my blog. Linked to Access - no.

Unknown said...

Using Excel 2003 Macro getting
'Runtime Error -2147467259 (80004005)'

ErikEJ said...

Info: Please post a repro, best on the MSDN forum or Stackoverflow, thanks.

Marco Plaza said...

Hi, excellent blog. Could you please post the complete connection string for password protected database and other useful parameters?, I've been looking everywhere and can't find it.

Thanks!!

ErikEJ said...

Marco: Look here http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx (use the ones with ssce: in front)

Marco Plaza said...

Thanks Erik! I'll be following you!

jarim said...

THANKS A LOT ...IT WORKS LIKE MAGIC!!! :D

arul said...

An error occurred on the server when processing the URL. Please contact the system administrator.

If you are the system administrator please click here to find out more about this error.

my sample code




set conn = Server.CreateObject("ADODB.Connection")

strCnxn = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;" & _
"Data Source=C:\inetpub\wwwroot\Training\App_Data\DBContext.sdf"

conn.Open strCnxn

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES", conn

do until rs.EOF
for each x in rs.Fields
Response.Write(x.name)
Response.Write(" = ")
Response.Write(x.value & "")
next
Response.Write("")
rs.MoveNext
loop
'
rs.close
conn.close




sql server compact is install my systems

ErikEJ said...

arul: Suggest you add error handling

Unknown said...

In your post you state "SQL Server Compact 4.0 OLEDB provider must be installed on the system". I have installed the standard 4.0 and the 4.0 SP1 main installation (I am an administrator) - but I still get an error. I am unable to locate a separate installation for the OLEDB driver.

ErikEJ said...

Matthew: Installing the 4.0 MSI should create the HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{2006C53A-C915-41EA-BAA9-9EAB3A1FBF97} and HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Microsoft.SQLSERVER.CE.OLEDB.4.0registry keys etc, which are required

Martin said...

Sorry for bumping an old post.
First, I want to say thanks! Great post.
Second, is there anyone who ran in to the same problem as me.
There seem to be an issue with the 4.0 SP1 version.
I cant get the script working unless running it as administrator.
Same goes if I use oledb from a program. Need to right click run as administrator.
Worked fine before SP1..

ErikEJ said...

@Martin: File location? OS update?

Unknown said...

I cannot make a connection. I get error '80004005' when I run this line of code:
rs_cmd.ActiveConnection = MM_datasource_STRING.

My connection string is:
MM_datasource_STRING = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=" & Server.MapPath("../database/datasite_eight.sdf").

I've tried everything I can think of and no luck. The ActiveConnection method fails.

ErikEJ said...

@James: In my sample code, I see no reference to the "ActiveConnection" property, so why would you expect that to work?

Unknown said...

I'm using ADOBD.Commands with parameters. I tried the other method and it's not supported.

ErikEJ said...

James, I think you have too high expectations for the functionality available. Stick to text based sql statements, no sql injection possible with sqlce anyway