Invalid object name error when trying to execute stored procedure?

Question

Not sure what the deal is I have the stored procedure named exactly what I am calling however it always gives me this invalid object error. Here is the connection code, the error is thrown on the second to last line there.

SqlConnection cnstr = new SqlConnection(ConfigurationManager.ConnectionStrings["darconn"].ConnectionString);
                    SqlCommand sqlcmd = new SqlCommand();

                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.Connection = cnstr;
                    sqlcmd.CommandText = "SetMapping";

                    String[] pullKodID = bundlelist.SelectedValue.ToString().Split(':');
                    int kod_id = System.Convert.ToInt32(pullKodID[0]);

                    sqlcmd.Parameters.Add("@kod_id", kod_id);
                    sqlcmd.Parameters.Add("@ell_id", courselist.Items[i].Text);
                    cnstr.Open();
                    sqlcmd.ExecuteNonQuery();
                    cnstr.Close();

Answer 1

Most likely the stored procedure isn't visible to your code because it was created/owned by a user other than dbo or the user specified in your connection string.

Have you tried prefixing the stored procedure name with dbo? For example "dbo.SetMapping".

Failing that, use Management Studio/Enterprise Manager to find out who owns the stored procedure and either recreate it as dbo or update your code/connection string to use the appropriate user.

Answer 2

sql profiler showing anything?

can you execute outside of the context of the app? from mgt studio or qry analyzer?

Answer 3

Are you absolutely sure you typed the name correctly?

Answer 4

Check the spelling of the table name(s) in the stored procedure.

When saving a stored procedure it checks the names of the fiels in the tables used, but it's possible to save a stored procedure that uses a table name that doesn't exist (or is misspelled).

Answer 5

Check the stored procedure to see if it is owned by 'dbo' thus the name would be 'dbo.SetMapping' and not something like 'SomeUser.SetMapping'

I would also explicitly specify the 'dbo.' in the name

sqlcmd.CommandText = "dbo.SetMapping";

Answer 6

This one is a long shot, but the common answers are already listed.

Rarely a script can get a different name in the catalog that in the script. I believe this could cause issues similar to what you are seeing.

The following script will check your db to see if there are items in your catalog that don't match the script. (You need SQL 2005 or higher for this to work)

-------------------------------------------------------------------------
-- Check Syntax of Database Objects
-- Copyrighted (2009).  Free to use as a tool to check your own code or in 
--  any software not sold. All other uses require written permission from Author
-- Author: Stephen Schaff
-------------------------------------------------------------------------
-- Turn on ParseOnly so that we don't actually execute anything.
SET PARSEONLY ON 
GO

-- Create a table to iterate through
declare @ObjectList table (ID_NUM int NOT NULL IDENTITY (1, 1), OBJ_NAME varchar(255), OBJ_TYPE char(2))

-- Get a list of most of the scriptable objects in the DB.
insert into @ObjectList (OBJ_NAME, OBJ_TYPE)
SELECT   name, type
FROM     sysobjects WHERE type in ('P', 'FN', 'IF', 'TF', 'TR', 'V')
order by type, name

-- Var to hold the SQL that we will be syntax checking
declare @SQLToCheckSyntaxFor varchar(max)
-- Var to hold the name of the object we are currently checking
declare @ObjectName varchar(255)
-- Var to hold the type of the object we are currently checking
declare @ObjectType char(2)
-- Var to indicate our current location in iterating through the list of objects
declare @IDNum int
-- Var to indicate the max number of objects we need to iterate through
declare @MaxIDNum int
-- Set the inital value and max value
select  @IDNum = Min(ID_NUM), @MaxIDNum = Max(ID_NUM)
from    @ObjectList

-- Begin iteration
while @IDNum <= @MaxIDNum
begin
  -- Load per iteration values here
  select  @ObjectName = OBJ_NAME, @ObjectType = OBJ_TYPE
  from    @ObjectList
  where   ID_NUM = @IDNum 

  -- Get the text of the db Object (ie create script for the sproc)
  SELECT @SQLToCheckSyntaxFor = OBJECT_DEFINITION(OBJECT_ID(@ObjectName, @ObjectType))

  begin try
    -- Run the create script (remember that PARSEONLY has been turned on)
    EXECUTE(@SQLToCheckSyntaxFor)
  end try
  begin catch
    -- See if the object name is the same in the script and the catalog (kind of a special error)
    if (ERROR_PROCEDURE() <> @ObjectName)
    begin
      print 'Error in ' + @ObjectName
      print '  The Name in the script is ' + ERROR_PROCEDURE()+ '. (They don''t match)'
    end

  end catch

  -- Setup to iterate to the next item in the table
  select  @IDNum = case
            when Min(ID_NUM) is NULL then @IDNum + 1
            else Min(ID_NUM)
          end  
  from    @ObjectList
  where   ID_NUM > @IDNum

