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

Extract data from SQL 6.5

I need to get some data from an enormous, creaky old SQL 6.5 database.
I know nothing about either the data schema (though I believe some sort
of documentation exists), nor 6.5 for that matter, having come to SQL
Server at 7.0.

My clients need the data in comma delimited format.

Please, can anyone suggest any possibilities? One thing that occurred
to me might be to create an Access application, use an ODBC link to the
SQL DB, and then leverage Access' not inconsiderable functionality to
get the data out.

Does anyone foresee any problems with this, or any better ways?

Forever in your debt.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.ukI also came to SQL Server well after version 6.5 but I believe that 6.5
had a version of Enterprise Manager (EM) and that EM supported access
to the 6.5 equilivent of DTS. If so you should be able to use the tool
to do what you need without being 'knowledgable'.

Between the GUI and a manual I have managed to stumble through the
first time I have had to perform certain tasks.

HTH -- Mark D Powell --|||(teddysnips@.hotmail.com) writes:
> I need to get some data from an enormous, creaky old SQL 6.5 database.
> I know nothing about either the data schema (though I believe some sort
> of documentation exists), nor 6.5 for that matter, having come to SQL
> Server at 7.0.
> My clients need the data in comma delimited format.
> Please, can anyone suggest any possibilities? One thing that occurred
> to me might be to create an Access application, use an ODBC link to the
> SQL DB, and then leverage Access' not inconsiderable functionality to
> get the data out.
> Does anyone foresee any problems with this, or any better ways?

If you know Access and is comfortable with that, I guess it will work
fine.

You cold also use BCP, and you could use the BCP that comes with SQL
Server 2000. (I do seem recall that there were some problems when accessing
SQL 6.5 if the database+table names were too long.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp