A part of my sp contains following code . The code executes successfully in my local sql database . but when i run it in sql azure it gives the error saying:

"Global temp objects are not supported in this version of SQL Server."

How can i change this query开发者_运维知识库 to make it work in azure.

    SET @query = N'SELECT STUDENT_ID, ROLL_NO, TITLE, STUDENT_NAME, EXAM_NAME, '+
                @cols +
                ' INTO ##FINAL 
                FROM
                (
                    SELECT ROLL_NO, TITLE, STUDENT_ID, SUBJECT_ID, STUDENT_NAME, EXAM_NAME, DISPLAYORDER, MARKS
                    FROM #AVERAGES
                    UNION 
                    SELECT ROLL_NO, TITLE, STUDENT_ID, SUBJECT_ID, STUDENT_NAME, EXAM_NAME, DISPLAYORDER, MARKS
                    FROM #MARKS 
                    UNION
                    SELECT ROLL_NO, TITLE, STUDENT_ID, SUBJECT_ID, STUDENT_NAME, EXAM_NAME, DISPLAYORDER, MARKS
                    FROM #GRACEMARKS
                    UNION
                    SELECT ROLL_NO, TITLE, STUDENT_ID, SUBJECT_ID, STUDENT_NAME, EXAM_NAME, DISPLAYORDER, MARKS
                    FROM #TOTAL
                    ) p
                    PIVOT
                    (
                    MAX([MARKS])
                    FOR SUBJECT_ID IN
                    ( '+
                    @cols +' )
                ) AS FINAL
                ORDER BY STUDENT_ID ASC, DISPLAYORDER ASC, EXAM_NAME ASC;'

    EXECUTE(@query)

Better late than never. Database Scoped Global Temporary Tables in public preview for Azure SQL DB:

Similar to global temporary tables for SQL Server, tables prefixed with ##table_name, global temporary tables for Azure SQL DB are stored in tempdb and follow the same semantics. However, rather than being shared across all databases on the server, they are scoped to a specific database and are shared among all users’ sessions within that same database. User sessions from other Azure SQL databases cannot access global temporary tables created as part of running sessions connected to a given database. Any user can create global temporary objects.

CREATE TABLE ##test ( a int, b int);
INSERT INTO ##test values (1,1);

And from MSDN CREATE TABLE:

Database scoped global temporary tables (Azure SQL Database)

Global temporary tables for SQL Server (initiated with ## table name) are stored in tempdb and shared among all users’ sessions across the whole SQL Server instance. For information on SQL table types, see the above section on Create Tables.

Azure SQL Database supports global temporary tables that are also stored in tempdb and scoped to the database level. This means that global temporary tables are shared for all users’ sessions within the same Azure SQL database. User sessions from other Azure SQL databases cannot access global temporary tables.

Global temporary tables for Azure SQL DB follow the same syntax and semantics that SQL Server uses for temporary tables. Similarly, global temporary stored procedures are also scoped to the database level in Azure SQL DB. Local temporary tables (initiated with # table name) are also supported for Azure SQL Database and follow the same syntax and semantics that SQL Server uses.