Solved

schema changes automatically

Posted on 2013-06-21
5
279 Views
Last Modified: 2013-07-09
Hi,

Database is MSSQL 2008.

The site is getting 500 internal error.While checking the logs it says object doesnt exists.

In the asp code, it was like dbo.testusernew.tablename1 and in db it looks like dbo.testuserold.tablename1

So i goahead and alter the schema ,also for one of the view in the select clause i make the changes as below

(select dbo.testuserold.tablename1 to select dbo.testusernew.tablename1) .

Open in new window


It worked fine after that .

Well , now the problem is again i got the error only on that VIEW .So i checked the db again.

The tables looks like dbo.tablename1 instead of dbo.testusernew.tablename1 ( still it works fine not throwing any error)

In the view i changed as below and then it worked fine.

(select dbo.testusernew.tablename1 to select dbo.tablename1)

Open in new window


My question is how it was automatically changed from dbo.schema.tablename to dbo.tablename ?

Any ideas , curious to understand it.

Thanks,
Magento
0
Comment
Question by:magento
5 Comments
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 250 total points
ID: 39265269
Any ideas , curious to understand it.

Then meet DDL triggers, your new best friends :)  Once setting up an trailing process using these, this feature will allow you to determine the event and probably origin of the change.  Please read the following link for more info:

http://msdn.microsoft.com/en-us/library/ms175941(v=sql.105).aspx

Tip: Look for the ALTER_SCHEMA event when it fires up.

Hope this helps
0
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 250 total points
ID: 39265528
Curious to see
the dbo is a default schema and the way you are writing your sql  looks like

select <default Schema>.<Schema>.<Object>
This is a standard procedure... The standard procedure is to just use the schema.Object notation.

select <Schema>.Object, something is wrong with your previous code but the currently changed one looks good.
0
 
LVL 5

Author Comment

by:magento
ID: 39269642
The new code is good.

I agree it, but it works with the same good code as below on last week.

(select dbo.testuserold.tablename1 to select dbo.testusernew.tablename1) .
                                 
But now it changed to the below with the same good code.

(select dbo.testuserold.tablename1 to select dbo.testusernew.tablename1) .
                                 

Thanks,
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39285322
I don't understand that notation with 2 schemas:

dbo.testuserold.tablename1

is should be either

dbo.tablename1

if the table is under the default schema or

testuserold.tablename1

if is under the testuserold schema.

The 4 way notation is:

server.database.schema.object

you can't have 2 schemas in the notation.
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question