Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently working on a project for a client. The application is written in ASP.NET C#. The Database used is SQL Server 2008. The project has been to migrate the old Oracle database to a new SQL Server database so it connects to the .NET code and works correctly. We used Microsoft Microsoft SQL Server Migration Assistant (SSMA) to convert the Oracle database across to SQL server. This worked for the most part, however we did have to make some changed to the stored procedures and some functions.

The problem is that now when i try and run the .NET code it will not pick up any of the stored procedures or functions as they now have a schema prefix and the .NET code does not pick this up.

The .NET code connects to the SQL Server database using Windowws Authentication and therefore i am unable to add a default schema to the user or login within SQL Server as it is a group.

Is there any way i can get around this?

If you would like more information i will be happy to provide it.

Thanks

Connection string

<add key="DatabaseConnectionString" value="Data Source=DATASOURCE;Initial Catalog=DATABASE;Integrated Security=true;"/>

Stored Procedure Call

// Open a connection to the database
            SqlConnection databaseConnection = GetDatabaseConnection();

            // Initialise the SQL command object
            SqlCommand cmd = new SqlCommand("PKG_PACKAGE_NAME$PR_PROCEDURE_NAME",
               databaseConnection);
            cmd.CommandType = CommandType.StoredProcedure;

Boldonglen

share|improve this question

migrated from stackoverflow.com Jul 19 '12 at 14:08

This question came from our site for professional and enthusiast programmers.

    
Please show you connection string(quote credentials) –  Johnny_D Jul 18 '12 at 8:58
    
@Johnny_D Connection styring added to origional post. –  Glen Robson Jul 18 '12 at 9:43
    
Got it, what do you mean by schema? Do you have to DATASOURCE prefix before stored procedure name? Can you also add some code of how do you invoke this stored procedures? –  Johnny_D Jul 18 '12 at 9:47
    
@Johnny_D Ok so basically the Datasource is the name of my Database, then within the database i have my tables and procedures etc with a prefix of the schema name. Lets say my database is called DB1 and my schema is called SCH1 my select statement would look like ... FROM DB1.SCH1.Table1. Within my .NET code there is no prefix of the Database or the Schema as they were not required when we were using Oracle. Origional post has been updated showing how i invoke a stored procedure. –  Glen Robson Jul 18 '12 at 9:56
    
That's rather uncommon for me situation. Think that main reason is Migration Assistant, maybe there is an option to ignore shemas. –  Johnny_D Jul 18 '12 at 10:33

1 Answer 1

up vote 2 down vote accepted

I'm not sure what is preventing you assigning a default schema to the user. You can simply use:

ALTER USER [NT AUTHORITY\Authenticated Users] 
WITH DEFAULT_SCHEMA = [Production]

Obviously replacing the user/schema names as appropriate.

However, it would be best if you could update the code to use a schema prefix - some more reading on that is here (functional reasons) and here (performance reasons).

share|improve this answer
    
I have tried creating a new user with the login name of the DOMAIN\Domain Users and with the default schema of the one that is used within the database however i still get the error: Could not find stored procedure 'PKG_PACKAGE_NAME$PR_PROCEDURE_NAME'. –  Glen Robson Jul 18 '12 at 11:33
    
Have you tried looking at the SQL that gets executed by using Profiler and then trying that manually? –  Matt Whitfield Jul 18 '12 at 12:09
    
I have triend running the code from the procedure that the .NET is calling and this works fine. The reason the .NET code is not working is because it is not picking up the schema default and therefore cannot find the stored procedure. –  Glen Robson Jul 18 '12 at 12:49
    
That's not really what I asked. I asked if you have run what is actually being run on your behalf by .NET, rather than what you think it ought to be running. Because if your user is actually in that default schema, then you don't really know that default schemas aren't working. You may find that you're connecting with a user name you don't expect, or something similar. –  Matt Whitfield Jul 18 '12 at 13:21
    
Ah i see what you mean. How can i find out exactly what user i am connecting with? –  Glen Robson Jul 18 '12 at 13:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.