Creating Dot Net Stored Procedures in SQL Server 2005

Question

Post reply

Creating Dot Net Stored Procedures in SQL Server 2005

Yelena Varshal

SSC-Dedicated

Points: 34286
More actions
June 20, 2005 at 3:23 pm

#167296

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

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

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

David.Poole SSC Guru Points: 76000 More actions · Answer 1

@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

bond007 SSCrazy Points: 2056 More actions · Answer 2

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 SSCertifiable Points: 6491 More actions · Answer 3

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

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

Francisco Lopez SSCommitted Points: 1645 More actions · Answer 5

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 SSCoach Points: 15370 More actions · Answer 6

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

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

David Lu SSC Eights! Points: 882 More actions · Answer 8

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 SSC Enthusiast Points: 149 More actions · Answer 9

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

Adam Machanic SSCoach Points: 15370 More actions · Answer 10

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 11

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 12

Adam,

Thanks for your reference. This is a good book

Yelena

Regards,Yelena Varsha

drnetwork SSCommitted Points: 1815 More actions · Answer 13

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.