CREATE TABLE should be a separate statement. It looks like the SQL engine is parsing INSERT as part of the CREATE statement because you've not put a semi-colon after it to terminate it. Try putting a semi-colon after the closing bracket of the CREATE statement to see if that helps. Here's an example:

http://msdn.microsoft.com/en-us/library/ms189872(SQL.90).aspx

However, you don't really need the CREATE TABLE statement at all:

http://msdn.microsoft.com/en-us/library/ms190750(v=SQL.90).aspx

As far as I am aware the semi-colon is only necessary in MS Access, other forms of SQL seem to manage without.

A couple of possible suggestions 1. In the insert into, there appears to be a space missing, between the comma and Parent_key (might make a difference) and 2. the select is selecting  the same field (location_key) twice (shouldn't matter, but worth pointing out anyway).

Charles is right though, if the above is all you need to do, then you don't need the temporary table, just the select statment.

Hi Keith

What is actually wrong is that your query does not have any select statement in it to output some data. An XML report should always have the last SELECT statement output a table of data for the report. Recorder looks in the query to find this statement, and changes it to a SELECT * INTO ... statement to put your report's data into a temporary table. For example

SELECT *
FROM Taxon

would be converted by Recorder to

SELECT *
INTO #REPORT_OUTPUT
FROM Taxon

Having your query output in a temp table like this makes it a lot easier for Recorder to use the data. But in your case, the last SELECT statement is actually part of an INSERT INTO statement. So your statement becomes:

    CREATE TABLE #Locations (Location_Key CHAR(16) COLLATE Database_Default PRIMARY KEY, Parent_Key CHAR(16) COLLATE Database_Default)
    INSERT INTO #Locations (Location_Key,Parent_Key)
    SELECT 
      LOCATION.LOCATION_KEY, LOCATION.LOCATION_KEY
    INTO #REPORT_OUTPUT FROM
      LOCATION
    WHERE  
      LOCATION.LOCATION_KEY = 'SR000149000000Y2'
      
    DROP TABLE #Locations

This is no longer valid SQL. So the quick fix might be to do something like SELECT TOP 1 * FROM #Locations at the end.

A top tip, if you are using SQL Express and therefore don't have access to the SQL Profiler tool (which can log the actual SQL sent to the server and is brilliant for troubleshooting things like this), then try the AnjLab SQL Profiler, which is Open Source. You can find it at http://anjlab.com/en/projects/opensource/sqlprofiler.