Migrating an old website to MS SQL 2005 from MS Access

There is one query I have that's giving me the "Maximum length is 128" error. My research mostly referred to single or double quotes or a stored proceedure in the db but that's not the case. I'm hoping that someone with see the obvious problem with the query while I look for a better SQL referenece for the 2005 server as I'm used to working with MySql.

<cfquery datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#" name="GetProperties">

                SELECT     P.Property_ID, P.Display_Order, P.Property_Title_File, P.Manager_ID, P.Property_Name,

                                P.Address, P.City, P.State, P.ZIP_Code, P.Medium_Pic_File, P.Medium_Pic_Height,

                                P.Medium_Pic_Width, P.Leasing_Office_ID, NRV.NotRented

                FROM         Properties AS P

                                LEFT JOIN [

                                    SELECT   A.Property_ID, count(Currently_Rented) AS NotRented

                  FROM     Apartments A

                  WHERE    Currently_Rented = No

                  GROUP BY A.Property_ID

                  ]. AS NRV ON P.Property_ID = NRV.Property_ID

                ORDER BY P.Display_Order, NRV.NotRented Desc, P.Property_Name, P.ZIP_Code

            </cfquery>

The error starts with the left join select....

Thanks!

David