Solved

SQL 2005 CLR stored procedure to use VSS lib

Posted on 2006-07-14
19
598 Views
Last Modified: 2011-10-03
I want to write a procedure which will delete some records from my tables
Each of these records will contain a path to a folder in VSS that also needs to be deleted.
So I decided to go for CLR procedure to access VSS with the help of Microsoft.VisualStudio.SourceSafe.Interop.dll

I may add here that I'm using CLR code for the first time.
So, I found this one on how to create a CLR procedure.
http://www.sqlteam.com/item.asp?ItemID=21927
I got PrintToday working fine.

Now deleting the records from table is also fine. These separate procedures are in place.

Now problem is that, I cannot add reference to the VSS dll in my above mentioned project.
So how I'm going to connect to VSS?
I tried copying the dll to my project's bin. Still it doesn't show up in the add reference dialog box.

I'm using VS 2005.

Is it not possible? Am I doing something wrong in my project selection?
0
Comment
Question by:puranik_p
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
  • +1
19 Comments
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17107536
Hi puranik_p,
Have you looked into using sp_cmd to delete the files?

Cheers!
0
 
LVL 14

Author Comment

by:puranik_p
ID: 17107698
I don't know what is sp_cmd.
I couldn't find it in BOL.

One update...
I opened the csproj file in notepad and added the reference to VSS lib.
Now, it's showing up in references and I can also code using intellisence.
What's more, it even builds successfully.
But when I 'Deploy', I get this error...
Error      1      Assembly 'microsoft.visualstudio.sourcesafe.interop, version=5.2.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.      CSharpPart1

Here's the complete code I've written...

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.VisualStudio.SourceSafe.Interop;


public partial class StoredProcedures
{

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void PrintToday()
    {
        // Put your code here
        try
        {
            //'— The SourceSafe INI file.
            string iniVssPath = "C:/CLRVSS";

            //'— The SourceSafe User ID/Password.
            string cVSSUserName = "Admin";
            string cVSSPassword = "";


            VSSDatabaseClass vssLib = null;
            vssLib = new VSSDatabaseClass();
            vssLib.Open(iniVssPath, cVSSUserName, cVSSPassword);

            VSSItem VSS_Item = vssLib.get_VSSItem("$/Test", false);
            VSS_Item.Destroy();

            SqlPipe p;
            p = SqlContext.Pipe;
            p.Send("success");
        }
        catch
        {
            SqlPipe p;
            p = SqlContext.Pipe;
            p.Send("error");
        }
    }
};

Any help?
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17107758
sorry dude,

Ive no  experience with CLR programming.

btw shldnt
string iniVssPath = "C:/CLRVSS";
be
string iniVssPath = "C:\CLRVSS";


that sould be xp_cmdshell



it lets you carry out tasks at the command line
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17107796
puranik_p,

you could use xp_cmdshell to delete the files.
0
 
LVL 14

Author Comment

by:puranik_p
ID: 17107938
Datrias,
with xp_cmdshell, I can delete windows folders not the VSS ones.
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17108033
what are VSS folders

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17108182
Actually, it would be this:

string iniVssPath = @"C:\CLRVSS";

    or this

string iniVssPath = "C:\\CLRVSS";

Here is a thought about that error:

Bob Beauchemin's Blog
http://staff.develop.com/bobb/weblog/default.aspx?date=2004-10-01

<Quote>
It turns out that you can force SQL Server to use FX libraries that are not on the approved list. Deploying user code that uses these through Visual Studio produces...
</Quote>

It appears that there is an "approved" list, and maybe the VSS interop library isn't on it.

Bob
0
 
LVL 14

Author Comment

by:puranik_p
ID: 17120311
TheLO,
From the post, it doesn't take me anywhere near to the solution.
I'm very very rusty with .Net nowadays. Primary focus being on SQL Server.
I opened the GAC, and VSS dll is already there.
Anything else in the world that I can do get this solved?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17120917
So, this didn't help:

CREATE ASSEMBLY foo FROM 'c:\types\foo.dll

Copying 'System.Runtime.Remoting' to c:\types produces another "Could not find assembly". Eventually I put my assembly in the FX lib directory so it can resolve everything and set the permission_set to unsafe.

Bob
0
 
LVL 14

Author Comment

by:puranik_p
ID: 17120931
I tried that. I got this...
Warning: The Microsoft .Net frameworks assembly 'microsoft.visualstudio.sourcesafe.interop, version=5.2.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' you are registering is not fully tested in SQL Server hosted environment.
Msg 6215, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'get_Spec' on type 'Microsoft.VisualStudio.SourceSafe.Interop.IVSSItem'  in safe assembly 'Microsoft.VisualStudio.SourceSafe.Interop' has invalid attribute 0x1003.

About
>>Eventually I put my assembly in the FX lib directory
I don't know what is a FX lib directory.
Any clue?
0
 
LVL 14

Author Comment

by:puranik_p
ID: 17128886
Finally got it working!!
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17129199
Awesome!!!  What was the solution, pray tell?

Bob
0
 
LVL 14

Author Comment

by:puranik_p
ID: 17129548
1. ALTER DATABASE [MyDatabase] Set TRUSTWORTHY ON
2. CREATE ASSEMBLY VSS FROM 'C:\Microsoft.VisualStudio.SourceSafe.Interop.dll'
WITH PERMISSION_SET = UNSAFE
3. Now, in the add reference window, this dll is visible.
4. Build and Deploy from buid menu option

The catch is, there are 3 permission levels for create assembly
1. SAFE - default mode - cannot access extrnal resource (files/folders)
2. EXTRNAL_ACCESS - can access extrnal resource (files/folders) bu not COM dlls.
3. UNSAFE - can do everything in the world including reference to COM dlls.

The naming of these options...SAFE, UNSAFE doesn't convey much.
But that's true for many microsoft things. :o(

Now my next challange is to deploy it on the db server where this VSS dll is not available.

Quite a learning anyways.

Thanks TheLO for the time you spent on this.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17129675
Yeah, I did see the UNSAFE permission set, but I was struggling to figure out what the FX library meant.  It was a wild goose chase, I guess.

Bob
0
 
LVL 14

Author Comment

by:puranik_p
ID: 17136200
wild cheetah is a better guess! :o)
0
 
LVL 14

Author Comment

by:puranik_p
ID: 17137125
TheLO, I'm requesting a PAQ/Refund for this, if it's okay with you.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17137590
OK with me ;)

Bob
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 17168259
Closed, 500 points refunded.

GhostMod
Community Support Moderator
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

728 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