SQLCLR SQL-Server UDF/SP binary parameter is cut after 8000 Bytes

I created an SQLCLR C# Stored Procedure which expects a paramter as large byte[] and returns the result in a large byte[].

Using the SQLBinary class as default, i run into the problem that the parameter was cut after 8000 Bytes.

How-i-fixed-it:
I found the right hints here:
http://stackoverflow.com/questions/840552/clr-udf-returning-varbinarymax

SqlBinary is VARBINARY(8000) while the default for SqlBytes is VARBINARY(MAX)

I found two ways to overcome the 8000 Bytes limit:

For the large return value i used:
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Data", SqlDbType.VarBinary, -1));

For the input Parameter i used:
SqlBytes roiBytes

C# SQLCLR SqlMetaData with SqlDbType.Float column getting InvalidCastException on SqlDataRecord.SetFloat

In my SQLCLR C# Stored Procedure on SQL Server 2008 R2 i returned the results using a SqlDataRecord that has a column of SqlDbType.Float.

Trying to set the value using the SetFloat Method, i got an InvalidCastException.

How-i-fixed-it:
I found out that SqlDbType.Float is equal to a System.Double data type. Using the SetDouble Method it works fine.

Here i found the right hints:
http://msdn.microsoft.com/de-de/library/vstudio/system.data.sqldbtype(v=vs.80).aspx

http://msdn.microsoft.com/de-de/library/vstudio/microsoft.sqlserver.server.sqldatarecord.setfloat.aspx

http://msdn.microsoft.com/de-de/library/vstudio/microsoft.sqlserver.server.sqldatarecord.setdouble.aspx






 



 

SQLCLR error: Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)

I was referencing the "System.Drawing" DLL (Version 2.0.0.0) in my SQLCLR Assembly Project.
It was running fine for ages on the Server until i deployed a new version of my Assembly to it today:
Error message when you execute a CLR routine or use an assembly in SQL Server: "Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)"

How-i-fixed-it:
I was able to fix my problem by executing this T-SQL command on my database:

Alter Assembly [System.Drawing]
From 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll'

The issue is also discussed here:
http://social.technet.microsoft.com/Forums/sqlserver/en-US/17df0715-7608-480d-8ad6-78dcf6c9d43a/different-version-in-gac-and-SQL

Link to Microsoft Knowledge Base Article:
http://support.microsoft.com/kb/949080

Can't open sqlproj with Visual Studio 2012? Install SQL Server Data Tools.

I recently updated to Visual Studio 2012 Ultimate.
When i tried to open an existing SQL Server SQLCLR Project (*.sqlproj), it says that the sqlproj is incompatible with the version I am running.

How-i-fixed-it:
I installed the SQL Server Data Tools from http://msdn.microsoft.com/en-us/data/tools.aspx.

I found the solution here:

http://stackoverflow.com/questions/11906691/visual-studio-2012-csproj-and-sqlproj

and here:
http://www.comoke.com/index.php/2012/10/visual-studio-2012-database-project-sqlproj-project-type-not-supported/

and here:
http://blog.tentaclesoftware.com/archive/2013/02/11/visual-studio-2010-and-2012-sqlproj-interoperability.aspx

SQL Server Message 6260, system.overflowexception because of arithmetic overflow, error caused by divison by 0

One of my C# SQLCLR table valued function started to throw an exception with some recently added data to the database:

SQL Server Message 6260
system.overflowexception Arithmetic Overflow

How-i-fixed-it:
It turned out to be caused by a divison by 0 happening at runtime because of bad records in the data. I added some code to check for this which fixed the problem of the arithemtic overlow.

Use System.Drawing.dll from SQLCLR C# code on SQL-Server

I created a SQLCLR-Assembly that uses the System.Drawing Namespace. I was not able to deploy it from Visual Studio to the SQL Server because of the error message:

SQL72014: .Net SqlClient Data Provider: Meldung 6503, Ebene 16, Status 12, Zeile 1 Die system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.-Assembly wurde im SQL-Katalog nicht gefunden.

How-i-fixed-it:

In SQL-Server Management Studio open up a new query:

ALTER DATABASE your_db_name
SET TRUSTWORTHY ON
GO
CREATE ASSEMBLY [System.Drawing]
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll'
WITH PERMISSION_SET = UNSAFE
GO

Maybe you receive the following error:

Msg 33009, Level 16, State 2, Line 2
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XYZ'. You should correct this situation by resetting the owner of database 'XYZ' using the ALTER AUTHORIZATION Statement.
Here if found the solution: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81989

USE your_db_name
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO