Change layer's sqlWorkspace dynamically, datasource

Question

Hi everyone
I got query layers in a map document which point to sql server databases. I'm trying to find a way to change the database at which the query layer is pointing to. When assigning the new sqlWorkspace (with new connection properties) to an existing query layer an exception is raised saying "featureclass already exists". Some snippets of my code: 
IWorkspace workspace = workspaceFactory.Open(connectionProps, 0);
ISqlWorkspace sqlWorkspace = (ISqlWorkspace)workspace;
IQueryDescription desc = sqlWorkspace.GetQueryDescription("select * from " + tblName);
ITable queryClass = sqlWorkspace.OpenQueryClass("MyQuery", desc);
IFeatureClass fc = (IFeatureClass)queryClass;
//assign the new fc to an existing map layer, this is where the exception happens
mapLayer.FeatureClass = fc;

how can i change the sql workspace of a map layer programmatically?

stefanschlaefli · Answer

Hi James
Thank you for the hint, it works. And you're right, the user will not see it until the layer is refreshed (switch visibility on/off for example). Actually I try to use this in a ArcGIS server mapservice. The client shall be able to choose a db which is the datasource of a mapservice. I guess the code in this thread would be running in a server object extension. Do you know any other way to make mapservices (or maybe any other AGS service types) dynamic in a way that the client determines the datasource of the service?
Thanks, 
Stefan

JamesMacKay · Answer

Hi Stefan,

I'm not sure if this will help, but try making the first parameter of OpenQueryClass dynamic and unique, i.e. append a timestamp. As long as the layer object is still reused I don't think the change will be visible to the user.

Hope this helps,
James