Add Numbers from string(varchar) in tsql

Once I was asked by my colleague to write a function which will add the numbers from a string, so I write a function for it. I used the TSQL substring,  Len, ASCII and char functions to achieve this task.

Code Snippet:

CREATE FUNCTION SelectNumbersFromString(@str varchar(50)) RETURNS varchar(MAX) AS BEGIN DECLARE @cchk char(5); DECLARE @len int ; DECLARE @aschr int;
SET @len =
  (SELECT len(@str)); DECLARE @count int
SET @count = 1 DECLARE @ans varchar(MAX)
SET @ans = '' While @count <= @len BEGIN
SET @cchk =
  (SELECT Substring(@str,@count,1));
SET @aschr =
  (SELECT ASCII(@cchk)); IF @aschr IN (49,
                                       50,
                                       51,
                                       52,
                                       53,
                                       54,
                                       55,
                                       56,
                                       57,
                                       58) BEGIN
SET @ans = @ans + CHAR(@aschr) END
SET @count = @count + 1; END RETURN @ans; END

Send DataSet/DataTable From Sql Clr Stored Procedure

In this post I will show you how to send a datatable or dataset from a sql clr procedure. Firstly create a new sql clr project. If you have no idea how to create a sql clr project then see the following post.

Creating a sql clr project

After creating sql clr project go to Add New Item and add a new stored procedure template. This will create the following code: 

public partial class StoredProcedures  
 {  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SendData()  
   {      
      
   }  
 };  

Now I will write two functions SendDataSet and SendDataTable. In SendDataSet We will check whether DataSet is null or not, if dataset is not null then we will call the SendDataTable function. In SendDataTable function we will first determine the datatypes of datacolumns, for this we have a function ExtractDataTableColumnMetaData. This function will return the metadata of the given datatable. Now we will assign the metadata to the SqlDataRecord. After assigning it we will use the SqlContext.Pipe methods to Send the row. Below is the complete code.

