Guide to Migrating from Microsoft Access to SQL Server 2005

gasownerΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 6 μήνες)

283 εμφανίσεις


Guide to Migrating from Microsoft Access to
SQL Server 2005

SQL Server Technical Article







Writers:
Yuri Rusakov, Alexey Kovaliov
, Eduard Kolosov

Technical Reviewer:
Darmadi Komo
, Irena Balin


Published:
December

2007

Applies To: SQL Server 2005


Summary
:

This
white

paper

covers
migrating

Microsoft Access databases to SQL
Server

2005

and discusses t
he d
ifferences between the two platforms. SQL

Server
Migration Assistant
for Access (SSMA Access)
is the best tool for this type of migration
;
this pape
r tells you

how to use
it

to
mitigate potential
problems in

database
conversion
.



Copyright


The information contained in this document represents the current view of Microsoft Corporation on the issues discussed
as of the date of publication. Because M
icrosoft must respond to changing market conditions, it should not be interpreted
to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented
after the date of publication.


This White Paper is for
informational purposes only.
MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR
STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT
.


Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright,

no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form
or by any
means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express
written permission of Microsoft Corporation.


Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual
property rights covering
subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the
furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intell
ectual
property.




2007

Microsoft Corporation. All rights reserved.


Microsoft,
SQL Server,
Visual Basic,
Visual C#,
Visual Studio,
and
Windows

are either registered trademarks or
trademarks of Microsoft Corporation in the United States and/or other coun
tries.


The names of actual companies and products mentioned herein may be the trademarks of their respective owners.



Guide to Migrating from
Microsoft
Access to SQL Server 2005


-

3

-



Table of Contents

Introduction

................................
................................
................................
......
1

Overview of Access to SQL Server 2005 Migration

................................
............
1

Microsoft Access Limitations

................................
................................
.............

2

Reasons for Migrating Access Databases to SQL Server 2005

................................

2

Migration Scenarios

................................
................................
.........................

3

Creating a Two
-
Tier Application
................................
................................
....

3

Creating a Client/Server Application

................................
.............................

3

Rewriting the Access Application

................................
................................
..

4

Access Version Compatibility

................................
................................
............

5

Migration Wizard

................................
................................
...............................
5

Migrating Database Objects

................................
................................
............

11

Sc
hema Mapping

................................
................................
...........................

11

Type Mapping

................................
................................
...............................

13

Converting Schema Properties

................................
................................
........

15

Default

................................
................................
................................
...

15

Nullability

................................
................................
................................

15

Allow Zero Lengt
h

................................
................................
....................

15

Converting Constraints

................................
................................
..................

15

Check Constraints

................................
................................
....................

16

Primary Keys

................................
................................
...........................

16

Foreign Keys

................................
................................
...........................

16

Migrating Indexes

................................
................................
.........................

16

Converting Expressions

................................
................................
..................

16

Handling Timestamps

................................
................................
...............

19

Naming

................................
................................
................................
...

19

Table Migration Potent
ial Problems

................................
................................
.

19

Access Table Does Not Have Unique Index or Primary Key

................................
.

19

Access Table Unique Index Contains Multiple Null Values

................................
....

20

Primary Key Includes Columns That Allow Null Values

................................
........

20

Access Table Contains Dates Out of SQL Server Range

................................
......

21

Access Table Contains Boolean Fields With Null Val
ues

................................
.......

21

Access Objects Have the Same Names as SQL Server Keywords

........................

21

Access Objects Names Include Special Characters

................................
.............

22

Hyperlink Column Jump Functionality Is Lost After Upsizing

................................

22

Memo Column Is Indexed in Access

................................
................................
.

22

Jet Syntax Potential Problems
................................
................................
.........

23

SELECT Statement

................................
................................
........................

23

Distinctrow Keyword

................................
................................
.................

23

Select I
nto Another Database

................................
................................
....

23

Incompatible Use of Alias

................................
................................
..........

24

Logical Expressions in a Select Statement

................................
...................

24

TOP N Clause

................................
................................
...........................

24

Multiple Dot Syntax

................................
................................
..................

25

Common Syntax Constructs

................................
................................
...........

25

Expressions

................................
................................
.............................

25

'&' as Concatenation Operator

................................
................................
....

25

'
\
' Operator (Integer Division)

................................
................................
....

25

'Mod' Operator (Modulus Division)

................................
..............................

26

'^' Operator (Raise to Power)

................................
................................
....

26

'Eqv' and 'Imp'

................................
................................
.........................

26

Date Literals Declaration

................................
................................
...........

27

Like
Operator

................................
................................
..........................

27

Join to View Converting Issues

................................
................................
........

28

Transform…Pivot Statement

................................
................................
...........

28

Functions

................................
................................
................................
........

30

String Functions

................................
................................
............................

30

Advanced Functions

................................
................................
.......................

36

Domain Agg
regate Functions

................................
................................
..........

38

Numeric/Mathematical Functions

................................
................................
.....

39

Date Functions

................................
................................
..............................

42

Data Type Conversion Functions

................................
................................
.....

48

Aggregate Functions

................................
................................
......................

48

Data Mig
ration

................................
................................
................................

51

Data Transfer Source

................................
................................
.....................

51

Data Transformation

................................
................................
......................

51

Technology Used for the Data Migration

................................
...........................

51

Transaction Boundaries

................................
................................
..................

51

Conclusion
................................
................................
................................
.......

52

Guide to Migrating from Microsoft Access to SQL Server 2005

1


1

Introduction

If

you are
considering migrati
ng

from Microsoft
®

Access to
Microsoft
SQL
Server
®

2005, take into account
the
differences between the two platforms. This
white

paper lists
the
known migration issues a
nd describes us
ing Microsoft
SQL

Server Migration Assistant

for Access

(SSMA

Access
)

as the tool for migration.
Th
is

white

paper

has

the following sections
:



Overview of Access to SQL Server 2005
M
igration
.
Outlines
the
reasons
why
a
M
icrosoft
Access user
would

want to migrat
e

to SQL Server

2005
, as well
as

the
ways to

approach this task.

SSMA Access

is useful in scenario
s in which
the Access front

end
will not
change
, but the back
-
end data
will be

moved to
overcome Access limitations and
to
ensure better
robustness and security.

The
section
also
contains
suggestions
for

other
migration
scenarios.



The
Migration Wizard
. Describes how to use the
SSMA Access

Migration
Wizard

the easiest way to perform a migration.



Migrat
ing

D
atabase
O
bjects
.
Examines
how
to co
nvert
objects of
the
Jet
database engine to
SQL Server

2005
.

(
Jet is
the
Access
database engine.
)

An
SSMA Access

user can control the way Access data types are mapped to
SQL
Server

2005

and how constraints, indexes
,

and foreign keys are transferred. As
SQL expression

syntax

