Solved

schema changes automatically

Posted on 2013-06-21
5
276 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now