I've used Crystal IDE to develop a report that calls a sproc with a non-default schema. My development used a named SQL instance during the design time.
When the report is deployed, the .NET code passes a connection object with Server Name, Database, User, & password.
When the product is deployed to a system with a named SQL instance the SQL Profiler shows that the statement includes the following:
"databasename"."schemaname"."sproc" (the rest of the statement).
This succeeds and returns the needed data for the report.
If you deploy the product to a system with a default SQL instance, the SQL Profiler shows that the statement includes the following
"sproc" (the rest of the statement)
This latter example fails with at SQL Database Vendor Code of 2812 (sproc not found).
Now, if you rebind the report using the Set Database Location in the Crystal IDE, and use the default SQL instance, the problem is then reversed. I did not trace this last instance change test, but I did confirm that the SQL Database Vendor code of 2812 goes away on the default SQL instance, but now appears on the named SQL instance.
It would appear that the Crystal .NET runtime builds differnent SQL statements depending on database used for the Set Location, and the actual name or unnamed instance used at runtime.
I would like to think that whether the report was developed on the default or named instance of a SQL server should work whether the runtime connection string information uses a named instance or the default instance.
I guess the tough question is how do I get the CR runtime to call SQL Server using
"database"."schema"."sproc" (rest of the statement)
regardless of whether the CR IDE used the named or unnamed instance during the set database location, and regardless of whether the runtime configured to use a named or unnamed instance of SQL Server.
CR2008 Developer Edition (SP3)
VS2010 .NET v4
MS SQL 2008.
Edited by: MarkCONIX on Oct 27, 2010 6:20 PM