How to write SQL CLR in VB 2013

Hi,

Its not very obvious to open new project in VS 2013 in VB to find option for SQL CLR.
What you need to do is following:

Open VS 2013

Go to New Project

Dialog box will Appear

Expand Other languages and click on SQL Server

Click on SQL Server Database Project and click ok

When the project load, right click on project and click Add new item

Now to change the project in VB, right click on project and go to properties.

Select SQL CLR tab.

There you will see Language option.

Change it to Visual Basic.

Now right click on project and click Add new item

Now you this time you will see SQL CLR VB in left menu.

Select it and then add desired file according to need to build Store procedure or function.

Happy coding!

Alter Database failed because a lock could not be placed on a database

I was trying to take offline my database in SQL Server but getting above error. Usually there is an open connection to a database which need to be killed.

To view connections:
EXEC sp_who2

Check for any connections open to the database and get the SPID.
Now execute:
KILL <SPID>

SPID is sql server process ID which is assigned by SQL Server when connected to database.

HTH

Change Server Name in Crystal Reports

Changing Server Name is not very obvious but very easy.

1. In field explorer, Right Click on Database Fields.

2. Click on Set DataSource Location. It will open following window

3. Expand the Properties of the existing connection and right Click on Data Source as shown below

4. Click Edit, and type in the NEW Server Name and hit enter. It will change server name for all sub reports too.

5. Test the report before moving to production.

HTH.

Output Clause in SQL 2005 / 2008 / 2012

SQL Server 2005 and above has new Output clause.
This is very help to create a copy of what is inserted / updated / deleted.
Output clause has an access to inserted and deleted tables (like triggers) and the data can be copied to
table variable / temp table / permanent table.

Lets see an example:

Note: Table variable declaration need to be selected while running query.

Example using Insert statement:

create table customer (name varchar(100), joindate date)
go

declare @customer table (name varchar(100), joindate date)

-- Insert into table and also output to table variable
insert into customer (name, joindate)
output inserted.name, inserted.joindate into @customer 
values ('John', '01/01/2012')

-- check results
select * from customer 
select * from @customer 

VB Datatable SQL functions

Some DataTable Functions in VB.net

Create table in VB.net:

      Dim table As New DataTable("Orders")
      table.Columns.Add("OrderID", GetType(Int32))
      table.Columns.Add("OrderQuantity", GetType(Int32))
      table.Columns.Add("CompanyName", GetType(String))
      table.Columns.Add("Date", GetType(DateTime))

      Dim newRow As DataRow = table.NewRow()
      newRow("OrderID") = 1
      newRow("OrderQuantity") = 3
      newRow("CompanyName") = "NewCompanyName"
      newRow("Date") = "2012, 1, 31"

      ' Add the row to the rows collection.
      table.Rows.Add(newRow)

Plain Select

Private Sub GetAllRows()
    ' Get the DataTable of a DataSet. 
    Dim table As DataTable = DataSet1.Tables("Shippers")
    Dim rows() As DataRow = table.Select()

    Dim i As Integer
    ' Print the value one column of each DataRow. 
    For i = 0 to rows.GetUpperBound(0)
       Console.WriteLine(rows(i)("Name"))
    Next i
End Sub


Select with Expression

Private Sub GetFilteredRows()
    Dim table As DataTable = DataSet1.Tables("Books")
    ' Presuming the DataTable has a column named PublishedDate. 
    Dim expression As String
    expression = "PublishedDate > #1/1/10#"
    Dim foundRows() As DataRow

    ' Use the Select method to find all rows matching the filter.
    foundRows = table.Select(expression)

    Dim i As Integer
    ' Print column 0 of each returned row. 
    For i = 0 to foundRows.GetUpperBound(0)
       Console.WriteLine(foundRows(i)(0))
    Next i
End Sub

Multiple Filters 

Dim foundRows() As DataRow
foundRows()  = table.Select("Col1 = 'foo' AND Col2 = 'bar'")

Sort
Dim foundRows() As DataRow
' Sort descending by column named Column1
Dim sortOrder As String = "Col1 ASC" 
foundRows() = table.Select("Col1 = 'foo' AND Col2 = 'bar'", sortOrder)

Reference to Microsoft.SQLServer.ManagedDTS.dll - SQL Server 2012

To reference above dll from SQL 2012 Client tools, the existing project need to have framework of 4.0 or higher.

Also, need to add following code in config file under configuration tag. 
Make sure this is added after  <configsections> tag if any.

 <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0"/>
 </startup>

 
Else you will get error:

Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information   

Parse Excel file using vb.net

The following method will load excel file in datareader which will be inserted to Sql server via Sqlbulkcopy.


Imports Microsoft.Office.Interop
Imports System.Data.OleDb
Imports System.Data.SqlClient


Public Class clsLoadExcel

Function Process(ByVal filePath As String) As Boolean

Dim objExcelConn As _
New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes""")

Dim objQuery As String
Dim objCMD As OleDbCommand
Dim objDR As OleDbDataReader

Dim sqlConn As New SqlConnection
Dim sqlCmd As SqlCommand = New SqlCommand(sqlConn)
Dim sqlBCopy As New SqlBulkCopy(sqlConn)

sqlConn.ConnectionString = "data source=servername;Integrated security=true;Initial Catalog=db;"

objQuery = "SELECT * FROM [Sheet1$]" 'you can change your sheet name

Try
objCMD = New OleDbCommand(objQuery, objExcelConn)
objExcelConn.Open()          
objDR = objCMD.ExecuteReader

' open sql connection
sqlConn.Open()

' now write to sql
sqlBCopy.DestinationTableName = "TempTable"
sqlBCopy.WriteToServer(objDR)
       
Catch ex As Exception

Finally

If Not IsNothing(objDR) Then

objDR.Close()
End If

sqlConn.Close()
sqlBCopy.Close()
End Try

End Function

End Class