Solved

schema changes automatically

Posted on 2013-06-21
5
281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 27

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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

617 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