is different

in
SQL Server

2005

and Access
,
the
replacements
SSMA Access

makes
during the migration

are described
.



T
able
M
igration

Issues
.
Explains
solutions for various problems

that
can arise

wh
en

converting Jet tables.

For example, the linked table in Access must have a
unique column
; i
f it is missing,
SSMA Access

can create the required column
automatically.

Or, s
ome Access columns might have names that are reserved in
SQL Server

2005
, which
ca
n create problems after

the migration
.
SSMA Access

provides a
warn
ing

so
that
you know
which

columns are candidates for
renaming.



Jet
S
yntax
I
ssues
.
D
escribes differences between the syntax of
SELECT

queries
in Access and in
SQL Server

2005
. In most cases,
SSMA Access

can handle
the
differences
automatically.
For example, Access uses
&
as a string concatenation
operator, while
SQL Server 2005

uses
+
.

SSMA Access

makes all necessary
replacements
when converting

the query.
In
those
rare cases
when you should
make manual updates,
this section

explain
s

how
.




Functions
.
D
escribe
s

the
functions implemented in
the
Jet database engine
,

and
how they correspond to
SQL Server

2005

functions.




Data
M
igration
.
Offers an overview of the
SSMA Access

data migration process.

Overview of Access to SQL Server 2005
M
igration

Microsoft Access is a database intended for small projects with few users.

It is

a
good tool for develop
ing
business applications, because it is
inexpensive
and easy to
use.

Still
,

y
ou
may
encounter problems
when
Access

limitations
prevent
you from
scaling the application when
your

database grows large and more users
need to

work with it.

At that stage,
you

can choose

the

more robust and secure database
solutions

of SQL Server

2005.

While
the
Access
Jet database engine is continually updated with many quality
functional and performance improvements, it is still a file
-
based
,

desktop
-
class data
store. The Jet engine is not intended or architected to support more demanding
business appl
ication requirements.
(For a d
etailed comparison
of

Access and SQL
Guide to Migrating from Microsoft Access to SQL Server 2005

2


2

Server

2005
, see
Microsoft Access or SQL Server 2005: What
'
s Right in Your
Organization?
)


M
icro
s
o
ft

Access Limitations

Access has several
inherent technical limitations.
Problems (slow performance,
corrupt database, application crashes)
may
occur if
an
Access application goes into
the Caution or Stop zones for any one of the
reasons

listed in the fol
lowing table.




O
kay

Caution

Stop

LAN
u
sers

<

5

5

10

> 10

Internet
u
sers

0

1

> 1

Frequency of
u
se

1 x day

2 x day

> 2

x day

User
p
ermissions

Read

only

Read/
w
rite

Read/
w
rite

D
atabase

size

< 100

MB

100

300

MB

>

300

MB

Data
s
tructures

Simple

Moderate

Complex

D
atabase
s
chema
d
esign

Good
d
esign

Poor
d
esign

Ad
h
oc


Reasons for
M
igrating Access
D
atabases to

SQL Server 2005


Following

are the main reasons for moving peripheral Access databases to a
SQL
Server

2005

environment:



Allow m
ultiuser access to a

database
.

Access databases
is

popular among
users
, but
can handle only a few users at a suitable performance level. When
more users use the application, the Access database does not scale well. SQL
Server

2005
, in contrast,

can handle thousands of users
,

making it
ideal for
multiuser environments.



Redesign and rewrit
e to

fewer and better applications
.

People outside
your

organization
'
s professional IT department may have developed
your

Access
applications.
They
may not have
develop
ed

the
code and database

according to
development standards
,
and
they
may have
lacked sufficient
knowledge or time.
In th
e
se situations
,

t
he
entire organization
can
benefit from standardizing and
unifying applications and data by centralizing common processes.



Interchang
e

data with the external world
.

SQL Server

2005

offers better
tools to communicate with other environments such as Oracle,
IBM
DB2
,

and
SAP. Through the Data Transformation Services/Integration Services (DTS/SSIS)
packages
,

you can automate
data interchange

with those environments or
integrate

them

in
to

existing applications.



Better a
nalyze and solve performance problems
.

Compared to Access, SQL
Server

2005
gives you
stronger tools, such as
SQL Profiler, execution plans in
Query Analyzer,
the
Index Tuning Wi
zard
,

and filegroups to spread data across
disks. SQL Server

2005
also has a better locking mechanism built specifically for
a
multiuser

environment.



Benefit from S
QL Server
2005
database security improvements
.

There are
many benefits to using

both SQL

Server and
Windows authentication to manage
users and access to multiple databases
,

database roles
,

and for
the
many ways
to grant permissions to the data

they provide
.

Guide to Migrating from Microsoft Access to SQL Server 2005

3


3

Migration Scenarios

M
igrating an Access database to
SQL Server

2005

moves some or all
data and data
definitions to the server. After the data is safely transferred to
SQL Server

2005
, you
must decide how to interact with the server
-
based data. The options
discussed in
this section
suggest

possible solutions
.


Creat
ing

a
T
wo
-
T
ier
A
pplication


You can link server
-
based tables to local tables in Access and use the other existing
database objects (queries, forms, reports, modules, and macros) as before.
This

creates a two
-
tier application, in which the server running
SQL Server

2005

stores
data
,

and copies of the interface are stored in Access on the users
'

computers.
Queries run on the client tier. Locally stored forms, reports, modules, and macros
provide the remaining elements of the user interface. The design of server
-
based
tables cannot be
modified from
within
Access.

The
advantage

of this
approach

Th
e main a
dvantage

of this approach
is
its

low transition cost because
front
-
end
database objects
do not require
modifications. Users continue to interact with a
familiar interface,
while

SQL Serv
er

2005

provides centralized data storage, back

up
and recovery, a
multiuser

environment, an integrated security model, and other
advantages.

After the data is in
SQL Server

2005
,
you can b
egin rewriting portions
of the application by using
a more robust e
nvironment such as Microsoft Visual
Studio
®

.NET and
SQL Server

2005

Reporting Services.
M
oving an Access
application towards
a
more scalable and robust platform is referred to as
upsizing.

When the migration is complete, all data
is

stored on the database server
,

and
Access continue
s

to operate smoothly as the desktop application.

The
disadvantages of this approach

The disadvantages

are that q
ueries are still processed locally, and a large amount of
network traffic is generated.

Impl
ementation

You can implement this

configuration

by
using either
the Access Upsizing Wizard
or

SSMA Access, but SSMA Access has more advanced functionality
, including
:



Multiple database upsizing



Schema and type mapping



A
F
ind
D
atabase wizard



Query conversio
n



User
-
friendly interface



Improved data migration mechanism



Advanced reporting



More b
ug fixes

Creat
ing

a
C
lient/
S
erver
A
pplication

Microsoft Access introduce
d

