Extract data from SQL Server 2005 by SMO or DMO

I am running an old script generator using SQL-DMO. Even on SQL Server 2005 it is working fine, but the new features like xml data type are not supported. So I switched to SMO. At the first view it looks pretty cool and easy. I changed the properties in the following source a thousand times but it doesn’t script any data to the file. Is it a bug or a stupid misunderstanding?

Transfer t = new Transfer(db);

t.CopyAllObjects = false;

t.CopyAllTables = true;

t.CopyData = true;

//t.Options.WithDependencies = true;

t.Options.ContinueScriptingOnError = true;

t.DestinationServer = "PC-E221\\SQLEXPRESS";

t.DestinationDatabase = "TestAgent";

t.DestinationLoginSecure = true;

t.CreateTargetDatabase = true;

t.Options.AllowSystemObjects = false;

t.Options.FileName = "testFile.sql";

t.Options.IncludeDatabaseContext = true;

t.Options.ToFileOnly = true;

Best regards

Wolfgang

Smo is a tool for generating scripts / manitaining the database not scripting the data out.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

thanks for your response.

1. If it is so, what does ".CopyData= true" mean, if it doesn't copy data?

2. How can I copy data, if DMO doesn't work either? As I said before, DMO doesn't copy xml data types.

br

Wolfgang

|||1. That is related to the TransferData method which will use DTS behind the scenes to transfer the data (read this somewhere sometime).

2. You could use a scripting utility like this here: http://vyaskn.tripod.com/code.htm to do the job. i don′t know if this is capable of using XMlL txypes, but its worth a try, because it can be really quick tested.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de