locked stored procedures in sql

Question

Hi,

I am not too familiar with sql server 2005.

I have a schema in sql which has stored procedures with small lock on them. As I understand they were created using C#, all these locked procedures have a source file in C# with the code of the procedures. The thing is I can't access them. I need to modify one of these procedures but it doesn't let me modify them. I have the source code (from visual studio) with these procedures but when I change something in the code, it doesn't affect the procedures in the sql.

How can I change the path to assembly in sql server 2005? Is there any other way I can access these stored procedures?

Thanks in advance,

Greg

Answer 1

The SQL scripts that you edit must be applied to the database before they will take effect.

You can do this with a query window from SQL Server Management Studio, or by deploying the script from a database project.

Answer 2

Thanks for answering. Can you please elaborate how can I apply the script to the database?

Answer 3

If these are CLR stored procedures and you have changed the source and recompiled a new DLL then you need to deploy the new DLL to the database. You cannot "change the path" to the assembly as it is not loading the DLL from the file system, it is stored in the database. You need to update the assembly in the database, to do that run the following script replacing the relevent bits with your assembly details:

ALTER ASSEMBLY MyAssembly DROP FILE ALL ADD FILE FROM 'path to MyAssembly'