Tag Archives: Sql Server

Convert rows to comma separated list and insert in a table

While performing one of my query diagnostics I had to gather some ids from one database server and use those ids in another. The best way was to gather all ids in a comma separated or in a proper list which can be used to insert into a temp table on another sql server.

I used the below query to gather the list of ids separated by ‘UNION ALL SELECT’

				  FROM dbo.Car c (NOLOCK)
				  INNER JOIN dbo.CarItem ci (NOLOCK) on c.CarId = ci.CarId
                   FOR XML PATH('')

The result produced an XML formatted file which I used in another server to insert in a temp table.

In the process I came to know of one restriction of the SQL Server select/insert that the maximum number of rows allowed are only 4096.

Connecting WebService from SQL stored procedure

When I wanted to connect a web service and perform some tasks in database at regular intervals of time, my first choice was going for windows service. That was easy and I was familiar with the process. Due to some constraints in my organization, I had to look for alternatives.I was able to access the webservice from sql clr.Steps:1. Create a wsdl class file of the webservice.using in command prompt

 wsdl http://www.test.com/WebService/Testsvc.asmx /language:vb /out:C:\TestSvc.vb 

 Include this class file in the application where you are creating a sql clr. Below is an example:
Imports Microsoft.VisualBasic
Imports System
Imports System.data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
Imports System.XmlPartial 

Public Class StoredProcedures_

  Public Shared Sub GetTest( ByRef value1 As SqlInt32, ByRef value2 As SqlString)
    Dim Connection As New SqlConnection("Context Connection = true")
    Dim iWeb As New com.WSMainTestDim result as Integer = iWeb.GetAdditionTest(value1, value2)Connection.Open()
    Dim insert As String = "Insert into tblResponse (Text) Values ('" + result + "')"
    Dim command1 As New SqlCommand(insert, Connection)
  End Sub
End Class3.

Once I created the clr built the application and turned this class into a dll and also web service is also created as dll. create sgen for webservice.4.Add all 3 dlls to the database on which you wanted to attach the sql and access the webservice. Get to the database, in the assembly folder add these 3 dlls. The main clr file which connects to dll need to have external access permission set.5.Once you add these 3 dlls to the assembly folder, create procedure for the stored procedure clr.

Create PROC [Stored Procedure definition]([Parameters])
   With EXECUTE AS CALLERASEXTERNAL NAME (dll name of the stored procedure).     (class name).(method name)

For my purpose I created a job to perform certain tasks at regular intervals of time.Hope this could help someone.Thanks,Kishore Patil