Can the Application Name passed to SQL Server (via the ADO Connection string) be changed once the connection is open without closing and re-opening the connection?
In particular, I'm using ADO, not ADO.NET, and the SQLOLEDB.1 OLE DB Provider.
We embed the session information in the Application Name, to help us identify the connection specific to a user's session. This primarily helps when troubleshooting bad queries or performance issues.
We currently do not use a connection pool for our ADO connections. I'm considering implementing a connection pool, but would like to update the Application Name when a connection is removed from the pool, so that we have accurate session information in the Application Name.
I would doubt that you can alter any attribute of a connection string after the connection has been created (in the pool or not). Connections are assigned to pools based on the value of the connection string itself, so if you could change the application name after the connection has been picked from the pool, it would essentially belong to a differnt pool from that point on, which would likely mess up connection pool management internally.
Furthermore, the application name is used as (I think you already know that as you said you're trying to use it for performance and troubleshooting reasons) to populate the program_name column of sys.dm_exec_sessions. So unless there is a way that this value can change for an existig connection, I think there is no way to do it from ADO.NET either.
Having that said, I use the application name for the same purpose. I just "hardcode" the application name to some symbolic string (e.g. "myapp-client") and that has always been sufficient to figure the exact calling application on a crowed server (at least together with the host_process_id column of sys.dm_exec_sessions).