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…