My old SQL statements don't work at DiscountASP

[securepagel]

I have a SQL2008 database that I imported/restored from a backup file from another hosting provider. Currently, in my ASP.net code, I use SQL commands by referencing the table names directly. For instance:

SELECT * from TableName Where Field='whatever'

But, I'm finding that with DiscountASP, I am forced to use the schema AND table name in my code. like this:

SELECT * from Schema.TableName Where Field='whatever'

However, this will take days to correct in my code, as I have thousands of SQL Create/Insert/Update/Select commands.

Is there any way to use my existing Asp.net/SQL code WITHOUT having to reference the Schema name?

Thanks!

[wisemx]

Hi,
This seems odd and I'm hoping one of the DASP crew will chime in for you soon.
I just tested one of my SQL Server DB's that has a Schema and I didn't have to specify anything, not even the DB name.
Are you really using [Select *] ?
I'm sure just about every SQL Server DBA will agree with me that you shouldn't on a Schema DB.

For what it's worth this is the query I just ran:
SELECT TOP 20
[SessionId]
,[Created]
,[Expires]
,[LockDate]
,[LockDateLocal]
,[LockCookie]
,[Timeout]
,[Locked]
,[SessionItemShort]
,[SessionItemLong]
,[Flags]
FROM [ASPStateTempSessions] order by LockDate

[dmitri]

The fully qualified table (and other db objects) reference has the following format:

[Server].[Database].[Schema].[Table]

When you run "SELECT * FROM MyTable" query, the Query Engine instead of "MyTable" substitutes the following:

ServerYouAreConnectedTo.YourDefaultDatabase.YourDe faultSchema.MyTable

The default server and database are the server and database you are connected to, and the default schema is set to "dbo" by default.

Let's say, you have a database SQL2008R2_123456_01 on sql2k804 server and you are running "SELECT * FROM MyTable" query. The query engine will actually execute the following:

"SELECT * FROM sql2k804.SQL2008R2_123456_01.dbo.MyTable"

If MyTable was created in the or was transferred to a different schema, you will need to specify it explicitely such as:

"SELECT * FROM DifferentSchema.MyTable"

And the fully qualified reference will be:

"SELECT * FROM sql2k804.SQL2008R2_123456_01.DifferentSchema.MyTab le"

You can transfer your tables to "dbo" schema to avoid changing your application code. You can transfer your tables to a different schema one-by-one or use a programmatic approach to tranfer all of your database objects. Please see this example (http://www.sql-articles.com/scripts/change-transfer-schema-for-all-objects):

Please note there is no guarantee that this trick will make your application to work as expected.

[securepagel]

Thank you all for your advice. The solution that ended up fixing the problem was to create a username that was identical to the schema I was trying to access. And then I made that user the owner of that schema, as well as setting that schema to be the default for that particular user.

[dmitri]

Thank you for your feedback. This is a neat solution and I'm glad it worked.