[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Create SQL Server CLR Stored Procedures Using Visual Studio 2005 Standard

Posted on 2008-11-04
9
Medium Priority
?
1,249 Views
Last Modified: 2013-11-26
Hello,

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 (http://www.sqldbatips.com/showarticle.asp?ID=23) 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?
0
Comment
Question by:JeffEdmunds
  • 5
  • 4
9 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22884714
Try looking at this:

Converting Between Calendar And Working Days In VB.NET
http://crossedlogic.blogspot.com/2008/09/converting-between-calendar-and-working.html

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...
0
 
LVL 1

Author Comment

by:JeffEdmunds
ID: 22888118
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:
SqlPipe

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.

Thanks

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22888165
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
go
 
reconfigure
go
 
CREATE ASSEMBLY MyAssembly FROM 'C:\SQLCLRProject.dll' WITH PERMISSION_SET = SAFE

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 1

Author Comment

by:JeffEdmunds
ID: 22894812
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?

Thanks,

Jeff

ps .. sorry I didn't respond more quickly - I out from work yesterday.
0
 
LVL 1

Author Comment

by:JeffEdmunds
ID: 22894919
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.' ".
0
 
LVL 1

Author Comment

by:JeffEdmunds
ID: 22895424
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.

Thanks!
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()
        Try
            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

0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22897592
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.
0
 
LVL 1

Author Closing Comment

by:JeffEdmunds
ID: 31514035
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,
Jeff
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22898088
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:
http://msdn.microsoft.com/en-us/library/ms345136.aspx

SQL Server DBA Guide to SQLCLR:
http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
The PowerShell Core 6.0 of .NET release is just the beginning. The upcoming PowerShell Core 6.1 would have artificial intelligence and internet of things capabilities. So many things to look forward to in the upcoming release.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question