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

ExtendedProperties Performance

Has anyone found a way to speed up the retrival of the "ExtendedProperties" on a column via SMO yet?

I had a search around but couldn't spot anything. I have an applicaiton that currently cyles through 180+ tables (with the number growing all the time) and the "ExtendedProperties" access is abosultly killing it ... taking it from seconds to minutes.

If anyone could provide any information (even if it's a "no it's not possible to speed up") or an alternative retrival method I'd greatly appreciate it.

Have you tried using the SetDefaultInitFields method to include the ExtendedProperties object? Insert this code after you connect to the server:

srv.SetDefaultInitFields(GetType(Table), "ExtendedProperties")

|||

I'm retriving the ExtendedProperties from Columns, not Tables :)

I've tried including the SetDefualtInitFields(typeof(Column), "ExtendedProperties") but it doesn't have much effect.

The people in this thread are having the same problem: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=571167&SiteID=1

|||

ExtendedProperties is a collection not a property therefore your solution will not work.

Try this:

Database db = ... // get your database root

ScriptingOptions so = new ScriptingOptions();

db.PrefetchObject(typeof(Table), so);

so.ExtendedProperties = true;

db.PrefetchObjects(typeof(Column), so);

Let me know if it works.

Ciprian Gerea, SqlServer SDE

|||

This,

"this.o_Database.PrefetchObjects(typeof(Column), scriptingOptions);"

doesn't work, I think this is because it is a child collection on Table rather than on Database.

But,

ScriptingOptions scriptingOptions = new ScriptingOptions();

scriptingOptions.ExtendedProperties = true;

this.o_Database.PrefetchObjects(typeof(Table), scriptingOptions);

works wonderfully :D It seems it prefetchs the ExtendedProperties on the Columns as well as the Tables!

Thanks for helping me optimize my 5 minute loop into a 10 second one :D

Extended Properties

When I add an extended property using SMO, it doesn't show up on the Properties of the table in SQL Management Studio. Also, if I add one in Managment Studio, I can't see it using SMO. What am I missing here?

Can you please provide some more information on how you are trying to add and retrieve the Extended Properties using SMO. And also what version of SQL Server are you using.

Your code should look similar to -

Table t = mydb.Tables["tableName"];

foreach(ExtendedProperty exp in t.ExtendedProperties)

{

Console.WriteLine(exp.Name + " " + exp.Value);

}

t.ExtendedProperties.Add(new ExtendedProperty("propertyName", "property value"));

|||My code is pretty much just like that. I am using SQL Server 2005.|||

Here is the code sample which I found working, let me know if you face any issues.

Server server = newServer("localhost");

Table table = server.Databases["mydatabase"].Tables["mytable"];

table.ExtendedProperties.Add(newExtendedProperty(table, "propertyName", "propertyValue"));

table.Alter();

table.Refresh();

foreach (ExtendedProperty e in table.ExtendedProperties)

{

Console.WriteLine(e.Name + " " + e.Value.ToString());

}

Thanks,

Kuntal

Extended Properties

When I add an extended property using SMO, it doesn't show up on the Properties of the table in SQL Management Studio. Also, if I add one in Managment Studio, I can't see it using SMO. What am I missing here?

Can you please provide some more information on how you are trying to add and retrieve the Extended Properties using SMO. And also what version of SQL Server are you using.

Your code should look similar to -

Table t = mydb.Tables["tableName"];

foreach(ExtendedProperty exp in t.ExtendedProperties)

{

Console.WriteLine(exp.Name + " " + exp.Value);

}

t.ExtendedProperties.Add(new ExtendedProperty("propertyName", "property value"));

|||My code is pretty much just like that. I am using SQL Server 2005.|||

Here is the code sample which I found working, let me know if you face any issues.

Server server = new Server("localhost");

Table table = server.Databases["mydatabase"].Tables["mytable"];

table.ExtendedProperties.Add(new ExtendedProperty(table, "propertyName", "propertyValue"));

table.Alter();

table.Refresh();

foreach (ExtendedProperty e in table.ExtendedProperties)

{

Console.WriteLine(e.Name + " " + e.Value.ToString());

}

Thanks,

Kuntal