Teeeeejirrrrr

You’re probably here because you searched for this:

Microsoft SQL-DMO (ODBC SQLState:42000)

[Microsoft][ODBC SQL Server Driver][SQL Server] To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO).

Bonus points if you’re using the Data Transformation Services and are now looking at an error screen similar to this:

Well, I unfortunately don’t have much substantial solutions to offer you. I apologize.

Connectivity MAY work if you use the SQL Native Client (SQLNCLI)  instead  of an OLE DB connection, but this is shaky at times and a much more complicated solution when wrapped into DTS.

If you’re looking to copy entire databases, then look here:

http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

If you’re looking to only pick and choose a few tables at a time, then that’s a different story altogether.

My solution was to use a horrifying transmogrifying method of SQL to CSV and back to SQL again for the few tables that we needed.

I try to be vendor-agnostic when it comes to any sort of IT solutions, but Microsoft has become increasingly infuriating and difficult to defend over the years.

It seems that they just wanted to stick a way in to make data interoperability between SQL 2005 and older versions in order to force their customers upgrade. In our case it might just work from them. The particular server that we have still running SQL 2000 is tied to an archaic legacy application which is poorly supported. We SHOULD upgrade it, but we can’t without breaking it.

This is another example of how they are not only shooting themselves in the foot, but their dwindling enterprise customer base as well.

So in the end, all I can offer is this diagram, while I prepare an upgrade path that does not ultimately involve Windows or  MSSQL Server: