WorldofCode.com

From time to time your sql database will get stuck in Restoring state.  Here are two different ways to get your database out of restoring state. 

RESTORE DATABASE MyDatabase 
   FROM DISK = ‘MyDatabase.bak’ 
   WITH REPLACE,RECOVERY

1. Stop the service (MSSQLSERVER);
2. Rename or delete the Database and Log files (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data…) or wherever you have the files;
3. Start the service (MSSQLSERVER);
4. Delete the database with problem;
5. Restore the database again.

I don’t use the Union statement to much in my SQL coding but today I needed to use it to combine results from two separate tables.  But I ran into an error when I wanted to add in my usual code for doing a dynamic order by.  SQL wouldn’t run my query and output this error message.

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

 To fix this problem I put my Union statement into a derived table by doing the following.

SELECT * FROM (

SELECT Column1, Column2 FROM Table_A

UNION ALL

SELECT Column1, Column2 FROM Table_B

) DerivedTableName (Column1, Column2)

Next I added in my dynamic Order By code as such.

SELECT * FROM (

SELECT Column1, Column2 FROM Table_A

UNION ALL

SELECT Column1, Column2 FROM Table_B

) DerivedTableName (Column1, Column2)

ORDER BY

CASE @OrderBy WHEN ‘Column1ASC’ THEN Column1 END ASC,

CASE @OrderBy WHEN ‘Column1DESC’ THEN Column1 END DESC