My old SQL statements don't work at DiscountASP

Discussion in 'Databases' started by securepagel, Sep 23, 2010.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. securepagel

    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!
     
    securepagel, Sep 23, 2010
    #1
  2. wisemx

    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
     
    wisemx, Sep 23, 2010
    #2
  3. dmitri

    dmitri DiscountASP.NET Staff

    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.YourDefaultSchema.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.MyTable"

    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:

    Please note there is no guarantee that this trick will make your application to work as expected.
     
    dmitri, Sep 23, 2010
    #3
  4. securepagel

    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.
     
    securepagel, Sep 24, 2010
    #4
  5. dmitri

    dmitri DiscountASP.NET Staff

    Thank you for your feedback. This is a neat solution and I'm glad it worked.
     
    dmitri, Sep 24, 2010
    #5
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page