CLR and SQL Function

Not my background at all

There is a clr process that was built and loaded on our sql server

In the database on that server is a function that consumes that clr.

When we try and do a select using the cfl(based) function, it just runs and runs.

Is there any way we can trace that and see at what point it stops?

After a while it just times out.

I see that I can look at the assemblie information with these selects
SELECT * FROM sys.assemblies WHERE assembly_id IN (66092,66093)

SELECT * FROM sys.assembly_files WHERE assembly_id IN (66092,66093)

SELECT * FROM sys.assembly_modules WHERE assembly_id IN (66092,66093) ORDER BY assembly_method
Larry Bristersr. DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Barry CunneyConnect With a Mentor Commented:
Are you able to post up the function/procedure definition which calls the assembly method?

I would try and use SQL Profiler to try and fully confirm exactly which point the timeout is occurring at - Like you need to fully confirm that it is denitely the call to the T-SQL procdure which calls the above CLR function that is timing out.

Then you establish that if the above CLR is called from your test method(confirm how you did this)  it works OK but that if it is called from the T-SQL function the timeout occurs
0
 
Barry CunneyCommented:
Is it possible to post up the function definition?

You need to try and determine what DLL is being referenced
select * from sys.assembly_modules

and then see if you can obtain the source code for that DLL
to see what exactly it is trying to do.
0
 
Ross TurnerManagement Information Support AnalystCommented:
You could use sql profiler to trace the function and output to file or table:

Have a look at the link below:
server-introduction-to-sql-server-profiler
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Larry Bristersr. DeveloperAuthor Commented:
BCUNNEY

There's a ton of assembly methods in the assembly

During the select...there's no way to trace the object_id?
0
 
Barry CunneyCommented:
What is in the cfl(based) function you mentioned?
0
 
Larry Bristersr. DeveloperAuthor Commented:
BCUNNEY

It's essentially using a web reference to send and receive ESignature documents with Sertifi.

It's been working fine for a year.

We've verified:
   Our SQL Agent Job is executing on time
   We can communicate with their web service from that particular server (using a test method to post and retrieve data)
   No security setting have changed on the server, network, nor port in our firewall

Basically...we've verified that to the best of our knowledge nothing has changed on our end.
Sertifi insists that while the method DOES have 2 new parameters on their end, NULLs are handled when we don;t pass in the information.

That's why we're befuzzled.  And I'm looking for a way to trace what's going on when we consume the actual function.

This is the code (method) that's being called in the assembly.
The exception that's returned is a simple "Timed out" message
 <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function GetSignedDoc(ByVal strFileID As String _
                                      , ByVal strDocID As String _
                                      , ByVal strDocumentName As String _
                                      , ByVal strDocumentPath As String) As SqlString
        Dim ReturnedMessage As String
        'Instantiate Sertifi's Webservice
        Dim Request As New Gateway()
        Dim byteStream As Byte()
        Dim SaveFileStream As FileStream

        Dim fileToDownload As String = strDocumentPath & "\" & strDocumentName & ".pdf"


        Try
            If (Not IO.Directory.Exists(strDocumentPath)) Then
                IO.Directory.CreateDirectory(strDocumentPath)
            End If

            byteStream = Request.DownloadSignedDocument(APICode, strFileID, strDocID)
            If byteStream.Length > 0 Then
                SaveFileStream = New FileStream(fileToDownload, System.IO.FileMode.Create)
                SaveFileStream.Write(byteStream, 0, byteStream.Length)
                SaveFileStream.Close()
                ReturnedMessage = "Success"
            Else
                ReturnedMessage = "No File Downloaded"
            End If
        Catch ex As Exception
            ReturnedMessage = ex.Message
        End Try

        Return ReturnedMessage
    End Function

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
Barry CunneyConnect With a Mentor Commented:
Also maybe create a debug version of the above CLR to log some debug info to see if the CLR actally does any of the steps

SqlContext.Pipe.Send("hello world")
0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks.  Great suggestions and thanks for sticking with me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.