Please Look at below statement where There is anInvalid use of bracket and Select statement.

Your code is little bit confusing.

AppServerSource = (Select IsNull(t1.ServerName,t2.ServerName) as ServerName

I tried to fix as below. Please try this one :

select distinct

t1.ServerName,

t1.[Application ID],

t1.[Application Name],

t2.VP1,

T2.VP2,

T2.SVP,

T2.[DC2015 Move Group],

T1.[System Type],

T3.Line,

IsNull(t1.ServerName,t2.ServerName) as ServerName,

IsNull(t1.[Application Name],t2.[Application Name]) as [Application Name],

Case

When t1.[Application Name] is null then 'Check'

When t2.[Application Name] is null then 'Payorder'

Else 'Both'

End AppServer

from Table1 t1 INNER JOIN Table2 t2

on t1.[Application ID]= t2.[Application ID] and t1.Servername = t2.ServerName JOIN BIM T3

ON t1.[Application Name]= t3.[Application Name]

order by Servername