Saturday, February 5, 2022

SQL Error 101 - Query not allowed in Waitfor

If you’re using the WAITFOR statement in SQL Server, and you get the following error, it’s probably because you’re providing a query as its argument.

Msg 101, Level 15, State 1, Line 2
Query not allowed in Waitfor.

The WAITFOR statement doesn’t accept queries for its "wait for" period. It only accepts a specific time or an interval.
Actually, it does accept RECEIVE statements, but this is only applicable to Service Broker messages, so if you’re not using Service Broker messages, the above error is quite self-explanatory.

Example:

When you incorrectly trying to use a query in WAITFOR statement

waitfor(SELECT firstname from [dbo].[Employee])

Result:

Msg 101, Level 15, State 1, Line 2
Query not allowed in Waitfor.

To overcome this issue, you’ll need to provide an actual time or a time delay.
For example, this will work.
WAITFOR DELAY '00:00:10';SELECT firstname from [dbo].[Employee]

No comments:

Post a Comment