asked on

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?

Jonathan Kelly

Hi puranik_p,
Have you looked into using sp_cmd to delete the files?

Cheers!

puranik_p

ASKER

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?

Jonathan Kelly

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

Jonathan Kelly

puranik_p,

you could use xp_cmdshell to delete the files.

puranik_p

ASKER

Datrias,
with xp_cmdshell, I can delete windows folders not the VSS ones.

Jonathan Kelly

what are VSS folders

Bob Learned

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

puranik_p

ASKER

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?

Bob Learned

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

puranik_p

ASKER

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?

puranik_p

ASKER

Finally got it working!!

Bob Learned

Awesome!!! What was the solution, pray tell?

Bob

puranik_p

ASKER

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.

Bob Learned

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

puranik_p

ASKER

wild cheetah is a better guess! :o)

puranik_p

ASKER

TheLO, I'm requesting a PAQ/Refund for this, if it's okay with you.

Bob Learned

OK with me ;)

Bob

ASKER CERTIFIED SOLUTION