schema changes automatically

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
LVL 5
magentoAsked:
Who is Participating?
 
Racim BOUDJAKDJIConnect With a Mentor Database Architect - Dba - Data ScientistCommented:
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
 
Surendra NathConnect With a Mentor Technology LeadCommented:
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
 
magentoAuthor Commented:
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
 
ZberteocCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.