This may help you: http://poshcode.org/2276

I got it working, finally!!!

I thought I would share what I've learned the hard way about transferring data between a SQL 2K sp4 source server and a SQL 2K8 R2...

I guess at the end of the day, I want to understand

1) why the transfer method fails on 2 out of 53 databases, given that the syscontraints view exists...

Mixed-mode logins (not my choice) w/ different sets of users on source and destination.

And George, yes to both: this is a nightly thing.

George,

Thanks, but we're not really interested in that approach.

Boooo. It didn't work, sysconstraints is definitely not in the views array, and it was still scripted.

See above, in original post: rather than adding items, it's setting properties of the transfer object.

This creates the transfer object:

$xfr = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer -argumentlist $sdb

Then the code above sets...

Thanks Gail, that moved me forward.

So now I have the correct collection of views in $Views

$Views = $sdb.Views | Where-Object {$_.IsSystemObject -eq $false}

So I've set TransferAllViews = $False, how...

Hi Gail,

I was hoping that this would prevent those objects being scripted, but it appears not.

$xfr.Options.AllowSystemObjects = $false

I certainly don't want any system objects transferred.

jlmoore03,

If you're using SQL 2K8, you can check what app your "smart" users are connecting with using a login trigger, and reject those bypassing the app.

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

We will be gleefully implementing...

Spot on, Lowell.

I guess it works from the command prompt on my machine since Excel is installed locally. But Excel isn't installed on the server -- and probably should not...

Solved: had to set CopyAllObjects = $false, and enumerate each of the object categories and set them to $true.

Boooo: I added RAISERROR ('test', 11' 1) at the end of the trigger (10 is converted to 0 by SQL Server -- See Erland Sommerskog's website http://www.sommarskog.se/error-handling-I.html#@@error)

The Insert occurs successfully,...

If possible, I prefer to avoid triggers altogether.

Having said that, on tables where business rules can be enforced using DRI and constraints, I use them for auditing.

However, my developers...

Thanks Jack,

That's what I was afraid of.

Ah well, it needs doing, so....

P