Microsoft Access Data Project, a new type of Access file
(.adp) designed as a client
/
server application. Communication between the client
and the server takes place through
a
n

OLE

DB component architecture, which was
specifically designed for efficient client/server communication. All tables and data
definition objects such as views, store
d procedures, and database diagrams are
stored on the server. The client stores only code
-
based or HTML
-
based objects such
Guide to Migrating from Microsoft Access to SQL Server 2005

4


4

as forms, reports, modules, and macros. Most data processing takes place on the
server, and only filtered data is sent to the client,

thus minimizing network traffic.

The
advantage

of this
approach

It results in a
n Access application that provides the best performance and scalability,
as well as the other positive attributes of
SQL Server

2005
.

The
disadvantage

of this
approach

I
t requires more development effort because Access objects such as forms, reports,
queries, and code
must

be redesigned to work directly with
SQL Server

2005
.

In
addition,
the

Data Project feature
may
be removed in
later
Microsoft Access
versions.

Implement
ation

This
configuration

is supported only by
the
Upsizing Wizard

that is supplied as a part
of Microsoft Access
.

Rewriting
the Access
A
pplication

In a small percentage of Access upsizing projects, the only possible is
to
migrat
e

from Access. Perhaps your
existing Access application
has outgrown
Access,
and
the
program

can
not
meet

your organization
'
s scalability, reliability, and performance

requirements
. Or, you
want

to migrate all or part of
the
application to the World
Wide Web.

In this scenario,
you use your
Access application as the
start

of
a new design.
You
use a
dditional technologies such as Visual Basic
®

(V
B
)
, Active Server Pages (ASP),
and Visual Studio .NET
to
rewrite the
Access
application. As part of
the
process, the
data moves to
SQL Ser
ver

2005
. Additionally, you can migrat
e

other data sources,
such as Oracle and
IBM
DB2 into
SQL Server

2005

for a centrally managed solution.

The advantage of this approach

The major advantage
is
the
flexibility

inherent in this approach
. You create an
app
lication that can target computers running Windows and the Web with minimum
changes. A more professional development environment, such as Visual Studio

.NET,
offers advantages such as team
-
based management, source code control, and
third
-
party
tools and co
mponents.
In

this scenario, you have a reliable, scalable, and
manageable application that can move from the business unit to the enterprise level.

The disadvantage of this approach

The disadvantage of this approach is cost
.
Because you are discarding the Access
application and its database, you are creating a new application with a new design,
development, and implementation project.
(T
hird
-
party products can help
cut

the
cost of front
-
end conversion.
C
onsider using Access Co
nverter
TM

from
Microtools
.
)

Implementation

Following

is an example
migration

sequence:

1.

Migrate

the Access back
-
end
schema and data.
Use either
SSMA Access
or the

Upsizing Wizard

(
SSMA Access

is preferable
)
.

2.

Migrate

the
Access
f
orms
to ASP
.NET

WebForms.
P
reserve the look

and

feel and
the operation of the original Access
f
orms
. All Access built
-
in features,
such as

auto lookup, implicit updates, shortcuts
,

and tool

tips can be emulated by

using
advanced WebForms
capabilities.

3.

Convert
Access
q
ueries to stored procedures.
SSMA Access

can help convert
SELECT

queries.

Guide to Migrating from Microsoft Access to SQL Server 2005

5


5

4.

The generated
VB.N
ET

code performs all database interactions and form
navigation. If necessary,
convert that
code
to
Visual

C#
®
.N
ET

with a
ny

number
of l
ow
-
cost VB

to C#.N
ET

conversion tools.

5.

Manually convert
Microsoft Access VBA code to VB
.NET or
C#.N
ET
. The
time
required

to migrate
Visual Basic for Applications (
VBA
)

code depends on the
volume and techniques employed. For example, applications that depen
d heavily
o
n

the
DAO
(Data Access Objects) interface
to perform database operations take
longer to convert than those
that use

built
-
in
Access
features.

Access
V
ersion
C
ompatibility


Note

that
SSMA Access

supports databases
used in

Access

97
and
later. If
you have
databases from an earlier version of Access, you must convert the
m
to a newer
version.
To convert,

open and sav
e

the databases in Access

97 or a later version
before you add them to
SSMA Access
.

Migration Wizard

If
you
lack SSMA Access
experience
and need to migrate
a
single database
, the

SSM
Access
Migration Wizard is the best choice
for an
easy and quick migration.

You can
configure
the Migration
Wizard to run every time
SSMA Access

starts.

The
first
screen
of the Wizard
is shown in Figure

1
.


Figure 1:
The
first screen of the
Migration Wizard


To use the SSM Acess Migration Wizard to migrate databases


1.

Step

1

of the wizard

(see
Figure

2
)
, p
rompts
for

a

name and location
for your
new
SSMA Access

project. You can confirm
the
default values or ent
er
your
own
.


Guide to Migrating from Microsoft Access to SQL Server 2005

6


6


Figure 2:
C
reate a new project


2.

Next,
add
databases to
the
project
, as shown in Figure

3.



Figure 3:
A
dd
Access
databases to the project


You can l
ocate
databases
manually
and add them
t
o
the
list

of databases to
migrate
, or use
the
Find
Database Wizard
, shown in Figure

4, which
makes
database selection easier
.


Guide to Migrating from Microsoft Access to SQL Server 2005

7


7


Figure 4:
Use the Find Databases Wizard for easier selection


3.

Select the objects to migrate.
Figure

5 shows
the
meta
-
base tree
from which you
select

objects
to migrate
.



Figure 5:
S
elect objects for migration


4.

C
reate a
connect
ion

to
SQL Server

2005
, as
shown in
Figure

6. (
If you
lack the
information
to
create a
connect
ion

to
SQL Server

2005
,
contact your database
administrator.
)


Guide to Migrating from Microsoft Access to SQL Server 2005

8


8


Figure
6
:
C
onnect to SQL Server 2005


5.

You can migrate
an
Access database to
an
existing database as well as to
a
new

one
. If
the database you
entered
doesn
'
t
exist on the
server running
SQL

S
erver
,

SSMA Access

prompts you

to create

it, as shown in Figure

7
.



Figure
7
:
Prompt to create the
specified database


6.

The screen shown i
n Figure

8

ask
s

whether to link tables.

If you want access to
data stored on the
SQL Server

2005

side
,

you need
a
link
to
SQL Server

2005

tables instead of
to
your Access objects.
The
operation changes
the
Access
datab
ase
,

but
SSMA Access

lets
you
link

and
unlink tables
at
any

time.


Guide to Migrating from Microsoft Access to SQL Server 2005

9


9


Figure
8
: The Migration Wizard asks whether to link tables


7.

You can see
the progress of the
migration
i
n
the
Migration Status window
, show
n

in Figure

9
.



Figure
9
: Check the migration
progress


