Creating Dot Net Stored Procedures in SQL Server 2005

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/yvarshal/creatingdotnetstoredproceduresinsqlserver2005.asp

    Regards,Yelena Varsha

  • @set PATH=%FrameworkSDKDir%\bin;%FrameworkDir%\%FrameworkVersion%;%PATH%;

    Shouldn't that read

    @set PATH=%FrameworkDir%\bin;%FrameworkDir%\%FrameworkVersion%;%PATH%;

    And in the source code where is the LogIt function defined?

    Other than that it is nice to see a practical example of the CLR functionality.

  • The path works for me by the looks of it, i just installed the framework with the defaults.  The logit function is in the code isn't it:

         Public Shared Sub LogIt(ByVal logMessage As String, ByVal w As TextWriter)

            w.Write(ControlChars.CrLf & "Log Entry : ")

            w.WriteLine("{0} {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())

            w.WriteLine("  :")

            w.WriteLine("  :{0}", logMessage)

            w.WriteLine("-------------------------------")

            ' Update the underlying file.

            w.Flush()

         End Sub

    Excellent article, thanks, i found very usefull.


    Growing old is mandatory, growing up is optional

  • Good stuff - I've been wondering how that was going to work.

  • David,

    It was a good point about the Path. That is why I say in the article that one should verify path in his/her installation. You pointed at FrameworkSDKDir versus FrameworkDir. I remember installing SDK on one computer and not installing on another computer (at least, explicitly) . This path works for 2 workstations and one server. All 3 running a lot of software.I would suggest finding the directory with VBC (Visual Basic Compiler) and relevant dlls in the compiler line and modify path accordingly.

    All,

    Thanks for reading the article and for your feedback! I will log in again to see if you have more questions.

    Yelena

    Regards,Yelena Varsha

  • Now, this is a good article and example of what can be done with the SQLCLR integration.

    I can't help but think, it's gotta make DBAs very nervous from a security and stability perspective.


    {Francisco}

  • I'm not certain that your stored procedure is thread safe. Remember, multiple processes may call this at the same time -- the file will be locked if someone else is writing to it, and you will throw an exception. Unfortunately, I'm not certain how to check first...

    On that same note, you should probably use w.Dispose() instead of w.Close(), and wrap the whole thing in a try/finally block in case something bad happens (e.g. an exception). If you do hit an exception currently, the Close() will not get hit -- and the file will remain locked until the GC happens to come along and do its thing.

    --
    Adam Machanic
    whoisactive

  • Adam and Francisco,

    You are certainly right. When I write production code I always think of security, thread safety, stability, handling exceptions and disposing of the objects that are no more in use. I think I may post something on security later, I have some unusual examples.

    This article is a "Proof-Of-Concept" that yes, we can write stored procedures in programming languages and yes, it is working. So I just used the most basic code.

    In addition, I wanted to use VB code that is as simple as possible and easy to read for those DBAs who have sysadmin background without programming experience.

    Yelena

    Regards,Yelena Varsha

  • Yelena:

    It is a good article, simple and make sense. Like you wrote, some database guy might not be familiar with the .Net, like me.

    So, simple example with good concept are good for me, thanks for the article. On the other hand, it looks like the quality of DB design today is less important as ever......

    p.s. What will be your recommandation about .Net VB and .Net C# self learning books?

    thanks

    -D

  • thanks, this is a good example for those who have limited knowlege of .net.

  • David,

    Get a copy of Jeffrey Richter's _Applied Microsoft .NET Framework Programming_ -- It's language independent and focuses on the framework itself. If you understand how the framework works, picking up any language that uses it -- C#, VB.NET, COBOL.NET, etc -- will be simple. That's really the best way to learn it, IMO.

    --
    Adam Machanic
    whoisactive

  • David,

    I used

    MCAD/MCSD Self-Paced Training Kit: Developing Windows-Based Applications with Microsoft Visual Basic.NET and Microsoft Visual C#.NET

    http://www.amazon.com/exec/obidos/tg/detail/-/0735619263/qid=1120066439/sr=1-1/ref=sr_1_1/002-1738938-5948008?v=glance&s=books

    And for Web Apps I like Jesse Liberty book

    Programming ASP.NET (O'Reilly Windows)

    by Jesse Liberty, Dan Hurwitz

    http://www.amazon.com/exec/obidos/tg/detail/-/0596001711/qid=1120066552/sr=8-18/ref=sr_8_xs_ap_i3_xgl14/002-1738938-5948008?v=glance&s=books&n=507846

    Additionally I used MSDN a lot when preparing to MCSD exams.

    Yelena

    Regards,Yelena Varsha

  • Adam,

    Thanks for your reference. This is a good book

    Yelena

    Regards,Yelena Varsha

  • I'm not sure why you use UNSAFE to get this to work. I was able to get it to work with EXTERNAL_ACCESS. I strongly named the compilation of the executable.

    I did have an interesting problem. I variablized the SOURCE for the event log procedure. When I enter the string 'EA - EventLogRecording Source' as my source, I get an error in the event log entry of:

    "The description for Event ID ( 0 ) in Source ( EA - EventLogRecording Source ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: This is a test message."

    I tried many variations of this string and couldn't get any other variations to generate this message. What is strange about this source as a string? Other strings just work.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply