Good question, thanks.

What's wrong with using the linked server in your query?

Unless I'm misunderstanding?

Been doing something similar myself - you need to look at DCOM settings & WMI security, instructions are here:

DCOM: http://msdn.microsoft.com/en-us/library/aa393266.aspx

WMI: http://msdn.microsoft.com/en-us/library/aa393613.aspx

Interestingly, once I'd enabled DCOM for the relevant user, I...

Note if you're on 2008 R2 backup compression is available in Standard edition too.

Depends on your type of data but > 60% space saving isn't unusual.

Or are you in the right database?

If speed isn't a issue here, I doubt it'd be worth it. I imagine there's easier ways to get bigger improvements too.

Best bet would be to test for each case...

Well I think it relies on the Script/Scripter method/object from SMO.

You can access it through other ways than Powershell.

Don't know of a way to do it from within SQL.

Have a look at Powershell:

http://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/

Check in your DB Mail account profile that the Display name field has something in it.

Can these changes/additions be codified?...

And if the 0's can be NULLs in the table instead, you can get rid of all the NULLIF's.

Not sure how it'll perform (and could depend on your definition of "first"), but:

SELECT COALESCE(

NULLIF([9671], 0),

NULLIF([9672], 0),

NULLIF([9673], 0),

NULLIF([9674], 0),

NULLIF([9781], 0),

NULLIF([9782], 0),

NULLIF([9783], 0),

NULLIF([9784], 0),...

You can set permissions to default to true in SSMS - Tools -> Options -> SQL Server Object Explorer -> Scripting

But I can't see Logins there.

Think Powershell will be your...

Although note if you want to capture information like who changed the data, CDC can't do that.

It's definitely one of the most complete examples of DDL I've seen on here - thanks.

But could do with seeing how far you've got with the proc so far.

Why the...