RE: calling a web service from SQL Server
- From: Gerhard <Gerhard@xxxxxxxxxxxxxxxx>
- Date: Wed, 29 Apr 2009 15:39:01 -0700
Thanks.
I almost have this working, the trigger calls the stored procedure correctly.
Only issue is that is that it works on a simple stored procedure that I can
compile per the documentation (command line like: vbc /target:library
helloworld.vb), however when I add a web reference to the web service, the
command line compile gives an error.
In the simple test version I am doing the code is as follows:
Public Class testTrigger
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub testTrigger(ByVal employee_id As Integer, <Out()>
ByRef message As String)
Dim localhost As New localhost.SyncUserData
Try
message = localhost.CallSyncUserData(employee_id)
Catch ex As Exception
message = ex.Message
End Try
End Sub
End Class
This all works fine when called from a .net web page, and it also compiles
fine in Visual Studio. However, when I try to compile on the command line I
get localhost.SyncUserData is not defined (this is the web reference that was
added).
I haven't been able to find a way to get around this. I don't have a fully
compiled version of the web service in .dll format (that won't compile as
above either as it is using SharePoint dlls).
Is there a way to get this to compile the way it is needed to be built into
an assembly (CREATE ASSEMBLY helloworld from
'c:\SourceCode\SP\helloworld.dll' WITH PERMISSION_SET = SAFE for instance)?
Thanks.
"Mark Han[MSFT]" wrote:
HI Gerhard,.
Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.
From your description, I understand that you would like to use SQL trigger
to call a web service from a SQL . If I have misunderstood, please let me
know.
in order to address you concern, I sum up the following 2 method for you.
1 it is supported to call a web services from a SQL CLR stored prodecure.
and the CLR stored procedure is able to be executed by a trigger. About CLR
stored procedure, there is an article to share with
you:http://msdn.microsoft.com/en-us/library/ms131094.aspx
2 we call a Web service successfully from an Integration Services package
by configuring an HTTP connection manager and the Web Service task. there
is an article to share with
you:http://technet.microsoft.com/en-us/library/cc952927.aspx Therefore, to
achieve you concern, we can create a SSIS package with web service task and
correct configuration of the HTTP connection. SSIS Package is able to be
executed by job. so we can create a job to execute the SSIS package without
schedule and involve the job in the sql trigger.
Based on my research, there is no article on the best practice of how to
call web service from SQL. according to my experience, I prefer to use SQL
CRL stored procedure to call web service and involve the CRL stored
procedure in the SQL trigger. however considering the scenario, we could
choose one of the above method.
If there is anything unclear, please do not hesitate to let me know. Have a
nice day!
Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
- Prev by Date: RE: Question About ProtectionLevel: ServerStorage
- Next by Date: Convert Sybase database to Sql Server
- Previous by thread: Question About ProtectionLevel: ServerStorage
- Next by thread: Convert Sybase database to Sql Server
- Index(es):
Relevant Pages
|
