Solved

CLR and SQL Function

Posted on 2013-01-24
9
562 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Database ERD 4 25
sql server query 12 24
Run Stored Procedure uisng ADO 5 20
SQL 2012 clustering 9 11
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

808 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