Fixing the Queries

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.

qryxSS

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.

Fixing qrySalesSummary

We will use the same technique to fix both qrySalesSummary and qryxSS . This involves the use of the T-SQL CASE structure.

qryxSS

This is a standard Crosstab query, which is unsupported by SQL Server. We will use the T-SQL CASE statement to replace this query, but return the same answer. This query is used as the basis for qrySalesSummary , which in turn is used to provide the recordset for the report Sales Summary .

Try It OutReplacing qryxSS with a Stored Procedure

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.

1. To create the procedure, click Queries New.

2. Select Create Text Stored Procedure.

3. 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 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.

qrySalesSummary

This query uses the Access 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

In the original query, 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.

Try It OutRecreating QrySalesSummary as a Stored Procedure

1. From the database window, click Queries New.

2. Click Design Stored Procedure .

3. Select tblCompany and tblSales from the Add Table dialog.

   As we shall be using SQL Server functions, we will enter the field information manually.

4. Click in the column pane (bottom half of the Query window).

5. Use the drop-down list to select the CompanyName field.

6. In the next row, enter the following: DATENAME(MONTH, dbo.tblSales.DateOrdered) . T he alias is entered as MonthName .

7. In row three, select Quantity using the dropdown.

8. In the row for Quantity , select SUM in the Group By column.

9. In the next free row, enter MONTH(dbo.tblSales.DateOrdered) with an alias of MonthName.

10. In the final row enter DATEPART(m, dbo.tblSales.DateOrdered) .

11. At this point your stored procedure should look like that shown next:

    

12. 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.

Try It OutChanging Report Sales Summary Record Source

Because the query 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:

1. Open the Sales Summary report in Design View .

2. Open the reports Property Sheet.

3. Click on the Data tab.

4. Click in the Record Source property.

5. 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:

6. Open the textbox SumOfQuantity.

7. Open the Controls properties sheet.

8. Click the Data tab.

9. Click in the Control Source and using the drop-down list select Quantity.

10. 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.