Tips
CLR function to delete older backup and log files in SQL Server
In a previous tip we looked at how to put together a CLR function for sorting text data. In addition, we have also written tips about how to mimic the functionality of maintenance plans without having to use a maintenance plan. In one of these previous tips, "Maintenance task to delete old backup files" we outlined how to delete older backup files by using a VB Script. To take this a step further, this tip will look at this same task to remove older backup and log files, but this time using a CLR function.
CLR String Sort Function in SQL Server 2005
With the introduction of SQL Server 2005, Microsoft released the Common Language Runtime (CLR) to allow developers and DBAs to take advantage of managed code outside of SQL Server. The original thought when this was announced was this was going to be a bad thing, because people that knew how to develop in a .NET language, but not T-SQL, would adopt this across the board. Over the past couple of years the opposite has happened. T-SQL still continues to be the primary language that is used and I have seen very few implementations where the CLR is being used. In this tip we will take a look at a simple example of where the CLR can come in handy and what a big improvement it can make on certain tasks.
Concat Aggregates SQL Server CLR Function
User written aggregates have proven very useful for solving a variety of problems that couldn't be handled easily before they became available in SQL Server 2005. The most useful for me has been the Concatenate aggregate to concatenate values in a column. I've used it frequently to create data files or in reports. In the original function the separator character is hard coded as a comma. What if you need a pipe as a separator? How about more than one character as a separator. And what if the concatenated string runs over 8000 characters?
Converting UTC to local time with SQL Server CLR
Suppose that your company uses an international ERP application which stores the date and time data as UTC, not as local time. Usually the conversion to the local time is handled at the application level. But this time you need a custom data export to another in house application and the date and time data should be expressed as local time. In this tip I'll explain a straightforward way to convert the UTC date and time to local time using a CLR scalar function.
Debugging SQL Server CLR functions, triggers and stored procedures
One of the nice things about developing your SQL Server CLR code in Visual Studio is that you get to take advantage of the debugging aspects of the tool. When developing it is always beneficial to use a debugger and step through your code to find any coding problems, but how is this done when writing CLR code for SQL Server?
Extending File System Operations in SQL Server Using CLR
While transferring data in and out of the file system, you need to perform file or directory operations like copy, move, or delete, with support for wildcards. As many SQL Server users are aware of, there is some support for file operations inside the Database Engine and Integration Services. In the first case, this is accomplished with calls to extended stored procedures, while in the second case this is accomplished by using the File System Task. In this tip we look at how we can extend these methods using SQL CLR functions.
How to execute a DOS command when xp_cmdshell is disabled in SQL Server
For security reasons many sites disable the extended stored procedure xp_cmdshell, which is used to run DOS commands or executables. When you really have to run a DOS command or an executable from a stored procedure how can you get around this limitation without a breakdown in security.
How To Return a Result Set from a SQL Server 2005 CLR Stored Procedure
We occasionally come up with a requirement that would be a good fit for a CLR function or stored procedure. For instance we would like to call a stored procedure to get the list of files in a particular folder. How can we return the list of files as a standard result set (i.e. rows and columns) using the CLR?
Implement financial functions in SQL Server using C#
You need to use financial functions as a tool to aid you in making business decisions and sometimes to run "what if" scenarios. Although there are numerous packages in the market offering this ability, they may be more than you actually need. Simply using the CLR feature in SQL Server, you are now able to develop your own set of financial functions to calculate, and possibly immediately store your results in the database.
Introduction to SQL Server CLR table valued functions
Table Value Functions (TVF) are great if you need to return multiple rows and/or multiple columns, because table valued user defined functions return their result as a table. Since result set acts like a table, you can just return the data or you can join the results to other tables. TVFs can be created using TSQL or the Common Language Runtime (CLR) and in this tip we walk through a simple example of a CLR function that returns file level contents from a folder.
Multi parameter CLR aggregate in SQL Server 2005
The very useful concat aggregate in the article How to pass multiple arguments to a CLR aggregate in SQL Server 2008 accepts multiple arguments and returns concatenated strings delimited by any string requested. That's more useful than the Concatenate aggregate from the article SQL Server CLR function to concatenate values in a column, which was orignally part of the Microsoft Sample code. There are still many systems running SQL Server 2005 and they could benefit from aggregates that accept multiple parameters. How is it possible to pass multiple parameters to a SQL Server 2005 CLR aggregate?
Read and Write Binary Files with the SQL Server CLR
You are a database developer looking for a common approach for handling read and write access to binary files. You may be a DBA wanting to read various information from binary files and collect it into tables. The code sample presented in this tip will get you started with binary file content handling in SQL Server.
Remove HTML tags from strings using the SQL Server CLR
There are situations when you may need to remove HTML tags from your character string data. As an example, consider having to submit a product data feed to a search engine like Google. The detailed product description is mandatory in this case. It is recommended that you remove all special characters and HTML formatting. This task can be handled in TSQL code, however in this case I have the opportunity to use .NET and the power of the regular expressions to manage the string. In this tip, I'll build a CLR function which cleans up a string of HTML tags and special characters. I'll use Visual Studio 2010 with C# as the programming language. Check out this tip for my solution.
SQL Server CLR and TSQL functions to parse a delimited string
There are several tips and articles on the internet that discuss how to split a delimited list into multiple rows. This tip shows two approaches to this problem a T-SQL function and a CLR function.
SQL Server CLR function to concatenate values in a column
Normalization is an important principal in database design, but it's often the opposite of what's needed in a good report. If a column is normalized, but the user really wants to see the values as a short comma separated list, how can I write a query that produces the list? Concatenating the values in a column would be pretty easy if SQL Server had a concatenate aggregate function, which it doesn't. What's more, for efficiency sake it's important to write the reporting queries without using cursors.
SQL Server function to validate email addresses
When you send e-mail to large lists, validating the e-mail addresses before sending out the e-mail is worth doing to prevent having mail rejection messages clog up your mail server. I had been doing the validation with a T-SQL User Defined Function (UDF), but it was getting too slow as the number of e-mails grew. I needed a faster alternative, what options do I have?
SQL Server random numerics data generation using CLR
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.
SQL Server Regular Expressions for Data Validation and Cleanup
You need to provide data validation at the server level for complex strings like phone numbers, email addresses, etc. You may also need to do data cleanup / standardization before moving it from source to target. Although SQL Server provides a fair number of string functions, the code developed with these built-in functions can become complex and hard to maintain or reuse. In this tip we look at CLR functions using regular expressions.
Use the SQL Server CLR to Read and Write Text Files
You are a database developer looking for a common approach for handling read write access to text files. You may be a DBA wanting to write procedure execution results to files for documentation purposes. The code samples presented in this tip will get you started with text file content handling in SQL Server.
Writing to an operating system file using the SQL Server SQLCLR
Reading files from the operating system can be done with T-SQL as I showed in the tip Using OPENROWSET to read large files into SQL Server. What if you want to write to an operating system file? For example, writing to a text file. There is no T-SQL that supports writing to a file.
Top 10
SQL Server Regular Expressions for Data Validation and Cleanup
You need to provide data validation at the server level for complex strings like phone numbers, email addresses, etc. You may also need to do data cleanup / standardization before moving it from source to target. Although SQL Server provides a fair number of string functions, the code developed with these built-in functions can become complex and hard to maintain or reuse. In this tip we look at CLR functions using regular expressions.
How to execute a DOS command when xp_cmdshell is disabled in SQL Server
For security reasons many sites disable the extended stored procedure xp_cmdshell, which is used to run DOS commands or executables. When you really have to run a DOS command or an executable from a stored procedure how can you get around this limitation without a breakdown in security.
SQL Server CLR function to concatenate values in a column
Normalization is an important principal in database design, but it's often the opposite of what's needed in a good report. If a column is normalized, but the user really wants to see the values as a short comma separated list, how can I write a query that produces the list? Concatenating the values in a column would be pretty easy if SQL Server had a concatenate aggregate function, which it doesn't. What's more, for efficiency sake it's important to write the reporting queries without using cursors.
Introduction to SQL Server CLR table valued functions
Table Value Functions (TVF) are great if you need to return multiple rows and/or multiple columns, because table valued user defined functions return their result as a table. Since result set acts like a table, you can just return the data or you can join the results to other tables. TVFs can be created using TSQL or the Common Language Runtime (CLR) and in this tip we walk through a simple example of a CLR function that returns file level contents from a folder.
Remove HTML tags from strings using the SQL Server CLR
There are situations when you may need to remove HTML tags from your character string data. As an example, consider having to submit a product data feed to a search engine like Google. The detailed product description is mandatory in this case. It is recommended that you remove all special characters and HTML formatting. This task can be handled in TSQL code, however in this case I have the opportunity to use .NET and the power of the regular expressions to manage the string. In this tip, I'll build a CLR function which cleans up a string of HTML tags and special characters. I'll use Visual Studio 2010 with C# as the programming language. Check out this tip for my solution.
Read and Write Binary Files with the SQL Server CLR
You are a database developer looking for a common approach for handling read and write access to binary files. You may be a DBA wanting to read various information from binary files and collect it into tables. The code sample presented in this tip will get you started with binary file content handling in SQL Server.
Converting UTC to local time with SQL Server CLR
Suppose that your company uses an international ERP application which stores the date and time data as UTC, not as local time. Usually the conversion to the local time is handled at the application level. But this time you need a custom data export to another in house application and the date and time data should be expressed as local time. In this tip I'll explain a straightforward way to convert the UTC date and time to local time using a CLR scalar function.
How To Return a Result Set from a SQL Server 2005 CLR Stored Procedure
We occasionally come up with a requirement that would be a good fit for a CLR function or stored procedure. For instance we would like to call a stored procedure to get the list of files in a particular folder. How can we return the list of files as a standard result set (i.e. rows and columns) using the CLR?
SQL Server function to validate email addresses
When you send e-mail to large lists, validating the e-mail addresses before sending out the e-mail is worth doing to prevent having mail rejection messages clog up your mail server. I had been doing the validation with a T-SQL User Defined Function (UDF), but it was getting too slow as the number of e-mails grew. I needed a faster alternative, what options do I have?
Writing to an operating system file using the SQL Server SQLCLR
Reading files from the operating system can be done with T-SQL as I showed in the tip Using OPENROWSET to read large files into SQL Server. What if you want to write to an operating system file? For example, writing to a text file. There is no T-SQL that supports writing to a file.
Last 10
Introduction to SQL Server CLR table valued functions
Table Value Functions (TVF) are great if you need to return multiple rows and/or multiple columns, because table valued user defined functions return their result as a table. Since result set acts like a table, you can just return the data or you can join the results to other tables. TVFs can be created using TSQL or the Common Language Runtime (CLR) and in this tip we walk through a simple example of a CLR function that returns file level contents from a folder.
Remove HTML tags from strings using the SQL Server CLR
There are situations when you may need to remove HTML tags from your character string data. As an example, consider having to submit a product data feed to a search engine like Google. The detailed product description is mandatory in this case. It is recommended that you remove all special characters and HTML formatting. This task can be handled in TSQL code, however in this case I have the opportunity to use .NET and the power of the regular expressions to manage the string. In this tip, I'll build a CLR function which cleans up a string of HTML tags and special characters. I'll use Visual Studio 2010 with C# as the programming language. Check out this tip for my solution.
Converting UTC to local time with SQL Server CLR
Suppose that your company uses an international ERP application which stores the date and time data as UTC, not as local time. Usually the conversion to the local time is handled at the application level. But this time you need a custom data export to another in house application and the date and time data should be expressed as local time. In this tip I'll explain a straightforward way to convert the UTC date and time to local time using a CLR scalar function.
Read and Write Binary Files with the SQL Server CLR
You are a database developer looking for a common approach for handling read and write access to binary files. You may be a DBA wanting to read various information from binary files and collect it into tables. The code sample presented in this tip will get you started with binary file content handling in SQL Server.
Use the SQL Server CLR to Read and Write Text Files
You are a database developer looking for a common approach for handling read write access to text files. You may be a DBA wanting to write procedure execution results to files for documentation purposes. The code samples presented in this tip will get you started with text file content handling in SQL Server.
SQL Server random numerics data generation using CLR
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.
Implement financial functions in SQL Server using C#
You need to use financial functions as a tool to aid you in making business decisions and sometimes to run "what if" scenarios. Although there are numerous packages in the market offering this ability, they may be more than you actually need. Simply using the CLR feature in SQL Server, you are now able to develop your own set of financial functions to calculate, and possibly immediately store your results in the database.
Extending File System Operations in SQL Server Using CLR
While transferring data in and out of the file system, you need to perform file or directory operations like copy, move, or delete, with support for wildcards. As many SQL Server users are aware of, there is some support for file operations inside the Database Engine and Integration Services. In the first case, this is accomplished with calls to extended stored procedures, while in the second case this is accomplished by using the File System Task. In this tip we look at how we can extend these methods using SQL CLR functions.
SQL Server Regular Expressions for Data Validation and Cleanup
You need to provide data validation at the server level for complex strings like phone numbers, email addresses, etc. You may also need to do data cleanup / standardization before moving it from source to target. Although SQL Server provides a fair number of string functions, the code developed with these built-in functions can become complex and hard to maintain or reuse. In this tip we look at CLR functions using regular expressions.
How to execute a DOS command when xp_cmdshell is disabled in SQL Server
For security reasons many sites disable the extended stored procedure xp_cmdshell, which is used to run DOS commands or executables. When you really have to run a DOS command or an executable from a stored procedure how can you get around this limitation without a breakdown in security.
