We recently upgraded our database servers from Microsoft Windows 2000 Server to 2003. A DTS package I made a while ago suddenly returned an error while executing. It’s a routine that will be included in our upcoming ‘campaign management’ part of our CRM module. It’s basically mass mailing with merging features. So you can send out e-mails to all your customers within a certain segment and personalise the e-mail with name. We used it internally when sending out newsletters to our customers and also in 24SevenSync (similar to Plaxo).
But back to the server and the error. The error occured when initialising the ADO connection object. The mail server is a linux server and uses MySQL. It is not part of the 24SevenOffice core application and thus I though the problem was with the Linux server. But after a while and some debugging I noticed the error message said ‘Microsoft OLE DB Provider for SQL Server’ even though the connection string was using the MySQL drivers. Turns out that Windows 2003 Server does not allow to use the same connection object against Microsoft SQL Server first and then to a MySQL database. I had to explicitly set the connection object to nothing and then recreate it. Then it worked like a charm again :)
It seems like this is a bug in Windows 2003 Server, ADO or ODBC. The error is from ODBC but ADO is the most likely cause of the problem.
If any others encounters the same problem:
Error string: Invalid connection string attribute Error source: Microsoft OLE DB Provider for SQL Server
Instead of this code which works fine in Windows 2000 Server:
set c_db = createObject("ADODB.Connection")
c_db.open ConnStringSQLSever
' Do something MSSQL
c_db.close
c_db.open ConnStringMySQL
' Do something MySQL
c_db.close
set c_db = Nothing
Use this workaround which also works in Windows 2003 Server:
set c_db = createObject("ADODB.Connection")
c_db.open ConnStringSQLSever
' Do something MSSQL
c_db.close
set c_db = Nothing
set c_db = createObject("ADODB.Connection")
c_db.open ConnStringMySQL
' Do something MySQL
c_db.close
set c_db = Nothing