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:
(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>
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?
ReplyDeleteThanks Rod. I think that Entity Framework + SQL Server Compact solves exactly that problem without involving SQL Server.
ReplyDeleteI 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!
ReplyDeleteMS is woking on making a SQL Server database very portable/independent/movable in the next release (Denali)
ReplyDelete...like a 'contained database' perhaps (that can be extended)?!
ReplyDeleteYes: http://msdn.microsoft.com/en-us/library/ff929071(v=sql.110).aspx
ReplyDeleteYour 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?
ReplyDeleteAnd is it possible to link the tables to an Access database?
Hans: ASP.NET 3.5 -Yes, see other posts in my blog. Linked to Access - no.
ReplyDeleteUsing Excel 2003 Macro getting
ReplyDelete'Runtime Error -2147467259 (80004005)'
Info: Please post a repro, best on the MSDN forum or Stackoverflow, thanks.
ReplyDeleteHi, 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.
ReplyDeleteThanks!!
Marco: Look here http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx (use the ones with ssce: in front)
ReplyDeleteThanks Erik! I'll be following you!
ReplyDeleteTHANKS A LOT ...IT WORKS LIKE MAGIC!!! :D
ReplyDeleteAn error occurred on the server when processing the URL. Please contact the system administrator.
ReplyDeleteIf 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
arul: Suggest you add error handling
ReplyDeleteIn 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.
ReplyDeleteMatthew: 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
ReplyDeleteSorry for bumping an old post.
ReplyDeleteFirst, 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..
@Martin: File location? OS update?
ReplyDeleteI cannot make a connection. I get error '80004005' when I run this line of code:
ReplyDeleters_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.
@James: In my sample code, I see no reference to the "ActiveConnection" property, so why would you expect that to work?
ReplyDeleteI'm using ADOBD.Commands with parameters. I tried the other method and it's not supported.
ReplyDeleteJames, I think you have too high expectations for the functionality available. Stick to text based sql statements, no sql injection possible with sqlce anyway
ReplyDelete