Local SQL Server: The specified procedure could not be found

Question

This problem has appeared on my PC a few days ago, without any changes I have made to the Visual Studio/SQL Server settings.

When trying to perform any manual operation on the database file (*.mdf) in Visual Studio, I get the following error:

The specified procedure could not be found. (Exception from HRESULT: 0x8007007F)

(For example, when trying to create a new table, or when showing an existing table's data)

How I can fix this error?

Answer 1

Be sure you or someone else has not changed your rights on master. In order to add anything a record is added to that table.

Addendum: You can run the following script to determine who has what permissions and whether they are implicit or explicit.

WITH perms_cte as
(
    select USER_NAME(p.grantee_principal_id) AS principal_name,
            dp.principal_id,
            dp.type_desc AS principal_type_desc,
            p.class_desc,
            OBJECT_NAME(p.major_id) AS object_name,
            p.permission_name,
            p.state_desc AS permission_state_desc
    from    sys.database_permissions p
    inner   JOIN sys.database_principals dp
    on     p.grantee_principal_id = dp.principal_id
)
--users
SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM    perms_cte p
WHERE   principal_type_desc <> 'DATABASE_ROLE'
UNION
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM    perms_cte p
right outer JOIN (
  select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
from    sys.database_role_members rm
INNER   JOIN sys.database_principals dp
ON     rm.member_principal_id = dp.principal_id
) rm
ON     rm.role_principal_id = p.principal_id
order by 1
--- thanks to Jamie Thomson for this ditty 

A more few suggestions;

Trace the call using profiler and confirm you are connecting as the user you think you are - sp_who2 might be another way to verify that.

Verify your process is in the correct database. Use the c# connection and run SELECT DB_NAME() - write the result somewhere you can read to confirm.

If this works from one environment but not another I would strongly suspect the connection string.

Check that you do not have authentication errors reported in your SQL logs. These again would indicate possible connection issues.

Answer 2

The conventional wisdom on this error is that it likely reflects a lost or corrupted DLL (in either SQL Server or Visual Studio). And the SOP is to reinstall, though it seems that that does not always work either.