Code not working
-
This used to work for me (with my own servers and dbs, and some other customizations) in PowerGUI.
Now, the TransferData() method throws "Exception calling "TransferData" with "0" argument(s): "Object reference not set to an instance of an object."." This happens with the exact code below.
When this same script is run from my SQL Servers as a CmdExec job, it succeeds.
What am I missing?
My local machine is running Powershell v4.
Thanks!
#Connect to the local, default instance of SQL Server.#Get a server object which corresponds to the default instance
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server
#Reference the AdventureWorks2012 database.
$db = $srv.Databases["AdventureWorks2012"]
#Create a database to hold the copy of AdventureWorks
$dbCopy = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Database -argumentlist $srv, "AdventureWorksCopy"
$dbCopy.Create()
#Define a Transfer object and set the required options and properties.
$xfr = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer -argumentlist $db
#Set this objects properties
$xfr.CopyAllTables = $true
$xfr.Options.WithDependencies = $true
$xfr.Options.ContinueScriptingOnError = $true
$xfr.DestinationDatabase = "AdventureWorksCopy"
$xfr.DestinationServer = $srv.Name
$xfr.DestinationLoginSecure = $true
$xfr.CopySchema = $true
"Scripting Data Transfer"
#Script the transfer. Alternatively perform immediate data transfer with TransferData method.
#$xfr.ScriptTransfer()
$xfr.TransferData()
-
i would think it has something to do with the object Microsoft.SqlServer.Management.SMO.Server that got created; is this a new local machine? maybe you didn't install SMO when you installed SQL or the SQL client tools?
can you look in %windir%\assembly and see if Microsoft.SqlServer.Management.SMO exists? i believe that is where the Global Assembly Cache exists(GAC)
on my machine i have 3 dll versions, from installing 2008R2/2012/2014 over time.
also the specific database would have to exist, and you do have that in place, right?
the .DatabaseObject could be the culprit as well.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Version 10 of smo and smoextended are both there; same on my SQL Servers.
-
schleep (10/15/2015)
Version 10 of smo and smoextended are both there; same on my SQL Servers.hmm; and the database? [AdventureWorks2012] definitely exists on your local machine, right? if you obfuscated it, the actual db you want to copy, then....
i'd be just slightly worried that a 2012 db might need SMO v 11 to be used, that might require some research.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Yep, all good: when I replace TransferData() with ScriptTransfer(), the appropriate code is spewed out.
It's the TransferData() method that's broken.
Both source and dest servers (in my implementation) show Monitor activity up until that point.
I copied that code straight from the MS Transfer class webpage, trying to keep it as simple as possible for illustration purposes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply