Getting There was a problem connecting to site when using PurgeCommerceData.exe to purge deleted marketing items

I am using the following command to remove deleted marketing items from the marketing database:

purgecommercedata.exe MySite -m -d 0

When I do so, I am receiving the following error message:

"There was a problem connecting to site 'mysite'."

However, the purgecommercedata.exe command works successfully when dealing with baskets and purchase orders.

Also, I can successfully connect to the Marketing system using the Marketing Manager, and my web-site code accurately displays advertisements.

I am using Commerce Server 2007 SP1.

Has anyone else seen this problem, or have any suggestions as to how to fix it.

Thanks in advance

Chris

Hi Chris,

My guesses would be an strangely formed marketing or mailing list connection string. Failing that you could try attaching a debugger and seeing if there's an exception being thrown that would give more detail.

Hope this helps,

David

I have attached a debugger as you suggested and can confirm that the problem does relate to the connection strings.

However, I do not believe the following connection strings are strangely formed.

Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=DCP_Marketing;Data Source=TDD80DEV83;

Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=DCP_Marketing_Lists;Data Source=TDD80DEV83;

I have looked at the code of purgecommercedata.exe using reflector. The problem seems to occur in the CleanSqlClientConnectionString(...) method of the MarketingPurgeStrategy class. The logic in this method returns an empty string when the connection string contains "Provider=SQLOLEDB;". This in turn then results in the "problem connecting to site" error.

Is there a reason that connection strings with "Provider=SQLOLEDB" are being rejected by the console app. If so, what should the connection strings be changed to

I have found a work-around for the problem.

We have change all our connection strings to use "Provider=SQLOLEDB.1" rather than "Provider=SQLOLEDB"