calling a web service from SQL Server

[Gerhard]

Is there a way to call a web service via a trigger in SQL Server, passing
certain info (like name and address) to it? If so, is there an article on
the best practice of how to do it?

[Mark Han[MSFT]]

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: ***@microsoft.com.
===========================================================
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.
=========================================================

[Gerhard]

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.

Post by Mark Han[MSFT]
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
===========================================================
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.
=========================================================

[Mark Han[MSFT]]

Hello Gerhard,

Thank you for the reply. I'm gald that the information I provided is useful
to you.

According to your description, it seems that web reference object is not
recognized when we use Vbc to compile the code. SInce the code is compiled
successully in Visual Studio, the library DLL migt be built also. So we
might be able to Loading and Running the Stored Procedure in SQL Server by
CREATE ASSEMBLY

Besides, in the code you posted, I found the following and
localhost.CallSyncUserData is different with localhost.SyncUserData. I am
a little confused on that.
message = localhost.CallSyncUserData(employee_id)
Dim localhost As New localhost.SyncUserData

Besides, I'm doing some tests and internal consults to verify if the web
reference is able to be compiled by Vbs command line directly.

For more information:
Compiling and Running Code Examples:
http://msdn.microsoft.com/en-us/library/aa237349(SQL.80).aspx
Creating the Web Service
Proxy:http://msdn.microsoft.com/en-us/library/aa179614(SQL.80).aspx

Thanks.

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: ***@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Mark Han[MSFT]]

Hello Gerhard,

Sorry for the deplay.

I 'm writing to see if the library DLL is able to be loaded into SQL Server
and we are able use the CRL stroed procedure to call the web reference.

Besides, after consulting with our .net engineer, the root cause of the
issue(we failed to use command line to make vbs to compile to the code) is
that the location of the web reference is not specified in the command
line. So when we compile the code by command line, the
localhost.SyncUserData is considered as "not defined".

Based on my research, Visual Studio also use Vbs command to compile the
code and in the command, they specify the web reference file. For example
"service references\servicereference1\reference.vb"

Hope the above helpful.

If you have any questions or concerns on the above, please let me know.

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: ***@microsoft.com.
===========================================================
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.
=========================================================

[Mark Han[MSFT]]

Hello Gerhard,

This is Mark Han again. I'm waiting for your update.

if you need me futher assistance, please let me know. I look forward to
your reply.

Thanks

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: ***@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================