Multiple Active Result Sets (MARS)
I created some sample applications to demonstrate MARS (MultipleActiveResultSets), a new feature of SQL 2005 and .NET 2. For this I used the various CTPs that Microsoft delivered. Since friday the RTM versions of SQL 2005 and Visual Studio 2005 have been available. Of course I could not wait to get my hands on it. Therefore I reinstalled my laptop and installed the new RTM bits.
Interestingly my MARS-samples do not execute anymore. I get the following exception:
The connection does not support MultipleActiveResultSets.
It seams that Microsoft changed the behavior of MARS from CTP to RTM. Now you have to specify an additional keyword within the connection string to explicitly enable MARS:
server=localhost;uid=demo;pwd=demo;database=AdventureWorks;async=true;MultipleActiveResultSets=true
With this change my MARS samples work fine. Heureka!
SQLDependency
Hey, what happened to SQLDependency? My application worked fine with VS2005 RC, it does not when I execute it with the RTM version. The following exception appears:
When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.
This exception is quite simple to correct. As the error message says there is a new function SqlDependency.Start that has to be executed in order to start the listener for receiving dependency change notifications from an instance of SQL2005:
SqlDependency.Start(connectionString.ConnectionString);
create endpoint
In previous releases of SQL2005 (e. g. June CTP) create endpoint did not care about whether IIS was listening on the same port that you specified. The RTM version of SQL2005 will not let you create an HTTP endpoint if IIS uses the same port (I tested with WindowsXP and developer edition of SQL2005):
An error occurred while attempting to register the endpoint '[...]'. One or more of the ports specified in the CREATE ENDPOINT statement may be bound to another process. Attempt the statement again with a different port or use netstat to find the application currently using the port and resolve the conflict.
You can resolve this issue by specifiying an alternate port that SQL2005 should use:
create endpoint [...]
as http ( path = '[...]', AUTHENTICATION = (INTEGRATED), PORTS = ( CLEAR ), CLEAR_PORT = 8080 ) for soap ( [...] )
UNSAFE assemblies
If you want to create an assembly with UNSAFE permission set the database has to have its trustworthy property set to on:
alter database AdventureWorks set trustworthy on