CLR - Stored Procedure Lock

Question

Post reply

CLR - Stored Procedure Lock

SqlUser-529296

SSCarpal Tunnel

Points: 4072
More actions
January 24, 2008 at 4:56 am

#185966

Hi All,
In CLR, if i create a Procedure with the use of an Assesbly that Procedure is being locked. After that creation i am not able to modify that particular Procedure.Anybody know why?
Thanks in Advance

Viewing 14 posts - 1 through 13 (of 13 total)

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

Aby Kurian SSC Veteran Points: 229 More actions · Answer 1

I too face the same problem can anyone give a solution for that???

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

What is the error that you get, and what process are you doing when you get it? If it is a dependent items failure during a deploy operation, you have two options. Don't use auto deploy, and manually issue Alter Assembly to update the assembly definition, or drop all references to the procedure, ie any other procedures that call this procedure, and then redeploy using the autodeploy features in VS.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

Of course, the real question is...

What have you written a CLR for that you think can't be done in T-SQL?

Seriously... several of us have had several major "races" and, with the exception of some RegEx replace, we've not only been able to do what was in the CLR in T-SQL, but we frequently beat the pants off the CLR for performance. So, tell us what your CLR does...

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

sqluser (1/24/2008)
Hi All,
In CLR, if i create a Procedure with the use of an Assesbly that Procedure is being locked. After that creation i am not able to modify that particular Procedure.Anybody know why?

The reason why is that other SQL Objects (other stored procedures, most likely) are referencing your CLR procedure and CLR procedures have a kind of implicit "schemabinding" built into them. There are several ways to handle this, Jonathan lists two.

What I like to do during development, when I might be making a lot of changes on a regular basis, is to create wrapper or "shim" SQL procs for all of my CLR procs. These wrappers have exactly the same parameters and almost the same name as the CLR procs and all they do is just call their corresponding CLR proc.

I make sure that everything calls the wrappers, and not the CLR procs directly. Then, when I need to reload the assembly I have a maintenance proc that drops all of the SQL wrappers, so I can drop/reload all of the CLR procs. Then I run another maintenance proc that recreates all of the wrappers again.

When it's time to go to formal testing or deployment, we just drop the SQL warppers and rename the CLR procs to have the same name that the wrappers had.

This makes it easy to reload new versions of the Assembly's & CLR procs whenever we want during development. It will, however, add extra overhead, so you do not want them in for performance evaluation.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Aby Kurian SSC Veteran Points: 229 More actions · Answer 5

Aby Kurian

SSC Veteran

Points: 229

July 6, 2008 at 9:51 pm

#838661

Thankz Dude 🙂

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

You still haven't answered the very important question of...

What have you written a CLR for that you think can't be done in T-SQL? Dude... 😉

--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)

Aby Kurian SSC Veteran Points: 229 More actions · Answer 7

i just wrote a select query.i think if we are creating procedures from clr sql they are locked by default.

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

If all you are doing is selecting data, then you shouldn't be doing this in CLR. This is a TSQL only task. By using CLR for this, you are killing Performance. This is covered by Microsoft in the following white paper/article in the books online:

http://msdn.microsoft.com/en-us/library/ms345136.aspx#sqlclrguidance_topic3a

Jorge A Cea Valued Member Points: 72 More actions · Answer 9

I have created several CLR's for reading data and sending it back to the user, no problems there, but now I have created a CLR that reads data from a WebServices, they send me the data as a DataTable the main problem is that I have to loop thru the datatable and update one of my tables in my DB, but when I tried to Set an "UPDATE" statement, I always get timeout within the CLR, I have run the same statement outside the CLR and it works perfect (just as a test) but when in the CLR the timeout prevails, I have tried to call a Store procedure to perform the same update but I also get a timeout, does anybody have encounter the same problem.

I have set my CLR to be EXTERNAL_ACCESS and also UNRESTRICTED and I get the same results

Here is my code

Public Function process_notifications(ByVal UserID As String, ByVal Password As String) As Boolean

Dim Request As New TNW.TPSTransmitterXMLWebservice

Dim TNW_Recordset As New TNW.UpdatesList

Dim Number_of_updates As Integer = 300

Dim dt As New DataTable

Dim row As DataRow

Dim Cmd As New SqlCommand

Dim Ssql As String = String.Empty

Dim Rows_affected As Integer

Dim ErrorFlag As String = String.Empty

Dim Response As New TNW.Response

Dim UpdateNumber As Long

Dim AgentID As String = String.Empty

Dim InvNumber As Long = 0

Dim CountryIDO As String = String.Empty

Dim ShortMessage As String = String.Empty

Dim Message As String = String.Empty

Dim CoID As Integer = 1

Dim ConfirmationNumber As String = String.Empty

Dim total_notifications As Long = 0

Try

TNW_Recordset = Request.GetUpdates(UserID, Password, Number_of_updates)

dt = TNW_Recordset.Tables(0) 'or we can use TNW_Recordset.Tables("Updates")

total_notifications = dt.Rows.Count

Catch ex As Exception

total_notifications = 0

End Try

If total_notifications > 0 Then

Using conn As New SqlConnection(ConnectionString)

conn.Open()

For Each row In dt.Rows

UpdateNumber = CType(row("Update_Number"), Long)

ConfirmationNumber = row("Claim_Number").ToString()

Message = "Update ID: " & UpdateNumber.ToString & " -- " & row("Message").ToString()

'row("Custom1").ToString()

'row("Custom2").ToString()

If get_transaction(ConfirmationNumber, CoID, AgentID, InvNumber) Then

Select Case row("Update_Code").ToString()

Case "1000"

ShortMessage = "Paid to Beneficiary"

Message += "Branch ID : " & row("Branch_ID").ToString() & " Beneficiary ID : " & row("Ben_ID_Type").ToString() & " Number : " & row("Ben_ID_Number").ToString()

ErrorFlag = "0650"

Ssql = "Update Trans set tr_error_flag = @ErrorFlag, tr_paid_flag = 1, " & _

"tr_downloaded = 1, receivedbyBenef = 1, DateReceivedByBenef = '" & row("Update_Date").ToString() & "'" & _