Figure 9 also shows
the same steps
you would follow to
convert

manually.

The
Migration Wizard groups these actions into a single process
that makes
migration
easier and less error

prone.

To check for errors during migration

1.

SSMA Access

converts d
atabase objects.

C
heck the
Assessment
r
eport
,
shown in Figure

10
,
which
reports

all conversion
problems.
For different views, change the window to
display
errors, warnings, or
information messages
.


Guide to Migrating from Microsoft Access to SQL Server 2005

10


10


Figure
10
:
Check the Assessment
r
eport to view
any
problems


2.

Load objects into
the
SQL database.

3.

Migrate
the
data.

This
generates the Data Migration
r
eport

(
Figure

1
1
)
, which shows
statistics
about each migrated table.



Figure 1
1
: The Data Migration
r
eport shows statistics on each migrated
tables


4.

Link SQL tables to
the
Access application.

Each step contains hyperlink
s

to show errors, warning
,

and information
messages
.
You can filter
the
message list
by using

buttons on the top of the
window
, as shown in Figure

1
2
.


Guide to Migrating from Microsoft Access to SQL Server 2005

11


11


Figure 1
2
:
The Error List


You can continue working with project after
the Migration W
izard finishe
s
. Keep in
the mind
that the
Migration Wizard creates
a
new project each time
you launch it
.

Migrat
ing

Database Objects

This section looks at

how to
migrat
e

Jet database objects by means of
SSMA Access
.
S
etting
the
proper schema and type mapping

is described first
.
After that,
SSMA
Access

conversion of constraints, indexes
,

and expressions

is covered
.

Schema
M
apping

Y
our first
migration
step
is to

decide on t
he physical structure of the target
SQL
Server

2005

database. In the simplest case, you can map the Access database to
a
SQL Server

2005

database.

SSMA Access

applies the selected schema
-
mapping method consistently when
converting database objects and ref
erences to them. You can change schema
mapping
by
using

the
Schema mapping

tab for
the
database on the right of
the
source schema tree
. Click

the

Modify

b
utton

to change the default mapping
.



Figure 1
3
:
Choosing the target schema


Guide to Migrating from Microsoft Access to SQL Server 2005

12


12

After you cho
o
se your optimal schema mapping, you can start creating the target
SQL Server

2005

database and its required schemas.

The typical
SSMA Access

migration includes connecting to the source Access
database and the
target
SQL Server

2005
, then
running

the
Conver
t Schema

command. When the target objects are created in the
SSMA Access

workspace, you
can save them
by
using the
Load to Database

command.

Next, execute the
Migrate Data

command, which transfers the data from the
source to the target tables, making the
necessary conversions.
(
The data migration
process is executed on the
SQL Server

2005

computer
.
)

Finally,
run

the

Link
Tables

command to
link
migrated
tables

to
the
Access application.

Each Access table is converted to a
SQL Server

2005

table.
A
ll indexes and
constraints defined for
each

table are also converted. When determining the target
table
'
s structure,
SSMA Access

uses type
-
mapping definitions.

Guide to Migrating from Microsoft Access to SQL Server 2005

13


13

Type
M
apping

The following
table shows the
default
type
mapping for migrating from Jet

4.0

(Acc
ess

2000 and
later
) to SQL Server

2005.


Size in bytes

(
J
et 4.0)

Jet

4.0

SQL Server
2005

Possible Custom

Map
p
ing


Size * 2

TEXT(size)

nvarchar(size)

Char(size), Varchar(size),
NChar(size), NVarchar(size),
Varchar(max), Nvarchar(max)


1073741823

MEMO

nvarchar(max)

Char(size), Varchar(size),
NChar(size), NVarchar(size),
Varchar(max), Nvarchar(max)


1

BYTE

t
inyint

tinyint, smallint, int, bigint,
numeric(p,s), decimal(p,s),
float(p), double precision,
real, smallmoney, money


2

INTEGER

s
mallint

smallint, int, bigint,
numeric(p,s), decimal(p,s),
float(p), double precision,
real, smallmoney, money


4

LONG INTEGER

i
nteger

smallint, int, bigint,
numeric(p,s), decimal(p,s),
float(p), double precision,
real, smallmoney, money


4

SINGLE

Real

numeric(p,s), decimal(p,s),
float(p), double precision,
real, smallmoney, money


8

DOUBLE

f
loat

numeric(p,s), decimal(p,s),
float(p), double precision,
real, smallmoney, money


9

DECIMAL

f
loat

tinyint, smallint, int, bigint,
numeric(p,s), deci
mal(p,s),
float(p), double precision,
real, smallmoney, money


16

GUID

u
niqueidentifier



8

DATE

d
atetime

smalldatetime, datetime


8

CURRENCY

Money

numeric(p,s), decimal(p,s),
float(p), double precision,
real, smallmoney, money


2

YESNO

t
inyint

t
inyint
, smallint, int, bigint


2147483647

LONGBINARY

varbinary(max)

image, binary, varbinary,
varbinary(max)


Size

BINARY(size)

varbinary(size)

image, binary, varbinary,
varbinary(max)


Guide to Migrating from Microsoft Access to SQL Server 2005

14


14

The next table shows
t
he default
type
mapping for
migrating from Jet

3.5

(Access 97
and
previous versions
) to SQL Server

2005.


Size in bytes (
J
et
3.5)

Jet

3.5

SQL Server
2005

Possible Custom Map
p
ing


size

TEXT(size)

varchar(size)

Char(size), Varchar(size),
NChar(size), NVarchar(size),
Varchar(max),

Nvarchar(max)


1073741823

MEMO

varchar(max)

Char(size), Varchar(size),
NChar(size), NVarchar(size),
Varchar(max),
Nvarchar(max)


1

BYTE

t
inyint

tinyint, smallint, int, bigint,
numeric(p,s), decimal(p,s),
float(p), double precision,
real,
smallmoney, money


2

INTEGER

s
mallint

smallint, int, bigint,
numeric(p,s), decimal(p,s),
float(p), double precision,
real, smallmoney, money


4

LONG
INTEGER

i
nteger

smallint, int, bigint,
numeric(p,s), decimal(p,s),
float(p), double precision,
real, smallmoney, money


4

SINGLE

real

numeric(p,s), decimal(p,s),
float(p), double precision,
real, smallmoney, money


8

DOUBLE

f
loat

numeric(p,s), decimal(p,s),
float(p), double precision,
real, smallmoney, money


16

GUID

u
niqueidentifier



8

DATE

d
atetime

smalldatetime, datetime


8

CURRENCY

money

numeric(p,s), decimal(p,s),
float(p), double precision,
real, smallmoney, money


2

YESNO

t
inyint

t
inyint
, smallint, int, bigint


2147483647

LONGBINARY

varbinary(max)

