Results of Upsizing the IceCream Database

This database upsizes fairly well. All the database tables and table fields upsize with no problems. However, there are one or two areas where you will have to manually recreate some objects. The following queries must be recreated manually. Note that the upsizing report will also show you the T-SQL that was attempted, which serves as a great start when repairing the damage.

The upsizing report provides you with much needed information about the entire process. All of the options you have chosen are displayed in the report. In addition, the following information is provided:

• TablesA listing of all tables, including the Access data type and the SQL Server data type they are converted to.

• IndexesAccess Indexes upsized to SQL Server Indexes.

• QueriesFor each query successfully upsized, you are shown the SQL Server object the query is converted to, including the SQL statement. For each query that fails to upsize, the report shows the SQL Server-generated SQL that was used in the attempt.

The upsizing report is a valuable tool which can assist you in tracking down problems with objects that fail to upsize.

The following table lists the Query Objects in the original Microsoft Access database and the resulting SQL Server objects. We also show those objects that were not upsized.

Access Query Name	SQL Server Object	Comment
qryAllSalesFigures	FUNCTION qryAllSalesFigures()	User-Defined Function
qryChapter4Sales	FUNCTION qryChapter4Sales()	User-Defined Function
qryCompanyLookup	FUNCTION qryCompanyLookup()	User-Defined Function
qryExample	FUNCTION qryExample()	User-Defined Function
qryIngredientLookup	FUNCTION qryIngredientLookup()	User-Defined Function
qryReOrder	qryReOrder	SQL Server View
qryReOrderSuppliers	Not Upsized	Uses DISTINCTROW Not Supported
qryResults	qryResults	SQL Server View
QrySalesSummary	Failed to Upsize	Access-Specific Function calls particularly the Format function
qryuCompanyLookup	qryuCompanyLookup	Stored Procedure
qryxSS	Failed to Upsize	Crosstab Query

As you can see we have a wide mixture of SQL Server objects to deal with, many of which may be new to you. The next section looks at each of the above objects and provides you with the opportunity to actually create them using the Access interface.