[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

CLR and SQL Function

Posted on 2013-01-24
9
Medium Priority
?
574 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
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

649 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