We recently were forced to migrate SQL Servers because of a failing OS. The failing OS was transpired by failing HDD's becuase of bad sectors. Now that the new server is up and running we are not able to perform a function that allows for us to search archived tickets.
Below is the query that is found within the failing stored procedure:
SELECT (2 * messageID) AS linkID, DateTimeStamp AS DateEntered,
Subject AS Title, '' AS Description,
cast(body as nvarchar(100)) MessageBody
FROM SupportMessagesArchive SM
WHERE TicketNumber = @ticketid
SELECT ((2 * AE.ActionEventID) + 1) AS ActionEventID,
AE.ActionEventDateEntered AS DateEntered,
AE.ActionEventTitle AS Title,
Description = cast(ActionEventDescription as nvarchar(100)),
MessageBody = cast(ActionEventCustomerComments as nvarchar(100))
FROM ActionEventsArchive AE ----WITH (NOLOCK)
LEFT JOIN ServerEventType ON AE.serverEventTypeID = serverEventType.eventTypeID
WHERE AE.ActionID = @ticketid
The results of the query as reported by Query Analyzer is
Server: Msg 3624, Level 20, State 1, Line 1
If I break the above UNION Query into separate queries, I get the same error.
If I take the first query and do a SELECT TOP 1 the same error is generated, however if I remove the WHERE clause, I get desired result set.
If I take the second query and do a SELECT TOP 1 without the WHERE clause I get the same error. But if I include the where clause I get the below error: