Assembly not found in SQL Catalog for VSS

I'm trying to connect to VSS from my CLR procedure.

I'm getting this error..

Assembly

'microsoft.visualstudio.sourcesafe.interop, version=5.2.0.0,

culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the

SQL catalog.

I added reference to VSS dll by opening the project file in notepad, as I couldn't right-click and do an add reference.

Here's the code...

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()

{

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");

}

}

};

from BOL 2005 (April), CREATE ASSEMBLY (Transact-SQL):

Assembly Validation

SQL Server performs checks on the assembly binaries uploaded by the CREATE ASSEMBLY statement to guarantee the following:

The assembly binary is well formed with valid metadata and code segments, and the code segments have valid Microsoft Intermediate language (MSIL) instructions.

The set of system assemblies it references is one of the following supported assemblies in SQL Server: Microsoft.Visualbasic.dll, Mscorlib.dll, System.Data.dll, System.dll, System.Xml.dll, Microsoft.Visualc.dll, Custommarshallers.dll, System.Security.dll, System.Web.Services.dll, and System.Data.SqlXml.dll. Other system assemblies can be referenced, but they must be explicitly registered in the database.

For assemblies created by using SAFE or EXTERNAL ACCESS permission sets:

The assembly code should be type-safe. Type safety is established by running the common language runtime verifier against the assembly.

The assembly should not contain any static data members in its classes unless they are marked as read-only.

The classes in the assembly cannot contain finalizer methods.

The classes or methods of the assembly should be annotated only with allowed code attributes. For more information, see Custom Attributes for CLR Routines.

|||

Visual Studio does not automatically register all references. That is the reason you are not allowed to add arbitrary references to your SQL Server project. You can add references to only supported .NET framework assemblies or assemblies that are already registered in the database.

What you need to do in this case - Register the assembly you want to refer in the database and then add a reference to it.

Thanks,

-Vineet.

Assembly MyAssembly was not found in the SQL catalog of database MyDB

I’m trying to register my CLR UDF in SQL 2005 using this code

CREATE FUNCTION GetSomething() RETURNS INT

AS EXTERNAL NAME MyAssembly.MyFunction.MyMethod

When I run it against my DB I get this error:

Assembly MyAssembly was not found in the SQL catalog of database MyDB

I’ve successfully registered my custom assembly in the DB (I see it under Assemblies folder), and I’ve set CRL Enabled to 1 in my DB.

What am I doing wrong?

Thanks in advance

A couple of things:

1. make sure that the CREATE FUNCTION call is actually executed in the database where MyAssembly is located.
2. Make sure that you spell the name correctly of the assembly

if both of those are OK:
1. then check that you do not have any namespaces in the class name. For example, VB injects a namespace into the assembly, so the class name would be [namespace.classname].
2. make sure that the classname and method name are capitalize correctly - they are case sensitive.

Niels

|||I have same problem and I do deployment using Visual Studio .NET 2005 final release, it is as nightmare to see these outstanding bug free products ...

Assembly MyAssembly was not found in the SQL catalog of database MyDB

I’m trying to register my CLR UDF in SQL 2005 using this code

CREATE FUNCTION GetSomething() RETURNS INT

AS EXTERNAL NAME MyAssembly.MyFunction.MyMethod

When I run it against my DB I get this error:

Assembly MyAssembly was not found in the SQL catalog of database MyDB

I’ve successfully registered my custom assembly in the DB (I see it under Assemblies folder), and I’ve set CRL Enabled to 1 in my DB.

What am I doing wrong?

Thanks in advance

A couple of things:

1. make sure that the CREATE FUNCTION call is actually executed in the database where MyAssembly is located.
2. Make sure that you spell the name correctly of the assembly

if both of those are OK:
1. then check that you do not have any namespaces in the class name. For example, VB injects a namespace into the assembly, so the class name would be [namespace.classname].
2. make sure that the classname and method name are capitalize correctly - they are case sensitive.

Niels

|||I have same problem and I do deployment using Visual Studio .NET 2005 final release, it is as nightmare to see these outstanding bug free products ...

assembly for stored procedure

I was playing around with the CLR in writing assemblies for the sql server 2005 stored procedure. I guess the example i found was for the beta version

This line is from the beta but no longer works. Any ideas what will fix this. There is no longer GetCommand property.

SqlCommand cmd =SqlContext.GetCommand();

Example

publicpartialclassStoredProcedures{[Microsoft.SqlServer.Server.SqlProcedure] publicstaticvoid StoredProcedure1()

{

// Put your code here

SqlCommand cmd =SqlContext.GetCommand();

cmd.CommandText="select firstname + ' ' + lastname + as [name] from person.contact";

SqlDataReader rdr = cmd.ExecuteReader();

SqlPipe sp =SqlContext.GetPipe();

sp.Send(rdr);

}};

Hi,

yes that has changed quite a bit till SQL 2005's RTM. Something like

using(SqlConnection connection = new SqlConnection("context connection=true"))
{

connection.Open();
SqlCommand cmd=new SqlCommand("select firstname + ' ' + lastname + as [name] from person.contact",connection);

SqlContext.Pipe.ExecuteAndSend(cmd);

}

For more information:http://msdn2.microsoft.com/en-us/library/ms190790.aspx