Changing/Igoring text in stored procedures

Brian Donahue

Hi Ben,

I think that the best thing to do for now would be to do the
modifications outside of the SQLCompare.Engine namespace. If you are writing
an application that is running the migration SQL rather than simply saving a
file to disk, you could use System.Data.SqlClient to run the migration SQL
produced by Toolkit and modify the SQL before you run it. Here is an example
of what I mean:

/* Assume you've already done a comparison and have an ExecutionBlock
returned by BuildFromDifferences */
ExecutionBlock eb = obWork.ExecutionBlock;
Batch b;
SqlConnection conn=new
SqlConnection("Server="+sDBTargetServer+";INITIAL
CATALOG="+sDBTargetDatabase);
SqlCommand sc=new SqlCommand();
sc.Connection=conn;
for (int iCount=0; iCount<eb.BatchCount; iCount++)
{
b=eb.GetBatch(iCount);
/* Replace server1 with server2 before running this! */
sc.CommandText=b.Contents.Replace("Server1", "Server2");
try
{
if (!b.Marker) sc.ExecuteNonQuery();
}
catch (SqlException se)
{
Console.WriteLine("SQL Execution failed with error:
"+se.Number);
}
}

I hope this helps!

Regards,

Brian Donahue
Red Gate Technical Support

"Benjamin S. Lam" <blam@appliedsystems.com> wrote in message
news:BszKmv%23eEHA.1224@server53...
> Hi!
>
> I'm currently evaluating the toolkit.
>
> We have a number of related databases and in many of the stored procedures
> and views we use 3 and 4-part names. When we move database objects to a
> different environment (QA, Production), we replace the server and/or
> database names in the objects.
>
> So, when I compare from my Development servers to my Production or QA
> servers, I know that all instances of "server1.database1.dbo.SP1" should
be
> replaced with "server2.database1.dbo.SP1" or "database1.dbo.SP1" should be
> replaced with "database2.dbo.SP1" so that I don't end up with thousands of
> differences that I expect.
>
> Is there anyway to do this string replacement before starting the compare
> after either registering a live database connection or loading the
database
> from disk? For instance, I looked at modifying the "Text" property of an
> Item of the StoredProcedures collection, but this property is read-only.
Is
> there another way to do this or would it be possible to make the "Text"
> property writable as well?
>
> Thanks for your time and assistance,
> Ben Lam
>
>