SQL Remove Old Backups: dbo

Showing posts with label dbo. Show all posts

Tuesday, March 6, 2012

2005: change all schema to dbo?

My hosting service moved my database to sql server 2005 and the schema
for the tables and procedures is no longer 'dbo'. I need to change all
the object owners back to 'dbo'. There was a script in sql 2000 to do
this, but not finding one for 2005. You would think people need to do
this often enough that there would be a menu command to do it in
management studio. I guess this is too obvious.
I can't find anything in BOL, all I know is from google. Some sort of
looping script over all the objects with ALTER SCHEMA? Or would there be
another trick to get the application working again?
Have a look in Books Online at ALTER SCHEMA ... TRANSFER
You could generate the list using:
SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.tables
WHERE schema_id != SCHEMA_ID('dbo');
SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.views
WHERE schema_id != SCHEMA_ID('dbo');
SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.procedures
WHERE schema_id != SCHEMA_ID('dbo');
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Paul" <invalid@.nospam.net> wrote in message
news:%23TBgQo%23aHHA.4140@.TK2MSFTNGP06.phx.gbl...
> My hosting service moved my database to sql server 2005 and the schema for
> the tables and procedures is no longer 'dbo'. I need to change all the
> object owners back to 'dbo'. There was a script in sql 2000 to do this,
> but not finding one for 2005. You would think people need to do this often
> enough that there would be a menu command to do it in management studio. I
> guess this is too obvious.
> I can't find anything in BOL, all I know is from google. Some sort of
> looping script over all the objects with ALTER SCHEMA? Or would there be
> another trick to get the application working again?
>
|||Perfect thanks. You just run the output of these select statements.
Aaron Bertrand [SQL Server MVP] wrote:
> Have a look in Books Online at ALTER SCHEMA ... TRANSFER
> You could generate the list using:
> SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
> FROM sys.tables
> WHERE schema_id != SCHEMA_ID('dbo');
> SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
> FROM sys.views
> WHERE schema_id != SCHEMA_ID('dbo');
> SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
> FROM sys.procedures
> WHERE schema_id != SCHEMA_ID('dbo');
>

2005: change all schema to dbo?

My hosting service moved my database to sql server 2005 and the schema
for the tables and procedures is no longer 'dbo'. I need to change all
the object owners back to 'dbo'. There was a script in sql 2000 to do
this, but not finding one for 2005. You would think people need to do
this often enough that there would be a menu command to do it in
management studio. I guess this is too obvious.
I can't find anything in BOL, all I know is from google. Some sort of
looping script over all the objects with ALTER SCHEMA? Or would there be
another trick to get the application working again?Have a look in Books Online at ALTER SCHEMA ... TRANSFER
You could generate the list using:
SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.tables
WHERE schema_id != SCHEMA_ID('dbo');
SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.views
WHERE schema_id != SCHEMA_ID('dbo');
SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.procedures
WHERE schema_id != SCHEMA_ID('dbo');
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Paul" <invalid@.nospam.net> wrote in message
news:%23TBgQo%23aHHA.4140@.TK2MSFTNGP06.phx.gbl...
> My hosting service moved my database to sql server 2005 and the schema for
> the tables and procedures is no longer 'dbo'. I need to change all the
> object owners back to 'dbo'. There was a script in sql 2000 to do this,
> but not finding one for 2005. You would think people need to do this often
> enough that there would be a menu command to do it in management studio. I
> guess this is too obvious.
> I can't find anything in BOL, all I know is from google. Some sort of
> looping script over all the objects with ALTER SCHEMA? Or would there be
> another trick to get the application working again?
>|||Perfect thanks. You just run the output of these select statements.
Aaron Bertrand [SQL Server MVP] wrote:
> Have a look in Books Online at ALTER SCHEMA ... TRANSFER
> You could generate the list using:
> SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
> FROM sys.tables
> WHERE schema_id != SCHEMA_ID('dbo');
> SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
> FROM sys.views
> WHERE schema_id != SCHEMA_ID('dbo');
> SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
> FROM sys.procedures
> WHERE schema_id != SCHEMA_ID('dbo');
>

2005 vs 2008 NOEXPAND hint behavior

