none
Cannot create store procedure automatically from dataset because it prefixes the username to the name of the procedure and I use a different schema RRS feed

  • Question

  • I am developing with VS2010. When I generate a store procedure (DATASET>RIGHT CLICK ADD>SELECT>NEW STORE PROCEDURE) for a data table of a dataset from VS2010, the enviroment generates this:

    IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectQuery' AND user_name(uid) = 'DOMAIN\USER-LOGIN')
     DROP PROCEDURE [DOMAIN\USER-LOGIN'].SelectQuery
    GO

    CREATE PROCEDURE [DOMAIN\USER-LOGIN'].SelectQuery
    AS
     SET NOCOUNT ON;
    SELECT contract_id, product_id, contract_description FROM dbo.TContracts
    GO

    The creation of the procedure ends with erros since my schema is dbo.

    So far, I work around copying and pasting the store procedure changing the schema and starting again from the beginning: DATASET>RIGHT CLICK ADD>SELECT>FROM EXISTING STORE PROCEDURE.

    Is there any way to configure VS2100 to generate the scripts avoinding to prefix the supposed schema of my user.

    Many thanks.

    Thursday, September 9, 2010 11:51 AM

All replies

  • Hi,

    What are the SQL Server User Mapping settings for your login for that database (Login Properties in SQL Server Management Studio)?

    Thanks,

    Cathy Miller

     

    Friday, September 10, 2010 6:45 PM
    Moderator
  • Thanks for you reply,

     

    it says that my default schema is: dbo ; shall I check any other property?

     

    Thanks

    Tuesday, September 14, 2010 1:28 PM
  • Hi,

    When I run a SQL Profiler trace at the time it is generating the script to create the stored procedure, I see the following query being executed:

    use [<database name>]
    SELECT
    user_name() AS [UserName],
    (select default_schema_name from sys.database_principals where name = user_name()) AS [DefaultSchema]

    If you go into SQL Server Management Studio and connect to the database in question as the same user, running the above query, what does it return?

    Thanks,

    Cathy Miller

    Wednesday, September 22, 2010 8:49 PM
    Moderator
  • I apologize for the delay Cathy,

     

    UserName DefaultSchema

    Domain\user dbo

    Tuesday, February 15, 2011 12:47 PM