In some of our Excel workbooks with external data queries to SQL Server 2000 setup, the connection string for the table is:
ODBC;DRIVER=SQL Server;SERVER=SQL1;UID=Fred;APP=Microsoft Office 2003;WSID=PC1;DATABASE=DB1;Trusted_Connection=Yes
The above worked ok until we renamed the SQL1 instance to something else (eg. SQLServer) and introduced a DNS alias called 'SQL1'. All other apps connect ok using the new DNS alias, 'SQL1'. However, since the change, some spreadsheets with external data queries have been failing with the message:
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not find server 'SQL1' in sysservers. Execute sp_addlinedserver to add the server to sysservers
It's of course true that 'SQL1' isn't in syservers because the server was renamed to something else. However, the new DNS alias 'SQL1' points to what used to be the SQL1 SQL Server instance (eg. 'SQLServer'). It appears to be that ODBC isn't attempting to find the server via DNS.
For now, we are working around the problem by changing the connection strings not to use the 'SQL1' DNS alias but we prefer to use the alias (eg. easier DR failover). Can someone tell me if this can be fixed so that these spreadsheet connections to SQL Server use the DNS alias?