SQL Server tips: Executing a query with the EXECUTE command

This short SQL Server blog post is meant to help people who have experienced the error messages 2812 and 203 with the EXECUTE command.
The goal is to execute a simple query from a variable in a string format with the EXECUTE (exec) command.
Very easy, isn’t it?
Ok, let’s GO!

If I execute “select * from sys.databases” in SSMS, in SQLCMD or in PowerShell – no problem, I have the list of all databases from my instance:

The next step is to create a variable with this command and to execute this simple command:

The error message is:

Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure ‘select * from sys.databases’.

It cannot find the stored procedure.

I have indicated the full name of the object (database.schema.object) in the query with the database master and [] around the objects to be sure.

Remarks: I have tested without the database master and it the result was good. The real difference is to use brackets.

Now, the error message is:

Msg 203, Level 16, State 2, Line 3
The name ‘select * from [master].[sys].[databases]’ is not a valid identifier.

The problem comes from the EXECUTE command. If you don’t use “()” for your variable, the variable is considered like a stored procedure and not like a query.

When you use “()”, the query is really interpreted like a query.

Finally, it is good to know how the EXECUTE command works and why we have error messages. I hope this can help developers and perhaps some DBAs…