XLSExport: A CLR procedure to export proc results to Excel
A CLR procedure utilizing the NPOI library to export the results of a passed stored procedure to an Excel spreadsheet.
2011-06-23
9,993 reads
Simon Hundleby,
2015-12-01 (first published: 2015-11-06)
CREATE ASSEMBLY [Hashbytes] FROM D:\HashBytes.dll --Location of precompiled DLL WITH PERMISSION_SET = UNSAFE; CREATE FUNCTION Hashbyte(@Inputstring NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) WITH EXECUTE AS CALLER AS EXTERNAL NAME Hashbytes.hashbytes.hasBytes C# Code --------- using System; using System.Collections; using System.Data; using System.Security.Cryptography; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public class hashbytes { public hashbytes() { } public static string hashBytes(SqlString Hashsource) { string str = Convert.ToString(Hashsource); SHA256 sha = new SHA256CryptoServiceProvider(); // used to create the SHA256 hash byte[] hashBytes = sha.ComputeHash(System.Text.UnicodeEncoding.Unicode.GetBytes(str)); System.Text.StringBuilder sb = new System.Text.StringBuilder(); for (int i = 0; i < hashBytes.Length; i++) { sb.Append(hashBytes[i].ToString("X2")); } return "0X" +sb.ToString(); } }
A CLR procedure utilizing the NPOI library to export the results of a passed stored procedure to an Excel spreadsheet.
You need to generate random data directly into SQL Server table columns or close to the database engine as variables or expressions. Looking at the SQL Server available functions, you notice that only RAND function offers support for random data generation. Although RAND([seed]) is a built-in function, it can only return a float value between 0 and 1, and has other limitations in regards to seed values. Because your table columns may be of various data types, and each data type may have a lower value and an upper value, you would prefer to create your custom random data generators. This is when SQL Server CLR functions come into play and provide a viable solution.
2011-03-24
3,275 reads
Using SqlTypes can have a significant impact on the performance of your SQL CLR implementations. Is it for the better? Read this article to find out.
2010-07-21
7,543 reads
When looking for impending problems due to lack of disk space it's necessary to know how much space is available on each drive. There have been other tips written about how to do this, but in this tip I show you a way this can be done using SQLCLR.
2010-04-27
3,390 reads
SQL CLR Table-Valued Functions can stream data back, but there aren't many examples of the quick and easy method. Solomon Rutzky brings us an article that givse you an example you can extend to your environment.