Can't debug CLR locally

Question

Can't debug CLR locally

MichaelC-698876

SSC Eights!

Points: 868
More actions
July 31, 2008 at 12:35 pm

#195163

I have a CLR UDF that I created a while back and I could debug it locally and remotely. However, I can no longer debug, or step into, it at all. In the output window in VS it says that the attachment succeeded, but, it never makes it to the break point in test.sql or any other break point. I have checked and rechecked the properties and all is in order. I know I haven't changed anything. I also followed a simple tutorial from the web (a couple of them) that don't do anything but return a string saying "hello", and the same problem occurs.
Funny thing to mention is that I was having the same problem yesterday, but it would work off and on. It worked three different times but doesn't seem to want to work at all now.
I have also deleted the binaries and redeployed and that doesn't seem to work either.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

I have no idea how to troubleshoot, never mind even write a CLR. But, I am interested in what your UDF does... if you don't mind.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

MichaelC (7/31/2008)
I have a CLR UDF that I created a while back and I could debug it locally and remotely. However, I can no longer debug, or step into, it at all. In the output window in VS it says that the attachment succeeded, but, it never makes it to the break point in test.sql or any other break point. I have checked and rechecked the properties and all is in order. I know I haven't changed anything. I also followed a simple tutorial from the web (a couple of them) that don't do anything but return a string saying "hello", and the same problem occurs.
Funny thing to mention is that I was having the same problem yesterday, but it would work off and on. It worked three different times but doesn't seem to want to work at all now.
I have also deleted the binaries and redeployed and that doesn't seem to work either.

I've had resource issues and/or times where the tests seem to fail because the previous test somehow fouled up the testing spot. I reboot and the issue goes away.

Also - try to keep your data set small (for the local testing). That too somehow would cramp things up big time.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

MichaelC-698876 SSC Eights! Points: 868 More actions · Answer 3

Jeff Moden (7/31/2008)
I have no idea how to troubleshoot, never mind even write a CLR. But, I am interested in what your UDF does... if you don't mind.

It doesn't matter which one I attempt to degub, they all have the same issue. It won't hit the breakpoint in test.sql (select functionName()). But, the answer to your question is I have tried three simple functions that return a simple string that says "hello". The other, which wasn't going to be use often, is a splitting function that splits strings and weights the out come and returns a string value. There is some other logic in there too.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Jeff is looking to find out what you are trying to use CLR for. If it is just string splitting, unless you are using extremely long strings, that extend well into a max datatypes, good TSQL code will outperform CLR, on large datasets by as much as 15-20 seconds faster. Jeff is one of the best at writing such TSQL, which is why he asked. He probably has a TSQL Solution that solves your problem, and does it better than CLR.

The issue is that you have extra overhead in CLR that doesn't exist in TSQL. Most often, the benefits gained by CLR are lost to the extra overhead, though it may be small, for pushing the data to the CLR stack, and then back to TSQL post processing.

MichaelC-698876 SSC Eights! Points: 868 More actions · Answer 5

I didn't mean for my reply to sound harsh. I am just stating that even a function that doesn't do anything but return a simple string won't debug.

I need a way to split long strings that are comma delimited, and I have to be able to pass in what part of the string I want back. A potential problem is that not all strings will have the parts that I am looking for. For example, if I am searching for the third part of a name that my look like (Smith,John). Notice there isn't a third part to the name and in some cases there may be no parts as the string could be blank.

Another question, can udf's be used in SSIS packages?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

You can do this with TSQL very easily with a split function. You can find one on the following link:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringArrayInput&referringTitle=Home

declare @namestring varchar(1000), @thirdvalue varchar(1000)

set @namestring = 'Smith, John'

SELECT @thirdvalue = value

FROM dbo.Split(',',@Namestring)

WHERE RowID = 3

select @thirdvalue

set @namestring = 'Smith, John, Jones'

SELECT @thirdvalue = value

FROM dbo.Split(',',@Namestring)

WHERE RowID = 3

select @thirdvalue

No need for CLR here. I know your post is about this, but while we are here, we might as well show you what we can. For the Debugging problem I usually have to reboot when I get that problem and it works fine after the reboot.

MichaelC-698876 SSC Eights! Points: 868 More actions · Answer 7

Thanks! It looks simple enough. I guess if the part I am looking for doesn't exist it will return null. I'm out of the office at the moment but I will try it and adjust if needed. Thanks again.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

That is correct, and you can easily modify the code to accept another input and return a scalar value back for a specific part only.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Michael,

Although using a WHILE loop to split a string is a tried and true method, it's relatively slow compared to other methods. Please see the following two articles... they're kinda long, but they're well worth the read even if I do say so myself...

This article explains the basis of the Tally table which is used to replace such loops in a set based manner and contains an example of how the Tally table method just beats the pants off of While loop splits...

http://qa.sqlservercentral.com/articles/TSQL/62867/

... and this article takes splits to a whole new level as well as explaining how to do "monster" splits...

http://qa.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

... and, heh... no "debug" is required on those... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Jonathan Kehayias (8/1/2008)
Jeff is looking to find out what you are trying to use CLR for. If it is just string splitting, unless you are using extremely long strings, that extend well into a max datatypes, good TSQL code will outperform CLR, on large datasets by as much as 15-20 seconds faster. Jeff is one of the best at writing such TSQL, which is why he asked. He probably has a TSQL Solution that solves your problem, and does it better than CLR.
The issue is that you have extra overhead in CLR that doesn't exist in TSQL. Most often, the benefits gained by CLR are lost to the extra overhead, though it may be small, for pushing the data to the CLR stack, and then back to TSQL post processing.

Correct on all counts... and thanks for the awesome compliment! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 12

Hmm, I have to go play with your code now Jeff. I am not surprised that you once again replaced a loop with a Tally/Numbers table. I am going to have to rerun one of my benchmark tests now, to see how the monster string method compares to CLR. Monster strings, and I mean monster, were the only place that CLR got any kind of headway against the Looping split method I referenced, and it wasn't significant enough to ever make CLR worth implementing, especially since it took 100,000 rows to see just a few seconds difference. I am willing to bet that your implementation will beat the CLR one no matter how much data I throw at it.

MichaelC-698876 SSC Eights! Points: 868 More actions · Answer 13

Thanks Jeff! I'll play with these too. I think you also posted some suggestions when I was working with this function a while back, but we thought we would not need this type of function after all. Well, we do...

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Jonathan Kehayias (8/1/2008)
Hmm, I have to go play with your code now Jeff. I am not surprised that you once again replaced a loop with a Tally/Numbers table. I am going to have to rerun one of my benchmark tests now, to see how the monster string method compares to CLR. Monster strings, and I mean monster, were the only place that CLR got any kind of headway against the Looping split method I referenced, and it wasn't significant enough to ever make CLR worth implementing, especially since it took 100,000 rows to see just a few seconds difference. I am willing to bet that your implementation will beat the CLR one no matter how much data I throw at it.

In the "monster split" testing I did, I found out that VARCHAR(MAX) is a pig compared to VARCHAR(8000) by running tests that will fit in both. Still, even the VARCHAR(MAX) split is nasty fast and the need for a CLR is easily avoided as you suggest.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions