Debugging stored procedure from a .net application

Question

Hi

I have a stored procedure, which is being called from .net application, I (or rather someone else) wrote:

SqlCommand.ExecuteNonQuery()

for executing the stored procedure. However, this command throws an exception that says the procedure tried to insert NULL value into table (which does not allow nulls). Here is what I have done so far:

1. Performed a trace to get the parameters, executed the procedure with exactly the same parameters, at that time no exception/error is thrown

2. I have figured out that there is a condition inside the procedure which somehow is getting set to TRUE when it is called from .net application

3. I have tried debugging it from SQL Management Console, that condition never gets satisfied when I debug it externally (as in, when the procedure is called explicityly)

4. I have enabled SQL debugging in VS projects, I have enabled .net/CLR debugging on my SQL server; I am also told that I have sysadmin rights on this SQL server

5. I have gone through the relevant threads in this forum and none seem to solve my problem

Now, for my problem, I want to step into stored procedure as soon as .net call is made from the application; however, my breakpoints are never reached in the procedure; I get some error that symbols are not loaded and breakpoint will never be hit. Is there a way to fix this?

Answer 1

A debug build in Visual Studio produces a symbol file (.pdb file) in addition to the DLL. To associate a .pdb file for debugging, you’d use ALTER ASSEMBLY, like this:

 

-- CREATE ASSEMBLY [yourasm] FROM ‘c:\yourdirectory\yourasm.dll’  -- this has already been done

ALTER ASSEMBLY [yourasm] ADD FILE FROM ‘c:\yourdirectory\yourasm.pdb’

 

Cheers, Bob

Answer 2

The PDB files for .net projects are already present in the required directories, do I need to modify the stored proc and add these lines in it? Can you please elaborate a little bit on this?

Answer 3

It’s not enough to have the pdb file in the same directory. The pdb file has to be in the SQL Server database as well, like the assembly is.

 

You can see if the pdb file is in the SQL Server database by querying inside the database (from SQL Server Management Studio):

 

SELECT * FROM sys.assembly_files;

 

This lists all SQLCLR assembly-related files in the database, assemblies as well as pdb files.

 

If the pdb is already in the database, then the error message about not having symbols can be safely ignored.

 

You don’t need to add anything to the stored procedure code itself.

 

And when a Visual Studio SQLCLR project does the deployment, the pdb is automatically cataloged into SQL Server with the assembly. I thought you were doing deployment outside of Visual Studio, using SQL Server DDL. If you’re using SQL DDL you need to add the pdb file by yourself (after you’ve created the ASSEMBLY, using ALTER ASSEMBLY from SQL Server Management Studio.

 

Hope this helps,

Cheers, Bob