• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

SQL 2005 CLR stored procedure to use VSS lib

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
puranik_p
Asked:
puranik_p
  • 8
  • 5
  • 4
  • +1
1 Solution
 
Jonathan KellyCommented:
Hi puranik_p,
Have you looked into using sp_cmd to delete the files?

Cheers!
0
 
puranik_pAuthor Commented:
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
 
Jonathan KellyCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Jonathan KellyCommented:
puranik_p,

you could use xp_cmdshell to delete the files.
0
 
puranik_pAuthor Commented:
Datrias,
with xp_cmdshell, I can delete windows folders not the VSS ones.
0
 
Jonathan KellyCommented:
what are VSS folders

0
 
Bob LearnedCommented:
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
 
puranik_pAuthor Commented:
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
 
Bob LearnedCommented:
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
 
puranik_pAuthor Commented:
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
 
puranik_pAuthor Commented:
Finally got it working!!
0
 
Bob LearnedCommented:
Awesome!!!  What was the solution, pray tell?

Bob
0
 
puranik_pAuthor Commented:
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
 
Bob LearnedCommented:
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
 
puranik_pAuthor Commented:
wild cheetah is a better guess! :o)
0
 
puranik_pAuthor Commented:
TheLO, I'm requesting a PAQ/Refund for this, if it's okay with you.
0
 
Bob LearnedCommented:
OK with me ;)

Bob
0
 
GhostModCommented:
Closed, 500 points refunded.

GhostMod
Community Support Moderator
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 8
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now