OK, I have tried just about everything over the past 9 hours. I think I've read through every forum regarding anything similar to errors with SMO and TransferData().
However, I still can't fix my problem.
What I'm trying to accomplish is a workaround for deploying my database application using ClickOnce. Since I can't include the database when upgrading (which would wipe out users' data), I want to have a 'Master Database' that is used as a reference to both initially create, and to selectively update, the main 'User Database'. I'm not sure if that is possible, yet...because SMO is not working for me (I'm sure there are more laborious methods to do what I want, but SMO should work...I can't help but think there is a bug).
Here is my current code:
Code Snippet
'Master Database
Dim dbName_Master As String
Dim SqlCon_Master As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.LM_Master_SQLConStr)
SqlCon_Master.Open()
dbName_Master = SqlCon_Master.Database.ToString()
Dim SC As New ServerConnection(SqlCon_Master)
Dim srv As New Smo.Server(SC)
'Verify User Instance connection
Console.WriteLine("User Instance Server: " & srv.InstanceName.ToString)
'Select the Master Database as the source db
Dim dbMaster As Smo.Database
dbMaster = srv.Databases(dbName_Master)
'Define a Transfer object and set the required options and properties.
Dim xfr As Smo.Transfer
xfr = New Smo.Transfer(dbMaster)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.CopyData = True
xfr.CreateTargetDatabase = True
xfr.DropDestinationObjectsFirst = True
xfr.DestinationDatabase = "TestDB"
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.Options.NoIdentities = False
xfr.Options.NoCollation = True
xfr.Options.Indexes = True
xfr.CopyAllDefaults = True
xfr.Options.AllowSystemObjects = True
xfr.Options.DriAll = True
xfr.Options.XmlIndexes = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
Dim StrColl As New System.Collections.Specialized.StringCollection
StrColl = xfr.ScriptTransfer()
For Each str As String In StrColl
Console.WriteLine(str)
Next
xfr.TransferData()
The final error is:
*****
DtsRuntimeException was unhandled...
Exception from HRESULT: 0xC0011008
*****
I also have not seen any examples or documentation on how to store the new database in a specific location on the hard drive, once the transfer is complete.
In fact, SMO has surprisingly little documentation that I can find (which is why the code above may seem a little redundant when setting the xfr options...). I would really appreciate some help.
Thank you,
AL
Try to set the connection up mannually. So instead of using the connection string:
Dim SqlCon_Master As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.LM_Master_SQLConStr)
Use the server.(Parameters) such as userid, password, ect.
I ran into the same issue a while back with the transfer, once I set my connection parameters to my server manually this corrected the problem.
Here is an example in c# that I wrote maybe it will help. The vb conversion should be straightforward:
Code Snippet
//Two Server SMO objects to hold source and destination
Server server;
Server Source;
try
{
//Cannot use the SqlConnection class as this locks out DTO from accessing the login method.
//It appears DTO needs to be able to set the login properties which the SqlConnection locks it
//out of doing. Below commented methods will not work in this instance.
//SqlConnection conn = new SqlConnection("Data Source=.\sqlserver;integrated security = true");
//Server server = new Server(new ServerConnection(conn));
//Destination DataBase connected in an SMO manner using serverconnection
ServerConnection connec = new ServerConnection(".\SqlServer");
//Set secure login
connec.LoginSecure = true;
server = new Server(connec);
//Create target database. Transfer method does contain a create command but have not figured
//out how to get this to work. Easier to use the SMO method at this time.
Database dbo =new Database(server,"AdventureWorksClone");
dbo.Create();
//Source Database connected in an SMO manner using serverconnection
ServerConnection conn = new ServerConnection(".\SQLServer");
//Set secure login
conn.LoginSecure = true;
Source = new Server(conn);
//Set source to existing database that is to be copied
Database db = Source.Databases["AdventureWorks"];
//Establish transfer passing in source database
Transfer t = new Transfer(db);
t.CopyAllObjects = true;
t.DropDestinationObjectsFirst = true;
t.CopySchema = true;
t.CopyData = true;
//Don't know if this is still needed as it has been set in an SMO manner
t.DestinationLoginSecure = true;
//Destination Server created above
t.DestinationServer = server.Name;
//Could not get this to work yet
//t.CreateTargetDatabase = true;
//Newly created database on destination - Clone not quite right word as in ADO uses clone
//to indicate copying of schema only - not the case here
t.DestinationDatabase = "AdventureWorksClone";
t.Options.IncludeIfNotExists = true;
//This will copy all of the data and relations
//Alternatively could use t.scripttransfer() to copy just the schema
//SqlBulkCopy might be faster but might not make a copy of entire database
t.TransferData();
//Release server objects
server = null;
Source = null;
//Give verification that code is complete
Console.WriteLine("Executed");
Console.ReadLine();
}
catch (Exception ex)
{
Console.Out.WriteLine(ex);
Console.ReadLine();
server = null;
Source = null;
}
}
}
Hope this helps a little, sorry the code is in c#.
Aaron
|||
SMO uses SSIS under the covers to perform the workflow and data transfer pieces, here is what the error number means:
"Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored."
IIRC SMO generates an XML file on the fly that represents the package, is your temp environment variable set correctly? No probs with disk space etc?
|||Euan,
I recieved the same error when testing the code listed above but could not trace it. As soon as I switched my server connection method it worked without a hitch( I think I mention this in the sample.) so I do not know if this is as clear cut.
|||This is a little strange and i can not repo so I sent mail to the PM/Arch for SMO to see what he has to say.|||
Thank you for the example, Aaron. However, it did not work. Please note that I cannot connect to the default instance of SQL Server (also note that I only have SQL Express installed). I did try to apply your approach as best I could considering that I need to connect to the User Instance of SQL Express running with my application:
Code Snippet
Dim srv As Smo.Server
Dim source As Smo.Server
'*******This is the code to determine the Master Database name, used in original code
Dim dbName_Master As String
Dim SqlCon_Master As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.LM_Master_SQLConStr)
SqlCon_Master.Open()
dbName_Master = SqlCon_Master.Database.ToString()
'This did not help: SqlCon_Master.ChangeDatabase("master")
Console.WriteLine("Master DB Name: " & dbName_Master)
'*******
Dim srvCon As New ServerConnection(SqlCon_Master)
srv = New Smo.Server(srvCon)
''Verify User Instance connection
Console.WriteLine("User Instance Server: " & srv.InstanceName)
If srv.Databases.Contains("MasterDB_Copy") Then
srv.Databases("MasterDB_Copy").Drop()
End If
Dim newDB As New Smo.Database(srv, "MasterDB_Copy")
newDB.Create()
'Create connection to Source DB
Dim sourceCon As New ServerConnection(SqlCon_Master)
source = New Smo.Server(sourceCon)
''Verify User Instance connection
Console.WriteLine("User Instance Server: " & source.InstanceName)
Dim MasterDB As Smo.Database = source.Databases(dbName_Master)
Dim xfr As New Smo.Transfer(MasterDB)
xfr.CopyAllObjects = True
xfr.DropDestinationObjectsFirst = True
xfr.CopySchema = True
xfr.CopyData = True
xfr.DestinationServer = srv.Name
xfr.DestinationDatabase = newDB.Name
xfr.Options.IncludeIfNotExists = True
Try
xfr.TransferData()
Catch ex As Exception
Console.WriteLine("Error: " & ex.Message)
End Try
srv = Nothing
source = Nothing
I still get the same error upon reaching TransferData().
If it means anything to this thread, I am using SMO for Backup and Restore successfully...so some SMO functions are working on my machine.
Is there anything else I can try?
Thanks,
AL
|||
Something I just tested:
I created a very simple test database, containing 2 tables; each table contains 2 columns. No relationships, constraints, or anything else. Just 2 tables with 2 columns each. I then changed the connection string to the new test database connection string.
I did this to determine if my Master Database is causing a problem for some reason, but I got the same error with the basic test database as I got with the more complex database.
So, the hunt for a solution continues...
|||You still are using a sqlconnection to initialize your server. Try initializing the the server with just the server instance name. Don't use the SqlClient to establish the server.
Code Snippet
Dim srvCon As New ServerConnection("ServerName")
'IE (".\SqlServer")
srvCon.LoginSecure = true;
'What ever other login properties set similar to above.
srv = New Smo.Server(srvCon)
When I replaced the sqlconnection with a direct server connection setting the connection properties manually with the ServerConnection I was able to transfer. There might be another issue, but I would definately try this first as this is the same issue I was having.
Aaron
|||
Okay, I have tried your method, Aaron (properly, this time...I think):
However, once again, it did not work...I could not connect to the server when trying to connect to the database directly. Your method, if it worked for me, would also imply that I cannot use SMO to transfer a database when I am using a user instance--there doesn't seem to be a User Instance option when creating the server connection (as I did below). Please look at the debugging information (comments) I provided in the Code Snippet below for details on what I tried to do:
Code Snippet
Dim srv As Smo.Server
Dim source As Smo.Server
'The connection string (auto-generated by Visual Studio):
' Data Source=.\SQLEXPRESS;AttachDbFilename="|DataDirectory|\LM_SQL.mdf";Integrated Security=True;Connect Timeout=20;User Instance=True
Dim sourceCon As New ServerConnection(".\SQLEXPRESS")
sourceCon.LoginSecure = True
'sourceCon.DatabaseName = My.Application.Info.DirectoryPath & "\LM_TestDB.mdf"
'My debugging information:
' ***I was able to connect to SQLEXPRESS when I didn't set the DatabaseName
' ***When I tried to use the above DatabaseName (which is in My Documents...Debug Folder), I got the error ArgumentException was unhandled..."The value's length for key 'initial catalog' exceeds it's limit of '128'."
'sourceCon.DatabaseName = "C:\Test\LM_TestDB.mdf"
' ***When I copied LM_TestDB.mdf (and ..._log.mdf) to a folder on the system drive (as shown right above this line), then set the DatabaseName to that file, I got the error "Failed to connect..."
' ***There doesn't seem to be a way to create a user instance when using the method above.
' ***Either way, I cannot connect to the server when I set the DatabaseName to a file...but what if I use the Database Name created when setting the test connection:
'sourceCon.DatabaseName = dbName_Master
' ***That did not work either.
sourceCon.ConnectTimeout = 30
sourceCon.Connect()
source = New Smo.Server(sourceCon)
'Verify database connection (NOT User Instance)
Console.WriteLine("Server Instance Name: " & source.InstanceName)
'More debugging:
' ***Now, I'll try to attach a database to the newly created server
Dim dbFilePathString As New System.Collections.Specialized.StringCollection()
'dbFilePathString.Add(My.Application.Info.DirectoryPath & "\LM_TestDB.mdf")
'dbFilePathString.Add(My.Application.Info.DirectoryPath & "\LM_TestDB_log.ldf")
' ***The file paths above did not work (Error: "Attach database failed for Server '[MyPCName]\SQLEXPRESS'."); now, I'll try the path to the DB in the test folder
dbFilePathString.Add("C:\Test\LM_TestDB.mdf")
dbFilePathString.Add("C:\Test\LM_TestDB_log.ldf")
' ***The file paths above worked, and the database was successfully added (as shown by the test below--all test tables were present in the console upon running)--but... (please scroll down to the next comment)
If source.Databases.Contains("TestDB") Then
source.DetachDatabase("TestDB", True)
End If
source.AttachDatabase("TestDB", dbFilePathString)
For Each db As Smo.Database In source.Databases
If db.Name = "TestDB" Then
Console.WriteLine("DB Name: " & db.Name)
Console.WriteLine("***")
Console.WriteLine("TestDB Found!")
Console.WriteLine("***")
For Each tbl As Smo.Table In db.Tables
Console.WriteLine(tbl.Name)
Next
Console.WriteLine("***End TestDB Tables***")
End If
Next
' ***I would not be able to use this method of connecting to the database because, with a ClickOnce install, the database files are stored in the users' Docs & Settings folders...which would make the file names too long for the purposes of attaching a database. The user would require administrative rights if my program tried to copy a file to anywhere but their Docs and Settings...considering the intended users of my application, that would not be a viable option.
Dim srvCon As New ServerConnection(".\SQLEXPRESS")
srvCon.LoginSecure = True
srvCon.ConnectTimeout = 30
srvCon.Connect()
srv = New Smo.Server(srvCon)
Dim newDB As New Smo.Database(srv, "TestDB_Copy")
If srv.Databases.Contains(newDB.Name) Then
srv.Databases(newDB.Name).Drop()
End If
newDB.Create()
Dim MasterDB As Smo.Database = source.Databases("TestDB")
Dim xfr As New Smo.Transfer(MasterDB)
xfr.CopyAllObjects = True
xfr.CopySchema = True
xfr.CopyData = True
xfr.DestinationServer = srv.Name
xfr.DestinationDatabase = newDB.Name
xfr.CreateTargetDatabase = True
xfr.DropDestinationObjectsFirst = True
xfr.Options.IncludeIfNotExists = True
xfr.TargetDatabaseFilePath = "C:\Test\CopyOfTestDB.mdf"
xfr.TargetLogFilePath = "C:\Test\CopyOfTestDB_log.ldf"
Try
xfr.TransferData()
Catch ex As Exception
Console.WriteLine("Error: " & ex.Message)
End Try
' ***I still got the same error, despite all the extra work of creating the connection to the database using the method above.
srv = Nothing
source = Nothing
The conclusion:
Aaron, your method will not work for my purposes. I will require a User Instance connection to SQL Server Express (it seems like User Instances are the only way to connect to a database stored a couple folder levels in the C: drive), and your method of connecting to the server and attaching a database does not work for my purposes will not work.
Also (and this is very important), I still got the same "Exception from HRESULT: 0xC0011008" error.
So, on to more ideas (hopefully).... Thank you for your help so far, Aaron.
|||
Hi Euan,
As you can see from the thread, Aaron's suggestion did not solve my problem. Have you heard anything yet from the PM/Arch for SMO?
Thanks,
AL
|||ALight,
Sorry about that, I thought the problem was something different. Did you ever get a solution?
|||
Sorry it took so long to respong, Aaron. No, I never found a solution to the problem. I'm guessing that SMO.Transfer simply will not work with User Instance server connections or databases.
However, I did find a better way, and probably one of the few ways, with ClickOnce/SQL Server Express apps:
Use build scripts to create and update the database. The information that I used was found in this White Paper:
http://msdn2.microsoft.com/en-us/library/bb264562.aspx
Scroll halfway down to the section entitled "Updating ClickOnce Deployments that Use SQL Server Express".
Certainly not as easy as SMO, but that will work.
Thanks for your help.
Andre
