Solved

SQL 2005 CLR stored procedure to use VSS lib

Posted on 2006-07-14
19
594 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
  • 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
 
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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 …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now