?
Solved

CLR and SQL Function

Posted on 2013-01-24
9
Medium Priority
?
569 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

741 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