end
-- Turn the ParseOnly back off.
SET PARSEONLY OFF 
GO

(As a side note, if you want to see all errors in your db add this after the if (ERROR_PROCEDURE() <> @ObjectName) block.)

else if (ERROR_MESSAGE() <> 'There is already an object named ''' + ERROR_PROCEDURE() + ''' in the database.')
begin
  -- Report the error that we got.
  print 'Error in ' + ERROR_PROCEDURE()
  print '  ERROR TEXT: ' + ERROR_MESSAGE() 
end

Answer 7

Is the database using a case-sensitive collation? If so, is the case in your C# code the same as the case in the database?

Answer 8

What is the name of actual object that it is complaining about ? I have seen the same problem in a stored proc and actually there was no problem with the sproc per se - but due to a typo the name of a table used in the stored proc was wrong (the sproc name had accidentally been pasted in as the table name) so it gave back the Invalid Object name error that matched the sproc name - which was very confusing.

Try changing the sproc name and call it again from your code and see what happens - or try running the sproc directly in SQL Management Studio.

If that doesn't work then be very methodical, divide and conquer - go right back to basics and check everything again end-to-end. Is the connection string correct, what database is it connecting to at runtime, what user id, does the sproc work in Sql management studio on its own etc

Hope that helps.

Answer 9

Hi, I'm having a very similar problem and I've been over and over it and I can't figure out what's going on. I've checked all my parameters, the values are correct. I can plug the values in and execute the stored procedure in SQL Server Mgmt Studio, and it runs just fine.

The exception that's popping up is "Invalid column name 'rowguid'". "rowguid" is a valid column in the table that's being queried (and it is actually there). Here's my C# code:

string curResult = "";
string DBCONN_DF = "Data Source=" + ConfigurationManager.AppSettings["dbServer"] + ";Initial Catalog=" + ConfigurationManager.AppSettings["dbName"] + " ;Integrated Security=SSPI";
SqlDataReader rdr = null;
SqlConnection conn = new SqlConnection(DBCONN_DF);
SqlCommand cmd = new SqlCommand("dbo.mySproc", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CustID", SqlDbType.VarChar, 10).Value = custID;
cmd.Parameters.Add("@FolderID", SqlDbType.Int).Value = curfid;
cmd.Parameters.Add("@CustFileID", SqlDbType.Int).Value = DBNull.Value;
cmd.Parameters.Add("@FileName", SqlDbType.VarChar, 255).Value = curFile;
conn.Open();
rdr = cmd.ExecuteReader();

And here is my stored procedure:

ALTER PROCEDURE [dbo].[mySproc] 
    -- Add the parameters for the stored procedure here
    @CustID varchar(10) = null,
    @FolderID int = null,
    @CustFileID int = null,
    @FileName varchar(255)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    BEGIN TRY

    -- Insert statements for procedure here
    DECLARE @DfTable varchar(13)  
    SET @DfTable = dbo.GetDigitalFolderTableName(@CustID) 

    if @DfTable = 'CustFileTBL00' begin 

        SELECT     
            CustFileID, CustID, FolderID, 
            [FileName], CheckOutUser, FileSize, 
            DateModified, UserModified, DateDeleted, 
            FileOption1, FileOption2, FileOption3, 
            FileData, rowguid
        FROM 
            CustFileTBL00
        WHERE
            ((CustID = @CustID) AND (FolderID = @FolderID) AND ([FileName] = @FileName)) OR
            (CustFileID = @CustFileID)

    end else if @DfTable = 'CustFileTBL01' begin....... and so on

Any ideas would be greatly appreciated.

Side note (in case anyone would think to bring this up) - other apps (not written by me) use this stored procedure so I can't go changing it willy nilly.