image,
binary, varbinary,
varbinary(max)


Size

BINARY(size)

varbinary(size)

image, binary, varbinary,
varbinary(max)


Type mapping can be defined
for

all database
s

and
for

each table separately
by
using
the
Type mapping

tab on the right of
the
source schema
tree
. To do
this
,
click

the

Edit

button

for
the
particular
source type
, as
shown

in Figure

1
4
)
.


Guide to Migrating from Microsoft Access to SQL Server 2005

15


15


Figure 1
4
:
Ch
oosing the source type


Note



Access and SQL Server

2005 have different default data types for
decimal

without parameters. You
must

map
the
Access
decimal

data type to
SQL
decimal(18, 2)

(not just
decimal
) on the
Type mapping

tab.

Conver
ting

S
chema
P
roperties

This section has
suggestions
for converting

a number of

column attributes.

Default

Jet
default

expressions on columns are mapped to
SQL

Server

2005

default

expressions on columns.

Nullability

The
Jet
nullability

column property is

mapped directly to
SQL Server

2005

NULL

column
property
.

Allow
Z
ero
Length

If
the
Allow zero length

column property is set to false on
text
/
memo

columns in
Jet
,
it
is
mapped to
a
check constraint with a constraint name
"
c_ssma_disallow_zero_length
"

on the
varchar
/
nvarchar

columns
in

SQL
Server

2005
.

For example
, the following code shows how to convert
the
Allow zero length

column property on column
c1

in table
table1
:

Create table
table1
(c1 nvarchar(10), constraint
c_ssma_disallow_zero_length check(len(c1) >0))

Converting

C
onstraints

This section describes how to convert

the

constraints of an Access table.

Guide to Migrating from Microsoft Access to SQL Server 2005

16


16

Check
C
onstraint
s


Check
constraints

(column
-
level or

table
-
level) are expressions that should be
parsed and converted to SQL.
(
For
details see
Converting Expressions
.
)

Primary
K
eys

Jet primary keys are mapped directly to
SQL Server

2005

primary keys.

Foreign
K
eys

Jet foreign keys are mapped directly to
SQL Server

2005

foreign keys.

Exception:



SQL Server 2005

does not allow the Jet functionality of linking text and memo
columns with different size via
a
foreign key constraint. To overcome that,
SSMA
Access

schema ty
pe mapping
is

changed to enable the creation of the foreign
key constraint on the
SQL Server

2005

side. Both columns participating in the
foreign key

constraint
are

mapped to the same
SQL Server

2005

type according
to the bigger Jet type.

Migrat
ing

I
ndexes


Jet indexes are mapped directly to
SQL Server

2005

indexes.

Exceptions:



In case
s

where
the
MEMO

column is indexed on the Jet side, the index is not
created on the
SQL Server

2005

side since
SQL Server

2005

does not support
indexing
nvarchar (max)

or
varchar (max)
.



Tables
that do not have a

unique index
and

that do not have a

primary key
are

upsized
,

but
an
identity column
is

added to creat
e a

unique index.

Conver
ting

E
xpressions

To convert expressions, a
pply the
se
rules
in sequence
:

1.

Remove any

leading

=

character in
a
Jet expression. For example:

Create table foo(x1 int default =5)

in Jet is converted to

Create table foo(x1 int 5)


for SQL Server

2005.

2.

Replace
a
double quote (
"
) with
a
single quote
(
'
).

For example:

"
abc
"

in Jet is
converted to
'
abc
'

for
SQL Server

2005
.

3.

D
ouble
any
embedded single quote
s
. For example:

"
a
'
a
"

in Jet is converted
to

'
a
"'
c
'

for
SQL Server

2005
. The quote in the middle of
"
a
'
a
"

is doubled to create
'
a
"
c
'
.

4.

Replace
#

around dates with single

quotes. For example
#1/6/2006#

in Jet is
converted to
'
1/6/2006
'

for
SQL Server

2005
.

Guide to Migrating from Microsoft Access to SQL Server 2005

17


17

5.

Replace the function tokens found inside the expression according to the
following table
.


Jet function token

Replace with
SQL Server 2005

function token

Yes

-
1

On

-
1

True

-
1

No

0

Off

0

False

0

Mod

%

