Try to use sp_msforeachdb

Here is link for you:

http://blogs.techrepublic.com.com/datacenter/?p=292

Good luck!

I am not sure I understand why we are comparing UPDATE and SELECT INTO?

Here is what we do with our huge tables.

Select *

INTO Table2

FROM TABLE1

WHERE YourStatement (something like Account_Start_Date < '01/01/1980'

Drop table Table1

EXEC sp_rename 'Table2', 'Table1'

Runs much faster than Delete because there is...

Chris,

Got it! 😛

At least he will have a possible solution!

Thanks!

Chris,

Thanks!... but it works... If not, please use http://blog.sqlauthority.com/2008/01/02/sql-server-fix-error-15281-sql-server-blocked-access-to-statement-openrowsetopendatasource-of-component-ad-hoc-distributed-queries-because-this-component-is-turned-off-as-part-of-the-se/

Jacob

Here it comes...

SELECT TOP (100) PERCENT *

FROM OPENROWSET('SQLOLEDB', 'Trusted_Connection=Yes;Server=(local);Database=Your_DB', 'exec usp_MySP)

...

Try to use TOAD for SQL Server that has this functionality built in.

Good luck!

Here is what I do when I need to update big tables (50M+ records)

Select

Column_1,

Column_2,

CAST('' as varchar(50)) As recreason

INTO t_claimfile

DROP TABLE

EXEC sp_rename 't_claimfile','claimfile'

Execution time between a...

JL,

THANKS!!!!

It worked!

For some reason it was missing mastlog.ldf

Thanks so much again!

JL, Thanks! I will try to change registry

We did not want to keep the db on C:\ drive

Lynn,

Yes! And the properties are the same.

THanks again

Jacob

Lynn,

Got it!

ResultSetType Property - ResultSetType_None

Connection Property - DEV12.biaatl.us.ups.com.SLIM_Develop.bia_db_sql_id (valid name that we use work perfectly!)

Parameter Mapping - No parameters are needed.

Thanks again for your time and effort

Jacob

Lynn,

You lost me there...:)

This package contains 7 other Execute SQL tasks...

Could you pleas elaborate your question?

THanks for your time!

Lynn,

One more thing:

Sometimes it runs fine even as a job!

But when it fails the only way to rerun is the query editor.

THanks again

Jacob