Wednesday, July 13, 2011

Using PowerShell to manage SQL Server Compact database files

I noticed that someone asked if you could manage SQL Server Compact from PowerShell, and yes you can, based on the reply here I put together the following sample:

[Reflection.Assembly]::LoadFile(“C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll”)

$connString = "Data Source=C:\data\sqlce\test\roads.sdf"
$cn = new-object "System.Data.SqlServerCe.SqlCeConnection" $connString

# create the command
$cmd = new-object "System.Data.SqlServerCe.SqlCeCommand"
$cmd.CommandType = [System.Data.CommandType]"Text"
$cmd.CommandText = "SELECT TOP (100) * FROM Road"
$cmd.Connection = $cn

#get the data
$dt = new-object "System.Data.DataTable"

$rdr = $cmd.ExecuteReader()


$dt | Out-Default | Format-Table


Christian said...

Hi! First off I would like to thank you for an really great site. Learnt a lot about SQL Server Compact thnx to your articles.

Was wondering if you could assist with an issue I have when attempting to use your code example above? When I run this without having SQL Server Compact installed (still loading the dll file from a valid path) I get the following errormsg:
"Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8482. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details."

On the following page:
You give the explanation that the "native DLLs" from "C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private" must somehow be loaded.

Is this possible to do by modifying using your Powershell script above? I'm not a programmer myself (working with operations), but I would love to use Sql Server Compact more with my Powershell scripts.

However it's not always your able/allowed to install applications, and would be great if I could just bring the dll's with my script and point/load to them when needed.

ErikEJ said...

I doubt that is possible with PowerShell, but I am not a PowerShell expert