Hello All,
I'm new to SQL CLR programmability. I have a CLR stored procedure that writes to a local file. But when I have multiple connections calling the same stored procedure, how do I synchronize access to the local file? Any inbuilt features? Or any other input is welcome.
Safe CLR assemblies inside SQL are not allowed to share state and hence cannot synchronize. For good reason, by blocking in synchronization you steal a worker from the SQL Server thread pool. Workers are in high demand, can't afford to loose them waiting for user synch. Unsafe assemblies can do anything using ordinary .Net Framework synchronization objects (Monitor, lock statements, ReaderWriter lock etc etc), but that's why they called 'unsafe': you may end up freezing the SQL Server instance.
Do your file access and synchronization from an external process.