Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Creating Dot Net Stored Procedures in SQL Server 2005 Expand / Collapse
Author
Message
Posted Monday, June 20, 2005 3:23 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/yvarshal/creatingdotnetstoredproceduresinsqlserver2005.asp


Regards,
Yelena Varshal

Post #192275
Posted Tuesday, June 28, 2005 2:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:15 AM
Points: 2,889, Visits: 1,778
@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.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #194608
Posted Tuesday, June 28, 2005 2:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 30, 2007 7:56 AM
Points: 208, Visits: 1

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
Post #194617
Posted Tuesday, June 28, 2005 2:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:26 AM
Points: 1,147, Visits: 1,067
Good stuff - I've been wondering how that was going to work.
Post #194623
Posted Tuesday, June 28, 2005 8:37 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579

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 Varshal

Post #194734
Posted Tuesday, June 28, 2005 9:54 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 12, 2012 11:03 AM
Points: 455, Visits: 45
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}
Post #194808
Posted Tuesday, June 28, 2005 11:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:02 AM
Points: 1,140, Visits: 701
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #194876
Posted Tuesday, June 28, 2005 12:07 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579

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 Varshal

Post #194911
Posted Tuesday, June 28, 2005 5:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 26, 2014 3:29 PM
Points: 192, Visits: 130

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




Post #195021
Posted Tuesday, June 28, 2005 5:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 21, 2006 1:32 PM
Points: 41, Visits: 1
thanks, this is a good example for those who have limited knowlege of .net.


Post #195022
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse