MS SQL Migration From MS Access

Hi,

I am looking to move up from MS Access to MS SQL.

What is the maximum amount of fields allowed in a MS SQL Record?

In my Access database I have had to create a number of tables to hold the data. I would like to keep everything in one table for the MS SQL DB.

Is there any problems I should watch for regarding data types etc when I change over.

Many Thanks

Darrell...One record on SQL Server cannot keep more than 8060 bytes - of course you can use text fields, but they are not so convenient.
Question is - what kind of database do you have? OLTP, warehouse or something else? Do you need to normalize your data or denormalize?|||Hi,

I believe I am looking at a normalised access database, that I want to migrate from.

The data is currently split across 5 tables, using refrential integrity with primary key the unique id for each record.

With the 8060 bytes of data, is that just adding up the data type sizes for the maximum amount data fields or is there some kind of overhead to bear in mind?

Thanks

Darrell...|||Originally posted by Darrell
Hi,

I believe I am looking at a normalised access database, that I want to migrate from.

The data is currently split across 5 tables, using refrential integrity with primary key the unique id for each record.

With the 8060 bytes of data, is that just adding up the data type sizes for the maximum amount data fields or is there some kind of overhead to bear in mind?

Thanks

Darrell...

You can create table like this with warning that it is impossible to add record if total amount data is more than 8060 bytes:

create table mytable( id int
,code1 varchar(8000)
,code2 varchar(8000)
,code3 varchar(8000)
,code4 varchar(8000)
,code5 varchar(8000)
)

See BOL about data types.