Login failed

VBourdagesVBourdages Posts: 8
edited October 7, 2005 3:26PM in SQL Toolkit Previous Versions
Hi,

I am trying to Data compare and synchronize 2 databases... I am able to compare, (so the login is working), but when I try to synchronize the databases I am always receiving this error message.

{"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." }

I am passing the password like this...
scp1=new SqlConnectionProperties(sourceServer, sourceDbName, sourceUser, sourcePw); 
scp2=new SqlConnectionProperties(targetServer, targetDbName, targetUser, targetPw);

Should I pass the User and password somewhere else for the synchronization?...

The user is not part of the domain, so I am "SQL Server connection authentication", not the Windows authentication.

The error occur here...
try
{
 utils.ExecuteBlock(block, targetServer, targetDbName);
}
 catch( Exception ex )
{
 System.Console.Out.Write( ex.Message );
}

Here the full code I am using: (please help, thanks)
namespace RegGateReplication
{
	/// <summary>
	/// Class TablesReplication: synchronization between two databases, 
	/// the database names and the tables list are located into the app.config file.
	/// </summary>
	public class TablesReplication
	{
		public ArrayList logList;
		public string sourceDbName="";
		public string targetDbName="";
		public string sourceServer="";
		public string targetServer="";

		public string SQLIntegratedSecurity="";
		public string sourceUser="";
		public string targetUser="";
		public string sourcePw="";
		public string targetPw="";
		public Database db1;
		public Database db2;
		public SqlConnectionProperties scp1;
		public SqlConnectionProperties scp2;
	    public string db1FieldValue=""; 
		public string db2FieldValue=""; 
		//Constructor
		public TablesReplication()
		{ 
			logList = new ArrayList();

			sourceDbName = System.Configuration.ConfigurationSettings.AppSettings["SourceDatabase"];
			targetDbName = System.Configuration.ConfigurationSettings.AppSettings["TargetDatabase"];
			sourceServer = System.Configuration.ConfigurationSettings.AppSettings["SourceServer"];
			targetServer = System.Configuration.ConfigurationSettings.AppSettings["TargetServer"];

			SQLIntegratedSecurity = System.Configuration.ConfigurationSettings.AppSettings["IntegratedSecurity"];
			sourceUser = System.Configuration.ConfigurationSettings.AppSettings["SourceUser"];
			targetUser = System.Configuration.ConfigurationSettings.AppSettings["TargetUser"];
			sourcePw = System.Configuration.ConfigurationSettings.AppSettings["SourcePw"];
			targetPw = System.Configuration.ConfigurationSettings.AppSettings["TargetPw"];

		}

