Easiest Way to Generate Scripts from API

Forum for users of SQL Toolkit 3,4,and 5

Easiest Way to Generate Scripts from API

Postby crancoder » Thu Jan 17, 2008 4:11 pm

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
 
Posts: 20
Joined: Thu Jan 03, 2008 8:40 pm

Postby crancoder » Fri Jan 18, 2008 2:43 pm

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.
crancoder
 
Posts: 20
Joined: Thu Jan 03, 2008 8:40 pm

Postby richardjm » Fri Jan 18, 2008 3:22 pm

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
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Postby crancoder » Fri Jan 18, 2008 3:25 pm

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.
crancoder
 
Posts: 20
Joined: Thu Jan 03, 2008 8:40 pm

Postby richardjm » Fri Jan 18, 2008 3:47 pm

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....

Code: Select all
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]
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Postby crancoder » Fri Jan 18, 2008 3:52 pm

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.
crancoder
 
Posts: 20
Joined: Thu Jan 03, 2008 8:40 pm

Postby richardjm » Fri Jan 18, 2008 4:11 pm

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
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Postby crancoder » Fri Jan 18, 2008 4:27 pm

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
 
Posts: 20
Joined: Thu Jan 03, 2008 8:40 pm

Postby crancoder » Fri Jan 18, 2008 5:36 pm

Finally got it using code from the SQLPackager API samples.

Setting SelectionDelegate to true seems to do the trick.

Thanks again.
crancoder
 
Posts: 20
Joined: Thu Jan 03, 2008 8:40 pm

Postby richardjm » Mon Jan 21, 2008 9:28 am

Great glad you've got it working :)
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd


Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests