Data Flask

Microsoft SQL Server

Microsoft T-SQL reference MsSQL (external).

Column Type

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

This will be the same text (up to the left paren) that will be provided to the CREATE TABLE command. 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 .dfg 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 [...]).

Instance Names (SQL Server 2005)

Microsoft SQL Express 2005 Login

When using SQL Server 2005 or SQL Express 2005, you will need to add a backslash and the instance name. For example: SERVER\SQLEXPRESS. Both the SQLNCLE and SQLOLEDB enumerators under Providers in the OleDb Data window will list the server names, but you must add \instancename. If you have installed Microsoft SQL Server 2005 (Express version) on your local machine, the name LOCALHOST\SQLEXPRESS, or .\SQLEXPRESS can be used.

You can select Open Data to open an .MDF file when using SQL Express installed locally. For example, load Spatial.mdf (installed from SqlServerSamples.msi here (External), into this folder by default). Note : Spatial.mdf must be copied out of Program Files\... before you may attach to it, otherwise you will get an error 5133.

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 Flask.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 (External) . If you usedrop database [Database] the .mdf file will be deleted.

Data Flask 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.

Sample Migration SQL

Load Customers from Nwind.mdb, 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=Flask;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).