As we have said, some of the queries in the Access database no longer work, mainly due to the use of the
Format function, which is not supported by SQL Server. In this section, we will look at the T-SQL required to repair the queries. This is where the upsizing report comes in handy. Instead of having to check each and every form/report, we simply check the upsizing report for failed queries.
This query failed to upsize because T-SQL does not support the Access
TRANSFORM ,
PIVOT, or
FORMAT syntax. SQL Server does not support Access Crosstab queries. The highlighted keywords cause the problem here as they are not supported by SQL Server.
TRANSFORM Sum(tblSales.Quantity) AS SumOfQuantity
SELECT tblCompany.CompanyName
FROM tblCompany INNER JOIN tblSales ON tblCompany.CompanyID = tblSales.fkCompanyID
GROUP BY tblCompany.CompanyName
PIVOT Format([DateOrdered],"mmmm") In ("January","February","March",
"April","May","June","July","August",
"September","October","November","December");
Before we look at the solutions to the above problems, let''s look quickly at some other problems you may face when upsizing. In general, the following will always fail to upsize:
Queries containing
DISTINCTROW
Crosstab queries
Pass-through queries
DDL queries
Queries that reference forms controls will also cause problems. It is common practice to pass parameters to queries from forms, for example, many developers use forms to collect dates for reports. When such a query is upsized, your Access SQL is replaced by T-SQL.
For example, consider the following query, which collects a company''s name using a form and filters the results:
SELECT tblCompany.CompanyName, tblCompany.Address, tblCompany.City FROM tblCompany WHERE (((tblCompany.CompanyName)=[forms]![frmcompany]![txtname]));
When upsized the form reference parameter will be changed to:
@forms_frmcompany
As you can see, this will not work, as you have lost the reference to the form value.
We will use the same technique to fix both
qrySalesSummary and
qryxSS . This involves the use of the T-SQL
CASE structure.
This is aSummary .
Because this stored procedure is fairly complex, we are unable to use the graphical tools to create it. The graphical tools allow you to create basic SQL procedures via the interface. In this case, we will be using conditional logic including the
CASE statement. As the graphical tools are just not smart enough to do this for us, we will have to enter the SQL manually into the procedure window. Using SQL, this is as close as you will get to re-creating a "crosstab query" in SQL Server. Third-party products are available that use complex stored procedures to produce output very similar to that of Access.
To create the procedure, click
Queries |
New.
Select
Create Text Stored Procedure.
Enter the following SQL into the Stored Procedure:
Create Procedure usp_qryss
AS
SELECT tblCompany.CompanyName,
CASE Month(DateOrdered) WHEN 1 THEN SUM(tblSales.Quantity)
ELSE 0 END AS ''January'',
CASE Month(DateOrdered) WHEN 2 THEN SUM(tblSales.Quantity)
ELSE 0 END AS ''February'',
CASE Month(DateOrdered) WHEN 3 THEN SUM(tblSales.Quantity)
ELSE 0 END AS ''March'',
CASE Month(DateOrdered) WHEN 4 THEN SUM(tblSales.Quantity)
ELSE 0 END AS ''April'',
CASE Month(DateOrdered) WHEN 5 THEN SUM(tblSales.Quantity)
ELSE 0 END AS ''May'',
CASE Month(DateOrdered) WHEN 6 THEN SUM(tblSales.Quantity)
ELSE 0 END AS ''June'',
CASE Month(DateOrdered) WHEN 7 THEN SUM(tblSales.Quantity)
ELSE 0 END AS ''July'',
CASE Month(DateOrdered) WHEN 8 THEN SUM(tblSales.Quantity)
ELSE 0 END AS ''August'',
CASE Month(DateOrdered) WHEN 9 THEN SUM(tblSales.Quantity)
ELSE 0 END AS ''September'',
CASE Month(DateOrdered) WHEN 10 THEN SUM(tblSales.Quantity)
ELSE 0 END AS ''October'',
CASE Month(DateOrdered) WHEN 11 THEN SUM(tblSales.Quantity)
ELSE 0 END AS ''November'',
CASE Month(DateOrdered) WHEN 12 THEN SUM(tblSales.Quantity)
ELSE 0 END AS ''December''
FROM tblCompany INNER JOIN
tblSales ON tblCompany.CompanyID =tblSales.fkCompanyID
GROUP BY tblCompany.CompanyName,month(DateOrdered)
How It Works
There is a lot of SQL here, and it demonstrates one or two areas of T-SQL. Note the use of the
CASE structure within the SQL statement. The
CASE statement checks the value of the
DateOrdered field. If the field contains a quantity the value is summed using the
SUM(Quantity) statement, otherwise it is set to
0 or
NULL . Finally, we use an alias for the result based on the month concerned. The output from the procedure can be seen below:
As we can see, it''s not as compact as the Access Crosstab, but not too shabby at all. The failure of SQL Server to support native Crosstabs has been a major complaint from Access developers. For some reason the SQL Server development team have so far refused to add
PIVOT and
TRANSFORM to T-SQL.
FORMAT function and thus fails to upsize to SQL Server.
SELECT tblCompany.CompanyName, Format([DateOrdered],"mmmm") AS MonthName, Sum(tblSales.Quantity) AS SumOfQuantity, DatePart("m",[DateOrdered]) AS MonthNumber
FROM tblCompany INNER JOIN tblSales ON tblCompany.CompanyID = tblSales.fkCompanyID
GROUP BY tblCompany.CompanyName, Format([DateOrdered],"mmmm"), DatePart("m",[DateOrdered])
ORDER BY tblCompany.CompanyName, DatePart("m",[DateOrdered]);
In order to reproduce this query as a SQL Server stored procedure, we must replace the Access-specific functions and add one or two SQL Server functions. We are going to use the
Datepart ,
DateName, and
Month functions to recreate this query. Once we have recreated the query as a stored procedure, we will repair the
Sales
Summary Report. In order to repair the query, we need to look as some SQL Server functions.
DATENAME
MonthName:
Format([DateOrdered],"mmmm") is the Access function used to return the month. We change this when using SQL Server and instead use the
DATENAME(MONTH, dbo.tblSales.DateOrdered) function. The
DATENAME function returns the part of the date specified as the first parameter to the function call. The following options are available using
DateName :
|
Function
|
Result
|
|---|---|
|
DATENAME(MONTH, DateOrdered)
|
Returns the Month
|
|
DATENAME(Quarter, DateOrdered)
|
Returns the quarter
|
|
DATENAME(week, DateOrdered)
|
Returns the week number
|
|
DATENAME(day, DateOrdered)
|
Returns the day
|
|
DATENAME(year, DateOrdered)
|
Returns the year
|
MONTH
We then use the
MONTH function to return the month number of the
DateOrdered field. The
MONTH function simply returns an integer representing the month passed to the function. For example, January will be 1, February 2, and so on. This is equivalent to
DatePart(mm,dateordered) , but we are using it to illustrate the functions available to you in SQL Server. The rest of the procedure is standard, as in Microsoft Access SQL, and should pose no problem to you.
From the database window, click
Queries |
New.
Click
Design Stored Procedure .
Select
tblCompany and
tblSales from the
Add Table dialog.
As we shall be using SQL Server functions, we will enter the field information manually.
Click in the column pane (bottom half of the Query window).
Use the drop-down list to select the
CompanyName field.
In the next row, enter the following:
DATENAME(MONTH, dbo.tblSales.DateOrdered) .
T he alias is entered as
MonthName .
In row three, select
Quantity using the dropdown.
In the row for
Quantity , select
SUM in the
Group By column.
In the next free row, enter
MONTH(dbo.tblSales.DateOrdered) with an alias of
MonthName.
In the final row enter
DATEPART(m, dbo.tblSales.DateOrdered) .
At this point your stored procedure should look like that shown next:
Close and save the procedure as
usp_qrySalesSummary .
Execute the query. The results are shown below:
How It Works
Most of what you have just completed is no different from creating a standard Access query. The main difference here is the use of SQL Server functions to replace non-supported Access functions.
The SQL for the
usp_qrySalesSummary stored procedure is also shown below for information:
PROCEDURE dbo.usp_qrySalesSummary
AS
SELECT
dbo.tblCompany.CompanyName, DATENAME(MONTH,
dbo.tblSales.DateOrdered) AS MonthName,
SUM(dbo.tblSales.Quantity) AS Quantity,
MONTH(dbo.tblSales.DateOrdered) AS MonthNumber
FROM
dbo.tblCompany INNER JOIN
dbo.tblSales ON dbo.tblCompany.CompanyID = dbo.tblSales.fkCompanyID
GROUP BY dbo.tblCompany.CompanyName,
DATENAME(MONTH, dbo.tblSales.DateOrdered),
MONTH(dbo.tblSales.DateOrdered)
ORDER BY dbo.tblCompany.CompanyName,
DATEPART(m, dbo.tblSales.DateOrdered)
The next step in the process, once we have written the stored procedure and tested its output using our original database for comparison, is to repair the
Sales Summary Report by changing the record source of the report to the stored procedure created. Once we do this, there will be one or two other items that require tweaking.
QrySalesSummary was not upsized, this report will have lost its record source. We are now going to replace it with the Stored Procedure just created:
Open the
Sales Summary report in
Design View
.
Open the reports
Property Sheet.
Click on the
Data tab.
Click in the
Record Source property.
Select
usp _
qrySalesSummary from the drop-down list. This is the Stored Procedure created in the earlier example.
Switch to standard report view. Now we get some errors. The first error message we receive informs us that a field does not exist. This is because the report is looking for a field named
SumOfQuantity . This happens because when the original query was written no alias was used for the field. We have to create an alias called
Quantity using the line
SUM(dbo.tblSales.Quantity)
AS
Quantity:
Open the textbox
SumOfQuantity.
Open the
Controls properties sheet.
Click the
Data tab.
Click in the
Control Source and using the drop-down list select
Quantity.
Close and Save the Report.
That''s it, you''re done! View the report and you should find that it is identical to its Access counterpart.
So, we''re dealt with the queries. Is there anything else causing problems? Let''s look at each object in turn.