With SQL 2005, using the NOEXPAND hint can result in an error:

Msg 8171, Level 16, State 2, Line 1

Hint 'noexpand' on object 'dbo.ervStats' is invalid.

I haven't tried it with 2008 yet but I expect the same behavior. That behavior can cause problems and I would like to understand the logic behind it. I would expect a Warning to be raised, not an error.

Scenario:

OLTP application using one or more indexed view to speed up some functionalities. This can happen as some db are OLTP but also used for real time reporting that can't be done on a mirror. At some point you may find out 1 indexed view is the cause of excessive locking for 1 specific site, so you remove the index....but must now recompile and redeploy the application to hundreds of desktops to remove that 8171 error message.

If a warning was issued instead of an error, the problem would not occur. Nothing is changed at the logical level and the query would run as expected is the hint was ignored.

There is possibly a very good reason to raise an error instead of a warning, but I can't see it right now. Before I create a feature request on Connect can somebody shed some light on that behavior? (I might change my mind once I understand the logic:-) )

PS: I KNOW indexed views are not meat for OLTP scenarios, but there are edge cases where they do help when designed properly. Sometimes business scenarios change and they must be changed or dropped.

thanks

Hi Lakusha - I'm not sure of the direct answer to your question, but as another option vs. recompiling and redeploying your application, have you looked at the possibility of using a plan guide for addressing the issue?

|||

Hi Chad,

No, I haven't looked at plan guides for this. Perhaps I am wrong, but I feel plan guides can't help much in our context. We have hundreds of servers at hundreds of locations (ISV context). I can manage plan guides easily but customers support teams won't. Even something as trivial as dropping an index on a view can prove too much because their schema becomes non standard and they must remember to drop it again after each major upgrades (and sometimes they won't and this will become a support call). Support will check immediately if they upgraded recently and forgot to run the drop index script but plan guides are another matter.

