Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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
0
magento
Asked:
magento
2 Solutions
 
Racim BOUDJAKDJICommented:
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 NathCommented:
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now