public partial class StoredProcedures  
 {  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SendData()  
   {  
     string connectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI;";  
     string cmd = "select * from categories; select * from customers";  
     using (SqlConnection conn = new SqlConnection(connectionString))  
     {  
       SqlCommand sqlCmd = new SqlCommand(cmd, conn);  
       SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);  
   
       DataSet ds = new DataSet("Test");  
       adapter.Fill(ds);  
       SendDataSet(ds);  
     }  
   }  
   
   public static void SendDataSet(DataSet ds)  
   {  
     if (ds == null)  
     {  
       throw new ArgumentException("SendDataSet requires a non-null data set.");  
     }  
     else  
     {  
       foreach (DataTable dt in ds.Tables)  
       {  
         SendDataTable(dt);  
       }  
     }  
   }  
   
   
   public static void SendDataTable(DataTable dt)  
   {  
     bool[] coerceToString; // Do we need to coerce this column to string?  
     SqlMetaData[] metaData = ExtractDataTableColumnMetaData(dt, out coerceToString);  
   
     SqlDataRecord record = new SqlDataRecord(metaData);  
     SqlPipe pipe = SqlContext.Pipe;  
     pipe.SendResultsStart(record);  
     try  
     {  
       foreach (DataRow row in dt.Rows)  
       {  
         for (int index = 0; index < record.FieldCount; index++)  
         {  
           object value = row[index];  
           if (null != value && coerceToString[index])  
             value = value.ToString();  
           record.SetValue(index, value);  
         }  
          
         pipe.SendResultsRow(record);  
       }  
     }  
     finally  
     {  
       pipe.SendResultsEnd();  
     }  
   }  
   
   private static SqlMetaData[] ExtractDataTableColumnMetaData(DataTable dt, out bool[] coerceToString)  
   {  
     SqlMetaData[] metaDataResult = new SqlMetaData[dt.Columns.Count];  
     coerceToString = new bool[dt.Columns.Count];  
     for (int index = 0; index < dt.Columns.Count; index++)  
     {  
       DataColumn column = dt.Columns[index];  
       metaDataResult[index] = SqlMetaDataFromColumn(column, out coerceToString[index]);  
     }  
   
     return metaDataResult;  
   }  
   
   private static Exception InvalidDataTypeCode(TypeCode code)  
   {  
     return new ArgumentException("Invalid type: " + code);  
   }  
   
   private static Exception UnknownDataType(Type clrType)  
   {  
     return new ArgumentException("Unknown type: " + clrType);  
   }  
   
   private static SqlMetaData SqlMetaDataFromColumn(DataColumn column, out bool coerceToString)  
   {  
     coerceToString = false;  
     SqlMetaData smd = null;  
     Type clrType = column.DataType;  
     string name = column.ColumnName;  
     switch (Type.GetTypeCode(clrType))  
     {  
       case TypeCode.Boolean: smd = new SqlMetaData(name, SqlDbType.Bit); break;  
       case TypeCode.Byte: smd = new SqlMetaData(name, SqlDbType.TinyInt); break;  
       case TypeCode.Char: smd = new SqlMetaData(name, SqlDbType.NVarChar, 1); break;  
       case TypeCode.DateTime: smd = new SqlMetaData(name, SqlDbType.DateTime); break;  
       case TypeCode.DBNull: throw InvalidDataTypeCode(TypeCode.DBNull);  
       case TypeCode.Decimal: smd = new SqlMetaData(name, SqlDbType.Decimal, 18, 0); break;  
       case TypeCode.Double: smd = new SqlMetaData(name, SqlDbType.Float); break;  
       case TypeCode.Empty: throw InvalidDataTypeCode(TypeCode.Empty);  
       case TypeCode.Int16: smd = new SqlMetaData(name, SqlDbType.SmallInt); break;  
       case TypeCode.Int32: smd = new SqlMetaData(name, SqlDbType.Int); break;  
       case TypeCode.Int64: smd = new SqlMetaData(name, SqlDbType.BigInt); break;  
       case TypeCode.SByte: throw InvalidDataTypeCode(TypeCode.SByte);  
       case TypeCode.Single: smd = new SqlMetaData(name, SqlDbType.Real); break;  
       case TypeCode.String: smd = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);  
         break;  
       case TypeCode.UInt16: throw InvalidDataTypeCode(TypeCode.UInt16);  
       case TypeCode.UInt32: throw InvalidDataTypeCode(TypeCode.UInt32);  
       case TypeCode.UInt64: throw InvalidDataTypeCode(TypeCode.UInt64);  
       case TypeCode.Object:  
         smd = SqlMetaDataFromObjectColumn(name, column, clrType);  
         if (smd == null)  
         {  
           // Unknown type, try to treat it as string;  
           smd = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);  
           coerceToString = true;  
         }  
         break;  
   
       default: throw UnknownDataType(clrType);  
     }  
   
     return smd;  
   }  
   
   private static SqlMetaData SqlMetaDataFromObjectColumn(string name, DataColumn column, Type clrType)  
   {  
     SqlMetaData smd = null;  
     if (clrType == typeof(System.Byte[]) || clrType == typeof(SqlBinary) || clrType == typeof(SqlBytes) ||  
   clrType == typeof(System.Char[]) || clrType == typeof(SqlString) || clrType == typeof(SqlChars))  
       smd = new SqlMetaData(name, SqlDbType.VarBinary, column.MaxLength);  
     else if (clrType == typeof(System.Guid))  
       smd = new SqlMetaData(name, SqlDbType.UniqueIdentifier);  
     else if (clrType == typeof(System.Object))  
       smd = new SqlMetaData(name, SqlDbType.Variant);  
     else if (clrType == typeof(SqlBoolean))  
       smd = new SqlMetaData(name, SqlDbType.Bit);  
     else if (clrType == typeof(SqlByte))  
       smd = new SqlMetaData(name, SqlDbType.TinyInt);  
     else if (clrType == typeof(SqlDateTime))  
       smd = new SqlMetaData(name, SqlDbType.DateTime);  
     else if (clrType == typeof(SqlDouble))  
       smd = new SqlMetaData(name, SqlDbType.Float);  
     else if (clrType == typeof(SqlGuid))  
       smd = new SqlMetaData(name, SqlDbType.UniqueIdentifier);  
     else if (clrType == typeof(SqlInt16))  
       smd = new SqlMetaData(name, SqlDbType.SmallInt);  
     else if (clrType == typeof(SqlInt32))  
       smd = new SqlMetaData(name, SqlDbType.Int);  
     else if (clrType == typeof(SqlInt64))  
       smd = new SqlMetaData(name, SqlDbType.BigInt);  
     else if (clrType == typeof(SqlMoney))  
       smd = new SqlMetaData(name, SqlDbType.Money);  
     else if (clrType == typeof(SqlDecimal))  
       smd = new SqlMetaData(name, SqlDbType.Decimal, SqlDecimal.MaxPrecision, 0);  
     else if (clrType == typeof(SqlSingle))  
       smd = new SqlMetaData(name, SqlDbType.Real);  
     else if (clrType == typeof(SqlXml))  
       smd = new SqlMetaData(name, SqlDbType.Xml);  
     else  
       smd = null;  
   
     return smd;  
   }  
   
 };  

