I need to create a stored procedure (SQL 2005). I did it in C# (Visual Studio). The .dll file (inside) uses web services of Project Server 2007 (I just need to log on, create a project and log out). I prepared 2 files (exactly the same code): dll an exe. the .exe works fine. the problem is with .dll file (stored procedure in SQL)...
by the way: I created serialization file with sgen tool.
the problems start when I am trying to register these 2 .dlls in SQL server. I can do that in unsafe mode only. when I am trying to register them in externall access (normal dll) and safe mode (serialization .dll) I am getting errors:
CREATE ASSEMBLY failed because method "add_QueueCheckInProjectCompleted" on type "ConsoleApplication11.ProjectWebSvc.Project" in external_access assembly "ConsoleApplication11" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies
thus I did it in unsafe mode (both of them). than I created the procedure.
Now, I tried to call that procedure. I am getting error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'Main':
System.Net.WebException: The request failed with the error message:
--
<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a href="http://my_comp:22278/projectserver/_layouts/1033/error.aspx?ErrorText=Object%20reference%20not%20set%20to%20an%20instance%20of%20an%20object%2E">here</a>.</h2>
</body></html>
--.
System.Net.WebException:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ConsoleApplication11.ProjectWebSvc.Project.QueueCreateProject(Guid jobUid, ProjectDataSet dataset, Boolean validateOnly)
at ConsoleApplication11.Program.Main()
That means "Object reference not set to an instance of an object". the problem occurs when calling QueueCreateProject method
I dont understand that error. I wrote the same code for .dll and .exe. the .exe file is working ok (no errors) but .dll (as a stored procedure) shows this error
my code in C#:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Net;
using PSLibrary = Microsoft.Office.Project.Server.Library;
namespace ConsoleApplication11
{
public class Program
{
public static void Main()
{
const string LOGINWINDOWS = "_vti_bin/PSI/LoginWindows.asmx";
string baseUrl = "http://localhost:22278/projectserver/";
CookieContainer cookies = new CookieContainer();
LoginWindowsWebSvc.LoginWindows loginWindows = new LoginWindowsWebSvc.LoginWindows();
loginWindows.Url = baseUrl + LOGINWINDOWS;
loginWindows.Credentials = CredentialCache.DefaultCredentials;
loginWindows.Login();
ProjectWebSvc.Project project = new ProjectWebSvc.Project();
project.Credentials = loginWindows.Credentials;
project.Url = baseUrl + "_vti_bin/psi/project.asmx";
ProjectWebSvc.ProjectDataSet dsProject = new ProjectWebSvc.ProjectDataSet();
ProjectWebSvc.ProjectDataSet.ProjectRow projectRow = dsProject.Project.NewProjectRow();
Guid projectGuid = Guid.NewGuid();
projectRow.PROJ_UID = projectGuid;
projectRow.PROJ_TYPE = 0;
projectRow.PROJ_NAME = "Jakis";
projectRow.PROJ_SESSION_UID = Guid.NewGuid();
dsProject.Project.AddProjectRow(projectRow);
Guid jobGuid = Guid.NewGuid();
project.QueueCreateProject(jobGuid, dsProject, false);
System.Threading.Thread.Sleep(5000);
loginWindows.Logoff();project.Credentials = null;
}
}
}
any help will be appreciated. my email is: stro.na@.interia.pl
Dear fcjh,
I too am trying to call a web service from a stored procedure in SQL Server 2005. I have followed the advice in the following very useful article:
http://www.awprofessional.com/articles/article.asp?p=473457&seqNum=10&rl=1
I have built a solution in Visual Studio 2005 to do this. In order to follow the article as closely as possible, I have three projects in the solution:
1) The web site including the web service.
2) A SQL Server Project for the Stored Procedure, which calls a User Defined Function, defined in the same project. This successfully deploys both to SQL Server 2005 and allows me to step into them to debug. The code of the User Defined Function (from the above article) is:
[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(Precision = 9, Scale = 2)]
public static decimal GetStockWS(string symbol)
{
Decimal price;
using (WindowsIdentity id = SqlContext.WindowsIdentity)
{
WindowsImpersonationContext c = id.Impersonate();
StockService s = new StockService();
// use the current credentials
s.Credentials =
System.Net.CredentialCache.DefaultNetworkCredentials;
price = s.GetStockPrice(symbol);
c.Undo();
}
return price;
}
3) A SQL Server Project for the Web Service Proxy. This also appears to deploy to SQL Server successfully.
However, at the point where the User Defined Function invokes the constructor of the Web Service Proxy (statement StockService s = new StockService(); above), I get an exception as follows:
.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.
I can't find anything useful about the cause of this. Has anyone else succeeded in calling a web service from SQL Server 2005?
Any help would be much appreciated.
|||Hi there,You might find Vineet's post here useful.
Cheers,|||
Dear Isaac,
Vineet's post is excellent. Everything now works perfectly and turns out to be very simple, thanks to the power of the Visual Studio 2005 SQL Server Project type and the use of a Web Reference.
Thanks very much for pointing me to that article.
No comments:
Post a Comment