Located below are the SQL, data and VB project files from my presentation at the Richmond Code Camp

RichmondCodeCampXquery.zip (12.6 KB)

Here is my powerpoint presention. It is more than a little light because the focus of CodeCamp is the CODE!

Richmond Code Camp XQuery Tips and Tricks.ppt (2.34 MB)

I would like to thank Andy Leonard, Susan Lennon, and the rest of the Richmond crew for a well run operation.

When working on code developing a SQLCLR stored procedure for my upcoming presentation at the Richmond Code Camp, I ran into this problem with the SqlPipe output.

Here is my code:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub procExtractSubjectDetail ()
'Return a string of Subject IDs/XML seperated by a ";"
Try
'Connect to database, notice the context connection
Dim Command As SqlCommand = New SqlCommand
Command.Connection = New SqlConnection("Context connection=true")
Command.Connection.Open()

'Generate the SQL query
Dim strSQL As String = "SELECT ID, Study FROM StudyDetail"

'Attach query to command object
Command.CommandText = strSQL

'Execute command object and get results back in a SQL reader object
Dim reader As SqlDataReader = Command.ExecuteReader()

'Create an efficient stringbuilder to hold the results
Dim Subjects As StringBuilder = New StringBuilder

'Add an emtpy string
Subjects.Append("")

'Collect the results
While reader.Read
'String output size limit is 4kb
'so let us restrict our output for this demo
Subjects.Append(reader.Item(0).ToString() + "/")
Dim sXml As SqlXml = reader.GetSqlXml(1)

Subjects.Append(sXml.Value.ToString)
End While

'Close and release the reader
reader.Close()
reader = Nothing

Dim strResult As String = Subjects.ToString()

'Is it too big?
Dim iSize As Integer = strResult.Length

'Send the results
SqlContext.Pipe.Send(strResult)

Command.Connection.Close()

Catch ex As Exception
'Catch the error and resend it. You could add more error code handling here.
Throw New ApplicationException("An error occurred. " + ex.ToString())
End Try

End Sub

--

This was failing when I ran the code. The issue? SqlContext.Pipe.Send(STRING) only allows a string size of 4k bytes. My output was over 7k.

OK. I'm a slacker.

I promised to get this up back a month ago. Here is my powerpoint presentation on developing user defined aggregates in SQL Server 2005 using .NET CLR. I gave this presentation at the NovaSQL user group. The group is managed superbly by Jeremy Kadlec, who also does MS SQL Tips.

April 18, 2007 (Wednesday)

Agenda

C-DUG Website Improvements

We have been working on improving the C-DUG Website. These changes are focused on creating a platform for sharing knowledge and information that would prove useful for seasoned and new DotNetNukers alike.

What’s New in DotNetNuke 4.5

A highly focused three month release cycle results in DotNetNuke® 4.5, a new release with integrated Microsoft ASP.NET AJAX support, a web-based installer, and a variety of other high value enhancements designed to improve the user experience.

Community Q&A

In an effort to get everyone involved we are going to open the floor for Q&A on anything DotNetNuke and ASP.net.  Anyone can ask questions, and anyone can answer them.

When: 7-9pm

Where: ATT Building (Basement) in Tysons Corner, behind Olive Garden Restaurant on Rt 7.

Directions: http://www.google.com/maps?q=1900+Gallows+Rd,+Vienna,+Virginia+22182,+USA&ie=UTF8&om=1&z=17&ll=38.914486,-77.227685&spn=0.003932,0.009978&t=h&layer=t

Point-of-Contact: Antonio Chagoury, email:antonio[AT]inspectorit.com