Easiest Way to Generate Scripts from API

crancoder

What is the easiest way to generate a SQL (as opposed to an XML) schema script and a data (records) script using the API?

Thank you.

crancoder

I should clarify - by "scripts", I mean full schema and data dumps.

I'm able to create a full schema script by using regions , but have not yet figured out how to do a full data dump.

Thank you.

richardjm

If you just want to generate the SQL required to move the data from one db to another you need to use the SQL Data Compare API. Full details and examples can be found at...

http://www.red-gate.com/support/SQL_Too ... ndline.htm

HTH

crancoder

Richard - Thanks, but that link isn't real helpful. Can you please be more specific? Your description of what I want to do is correct.

Thank you.

richardjm

Well there are samples for the code to transfer the data from one db to another. Copied verbatim from the sample file available as a download off that link....

using System;
using RedGate.SQL.Shared;
using RedGate.SQLCompare.Engine;
using RedGate.SQLDataCompare.Engine;

namespace SQLDataCompareCodeSnippets
{
	public class SqlProviderExample
	{
		
		public void RunExample()
		{
			Database db1=new Database();
			Database db2=new Database();
    
			db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"), Options.Default);
			db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);
    
			// Create the mappings between the two databases
			TableMappings mappings = new TableMappings();
			mappings.CreateMappings(db1.Tables, db2.Tables);
    
			//
			//Additionally set up trim trailing spaces...
			//
			mappings.Options = new EngineDataCompareOptions( 
				MappingOptions.Default, 
				ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default, 
				SqlOptions.Default); 

			using (ComparisonSession session=new ComparisonSession())
			{
				//
				// Remember to set up the session options
				//
				session.Options = mappings.Options;
				session.CompareDatabases(db1, db2, mappings);

				// now get the ExecutionBlock containing the SQL
				// we want to run this on WidgetLive so we pass on true as the second parameter
				SqlProvider provider=new SqlProvider();            
				//
				// Also rememeber to set up the provider options
				//
				provider.Options = session.Options;
				ExecutionBlock block;
				try
				{
					block = provider.GetMigrationSQL(session, true);
    
					Console.WriteLine("The synchronization SQL contains {0} lines in {1} batches", block.LineCount, block.BatchCount);
    
					// if the ExecutionBlock was very large this could cause memory problems
					Console.WriteLine("The SQL to be run is:");
					Console.WriteLine(block.GetString());
    
					// we can access the SQL in a memory efficient manner by accessing the underlying stream
					// FileStream stream=block.GetFileStream();
    
					// run the SQL ( commented out by default )
					// BlockExecutor executor = new BlockExecutor();
					// executor.ExecuteBlock(block, ".", "WidgetLive");
				}
				finally
				{
					block = provider.Block;
					if (block != null)
					{
						block.Dispose();    // dispose of the objects to delete temporary files
					}
				}
			}
			db1.Dispose();
			db2.Dispose();
		}
	}
}

If you want more detail on each step go through the worked example in the SQL Data Compare API documentation here...

http://help.red-gate.com/help/SQLDataCo ... frame.html[/url]

crancoder

I've tried the sample code and it doesn't seem to work if the target database is empty. Either an error is raised, or the generated script calls for deletes rather than inserts.

Thank you.

richardjm

Right in that case if the target database has no schema present you need to use the SQL Packager API, also you can use a call to CompareDatabases where db2 is null, similarly with CreateMappings. Have a look at the SQL Packager API or adapt the above sample so that db2 is null.

HTH

crancoder

Obviously, I'm doing something wrong.
I revised your code as suggested (below) and got the following results:

The synchronization SQL contains 11 lines in 11 batchesThe SQL to be run is:SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)

BEGIN TRANSACTION

COMMIT TRANSACTION
GO



REVISED CODE:
public TestDump()
{
InitializeComponent();
Database db1=new Database();

db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"), Options.Default);

// Create the mappings between the two databases
TableMappings mappings = new TableMappings();
mappings.CreateMappings(db1.Tables, null);

//
//Additionally set up trim trailing spaces...
//
mappings.Options = new EngineDataCompareOptions(
MappingOptions.Default,
ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
SqlOptions.Default);

using (ComparisonSession session=new ComparisonSession())
{
//
// Remember to set up the session options
//
session.Options = mappings.Options;
session.CompareDatabases(db1, null, mappings);

// now get the ExecutionBlock containing the SQL
// we want to run this on WidgetLive so we pass on true as the second parameter
SqlProvider provider=new SqlProvider();
//
// Also rememeber to set up the provider options
//
provider.Options = session.Options;
ExecutionBlock block;
try
{
block = provider.GetMigrationSQL(session, true);

rtfResults.AppendText("The synchronization SQL contains "+block.LineCount+" lines in "+block.BatchCount+" batches");

// if the ExecutionBlock was very large this could cause memory problems
rtfResults.AppendText("The SQL to be run is:");
rtfResults.AppendText(block.GetString());

// we can access the SQL in a memory efficient manner by accessing the underlying stream
// FileStream stream=block.GetFileStream();

// run the SQL ( commented out by default )
// BlockExecutor executor = new BlockExecutor();
// executor.ExecuteBlock(block, ".", "WidgetLive");
}
finally
{
block = provider.Block;
if (block != null)
{
block.Dispose(); // dispose of the objects to delete temporary files
}
}
}
db1.Dispose();
}

crancoder

Finally got it using code from the SQLPackager API samples.

Setting SelectionDelegate to true seems to do the trick.

Thanks again.

richardjm

Great glad you've got it working