Updating connection for BDC to a different SQL Server

Question

Is there a way other than exporting the BDC definition, changing the xml, and re-importing the definition to change the connection for the BDC application to a different server. We are setting up a separate development SQL server and need to update the BDC.

Answer 1

Not really as the BDC is entirely based on the definition. However you can use a SQL alias and change the alias to point to a different server.

For more information on how to setup an alias there's a good writeup here:

http://www.mssqltips.com/tip.asp?tip=1620

Answer 2

You can use the BDC Administration API to update the definition programatically. Another option might be BDC Meta Man web edition if it supports that.