Red Gate forums :: View topic - Easiest Way to Generate Scripts from API
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Toolkit Previous Versions
SQL Toolkit Previous Versions forum

Easiest Way to Generate Scripts from API

Search in SQL Toolkit Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
crancoder



Joined: 03 Jan 2008
Posts: 20

PostPosted: Thu Jan 17, 2008 4:11 pm    Post subject: Easiest Way to Generate Scripts from API Reply with quote

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.
Back to top
View user's profile Send private message
crancoder



Joined: 03 Jan 2008
Posts: 20

PostPosted: Fri Jan 18, 2008 2:43 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 383
Location: Red Gate Software Ltd

PostPosted: Fri Jan 18, 2008 3:22 pm    Post subject: Reply with quote

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_Toolkit/FAQ/toolkit_api_commandline.htm

HTH
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd
Back to top
View user's profile Send private message Send e-mail
crancoder



Joined: 03 Jan 2008
Posts: 20

PostPosted: Fri Jan 18, 2008 3:25 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 383
Location: Red Gate Software Ltd

PostPosted: Fri Jan 18, 2008 3:47 pm    Post subject: Reply with quote

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:

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/SQLDataCompareAPIv5/4/en/webframe.html[/url]
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd
Back to top
View user's profile Send private message Send e-mail
crancoder



Joined: 03 Jan 2008
Posts: 20

PostPosted: Fri Jan 18, 2008 3:52 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 383
Location: Red Gate Software Ltd

PostPosted: Fri Jan 18, 2008 4:11 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
crancoder



Joined: 03 Jan 2008
Posts: 20

PostPosted: Fri Jan 18, 2008 4:27 pm    Post subject: Reply with quote

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();
}
Back to top
View user's profile Send private message
crancoder



Joined: 03 Jan 2008
Posts: 20

PostPosted: Fri Jan 18, 2008 5:36 pm    Post subject: Reply with quote

Finally got it using code from the SQLPackager API samples.

Setting SelectionDelegate to true seems to do the trick.

Thanks again.
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 383
Location: Red Gate Software Ltd

PostPosted: Mon Jan 21, 2008 9:28 am    Post subject: Reply with quote

Great glad you've got it working Smile
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group