Hello, I call wcf service (on sharepoint 2010) from MSSQL 2008 x64.
First open Visual studio, and create empty sharepoint project and add wcf project.
example
using Microsoft.SharePoint.Client.Services;
using System.ServiceModel.Activation;
using Microsoft.SharePoint;
using System;
using System.Text;
namespace SharePointWCF
{
[BasicHttpBindingServiceMetadataExchangeEndpoint]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
public class WCFSql : IWCFSql
{
// To test this service, use the Visual Studio WCF Test client
// set the endpoint to http://sp1/_vti_bin/SharePointWCF/WCFSql.svc/mex
public string UpdateStatus(string url,string testSTR)
{
SPSecurity.RunWithElevatedPrivileges(delegate() {
using (SPWeb web = new SPSite(url).OpenWeb())
{
SPList list = web.Lists["HelloSql"];
SPListItem item = list.AddItem();
item["Title"] = testSTR;
item.Update();
}
});
return "true";
}
}
}
First open Visual studio, and create empty sharepoint project and add wcf project.
example
using Microsoft.SharePoint.Client.Services;
using System.ServiceModel.Activation;
using Microsoft.SharePoint;
using System;
using System.Text;
namespace SharePointWCF
{
[BasicHttpBindingServiceMetadataExchangeEndpoint]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
public class WCFSql : IWCFSql
{
// To test this service, use the Visual Studio WCF Test client
// set the endpoint to http://sp1/_vti_bin/SharePointWCF/WCFSql.svc/mex
public string UpdateStatus(string url,string testSTR)
{
SPSecurity.RunWithElevatedPrivileges(delegate() {
using (SPWeb web = new SPSite(url).OpenWeb())
{
SPList list = web.Lists["HelloSql"];
SPListItem item = list.AddItem();
item["Title"] = testSTR;
item.Update();
}
});
return "true";
}
}
}
Example sql Trigger:
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Web;
using SqlServerProject1.SQLService;
using System.ServiceModel;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
public static void Trigger1()
{
System.ServiceModel.EndpointAddress ep = new System.ServiceModel.EndpointAddress(new Uri("http://sp1/_vti_bin/SharePointWCF/WCFSql.svc"));
BasicHttpBinding binding = new BasicHttpBinding();
binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm;
//Create an instance of the service proxye
SqlServerProject1.SQLService.WCFSqlClient SqlService = new SqlServerProject1.SQLService.WCFSqlClient(binding, ep);
SqlService.ChannelFactory.Credentials.Windows.ClientCredential = new System.Net.NetworkCredential("login", "pass", "domain");
SqlService.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential("login", "pass", "domen");
SqlService.ClientCredentials.UserName.UserName = @"domain\login";
SqlService.ClientCredentials.UserName.Password = @"pass";
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlConnection conn = new SqlConnection("context connection =true");
conn.Open();
SqlCommand sqlComm = conn.CreateCommand();
SqlPipe sqlP = SqlContext.Pipe;
SqlDataReader dr;
sqlP.Send("ddddd");
sqlComm.CommandText = "SELECT [SP_ID],[Status] from inserted";
dr = sqlComm.ExecuteReader();
while (dr.Read())
SqlService.UpdateStatus("http://sp1/",dr[0].ToString());
}
}
Next go to MSSQL2008.
Steps.
1. Go to the management studio of SQL Server, first makes sure we are CLR enabled.
user master
go
sp_configure 'clr enabled', 1
go
reconfigure
go
2. Set the database trushworthy
alter database [database name]
set trustworthy on;
go
3. Create assemblies. When I created relate assemblies, I got numerous errors. I' going to explain how I made them worked.
1. Go to the management studio of SQL Server, first makes sure we are CLR enabled.
user master
go
sp_configure 'clr enabled', 1
go
reconfigure
go
2. Set the database trushworthy
alter database [database name]
set trustworthy on;
go
3. Create assemblies. When I created relate assemblies, I got numerous errors. I' going to explain how I made them worked.
problem 1)
Msg 33009, Level 16, State 2, Procedure usp_xxxxx, Line 0
The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘YourDatabase’. You should correct this situation by resetting the owner of database ‘YourDatabase’ using the ALTER AUTHORIZATION statement.
=> I executed the below command.
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
Msg 33009, Level 16, State 2, Procedure usp_xxxxx, Line 0
The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘YourDatabase’. You should correct this situation by resetting the owner of database ‘YourDatabase’ using the ALTER AUTHORIZATION statement.
=> I executed the below command.
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
Sum up
all created assemblies and their paths are the following.
1) System.Runtime.Serialization
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\System.Runtime.Serialization.dll'
2) SMDiagnostics
'c:\Windows\Microsoft.net\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll'
3) System.Web
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'
4) System.Messaging
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Messaging.dll'
5) System.IdentityModel
'C:\Windows\winsxs\msil_system.identitymodel_b77a5c561934e089_6.1.7601.17514_none_f1b6cabd00b1e098\System.IdentityModel.dll'
6) System.IdentityModel.Selectors
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'
7) Microsoft.Transactions.Bridge
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'
8) System.ServiceModel
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll'
9) System.Drawing
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Drawing.dll'
1) System.Runtime.Serialization
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\System.Runtime.Serialization.dll'
2) SMDiagnostics
'c:\Windows\Microsoft.net\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll'
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'
4) System.Messaging
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Messaging.dll'
5) System.IdentityModel
'C:\Windows\winsxs\msil_system.identitymodel_b77a5c561934e089_6.1.7601.17514_none_f1b6cabd00b1e098\System.IdentityModel.dll'
6) System.IdentityModel.Selectors
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'
7) Microsoft.Transactions.Bridge
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'
8) System.ServiceModel
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll'
9) System.Drawing
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Drawing.dll'
Test you trigger!