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

In SQL Server, you may face an error and get the below error message:

Msg 104, Level 16, State 1, Line 4
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Below is the solution of your query, make sure you have followed the pattern as below

Select * from
(               
            SELECT   field1, field2 FROM Table1 WHERE  field1 = 'abc'
            UNION
            SELECT   field1, field2 FROM Table2 WHERE  field2 = 'xyz'
) VarTable
                    order by case when field1 is not null then field1 else field2 end desc

Let us know your feedback.

Thank you Hiral Shah for this post!

By AlpesH Shah

Surface Area Configuration tool in SQL Server 2008

I installed SQL server 2008 and tried to find "Surface Area Configuration Tool".
I couldn't find that easily and Googled for that.

I got a good link that says where is the tool in sql server 2008.

Click here to visit the original site.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I have recently installed Microsoft SQL Server 2008 in my PC having Windows 7 Ultimate x86 as Operating system.

When I tried to connect my SQL server from remote computer, it gave me the below error message:

 A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

After spending a whole day for proper solution, I couldn't find any.

Finally as an alternative I got a silly solution. Just Disable OR Turned Off Windows Firewall and in my surprise, I was able to connect.

I am not satisfied with this alternative and still searching for the formal solution. Once I have it, I will post it here for all.


If you find the above link useful, let us know your feedback, it will help us to improve our posting(s). or You can send your feedback linkOblast.
Report Broken Link

Change Logical Filenames in SQL Server

I got a backup of a database from my co-worker, that i restored as "ABC" database.

It restored okay but when I check the logical named for both Data and Log file were "XYZ" and "XYZ_Log" respectively.

I just Googled for a solution and got a command to rename the logical files.

The command lines as below

    USE MASTER
    GO

    ALTER DATABASE DBName
    MODIFY FILE (NAME = ExistingLogicalDataFileName, NEWNAME='NewDataFileName')
    GO

    ALTER DATABASE DBName
    MODIFY FILE (NAME = ExistingLogicalLogFileName, NEWNAME=NewLogFileName)
    GO

If you want to visit the original article by Jon Galloway, click here