We have a purchased product. This product has a SQL Server 2005 Compatibility
'Update' but that update is lacking some fixes to make it compatible with SQL
Server 2005. User testing has generated at least one error related to the use
of outer join operators *= and =*.  The turn-around time and effort required
to work with the vendor is less than desirable so we have been trying to get
as many details to them to speed up the fix as possible.

I restored a backup of the databases from post-2005 compatibility patch but
pre-migration to the 2005 instance and used Upgrade Advisor to identify
objects in the databases of concern. Of course this product has T-SQL in the
application, too. The users are testing using the databases on the 2005
instance and I have a trace running on that activity but, Upgrade Advisor
doesn't seem to be able to use a 2005 trace file.

We don't want to have the users test against the databases on the 2000
instance - just so that we can capture the SQL Statements. Any ideas on how
we can evaluate the trace results from the 2005 instance for 2005
incompatibilities?

How to convert a 2005 trace file to a 2000 trace file for Upgrade Advisor to
analyze:

1. Use the TSQL_Replay Template in SQL Server 2005 for profiling activity on
the 2005 Server.
2. Exract the Events to a .sql file (File, Export, Extract SQL Server
Events, Extract Transact-SQL Events)
3. Open the sql file using SQL Server 2000 Profiler.
4. Save the results as a trace file.
5. Run the Upgrade Advisor on the trace file.

I'm not sure if you have to have the databases of interest on the 2000 SQL
Server or if Upgrade Advisor will analyze the trace file even if the
databases that you're interested in from the trace file are not on the 2000
server. You DO have to select at least one database in Upgrade Advisor - you
can't just analyze a trace file by itself. So, let's say that your trace file
includes activity in a database called BADDATABASE but you don't have a copy
of that database on the 2000 server. Could you just pick a different database
in Upgrade Advisor but still analyze the activity in the trace file for
BADDATABASE? I don't need an answer to this but somebody else might.

The Upgrade Advisor DID find the "outer join operators *= and =*" issue
captured in the trace file. In this case, it turns out that the statement was
in one of the stored procedures that I previously identified. However, this
might be handy for people that already migrated to 2005 and thought that they
took care of everything, don't have a way to restore the database with the
2005 fixes to a 2000 server, or are counting on a vendor to have made the
required changes but have to support the users that are using the application
in its incompatible state.

I know that there are some 2000 system stored procedures that the app is
executing when you use it to set up security. We have other issues with how
the vendor has implemented that. So, I'll be reproducing this and using the
above steps to see what the Upgrade Advisor has to say.

Show quoteHide quote

Michelle (Miche***@discussions.microsoft.com) writes:
You could address the *= and =* issue with setting the compatibility
level to 80.

But in your other post, you mentioned system procedures, and for the
system tables there are some changes where compatibility level does
not help. To wit, by default users only see metadata if they have
permission for it.

If the vendor claims to have an SQL 2005 "compatibility update" but
has not fixed the outer join syntax, that sounds scary. Does the
vendor pay you back for testing the software they sold to you?

There are a number of SQL Server issues with this software. Poor-use of
security is high on the list. I'm not talking about the lazy use of 'sa' that
a lot of software companies have (just use the sa login then you won't have
any problems with our software!). This product uses the public role for
assigning database permissions and although the application might restrict
users to certain 'forms' or context areas - all of the users have insert,
update, and delete to all of the database tables. Of course they have
Crytstal Reports and probably MS Access on their desktops.

Their failure to address basic 2005 compatibility issues seems ridiculous.
We have a lot of internally-developed databases and have just started our own
2005 Migration Projects. I'm sure that we'll miss things as well. BUT, when
this join issue is so obviously documented and easily identified by free
tools provided by Microsoft - it makes me question our investment in this
product. I will be documenting these issues for management.

I believe that we've found 'holes' in the management tools if you have a
database on SQL Server 2005 with the 8.0 compatibility setting. Perhaps it
was just the lack of a graphical view of the database files? I don't
remember. But, we'd rather not have to touch this product again for a while
and get it up to where it should be.

Thanks for hearing my story :)


Show quoteHide quote

Management Studio keeps blowing up
Database Script Does Not Account for Dependencies
Utility to read transaction logs
CPU-bound or I/O bound?
Re: SQL Profiler 2005