SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating Dot Net Stored Procedures in SQL Server 2005


Creating Dot Net Stored Procedures in SQL Server 2005

Author
Message
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9000 Visits: 600
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/yvarshal/creatingdotnetstoredproceduresinsqlserver2005.asp


Regards,
Yelena Varshal

Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16868 Visits: 3403
@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
www.simple-talk.com
bond007
bond007
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 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
Michael Lysons
Michael Lysons
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2226 Visits: 1452
Good stuff - I've been wondering how that was going to work.
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9000 Visits: 600

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

Francisco Lopez
Francisco Lopez
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 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}
Adam Machanic
Adam Machanic
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3011 Visits: 714
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
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9000 Visits: 600

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

David Lu
David Lu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 135

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





bhupinderd
bhupinderd
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 1
thanks, this is a good example for those who have limited knowlege of .net.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search