Solved

CLR and SQL Function

Posted on 2013-01-24
9
563 Views
Last Modified: 2013-01-24
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
0
Comment
Question by:lrbrister
9 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38814255
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
 
LVL 7

Expert Comment

by:Ross Turner
ID: 38814263
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
 

Author Comment

by:lrbrister
ID: 38814264
BCUNNEY

There's a ton of assembly methods in the assembly

During the select...there's no way to trace the object_id?
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38814274
What is in the cfl(based) function you mentioned?
0
 

Author Comment

by:lrbrister
ID: 38814319
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
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 38814388
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38814406
0
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 500 total points
ID: 38814407
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
 

Author Closing Comment

by:lrbrister
ID: 38814505
Thanks.  Great suggestions and thanks for sticking with me.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question