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’
SELECT DISTINCT CONVERT(varchar,ci.Id) + ' 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.
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:
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)
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