SQL Offline

Microsoft SQL Server

Native Client Driver: Microsoft SQL Server 2012 Native Client
SQL Server Compact Driver: Microsoft SQL Server Compact 4.0
SQL Server Compact 3.5 Driver: Microsoft SQL Server Compact 3.5

SQL Language: Transact-SQL Reference
SQL Server: SQL Server Express Edition

Microsoft SQL Server Compact 4.0 cannot read SQL Server Compact 3.5 files.

Column Types

MsSql Column Type Information.

When creating or modifying a table using OleDb Admin, you can specify a specific MsSQL column type from a list.

mssql column types
MsSQL provider types

Additional qualifiers can be added. For example,

datetime DEFAULT CURRENT_TIMESTAMP

will specify that a field defaults to the time the record was appended. The MsSQL provider type will be passed directly to the CREATE TABLE command and retained in the .OL grid file.

If I4 and ISAUTOINCRIMENT is selected as a column flag

int IDENTITY

will be used to create the column. Identity values can be pre-loaded if

SET IDENTITY_INSERT [Tablename] ON

is included before the first INSERT command. To get the normally read-only identity column values to appear in the Write Data Preview (SQL) , temporarily set the WRITE column data flag (by making the last digit a 4 as in 0x50000014, or pressing SHIFT with [...]).

The first time an .MDF file is selected, the database will be attached (this is done by the server as a result of the AttachDbFilename= in the connection string). Exit and Reload Offline.exe. Initialize the master data source and pick Admin/Sql Execute to run:

master.dbo.sp_detach_db [Database]

where Database is the base name of the file (no path or extension). More information about attaching and detaching is described here . If you use drop database [Database] the .mdf file will be deleted.

SQL Offline will not attempt to open an .MDF file into any server\instance other than localhost\sqlexpress (.\sqlexpress). To load into another instance, or from another server, use the connection string in the results tab as a template for a new connection.

Samples

Load Customers from Northwind.accdb, as in the Crash Course Tutorial. Replace the connection string with a link to a MsSQL server database, for example Provider=SQLOLEDB.1;Initial Catalog=Offline;Integrated Security=SSPI;Data Source= .\SQLEXPRESS . Remove ISNULLABLE from CustomerID. A Write Data command would create a table with:

CREATE TABLE [Customers] (
[CustomerID] nvarchar(5) NOT NULL,
[CompanyName] nvarchar(40) NOT NULL,
[ContactName] nvarchar(30) NULL,
[ContactTitle] nvarchar(30) NULL,
[Address] nvarchar(60) NULL,
[City] nvarchar(15) NULL,
[Region] nvarchar(15) NULL,
[PostalCode] nvarchar(10) NULL,
[Country] nvarchar(15) NULL,
[Phone] nvarchar(24) NULL,
[Fax] nvarchar(24) NULL

and create keys and indexes using

ALTER TABLE [Customers] ADD PRIMARY KEY ([CustomerID])
CREATE INDEX [CompanyName] ON [Customers] ([CompanyName])
CREATE INDEX [City] ON [Customers] ([City])
CREATE INDEX [Region] ON [Customers] ([Region])
CREATE INDEX [PostalCode] ON [Customers] ([PostalCode])

After creating the table, Write Data Preview (SQL) would produce

-- updating Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
UPDATE [Customers] SET [Address]='Foo House'+CHAR(13)+CHAR(10)+'Crowther Way' WHERE [CustomerID]='ISLAT'
-- update complete (1 of 91 rows had changes) ALTER TABLE [Customers] ADD PRIMARY KEY ([CustomerID])

after 'Garden' is changed to 'Foo' in cell 38,5 (ISLAT, address). Note that the cell contains a newline, CHAR(13)+CHAR(10).