log4net AdoNetAppender stops logging / does not recover if database connection is lost / database is down, ReconnectOnError

I use the Apache log4net AdoNetAppender to log information from my application into a SQL database. In case, the network connection to the database is lost or the database is down for some reason, the AdoNetAppender quits logging (forever) to the database and does not recover even if the database is online again.

How-I-fixed-it:
I found the right hints here:
https://www.mail-archive.com/log4net-user@logging.apache.org/msg04655.html
http://logging.apache.org/log4net/release/sdk/log4net.Appender.AdoNetAppender.ReconnectOnError.html

Add the tag:

to the log4net XML config file:



   

Keep in mind, that it then tries to reconnect to the database until the connect times out. This blocks the thread of the application!

You can add:
Connect Timeout=1 to the connectionString  property to Limit the blocking to 1 second:
   
   
   
  


   
   
   
   
  

How to initialize / set a binary variable in T-SQL?

I have a stored procedure that expects a binary input parameter. I wanted to test it using a T-SQL statement in the query window. But i was wondering how to declare and set a binary variable using T-SQL.

How-i-fixed-it:
Just define the variable and set the bytes individually.

Example for a 6-Byte-Array:
declare @roi varbinary(6);

set @roi = 0x100010001000

exec sp_GetData_ROI 3001, @roi

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.