none
I can't create store procedure using a different dbo schema RRS feed

  • Question

  • Hi,
    I'm trying to create my stored procedure in my custom schema, but i don't know ho to do that, VS2005 always create it in "dbo" schema.

    Code Snippet

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;

    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void SP_GETUSERINFO(string userId)
        {
            SqlConnection connection = new SqlConnection("context connection=true");
            try
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand("SELECT USERID, LNAME, FNAME FROM USER WHERE USERID = '" + userId + "'", connection);
                SqlDataReader dr = cmd.ExecuteReader();
                SqlContext.Pipe.Send(dr);
                dr.Close();
                connection.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }
    };


    Thanks,

    Carl
    Tuesday, July 10, 2007 7:31 PM

All replies

  • EDIT: I think I misunderstood your question, as I see you are using the attribute to automagicaly register the SP....

    Are you doing the CREATE PROCEDURE thing on the SQL server side? There you can assign an schema to your procedure:

    CREATE PROCEDURE MYSCHEMA.GETUSERINFO
    (
    )
    AS EXTERNAL NAME StoredProcedures.GETUSERINFO;
    Tuesday, July 10, 2007 8:53 PM
  • Hi Eperales,

    I trying to create the sp with SQL Server project, when i build this project, it create the sp in my database.
    The main problem is i don't know how to assign the schema to this sp. That source code run in VS2005 with SQL Sever 2005 Enterprise edition.

    Thanks,

    CDJN
    Wednesday, July 11, 2007 9:54 AM