After writing the whole code, Deploy the solution.Before deploying Must remember to change the Permission Level to external or unsafe, and also remember to give trustworthy right to the database by running the following command on the sql server management studio.

alter database DataBaseName set trustworthy on  

and one more thing CLR integration must be enabled, if it is not then run this command

sp_configure 'clr enabled', 1     
 GO     
 RECONFIGURE     
 GO  

Download the whole solution from skydrive, just change the database reference. Thanks.
SendDataSet

References:
The below msdn post helps me alot
http://msdn.microsoft.com/en-us/library/ff878201.aspx

Steps to enable CLR Integration, OLE Automation and xp_cmdshell procedures in sql server 2005 and 2008

For SQL Server 2005:
Follow these steps:
1) From start menu open Sql Server 2005 surface are configuration

2) Select Surface Area Configuration For Features

Select OLE Automation from Database Engine node and then checked the Enable OLE Automation checked box to enable the feature. Follow the same step for CLR Integration and xp_cmdshell. Then click the apply button and close the window. The CLR Integration, OLE Automation features and xp_cmdshell features are now enabled.


For SQL Server 2008:
1) Open the SQL Server 2008 and follow the below steps

After performing the above steps click he OK button.  The OLE Automation features and xp_cmdshell features are now enabled.
To enable CLR Integration select the server configuration from the drop down menu.

Change the ClrIntegrationEnabled value from false to true to enable it. Now CLR Integration is enabled.
or run the following script to enable CLR Integration

sp_configure 'show advanced options', 1;   
  GO   
  RECONFIGURE;   
  GO   
  sp_configure 'clr enabled', 1;   
  GO   
  RECONFIGURE;   
  GO   

Reference:
http://msdn.microsoft.com/en-us/library/ms161956.aspx

Reading and Writing Files using T-SQL ( Sql Server )

Reading a File:

OPENROWSET is a T-SQL function that allows reading data from many sources including using the SQL Server’s BULK import capability. One of the useful features of the BULK provider is its ability to read individual files from the file system into SQL Server, such as loading a data from a text file or a Word document into a SQL Server table.

The BULK option was added to T-SQL in SQL Server 2005 and it persists in SQL Server 2008.
When used with the BULK provider keyword you can name a data file to read as one of three types of objects:

1. SINGLE_BLOB, which reads a file as varbinary(max)
2. SINGLE_CLOB, which reads a file as varchar(max) 
3. SINGLE_NCLOB, which reads a file as nvarchar(max) 

OPENROWSET returns a single column, named BulkColumn, as its result. Here’s an example that reads a text file:
Query:

