Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

ASP.net 2.0 C#: Executing Stored procedure gives error "Cannot find <sp_name>"

  • 20-02-2009 11:28am
    #1
    John_Mc
    Registered Users, Registered Users 2 Posts: 2,791 ✭✭✭


    Hi,

    I've developing on my local machine and have added a stored procedure to my SQL SERVER 2005 Express database. When I try to execute it, .net throws an exception saying that it cannot find the SP.

    Anyone know what I have to do to get around this?

    Thanks


Comments

  • #2
    fasty
    Registered Users, Registered Users 2 Posts: 981 ✭✭✭fasty


    Where are you executing the SP from? Do you have permissions to execute it? What is the SP? Post code etc... It could be a load of different things.


  • #3
    John_Mc
    Registered Users, Registered Users 2 Posts: 2,791 ✭✭✭John_Mc


    fasty wrote: »
    Where are you executing the SP from? Do you have permissions to execute it? What is the SP? Post code etc... It could be a load of different things.

    I'm afraid the code is at home on my computer so I wont be able to get it until tonight. It's just a simple piece of SQL to insert a new record into a table with 4 input parameters.

    The SP is executed in a DB access class. I setup the required variables like SqlConnection and SqlCommand. I set the SQLCommand.CommandType=CommandType.StoredProcedure and it's Command text to "spInsertNewAuditHistory" which is the name of my SP.

    My knowledge of setting up users in SQL server is limited. But I've created a new user called netUser which uses SQL Authentication. I've set the credentials in my connection string and set the user to a couple of different types that I thought would make a difference like adminAccess, dbWriter etc. None of these have worked though :(


  • #4
    Ginger
    Registered Users, Registered Users 2 Posts: 2,931 ✭✭✭Ginger


    On SQL 2005, you need explicit permissions. If you are running as DBO against the database then you are fine.. but you may not be

    Bring up the SQL Server Management Studio (SMSS) and log into your db server. Click on Security and double click on your user that you created. Check and see if they have access to the database you need and also check what permissions they have... you can quickly add them as dbo to check and then try and run it again as your user.

    Once you see its working you can reduce them to datareader and/writer and then assign execute permissions on the stored proc for them


  • #5
    John_Mc
    Registered Users, Registered Users 2 Posts: 2,791 ✭✭✭John_Mc


    Ginger wrote: »
    On SQL 2005, you need explicit permissions. If you are running as DBO against the database then you are fine.. but you may not be

    Bring up the SQL Server Management Studio (SMSS) and log into your db server. Click on Security and double click on your user that you created. Check and see if they have access to the database you need and also check what permissions they have... you can quickly add them as dbo to check and then try and run it again as your user.

    Once you see its working you can reduce them to datareader and/writer and then assign execute permissions on the stored proc for them

    Nice one for that Ginger, will try it later on when I get home and report back.


Advertisement