Sgn(

Sign(

chr(

Char(

Chr$(

Char(

Asc(

ascii(

str$(

str(

int(

floor(

Cint(

convert(smallint,

Clng(

convert(int,

csng(

convert(real,

cdbl(

convert(float,

Cstr(

convert(varchar,

ccur(

convert(money,

cvdate(

convert(datetime,

space$(

space(

lcase(

lower(

lcase$(

lower(

ucase(

upper(

ucase$(

upper(

len(

datalength(

Ltrim$(

Ltrim(

Rtrim$(

Rtrim(

Right$(

Right(

Mid(

substring(

Mid$(

substring(

Now()

getdate()

Guide to Migrating from Microsoft Access to SQL Server 2005

18


18

Jet function token

Replace with
SQL Server 2005

function token

Date()

convert(datetime,convert(varchar,getdate(),1))

year(

datepart(yy,

month(

datepart(mm,

Day(

datepart(dd,

weekday(

datepart(dw,

Hour(

datepart(hh,

minute(

datepart(mi,

second(

datepart(ss,

time()

convert(datetime, convert(varchar,getdate(),14))


6.

Use

the next table to r
eplace the format tokens inside
datepart(),dateadd()
datediff()

functions
.


Jet strings format part of
datepart(),dateadd() datediff() functions

Replace with
SQL
Server 2005

format

"
yyyy
"

Yy

"
q
"

Qq

"
m
"

Mm

"
y
"

Dy

"
d
"

Dd

"
w
"

Dw

"
ww
"

Wk

"
h
"

Hh

"
n
"

Mi

"
s
"

Ss

"
yy
"

Yy

"
qq
"

Qq

"
mm
"

Mm

"
dy
"

Dy

"
dd
"

Dd

"
dw
"

Dw

"
wk
"

Wk

"
hh
"

Hh

"
mi
"

Mi

"
ss
"

Ss



Guide to Migrating from Microsoft Access to SQL Server 2005

19


19

Handling
Timestamp
s

After

you
upsiz
e
the performance of

record

editing
,

performance
may

degrade

for
some
column type
s
.
This

is because Access
must
check
whether

each column was
changed before committing the changed data.
To fix this, add

a
timestamp

column
by
using
SSMA.
You
can control this addition
in

Project Settings
, as shown in
Figure

1
5
.


Figure 1
5
:
Adjust timestamp handling
in

Project Settings

Naming

SQL
Server

2005 support
s

spaces and nonalphanumeric characters in object name
s
,

as long they are
surrounded by

square brackets in DDL statement
s
.

Table
M
igration

Potential Problems

This section

cover
s

typical situations
in which
migration problems can arise.
I
t

includes

SSMA Access

handling or user solutions.

Access
T
able
Does Not Have
U
nique
I
ndex or
P
rimary Key



If a table without
a
unique index is upsized
,

it will be
un
editable after the
upsizing.



If the table does not have a
primary key
or unique index
,

the
destination script
display
s the following message:

"
Table <table name> does not have a primary key or unique index and
therefore cannot be edited from within Access
.
"



If the table does not have an auto number index
,

SSMA Access

can automatically
create a n
ew column called
ssma_rowid

and create a
primary key

on that
column.
You

will see the following warning
s

in the destination code
:



"
A new identity column
'
SSMA_RowID
'

was created for the primary key.
"



"
Primary key
'
SSMA_PK$Table1
'

was created for the identity column
.
"

You

can
enable or disable the a
utomatic

generation of
a primary key

column
in
Project Settings
, as shown in Figure

1
6
.

Guide to Migrating from Microsoft Access to SQL Server 2005

20


20



Figure 1
6
:
Create a primary key in Project Settings


Access
T
able
U
nique
I
ndex
C
ontains
M
ultipl
e
N
ull
V
alues

Data containing multiple null values in
an
Access table
column
that has
a
unique
index can
not
be transferred to
SQL Server

2005
. This is

because
a

SQL Server

2005

unique index
does not allow

multiple nulls.

During data migration
you will

receive
the following

error

message
:

"
Cannot insert duplicate key row in object <table name> with unique index
<index name>
.
"

Primary
K
ey
I
nclud
es
C
olumns
T
hat
A
llow
N
ull
V
alues

In Access,
a
primary key can include columns with
the
Required

field set to false.
This

is not allowed in
SQL Server

2005
.

By default,
SSMA Access

produce
s

the
following warning:

"
Column <column name> of the primary key is nullable, which
SQL Server
2005

does not allow. The nullable flag was removed from the target column
to avoid problems during loading database objects into
SQL Server

2005
. If
there are rows that contain NULL values, you might encounter problems
during data migration
.
"

C
ontrol
SSMA beha
vior in
this
situation

with Project Settings
, as shown in Figure

1
7
.


Guide to Migrating from Microsoft Access to SQL Server 2005

21


21


Figure 1
7
:

Warn when the primary key include
s

nullable columns


Your choices are:



Warning
:
(Set by d
efault
.)

All columns with
a
NOT NULL

attribute will form the
primary key
.




Error
:

SSMA
Access
generates
an
error and does not convert the table.



No message
: Tables are converted
as is, ignoring possible application problems.

Access
T
able
C
ontains
D
ates
O
ut of

SQL Server
R
ange

The
SQL Server 2005

datetime

type accepts dates only in the

range of 1

Jan

1753
to 31

Dec

9999. Access
, in contrast,

accepts dates in the range of 1

Jan

100 to
31

Dec

9999.
This

can cause data transfer to fail.

SSMA Access

change
s

an
incorrect
value to nearest date limit.

For example
,
SSMA Access

migrate
s

1/01/1700 as
1/01/1753.

Access
T
able
C
ontains Boolean
F
ields
W
ith
N
ull
V
alues

After linking a table that has Boolean fields containing nulls
,

the table
cannot be
edited in

Access
.
SSMA Access

displays

the following warning:

"
The bit column
'
Checked
'

has n
o default value. To avoid problems with null
values in Access applications, the default value
'
0
'

was added to target
column
"

Access
O
bjects
Have the Same Names as


SQL Server
K
eyword
s

SQL Server 2005

can accept objec
t
s

(
such as

tables,

columns,

indexes,

schemas)
that ha
ve

the same names as

SQL Server

2005

keyword
s

if

you
surround
the
object names

with quotation marks

in the DDL statement.

A
ll queries
that
referenc
e

th
e
se objects

must
include the quotation marks around
names as well.
T
herefore,
Access quer
ies that use names
without quotation marks
can

fail.

Guide to Migrating from Microsoft Access to SQL Server 2005

22


22

SSMA Access

tr
ies

to avoid
th
e

problem
by

adding square brackets and
displaying

the
warning message:

"
Column <column name>

has a name that might cause problems for the
Access application to function correctly against
SQL Server

2005
.
"

Access
O
bjects
N
ames
Includ
e

S
pecial
C
h
aracters

SQL Server 2005

can accept objec
t
(
for example,
tables,

columns,

indexes,

schemas) names
that

include special c
h
aracters

(
space
,

&
,

+
,

*
)

if

you
surround the

names
with quotation marks
in the DDL statement.
A
ll queries
that
referenc
e

th
e
se
objects
must
include the quotation marks around

names as well.
Th
at

means that
it
is possible
that Access quer
ies that
do not have the quotation marks

will fail.

SSMA Access

tr
ies

to avoid
th
e

problem
by
adding square brackets and
displaying

the
warning message
:

"
Column <column name> has a name that might cause problems for the
Access application to function
correctly against SQL Server

2005.
"

Hyperlink
C
olumn
J
ump
F
unctionality
I
s
L
ost
After

U
psizing

After upsizing
,

Jet hyperlink columns are treated
the same as

regular memo columns
and loose the
ir

jump functionality.
This

is because the hyperlink is a Jet col
umn flag
that is read from
SQL Server

2005

after the upsizing
. S
ince SQL does not have
th
at

flag
,
th
e

information is lost.

In
this

situation
SSMA Access

displays

the following
warning:

"
The hyperlink column <column name> was converted to a text column and,

as a result, is no longer a link
.
"


Memo
C
olumn
I
s
I
ndexed in Access

Access supports indexing
the
memo column
,

but
SQL Server

2005

does not support
indexing
nvarchar(max)
.

If
you
use the

default type mapping
,

SSMA Access

displays

the
following
warning:

"
Index <column name> is on a memo column and cannot be created on
SQL
Server

2005

with the current type mapping settings.
"

Figure 18 shows how to
control this
situation

in

Project Settings (
similar to

the way
you handle primary keys

with nulls)
.


Guide to Migrating from Microsoft Access to SQL Server 2005

23


23


Figure 1
8
:
Option
s

for handling index memo columns


Jet Syntax
Potential Problems

The c
urrent version of
SSMA Access

can convert only
SELECT

statements into
T
ransact
-
SQL views.
INSERT
,
UPDATE
,

and
DELETE

quer
y

conversion is

not

implemented.

T
his section describes
the
syntax difference

between the two
platforms.

If

SSMA Access

can

handle an issue,
it is marked with

"
SSMA
Access
support

=
Yes
"

in the statement descriptions in this section
and
the solution is
explain
ed
.

If
SSMA
Access

cannot make the automatic conversion,
the solution for

manual handling

is
included
.

SELECT

S
tatement

This section describes how
some incompatible

clauses of
the
SELECT
statement can be handled

during the migration.

Distinctrow
K
eyword

Issue
: Jet SQL can use
the
Distinctrow

keyword
,

but T
ransact
-
SQL
doesn
'
t have
such
a
keyword.

SSMA Access

s
upport
:Yes

Solution
:
E
mulate with
a
subquery.

Example
:

Jet

SELECT

Distinctrow
<
FieldsList
>

FROM

Table

SQL

Server 2005

SELECT

<
FieldsList
>

FROM

(
SELECT

distinct
*

FROM

Table
)

Table

Select
I
nto
A
nother
D
atabase

Issue
:

Jet SQL syntax is different from T
ransact
-
SQL.

SSMA Access

s
upport
:

No

Guide to Migrating from Microsoft Access to SQL Server 2005

24


24

Solution
:

Modify syntax
so that it is more like

T
ransact
-
SQL
.

Example
:

Jet

SELECT

Suppliers
.*

INTO

Suppliers
IN

'
Backup.mdb
'

FROM

Suppliers
;

SQL Server 2005

SELECT

Suppliers
.*

INTO

BackupDB..Suppliers
FROM

Suppliers
;

Incompatible Use of
Alias

Issue
:

Jet SQL allow
s

the
use
of an
alias in the same statement
in which it

i
s
defined.

SSMA Access

s
upport
:

No

Solution
:

Use
the f
ull expression instead of
an
alias.

Example
:

Jet

SELECT

(Price * discount)
AS

saving, (Price


saving)
AS

NewPrice

FROM

Products

SQL Server 2005

SELECT

(Price * discount)
AS

saving, (Price


Price * discount)
AS

NewPrice

FROM

Products

Logical
E
xpressions in
a

Select
S
tatement

Issue
:

Jet SQL interprets two expressions with
=

between them in the
SELECT

statement as Boolean expression
s
.

SSMA Access

s
upport
:

Yes

Solution
:

U
se
a
CASE

statement to
emulat
e

similar
behavior
. Keep in the mind that
the
Boolean type in the Access has

the

n
umeric
value
-
1
.


Example

1
:

Jet

SELECT

Sum(discount = 0.8)
FROM

Products

SQL Server 2005

SELECT

Sum(Case discount when 0.8 then
-
1 Else 0)

FROM

Products

Example

2
:


Jet

SELECT Sum(<logical expression>) FROM Products

SQL

Server 2005

SELECT

Sum(
CASE

when <logical expression> then
-
1
ELSE

0)

FROM

Products

TOP
N

C
lause

Issue
:

Jet SQL calls
the
TOP

operator using
WITH TIES

by default.

SSMA Access

s
upport
:

Yes

Solution
:

Add
WITH TIES

to
the
T
ransact
-
SQL statement.

Example
:

Guide to Migrating from Microsoft Access to SQL Server 2005

25


25

Jet

SELECT

top 3 *
FROM

Products
Order by

SomeField

SQL Server 2005

SELECT top 3 With Ties * FROM Products Order by SomeField

Multiple
D
ot
S
yntax

Issue
:

Jet SQL allows multi
ple

dot identifiers.

SSMA Access

s
upport
:

No

Solution
:

Add aliases to all fields
and use these aliases in the top
-
level query.

Example
:


Jet

SELECT

x
.
a
.
AAAA
,

x
.
b
.
AAAA

FROM

[SELECT a.AAAA, b.AAAA FROM table1 a
Inner Join Table2 b On a.ID = b.ID]
.

AS

x

SQL Server 2005

SELECT x
.
Field1
,

x
.
Field2 FROM [SELECT a.AAAA as Field1, b.AAAA as
Field2

FROM

table1 a Inner Join Table2 b On a.ID = b.ID]
.

AS

x

Common
S
yntax
C
onstructs

This section covers
converting

incompatible operators in the
SELECT
statement
expressions
.

Expressions

Access Jet SQL has expressions similar to T
ransact
-
SQL expression
s. Most operators
can move to
SQL Server

2005

without any conversion.
These include
comparison
operators (
<
,
>
,
=
,
>=
,
<=
,
<>
,
between
), logical operators (
And
,
Or
,
Xor
),

and

arithmetic operators (
+
,
-
,
*
,
/
). The other
s

are described
next
.

'
&
'

as
C
oncatenation
O
perator

Issue
:

Jet can use
&

as
a
string concatenation operator while
SQL Server

2005

uses
only the
+

character for string concatenation.

SSMA Access

support
:

No

Solution
:

Convert any
&

character used for concatenation to
+

in
SQL Server

2005
.

Example
:

Jet

SELECT

*
FROM

table1

WHERE

c1 =
'
a
'

&
'
b
'


SQL Server 2005

SELECT

*
FROM

table1

WHERE

c1 =
'
a
'

+
'
b
'

'
\
'

O
perator (
I
nteger
D
ivision
)

Issue
:

Jet has
an
integer division operator
,

but T
ransact
-
SQL
does not.

SSMA Access

support
:

No

Solution
:

Use just
the
division operator
/
,

but
make
Dividend

and
Divisor

rounded and reduced to
an
integer type.

Example
:

Guide to Migrating from Microsoft Access to SQL Server 2005

26


26

Jet

a
\

b

SQL Server 2005

Cast(Round(@a, 0) as int) / Cast(Round(@b, 0) as int)


'
M
od
'

O
perator
(
M
odulus
D
ivision)

Issue
:

Jet has

a

different operator
for

modulus division.

SSMA Access

support
:

Yes

Solution
:

Use
the
T
ransact
-
SQL operator
%

but
Dividend

and
Divisor

should be
rounded and reduced to
an
integer type.

Example
:


Jet

a mod b

SQL Server 2005

Cast(Round(@a, 0) as int) %
Cast(Round(@b, 0) as int)

'
^
'

O
perator
(
R
aise to
P
ower
)

Issue
:

Jet has
a
different operator to raise to
a
power.

SSMA Access

support
:

Yes

Solution
:

Use
the
T
ransact
-
SQL function
Power(a, b)

to emulate
the

expression.

Example
:

Jet

a ^ b

SQL Server 2005

Power(@a, @b)


'
Eqv
'

and
'
Imp
'

Issue
:

T
ransact
-
SQL
does

not

have
Eqv

nor
Imp

operators.

SSMA Access

support
:

Yes

Solution
:

Emulate
these

expressions. But
because
T
ransact
-
SQL

does not have
Boolean variables
, you
must

convert
b
oolean

values into
bit
.

Example

1

Jet

SELECT

(a > b) eqv (b < c)

SQL Server 2005

SELECT

CASE

(
Case when a
>

b
Then
1 else 0 end
)

WHEN

(
Case


WHEN

b
<

c Then

1 else 0 end
)

then
1
ELSE

0
END

Example

2

Jet

SELECT

(a > b) imp (b < c)

SQL Server 2005

SELECT

CASE

(
Case when a
>

b
Then
1
ELSE

0
END
)

WHEN

(
Case


WHEN

b
<

c Then

1 else 0 end)

then
1

ELSE

0

END

Guide to Migrating from Microsoft Access to SQL Server 2005

27


27

Example

3

Jet

SELECT

* From SomeTable where (a > b) eqv (b < c)

SQL Server 2005

SELECT

* From SomeTable Where
(
Case when a
>

b

Then

1
ELSE




0 end
)

=
(
Case when b
<

c Then 1
ELSE

0
END
)

Example

4

Jet

SELECT

* From SomeTable where (a > b) imp (b < c)

SQL Server 2005

SELECT

* From SomeTable Where
(
Case when a
>

b Then

1
ELSE

0



end
)

<>
(
Case when b
<

c
Then

1
ELSE

0
END
)

Date
L
iterals
D
eclaration

Issue
:

Jet SQL encloses date literals in
#

symbols
,

but
SQL Server

2005

can
only
use
'

(apostrophe)
.

SSMA Access

support
:

Yes

Solution
:

Replace
#

symbol with
'

(apostrophe)
in the date literals.

E
xample
:


Jet

#03
-
22
-
2006#

SQL Server 2005

'
03
-
22
-
2006
'

Like
O
perator

Issue
:

Jet SQL has two
Like

operators
:
Alike

and
Like
. The f
ormer
works with
SQL
Server

2005

Compatible Syntax (ANSI

92) (in Access

2002 and
later

versions
).
R
eplace

it
with
the
Like

keyword during conversion to
SQL Server

2005
. The
latter

has different syntax in patterns.

SSMA Access

support
:

Yes

Solution
:

R
eplace
the
keyword
alike

with
like
,

or modify
the
pattern according

to

the following
table.


Jet SQL

T
ransact
-
SQL

?

_

#

[0
-
9]

*

%

[<charlist>]

[<charlist>]

[!<charlist>]

[^<
charlist>]

^

\

(backslash)


In the table
,

<charlist>

is a sequence of characters (
such as
ABC
) or
a
character
range (
A
-
Z
).

Guide to Migrating from Microsoft Access to SQL Server 2005

28


28

Example
:

Jet

a like
'
1234*
'
, a alike
'
1234%
'

(in the Access 2002 and


higher)

SQL Server 2005

@a like
'
1234%
'

Join to View
C
onverting
I
ssues

C
onvert
ing

Access
Query with Joins

to SQL
View

can result in a

problem with field
name duplication.

For example:


Create view SomeView

As

Select a.Fleld1, b.field1

From a Inner join b On a.ID = b.ID


To avoid
this,

add
aliases

to
the
query

field
:


CREATE

view

SomeView

AS

SELECT

a.Fleld1
AS

a_Field1, b.field1
AS

b_Field1

FROM

a Inner join b On a.ID = b.ID


Transform

Pivot
S
tatement

The solutions for converting TRANSFORM…PIVOT statements use

a
source statement
in the
following
form

as an example
:


TRANSFORM
"
aggfunction
"

"
selectstatement
"

PIVOT
"
pivotfield
"

[IN (
"
value1
"
[,
"
value2
"
[, ...]])]


The following
code
examples
show
ways to convert

these

statement
s

into
T
ransact
-
SQL commands.
All

three
example
cases

assume that

the parameter

aggfunction

is

neither

the

First()
n
or

Last()
function
.


Case
1.
The
PIVOT clause contains IN(…) part.

Convert

the following statement:



TRANSFORM aggrfunction(Col1)


SELECT <Col3,..., ColN> FROM Table AS t


GROUP BY <Col3,..., ColN>


ORDER BY <Column list>


PIVOT (Col2) in (<col2_value
1,col2_value2,...,col2_valueN>)

T
o the following code:


SELECT * FROM (

Guide to Migrating from Microsoft Access to SQL Server 2005

29


29


SELECT <Col3,..., ColN>,Col1,Col2 FROM Table

)

T


PIVOT


(


aggrfunction (Col1) FOR Col2
IN([col2_value1],[col2_value2],...,[col2_valueN])


) T


ORDER BY <Column list>

Case
2
.
The
PIVOT clause does not contain IN(…) part and Col2 data type is
a
natural number.

Convert

the following statement:



TRANSFORM aggrfunction(Col1)


SELECT <Col3,..., ColN> FROM Table AS t


GROUP BY <Col3,..., ColN>


ORDER BY <Column list>


PIVOT (Col2);


T
o the following

code
:


declare







@colunm_list nvarchar(max)





SET

@colunm_list=
''





SELECT

@colunm_lis
t=@colunm_list+











CASE

when rnum = 1 then quotename(ID)













ELSE

'
,
'

+quotename(ID)









END










FROM

(
SELECT

ROW_NUMBER() over(order by ID asc) as rnum, id









FROM

(SELECT distinct(Col2) as ID FROM
"
Table
"











)
T

)
t1


exec (
'









SELECT * FROM (











SELECT <Col3,..., ColN>,Col1,Col2 FROM
"
Table
"













) T









PIVOT











(aggrfunction(Col1) FOR Col2 N(
'
+@colunm_list+
'
)













) T









ORDER BY <Column list>
'
)

Case
3.
The
PIVOT clause does not contain IN(…) part and Col2 data type is
a
real number.

Access replace
s

,

in real number
s

with

_
.


Guide to Migrating from Microsoft Access to SQL Server 2005

30


30


TRANSFORM aggrfunction(Col1)


SELECT <Col3,..., ColN> FROM Table AS t


GROUP BY <Col3,..., ColN>


ORDER BY <Column list>


PIVOT (Col2);


The following code
emulate
s

this Access feature:


declare





@colunm_list nvarchar(max)



SET

@colunm_list=
''



SELECT

@colunm_list=@colunm_list+







CASE

when rnum = 1 then quotename(replace(cast(ID
asvarchar(100)),
'
.
'
,
'
_
'
))









ELSE

'
,
'

+quotename(replace(cast(ID
AS

varchar(100)),
'
.
'
,
'
_
'
))







END




FROM ( SELECT

ROW_NUMBER() over(order by ID asc)
AS

rnum, id



FROM

( SELECT distinct(Col2)
AS

ID FROM
"
Table
"

















)T














) t1


exec (
'



SELECT * FROM (







SELECT <Col3,..., ColN>,Col1,









REPLACE
(cast(Col2 as varchar(100)),
'
.
'
,
'
_
'
) as Col2 FROM
"
Table
"
















) T







PIVOT







(









aggr_function(Col1) FOR Col2 IN(
'
+@colunm_list+
'
)







) T



ORDER BY <Column list>
'
)

Functions

M
any standard
Access
functions have no direct
correspondence

in
SQL Server

2005