A plan guide will also lock you up. In a complex query I might want to use a NOEXPAND on a materialized view (especially when the Enterprise query optimizer sometimes can't be trusted to choose the view but also because some customers use the Enterprise versions while others use Standard...so the lowest common denominator wins) but I certainly don't want to force the plan on the entire query because it will certainly be a good plan for some customers but not for others.

I also think that discarding a hint is certainly worth a warning but not an error since it does not change anything from a logical stand point. The exact behavior could even be controlled at the db level, or even better, at the hint level.

Most of all, I would like to understand why they choose to raise an error and not a warning. In the end it is best to understand why things work a certain way.

PS: I know MS Research has done a lot of work to support outer joins and stacked indexed views but I haven't seen anything new advertized for indexed views in SQL2008...

thanks

|||

Hi Lakusha. Let me try to address points mentioned in this thread one by one.

The behavior of noexpand hint is same as any other hint and, as far as I know, except consistency with other hints there are no other fundamental reasons why it is an error instead of a warning. You definitely can propose this via Connect.

Plan guides will not really solve this problem (with one exception, see my next bullet) since noexpand hint is not a query hint.

You can make a plan guide with USE PLAN providing plan which has indexed view in it and this will force using indexed view same way as noexpand hint. But ... you will be pinning whole plan not allowing for potentially better alternatives as circumstances change, and in SQL Server 2005 it still will generate an error if index is not present on the view. In CTP5 we are planning to have plan guides functionality which will not error out in such case but rather generate plan as if no plan guide was present.

The only workaround I know about is to wrap your query in an SP with following logic:
if <Index is present on the view>
then <run query with noexpand hint in it>
else <run same query without noexpand hint>
There is an added inconvenience of having to wrap it into SP, but it will let you avoid redeployments you mention.

|||

Thanks Alex.

I might make a suggestion about this on Connect then. As for plan guides, I am aware of the drawbacks. Using a SP is indeed a good suggestion. I did not consider it because, for internal reasons, we have chosen to never use them. It is the old adhoc vs SP debate again and in our case ad hoc queries won (we use a DAL that works quite a bit like LINQ).

Thanks

|||

Thanks for the post. It's very helpful.

Do you mind providing some more insight regarding the 3rd bullet? I have an ad-hoc query submitted from Business Objects. When I copy the query from Profiler and add a "noexpand", the query runs lightening fast. Without the "noexpand", the query is tremendously inefficient.

So, I then ran the query (with the noexpand) and captured the showplan XML and stuffed the showplan results into the use plan option. Next, when I run the query (with the noexpand and the use plan), it runs great.

Then, when I remove the "noexpand" from the query (but not the use plan), the following error is issued:

Msg 8698, Level 16, State 0, Line 2

Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.

The reason I removed "noexpand" from the query is because Business Objects does not submit the query with noexpand. I suspect that the reason the error was issued is because the query expanded the view and the plan did not.

In all cases, the query referenced the indexed view. We are running the Enterprise version.

So, how can noexpand be implemented when my company is not the author of the code?

- Is there a machine or database level configuration that could be set?

- How can a noexpand appear in the plan but not in the query without getting the error message above?

- Other?

Any insight you provide will be greatly appreciated!

|||(DaveIII@.discussions.microsoft.com) writes: > Then, when I remove the "noexpand" from the query (but not the use > plan), the following error is issued: > > > > Msg 8698, Level 16, State 0, Line 2 > > Query processor could not produce query plan because USE PLAN hint > contains plan that could not be verified to be legal for query. Remove > or replace USE PLAN hint. For best likelihood of successful plan > forcing, verify that the plan provided in the USE PLAN hint is one > generated automatically by SQL Server for the same query. I guess this is because that in order to validate that your plan is valid, the optimizer keeps generating plans, until it has generated yours. But when the query has NOEXPAND, the optimizer never sees the indexed view; it sees the expanded query, and then will have to try view substitution to come back to the view. And since view substitution is an expensive operation, it does not try everything. Clearly, this is not a very pleasant situation. If nothing else, it's not a very intuitive behaviour. I have not checked Books Online, to see if this restriction is documented. I think you should submit this issue on Connect, but check Books Online first, as I think you should have a different angle depending on whether it's documented or not. If it's not documented, just submit it as a bug. If it's documented, you are better off making this as a suggestion, I think. Of course, in either case, the devs would appreciate if you can supply a repro. -- Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

2005 vs 2008 NOEXPAND hint behavior

With SQL 2005, using the NOEXPAND hint can result in an error:

Msg 8171, Level 16, State 2, Line 1

Hint 'noexpand' on object 'dbo.ervStats' is invalid.

Scenario:

If a warning was issued instead of an error, the problem would not occur. Nothing is changed at the logical level and the query would run as expected is the hint was ignored.

PS: I KNOW indexed views are not meat for OLTP scenarios, but there are edge cases where they do help when designed properly. Sometimes business scenarios change and they must be changed or dropped.

thanks

|||

Hi Chad,

Most of all, I would like to understand why they choose to raise an error and not a warning. In the end it is best to understand why things work a certain way.

PS: I know MS Research has done a lot of work to support outer joins and stacked indexed views but I haven't seen anything new advertized for indexed views in SQL2008...

thanks

|||

Hi Lakusha. Let me try to address points mentioned in this thread one by one.

Plan guides will not really solve this problem (with one exception, see my next bullet) since noexpand hint is not a query hint.

|||

Thanks Alex.

Thanks

|||

Thanks for the post. It's very helpful.

Then, when I remove the "noexpand" from the query (but not the use plan), the following error is issued:

Msg 8698, Level 16, State 0, Line 2

In all cases, the query referenced the indexed view. We are running the Enterprise version.

So, how can noexpand be implemented when my company is not the author of the code?

- Is there a machine or database level configuration that could be set?

- How can a noexpand appear in the plan but not in the query without getting the error message above?

- Other?

Any insight you provide will be greatly appreciated!

2005 vs 2008 NOEXPAND hint behavior

With SQL 2005, using the NOEXPAND hint can result in an error:

Msg 8171, Level 16, State 2, Line 1

Hint 'noexpand' on object 'dbo.ervStats' is invalid.

Scenario:

If a warning was issued instead of an error, the problem would not occur. Nothing is changed at the logical level and the query would run as expected is the hint was ignored.

PS: I KNOW indexed views are not meat for OLTP scenarios, but there are edge cases where they do help when designed properly. Sometimes business scenarios change and they must be changed or dropped.

thanks

|||

Hi Chad,

Most of all, I would like to understand why they choose to raise an error and not a warning. In the end it is best to understand why things work a certain way.

PS: I know MS Research has done a lot of work to support outer joins and stacked indexed views but I haven't seen anything new advertized for indexed views in SQL2008...

thanks

|||

Hi Lakusha. Let me try to address points mentioned in this thread one by one.

Plan guides will not really solve this problem (with one exception, see my next bullet) since noexpand hint is not a query hint.

|||

Thanks Alex.

Thanks

|||

Thanks for the post. It's very helpful.

Then, when I remove the "noexpand" from the query (but not the use plan), the following error is issued:

Msg 8698, Level 16, State 0, Line 2

In all cases, the query referenced the indexed view. We are running the Enterprise version.

So, how can noexpand be implemented when my company is not the author of the code?

- Is there a machine or database level configuration that could be set?

- How can a noexpand appear in the plan but not in the query without getting the error message above?

- Other?

Any insight you provide will be greatly appreciated!

Sunday, February 19, 2012

2005 Query help

Please help.

On sql 2000 i have a query like this where the columns are primary keys.

Select count(*) from db.dbo.table1

where convert(varchar(3), col1) + convert(varchar(10), col2) not in

(select convert(varchar(3), col1) + convert(varchar(10), col2) from db.dbo.table2)

It completes in 1 second with sql 2000. I have restored the db to sql 2005 and run the same query. The processors peg and it goes to la la land. I have updated statistics and installed SP1. Anyone have ideas? The sql 2005 is even way better than the sql 2000 box. If you have a better way to perform the same task, please let me know.

Thanks!

can you compare execution plans on both servers? That should give a head start.|||

Hey,

I did compare the execution plans. For some reason, the 2005 execution plan has more to it and mentions parallelism. It just does not run the query. The box just maxes out and stays maxed out. Very strange.

I've rebooted the box for the heck of it and it doesn't matter. It doesn't want to run. Thanks for the response.

|||Moving to T-SQL forum. Maybe there's a way to rewrite the query.|||

Try this using EXISTS:

select *
from db.dbo.table1 as table1
where not exists (select *
from db.dbo.table2 as table2
where table1.col1 = table2.col1
and table1.col2 = table2.co2)

This should perform better and give the same results (actually more correct, because the varchar conversions could in some rare cases given invalid values).

As for the long run times, How much data is involved? One of the problems I have run into is a lot of waits during parallel operations when some larger operations go parallel. I had to tune some of my data warehouse queries by setting MAXDOP to 1.

I found this by executing this query:

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,
execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

And checking the wait type. Lots of huge CXPACKET waits. Once you get into the wait, watch the results here and post them. You can see where the execution is at by watching the currentExecutingCommand column (it is really cool to watch when you aren't stuck :)

|||

Hey Louis. Thanks for the response!

The exists does work very well. I use that most of the time. I also will put a hyphen between the converts to help avoid getting errors when I do use the "in" style.

I tried running the query you posted on the 2005 box and it complains about '.' near the end of the query saying incorrect syntax. I don't see why though.

It's very strange though how they will be handled so differently between 2000 and 2005.

Thanks again!

|||

The other concern with the IN style is indexing. If you put values in functions or expressions it invalidates use of indexes. But putting seperators that cannot exist in the data will make it "technically" safe.

I took that query verbatim and ran it on my express instance and it worked fine. It is version:

I will try it on my 2005 SP1 box and make sure it works there, but that is interesting.

|||

Hey Louis. Thanks for the info.

Thanks too for trying it on your boxes!

One of my tables has 1.4 million records and the other has about 40K.

Thanks

Tuesday, March 6, 2012

2005: change all schema to dbo?

2005: change all schema to dbo?

2005 vs 2008 NOEXPAND hint behavior

2005 vs 2008 NOEXPAND hint behavior

2005 vs 2008 NOEXPAND hint behavior

Sunday, February 19, 2012

2005 Query help

SQL Remove Old Backups

Blog Archive

About Me