locked
Could not find Stored Procedure RRS feed

  • Question

  • Hi,

    I am trying below code but keeps getting Could not find Stored Procedure.

    The stored procedure does exist and I am able to run it on the SQL server itself

    Kindly help..

    private DataSet get_data()
    {
        data_table = new System.Data.DataTable();
    
        sql_connection = new SqlConnection(serverClass.connectionstring("Cosmo", "Cosmo", "Server"));
        sql_connection.Open();
    
        sql_command = new SqlCommand("dbo.sp_export_smashburger_seef_daily_sales(@business_date)", sql_connection);
        sql_command.CommandType = CommandType.StoredProcedure;
        sql_command.Parameters.Add("@business_date", SqlDbType.Date).Value = "2016-09-01";
        sql_adapter = new SqlDataAdapter(sql_command);
    
        data_set = new DataSet();
        sql_adapter.Fill(data_set, "MeskHoldingsExport");
    
        return data_set;
    }

    this is what I get when I run on the SQL Server and I am getting the result but not in C#.

    USE [EDSS]
    GO
    
    DECLARE	@return_value int
    
    EXEC	@return_value = [dbo].[sp_export_smashburger_seef_daily_sales]
    		@business_date = "2016-09-01"
    
    SELECT	'Return Value' = @return_value
    
    GO
    

    Thanks,

    Jassim


    • Edited by Jassim Rahma Thursday, September 15, 2016 1:44 PM
    Thursday, September 15, 2016 1:39 PM

Answers

  • When specifying

    sql_command.CommandType = CommandType.StoredProcedure;
    

    you are supposed to pass in only the procedure name in the command text.

    Either use CommandType.Text or change the command text to just the name of the procedure.

    Thursday, September 15, 2016 2:46 PM

All replies

  • Hello,

    Please rename the stored procedure. Do not use the sp_ prefix as mentioned on below article:

    http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix

    Stored procedures with that prefix are supposed to be located on master database.

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    Thursday, September 15, 2016 2:10 PM
  • When specifying

    sql_command.CommandType = CommandType.StoredProcedure;
    

    you are supposed to pass in only the procedure name in the command text.

    Either use CommandType.Text or change the command text to just the name of the procedure.

    Thursday, September 15, 2016 2:46 PM