Everything works in Crystal as well as VS2005 code. The moment I publish the site I get the following error.

Failed to retrieve data from the database. Failed to retrieve data from the database. Error in File report1{BIA0AA0b-1234-A234-C2325A3S3S}.rpt: Failed to retrieve data from the database.

Before I display the report I do provide the active directory user and password to the report. If I run only the SQL command or only the view then everything works. The moment I link these 2 I get the above error. I even tried to impersonate and I still get the error.

If I save the SQL command as a view in SQL database and then add both views and link them, they run without any issues.

I just can't seem to get this work with a dynamuic SQL command. Could some one help me with this. I have been working on this for too long and I have no idea how to solve this. Creating a view in the SQL server for the SQL command is not an option, as we do create many reports with different SQL commands.

Any help is greatly appreciated. Please help.