Create SQL Server CLR Stored Procedures Using Visual Studio 2005 Standard


I have created a SQL script in SQL Server 2005 - a query which generates its results in XML format. I want to be able to automate the process of running the script and saving the query results as an XML file. I've seen where others have used BCP to do this, but I'm under the impression that requires saving the sa username and password in the procedure call... I've also read that it is preferable to create the file using Visual Studio to create a CLR stored procedure. I have some (limited) experience with Visual Basic.NET in older versions of Visual Studio, so I figured I'd try my hand at this technique. Unfortunately, the version of Visual Studio I have here is 2005 Standard, and I'm learning that the SQL Server integration support is limited unless you have the Professional edition. For example, System.Data.SqlServer apparently doesn't exist. For that matter, 'SQL Server Project' isn't even among the new project choices...

I found a cool article ( that seems to describe what I would want to do, but it doesn't work with 2005 Standard.

My question then is, can I accomplish with Visual Studio 2005 Standard what would normally be done with Visual Studio 2005 Professional?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Try looking at this:

Converting Between Calendar And Working Days In VB.NET

Don't be turned off by the title of the blog post as it was following a series of a topic.  It shows how to create a SQL CLR function in this case even using Visual Studio Express where the same problem exists that the nice project and things don't exist; however, as you will see you can still get around pretty well.

There are two links in the reference section that I found helpful when useful in writing the post.

The examples are SqlFunction, but as you will see Stored Procedures are the same process.  This may be totally over simplifying things, but in general:

VB Function <== SQL User Defined Function
VB Sub <== SQL Stored Procedure

If the information is not clear enough to adapt to your case, please let me know...
JeffEdmundsAuthor Commented:
Hi mwvisa1,

I'm not certain I follow your article, at least not how it applies here.

From what I can tell, the code from the example I linked in my original post would work if I had VS 2005 Professional, because that version of VS includes additional components for SQL Server integration which the Standard version doesn't include. Unfortunately, when I try to build it in VS 2005 Standard it doesn't accept this line:

Imports System.Data.SqlServer

It doesn't accept this variable definition:

For that matter, it has issues with this entire line:
SqlPipe sqlP = SqlContext.GetPipe()

I presume this is because VS Professional includes SQL Server 2005 integration functionality that VS Standard does not. I'm looking for a way to accomplish the same thing without the additional features of the Professional edition. Possibly something that requires additional code in place if the nicely packaged features of the Pro edition, but performs the same task.


Kevin CrossChief Technology OfficerCommented:
If you look at the link I posted, it is a discussion of using Visual Studio version that is not professional to create an assembly within SQL Server which is what you will need to do.  Since this is my post I won't sue my self for plagerizing so this is what I was pointing you too in the post along with real life code example:

Create a new Class Library project in VS and ensure that you go into the properties of the project and remove the root namespace (you can leave this as-is or change to another namespace to have structured classes, but just take note of that for later as using methods within assemblies are of the format AssemblyName.ClassName.MethodName so if you have a class in a long namespace it must be declared like AssemblyName.[NamespaceName.ClassName].MethodName). Add a class item to your project and code away. Above code functions as-is and is a good example of all that is needed to make a normal VB function to SqlFunction or sub to SqlStoredProcedure.

These are the imports I used:
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Once you are done coding, you would run this on SQL Server to add assembly:
sp_configure N'clr enabled', 1

Open in new window

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

JeffEdmundsAuthor Commented:
Sorry to be a pain - I still don't think I completely understand.

I know I need to create a new Class Library in VB.NET, compile it into a DLL and use the CREATE ASSEMBLY line to import the DLL into SQL Server. The example I linked to shows this being done (although there are some syntax differences in their CREATE ASSEMBLY line and the line where that author enables CLR). My concern is that the imports used in that example don't exist in the pro edition, and subsequently other pieces of that example also don't work.

I think the area that I am getting hung up on is the actual process used to create the dll itself and the namespaces it needs to use, before bringing it into SQL.

When I use the example I found, System.Data.SqlServer displays the error:
"Namespace or type specified in the type Imports 'System.Data.SqlServer' doesn't contain any public member or cannot be found."

SqlPipe shows the error:
"Type SqlPipe is not defined."

and File.WriteaAll shows the error:
" 'WriteAll' is not a member of System.IO.File' "

I tried removing the root namespace in the project properties, as suggested, but I don't really know what that did... I still show the same errors in the same places. You say, "Add a new class item to your project and code away"... Is this what I am missing? If so, what do I need to do to create the class item?



ps .. sorry I didn't respond more quickly - I out from work yesterday.
JeffEdmundsAuthor Commented:
I also tried replacing the imports I was using with the ones in your example...

The error on SqlPipe went away... but now SqlContext.GetPipe() gives the error " 'GetPipe' is not a member of 'Microsoft.SqlServer.Server.SqlContext' " and File.WriteAll gives the error "Name 'File' is not declared.' ".
JeffEdmundsAuthor Commented:
OK... I made a couple of modifications, got it to be free from errors, and compiled it into a DLL... but I don't know if this will work or not. The attached code snippit contains the vb source code I used to make the DLL.

I replaced SqlContext.GetPipe() with SqlContext.Pipe() and changed File.WriteAll to IO.File.WriteAllText... and used the Includes you suggested in your post.

Could you tell me if you feel this will work, or if I am missing something before I try building the assembly in SQL Server? The query I created in T-SQL works and creates well-formed XML. I need a stored procedure which will allow me to save the displayed results from that query to a file, preserving the XML formatting.

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Public Class SQLCLRIO
    Public Shared Sub WriteToFile(ByVal content As String, _
                                  ByVal filename As String)
        Dim sqlP As SqlPipe = SqlContext.Pipe()
            IO.File.WriteAllText(filename, content & vbNewLine)
        Catch ex As Exception
            sqlP.Send("Error writing to file " & ex.Message.ToString)
        End Try
    End Sub
End Class

Open in new window

Kevin CrossChief Technology OfficerCommented:
I am glad you found the methods, I was just catching up on EE mail and was going to respond and saw your last post.

This should work for you fine if the example code you got this from worked correctly as the replacements you had to make were just pointing to the right namespaces for the most part.  VB.NET uses SqlContext.Pipe() whereas I would suspect the SqlContext.GetPipe() is the C# construct to do same thing.

On the surface that looks correct to me as there is not much to it to go wrong, but I would test to your content.

I would add assembly and procedure.  If you don't like it, the links show how to delete it. :) Then you can create from an updated assembly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JeffEdmundsAuthor Commented:
Thanks for your help!!

I was able to create an assembly (a basic Hello World message) earlier this morning with permission_set = SAFE. The stored procedure I used with it worked fine.

However, because the code I am working on for this XML project needs to write to a file, I'm trying to use permission_set = EXTERNAL_ACCESS and I'm getting some errors. I'm guessing these issues are just because I am not the database owner...something I'll need to work out with our security team.

As far as this goes though, if you feel the code I using including as the source for the DLL file is good then I'm all set with this part.

Thanks again,
Kevin CrossChief Technology OfficerCommented:
Good luck!

Yes the permissions granted to the assemblies is a whole other ball game.  Think the Microsoft (MSDN) link may have dived into that in detail -- try the two reference links:

Using CLR Integration in SQL Server 2005:

SQL Server DBA Guide to SQLCLR:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.