none
Can't access to stored procedures from C# RRS feed

  • Question

  • Hello,

    I had a SQL Server 2008 database that I first developed on a .NET mutualised web hosting.

    One first thing that looked special to me, it's related to the host's configuration : when you create anything in the database it's associated with a schema that as the same name as the login that created stuff instead of "dbo".

    And then, I had a C# application accessing to it.

    I solved some schema problems by putting everything in the same schema and I now use only one login, for C# access as well as for SQL Management Studio.

    Right now I want to make that DB work on my local computer, and later on my laptop.

    With MSSMS, everything looks fine, I can access everything in my DB using the login associated with the schema.

    But with C#

    - I can create a connection (sqlConnection.Open();) --> ok

    - But suddenly I can't access my stored procedures. --> not ok

    Here's my connexion string:

    @"Server=.\SQLEXPRESS;User ID=[login];Password=[password];initial catalog=[DataBase];Trusted_Connection=False"


    Thank you for you help.

    • Edited by ThomasMSN Monday, March 19, 2012 11:39 AM
    • Moved by Bob Beauchemin Monday, March 19, 2012 4:31 PM Moved to the appropriate forum for SqlClient questions (From:.NET Framework inside SQL Server)
    Monday, March 19, 2012 11:34 AM

Answers

  • What error are you getting?  Are you schema-qualifying the stored procedure name (a best practice)?  For example:

    var command = new SqlCommand("MySchema.MyProc", connection);
    command.CommandType = CommandType.StoredProcedure;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by ThomasMSN Monday, March 19, 2012 12:50 PM
    Monday, March 19, 2012 11:52 AM

All replies

  • What error are you getting?  Are you schema-qualifying the stored procedure name (a best practice)?  For example:

    var command = new SqlCommand("MySchema.MyProc", connection);
    command.CommandType = CommandType.StoredProcedure;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by ThomasMSN Monday, March 19, 2012 12:50 PM
    Monday, March 19, 2012 11:52 AM
  • Well thank you, schema-qualifying the stored procedures from C# solved it, I also need to schema-qualify procedure/function calls inside procedures ... I'd prefer a solution that solve the "schema" problem in a more simple way.
    Monday, March 19, 2012 12:06 PM
  • Another method is to change the default schema for the login.  However, IMHO, it's better to treat the schema as part of the object name.  You can run the following in your database to change the user's default schema.

    ALTER USER [user] WITH DEFAULT_SCHEMA = [MySchema];


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Monday, March 19, 2012 12:20 PM
  • I tried this last one, did'nt change anything. I'll go with the first solution for now.
    Monday, March 19, 2012 12:50 PM