		public void RunTablesReplication()
		{   
			
			//Red-Gate Create Database connection...
			SqlProvider provider=new SqlProvider();
			if (SQLIntegratedSecurity.ToUpper() == "TRUE")
			{
scp2=new SqlConnectionProperties(targetServer, targetDbName);
				scp1=new SqlConnectionProperties(sourceServer, sourceDbName); 
				scp2=new SqlConnectionProperties(targetServer, targetDbName);
				scp1.IntegratedSecurity = true;
				scp2.IntegratedSecurity = true;
			}
			else
			{
				scp1=new SqlConnectionProperties(sourceServer, sourceDbName, sourceUser, sourcePw); 
				scp2=new SqlConnectionProperties(targetServer, targetDbName, targetUser, targetPw);
				scp1.IntegratedSecurity = false;
				scp2.IntegratedSecurity = false;
			}
			db1=provider.GetDatabase(scp1);
			db2=provider.GetDatabase(scp2);
			
	
			//Red-Gate Compare Database...
			ComparisonSession session=new ComparisonSession();
			TableComparisonSettings settings=new TableComparisonSettings();

			Tables commonTables=Tables.CreateFromIntersection(db1.Tables, db2.Tables);
			 
			
			NameValueCollection tablelist = (NameValueCollection)ConfigurationSettings.GetConfig("Tables");

			foreach(string item in tablelist.Keys)
			{
				foreach (Table table in commonTables)
				{
					if (item.ToUpper() == table.Name.ToUpper()) 
					{
						settings.Add(new TableComparisonSetting(table.FullyQualifiedName, table.Fields, table.PrimaryKey.Fields));
					}
				}
			}

			session.CompareDatabases(db1, db2, settings);
			//Red-Gate show Compare Database result...

			logList.Add("Database Compare");
			logList.Add("Comparing Source DB:["+sourceServer+"]-"+sourceDbName+"with Target DB:["+targetServer+"]-"+targetDbName);
			logList.Add(String.Format("Starting at: {0}", DateTime.Now));
			foreach (TableDifference difference in session.TableDifferences)
			{
				//set this to true so the difference will be included in the migration SQL
				difference.Selected=true;

				logList.Add(String.Format("{0}", difference.Name));
				logList.Add("=======");

				logList.Add(String.Format("{0} identical records, {1} different records, {2} records only present in {4}, {3} records only present in {5}", difference.SameCount, difference.DifferentCount, difference.In1Count, difference.In2Count, sourceDbName, targetDbName));

				//print out the different records

				ResultsStore differentRecords=difference.DifferentRecords;

				logList.Add("Different records");
				logList.Add("=================");

				//loop through the rows

				for (long rowIndex=0; rowIndex<differentRecords.RowCount; rowIndex++) 
				{
					
					logList.Add(String.Format("Row {0}", rowIndex)); 
					logList.Add("-----"); 
	
					//loop through the fields
		
					Row row=differentRecords.GetRow(rowIndex);
		
					foreach (Field field in difference.TableComparisonSetting.Fields)
					{ 
						if (field.OrdinalInResults1>=0)
						{
							//there are two records in the ResultsStore for this field

							object value1=row.Values[field.OrdinalInResults1];
							object value2=row.Values[field.OrdinalInResults2];


							if (value1 == null)
							{
								db1FieldValue="Null"; 
							}
							else
							{
								db1FieldValue=value1.ToString(); 
							}
							if (value2 == null)
							{
								db2FieldValue="Null"; 
							}
							else
							{
								db2FieldValue=value1.ToString(); 
							}


							if (ResultsStore.IdenticalValues(value1, value2))
							{
								//logList.Add(String.Format("{0} {1} == {2}", field.Name, value1.ToString(), value2.ToString()));
								logList.Add(String.Format("{0} {1} == {2}", field.Name, db1FieldValue, db2FieldValue));

							}
							else
							{
								//logList.Add(String.Format("{0} {1} <> {2}", field.Name, value1.ToString(), value2.ToString()));
								logList.Add(String.Format("{0} {1} <> {2}", field.Name, db1FieldValue, db2FieldValue));
							}
						}
						else
						{
							//there is only one record in the ResultsStore
							//the field is part of the unique index
							object value=row.Values[field.OrdinalInResults];

							logList.Add(String.Format("{0} ** {1}", field.Name, value.ToString()));
						}
					}
				}

			}

			//Begin synchronization...
			//we want to run the SQL on db2 so pass in true as the second parameter
			ExecutionBlock block=provider.GetMigrationSQL(session, true);
			logList.Add("Migration SQL:");
			//logList.Add(block.ToString());

			//Synchronize...
			Utils utils=new Utils();
			try
			{
				utils.ExecuteBlock(block, targetServer, targetDbName);
			}
			catch( Exception ex )
			{
				System.Console.Out.Write( ex.Message );
			}
			logList.Add( "Ending at: " + DateTime.Now.ToString() );

			// Free stuff
			session.Dispose();
			block.Dispose();



		
		}

	}
	}

Thanks[/quote]
· Share on Twitter