SELECT BulkColumn 
FROM OPENROWSET (BULK 'C:\Test\test.txt', SINGLE_CLOB) MyFile

Output:

Writing a File:

1) Write a File using  xp_cmdshell:

Before using xp_cmdshell, we first have to enable xp_cmdshell feature, due to security reasons it is disabled by default.
See the following post
Steps to enable OLE Automation and xp_cmdshell procedures in sql server 2005 and 2008
                                                                     OR

EXEC sp_configure 'show advanced options', 1

GO

--- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

--- To update the currently configured value for this feature.

RECONFIGURE

GO  

Query:

Declare @text varchar(max)
Declare @data cursor
Declare @a varchar(20)
Declare @b varchar(30)
DECLARE @cmd sysname, @var sysname

SET @data = Cursor for
Select FirstName,LastName from dbo.Employees // Northwind database


Open @data
WHILE (1 <> 0)
BEGIN

    FETCH NEXT FROM @data INTO @a , @b

    SET @text ='First Name :'+@a+' and Last Name :'+@b

    IF ( @@FETCH_STATUS <>  0 )
    BEGIN
        BREAK;
    END

SET @cmd = 'echo ' + @text + ' >> c:\var_out.txt'
exec master..xp_cmdshell @cmd

END

In the above code, we declare a cursor  @data in which we stored first name and last name of employees and then open the @data cursor and place an infinite while loop. The IF condition is placed inside the while loop to check @@Fetch_Status. The loop will continue works until the @@Fetch_Status does not return 0.
In the end the records are written to the var_out.txt file. There are multiple records in the employees table, all of them are appended in the same file.

2) Using OLE Automation Stored Procedures:

CREATE PROCEDURE sp_OAToFile(@FileName varchar(255), @Text1 text) AS
DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

GO

SET QUOTED_IDENTIFIER OFF
GO 
SET ANSI_NULLS ON
GO

In the above code, we created a stored procedure which takes two parameters file name and text. File name is the name of the file which will be created and text is the content which is written to the file.
sp_OACreate creates an instance of OLE object.
sp_OAMethod calls the method of OLE object.
and last one sp_OADestroy procedure destroys the OLE object.

References:
OLE Automation Stored Procedures.
http://msdn.microsoft.com/en-us/library/ms190501(v=sql.110).aspx
Read file reference
http://www.mssqltips.com/sqlservertip/1643/using-openrowset-to-read-large-files-into-sql-server/

Important MS SQL server system Views

Microsoft SQL server has a lot of built in system views which provides information about almost all of the objects in the database. Let's talk about the most important system views:

1)sys.databases
This view will return all the databases in the instance of a Sql Server.

Columns:
name ( datatype sysname ) of database is unique with in the instance of a SQL Server
database_id ( datatype int ) of database is unique with in the instance of a SQL Server
Database state ( type tinyint ) :

0 = ONLINE

1 = RESTORING

2 = RECOVERING

3 = RECOVERY_PENDING

4 = SUSPECT

5 = EMERGENCY

6 = OFFLINE

2) sys.objects

This view provides information about all of the objects in the selected database. 

The name ( datatype sysname ) is the name of the object.

The object_id ( datatype int ) is unique with in the database.

The type column ( datatype char(2) ) specifies whether it is a function, procedure or a table etc.

Important types are given below.

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

Example:

select * from sys.objects     
where type='V'
            OR
select * from sys.views
--Both queries will bring the same result.

3) sys.procedures

This procedure will return all the procedures from the selected database

3) sys.tables

This view will returns all the tables from the selected database

4) sys.views 

This view will return all the views from the selected database.

5) sys.triggers;

This view will return all the triggers from the selected database.

6) sys.columns
This will retrun all the columns of the objects ( Table,Views etc ) in the selected database

Source:
MSDN
Read more at

http://msdn.microsoft.com/en-us/library/ms178534.aspx

http://msdn.microsoft.com/en-us/library/ms189783.aspx