Solved

SQL 2005 CLR stored procedure to use VSS lib

Posted on 2006-07-14
19
593 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 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

12 Experts available now in Live!

Get 1:1 Help Now