Comments

  • VBourdagesVBourdages Posts: 8
    Oups sorry... Missing End of post.

    Here the full code I am using: (please help, thanks)

    namespace RegGateReplication
    {
    /// <summary>
    /// Class TablesReplication: synchronization between two databases,
    /// the database names and the tables list are located into the app.config file.
    /// </summary>
    public class TablesReplication
    {
    public ArrayList logList;
    public string sourceDbName="";
    public string targetDbName="";
    public string sourceServer="";
    public string targetServer="";

    public string SQLIntegratedSecurity="";
    public string sourceUser="";
    public string targetUser="";
    public string sourcePw="";
    public string targetPw="";
    public Database db1;
    public Database db2;
    public SqlConnectionProperties scp1;
    public SqlConnectionProperties scp2;
    public string db1FieldValue="";
    public string db2FieldValue="";
    //Constructor
    public TablesReplication()
    {
    logList = new ArrayList();

    sourceDbName = System.Configuration.ConfigurationSettings.AppSettings["SourceDatabase"];
    targetDbName = System.Configuration.ConfigurationSettings.AppSettings["TargetDatabase"];
    sourceServer = System.Configuration.ConfigurationSettings.AppSettings["SourceServer"];
    targetServer = System.Configuration.ConfigurationSettings.AppSettings["TargetServer"];

    SQLIntegratedSecurity = System.Configuration.ConfigurationSettings.AppSettings["IntegratedSecurity"];
    sourceUser = System.Configuration.ConfigurationSettings.AppSettings["SourceUser"];
    targetUser = System.Configuration.ConfigurationSettings.AppSettings["TargetUser"];
    sourcePw = System.Configuration.ConfigurationSettings.AppSettings["SourcePw"];
    targetPw = System.Configuration.ConfigurationSettings.AppSettings["TargetPw"];

    }

    public void RunTablesReplication()
    {

    //Red-Gate Create Database connection...
    SqlProvider provider=new SqlProvider();
    if (SQLIntegratedSecurity.ToUpper() == "TRUE")
    {
    scp2=new SqlConnectionProperties(targetServer, targetDbName);
    scp1=new SqlConnectionProperties(sourceServer, sourceDbName);
    scp2=new SqlConnectionProperties(targetServer, targetDbName);
    scp1.IntegratedSecurity = true;
    scp2.IntegratedSecurity = true;
    }
    else
    {
    scp1=new SqlConnectionProperties(sourceServer, sourceDbName, sourceUser, sourcePw);
    scp2=new SqlConnectionProperties(targetServer, targetDbName, targetUser, targetPw);
    scp1.IntegratedSecurity = false;
    scp2.IntegratedSecurity = false;
    }
    db1=provider.GetDatabase(scp1);
    db2=provider.GetDatabase(scp2);


    //Red-Gate Compare Database...
    ComparisonSession session=new ComparisonSession();
    TableComparisonSettings settings=new TableComparisonSettings();

    Tables commonTables=Tables.CreateFromIntersection(db1.Tables, db2.Tables);


    NameValueCollection tablelist = (NameValueCollection)ConfigurationSettings.GetConfig("Tables");

    foreach(string item in tablelist.Keys)
    {
    foreach (Table table in commonTables)
    {
    if (item.ToUpper() == table.Name.ToUpper())
    {
    settings.Add(new TableComparisonSetting(table.FullyQualifiedName, table.Fields, table.PrimaryKey.Fields));
    }
    }
    }

    session.CompareDatabases(db1, db2, settings);
    //Red-Gate show Compare Database result...

    logList.Add("Database Compare");
    logList.Add("Comparing Source DB:["+sourceServer+"]-"+sourceDbName+"with Target DB:["+targetServer+"]-"+targetDbName);
    logList.Add(String.Format("Starting at: {0}", DateTime.Now));
    foreach (TableDifference difference in session.TableDifferences)
    {
    //set this to true so the difference will be included in the migration SQL
    difference.Selected=true;

    logList.Add(String.Format("{0}", difference.Name));
    logList.Add("=======");

    logList.Add(String.Format("{0} identical records, {1} different records, {2} records only present in {4}, {3} records only present in {5}", difference.SameCount, difference.DifferentCount, difference.In1Count, difference.In2Count, sourceDbName, targetDbName));

    //print out the different records

    ResultsStore differentRecords=difference.DifferentRecords;

    logList.Add("Different records");
    logList.Add("=================");

    //loop through the rows

    for (long rowIndex=0; rowIndex<differentRecords.RowCount; rowIndex++)
    {

    logList.Add(String.Format("Row {0}", rowIndex));
    logList.Add("
    ");

    //loop through the fields

    Row row=differentRecords.GetRow(rowIndex);

    foreach (Field field in difference.TableComparisonSetting.Fields)
    {
    if (field.OrdinalInResults1>=0)
    {
    //there are two records in the ResultsStore for this field

    object value1=row.Values[field.OrdinalInResults1];
    object value2=row.Values[field.OrdinalInResults2];


    if (value1 == null)
    {
    db1FieldValue="Null";
    }
    else
    {
    db1FieldValue=value1.ToString();
    }
    if (value2 == null)
    {
    db2FieldValue="Null";
    }
    else
    {
    db2FieldValue=value1.ToString();
    }


    if (ResultsStore.IdenticalValues(value1, value2))
    {
    //logList.Add(String.Format("{0} {1} == {2}", field.Name, value1.ToString(), value2.ToString()));
    logList.Add(String.Format("{0} {1} == {2}", field.Name, db1FieldValue, db2FieldValue));

    }
    else
    {
    //logList.Add(String.Format("{0} {1} <> {2}", field.Name, value1.ToString(), value2.ToString()));
    logList.Add(String.Format("{0} {1} <> {2}", field.Name, db1FieldValue, db2FieldValue));
    }
    }
    else
    {
    //there is only one record in the ResultsStore
    //the field is part of the unique index
    object value=row.Values[field.OrdinalInResults];

    logList.Add(String.Format("{0} ** {1}", field.Name, value.ToString()));
    }
    }
    }

    }

    //Begin synchronization...
    //we want to run the SQL on db2 so pass in true as the second parameter
    ExecutionBlock block=provider.GetMigrationSQL(session, true);
    logList.Add("Migration SQL:");
    //logList.Add(block.ToString());

    //Synchronize...
    Utils utils=new Utils();
    try
    {
    utils.ExecuteBlock(block, targetServer, targetDbName);
    }
    catch( Exception ex )
    {
    System.Console.Out.Write( ex.Message );
    }
    logList.Add( "Ending at: " + DateTime.Now.ToString() );

    // Free stuff
    session.Dispose();
    block.Dispose();




    }

    }
    }



    Thanks
    · Share on Twitter
  • VBourdagesVBourdages Posts: 8
    Again missing the end of the post... :cry:
    resending the missing part...

    Here it is...

    //print out the different records

    ResultsStore differentRecords=difference.DifferentRecords;

    logList.Add("Different records");
    logList.Add("=================");

    //loop through the rows

    for (long rowIndex=0; rowIndex<differentRecords.RowCount; rowIndex++)
    {

    logList.Add(String.Format("Row {0}", rowIndex));
    logList.Add("
    ");

    //loop through the fields

    Row row=differentRecords.GetRow(rowIndex);

    foreach (Field field in difference.TableComparisonSetting.Fields)
    {
    if (field.OrdinalInResults1>=0)
    {
    //there are two records in the ResultsStore for this field

    object value1=row.Values[field.OrdinalInResults1];
    object value2=row.Values[field.OrdinalInResults2];


    if (value1 == null)
    {
    db1FieldValue="Null";
    }
    else
    {
    db1FieldValue=value1.ToString();
    }
    if (value2 == null)
    {
    db2FieldValue="Null";
    }
    else
    {
    db2FieldValue=value1.ToString();
    }


    if (ResultsStore.IdenticalValues(value1, value2))
    {
    //logList.Add(String.Format("{0} {1} == {2}", field.Name, value1.ToString(), value2.ToString()));
    logList.Add(String.Format("{0} {1} == {2}", field.Name, db1FieldValue, db2FieldValue));

    }
    else
    {
    //logList.Add(String.Format("{0} {1} <> {2}", field.Name, value1.ToString(), value2.ToString()));
    logList.Add(String.Format("{0} {1} <> {2}", field.Name, db1FieldValue, db2FieldValue));
    }
    }
    else
    {
    //there is only one record in the ResultsStore
    //the field is part of the unique index
    object value=row.Values[field.OrdinalInResults];

    logList.Add(String.Format("{0} ** {1}", field.Name, value.ToString()));
    }
    }
    }

    }

    //Begin synchronization...
    //we want to run the SQL on db2 so pass in true as the second parameter
    ExecutionBlock block=provider.GetMigrationSQL(session, true);
    logList.Add("Migration SQL:");
    //logList.Add(block.ToString());

    //Synchronize...
    Utils utils=new Utils();
    try
    {
    utils.ExecuteBlock(block, targetServer, targetDbName);
    }
    catch( Exception ex )
    {
    System.Console.Out.Write( ex.Message );
    }
    logList.Add( "Ending at: " + DateTime.Now.ToString() );

    // Free stuff
    session.Dispose();
    block.Dispose();




    }

    }
    }
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Sorry about that -- We'd updated PHPBB yesterday and neglected to change the maximum read size to the SQL Server. Long story. You should see your whole post now!

    Anyhow, you will probably need to give the username and password to the ExecuteBlock method the same as you did when you registered the databases: 
    public void ExecuteBlock&#40; 
   ExecutionBlock block,
   string server,
   string database,
   bool integratedSecurity,
   string userName,
   string password
&#41;;
    · Share on Twitter
  • VBourdagesVBourdages Posts: 8
    Great!

    It is working now...


    Thanks.
    · Share on Twitter
This discussion has been closed.