Solved

SQL 2005 CLR stored procedure to use VSS lib

Posted on 2006-07-14
19
597 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
help converting varchar to date 14 25
Sql case statement to calculate totals 5 37
Split string into 3 separate fields 5 22
Need multiple Group By's 8 28
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

730 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