| Author |
Topic |
|
Aaron
Starting Member
20 Posts |
 Posted - 2007-08-18 : 17:26:29
|
| I'm trying to send a SOAP XML envelope to a Web Service in a SQL Server 2005 Stored Procedure. Is this possible? I've found some code on the internet that seems appropriate using sp_OA_Create but it's failing. The message I get is:msxml4.dllThe parameter is incorrect.Does this make sense to anyone?Could someone even point me in the right direction since I can't even seem to find documentation to help me do this.Thanks in advance,Aaron |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-18 : 17:33:51
|
| why are you not using http soap endpoint?if you're trying to call a web service from sql server... don't do it. find a different way.look into CLR._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
Aaron
Starting Member
20 Posts |
Posted - 2007-08-18 : 19:05:24
|
| Why should I not try to call a webservice from SQL? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-18 : 19:09:55
|
| the question is why should you?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-18 : 19:52:23
|
SQL Server 2005 is capable to create it's own http endpoints.With this, I think you can call web-services for various reasons.What do Books Online tell you about "http endpoints"?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-18 : 19:59:34
|
| there's a difference between calling a web service from sql server and having http endpoint act as a webservice provided by sql server._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Aaron
Starting Member
20 Posts |
Posted - 2007-08-18 : 22:49:27
|
| Exactly, I thought the SQL Endpoint was to allow others to access to your sql server, not to allow you to get access to web services. Am I on the wrong path? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-19 : 09:29:44
|
| your thinking is correct. however calling a webservice from sql server is not good practice because of the synchronous nature of the operationwhich can slow down sql server.can you tell us why exactly do you want to do this? maybe we can suggest an alternative._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Aaron
Starting Member
20 Posts |
Posted - 2007-08-19 : 10:15:56
|
| I am processing an incoming xml document, then have to send back an acknowledgment to the sender. Up to now I've been doing this all through a powerbuilder app, but we're getting rid of it and I'd like the whole process to run as a job on sql server. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-19 : 10:32:34
|
| well if sending an ack means calling a web service your only way from doing it inside SQL Server is using CLR integration._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Aaron
Starting Member
20 Posts |
Posted - 2007-08-19 : 10:44:55
|
| Is that a difficult thing to do ( CLR integration )? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-19 : 10:48:28
|
no not really  you can easily integrate the CLR into sql server 2005.create a CLR stored procedure that calls the web service.http://technet.microsoft.com/en-us/library/ms131094.aspx_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Aaron
Starting Member
20 Posts |
Posted - 2007-08-19 : 11:37:42
|
| Thank you very much, Spirit1.Aaron |
|
|
|
Aaron
Starting Member
20 Posts |
Posted - 2007-08-27 : 17:34:27
|
| Ok, I attempted to create a vb.net dll to call my webservice. When I try to create a SQL assembly of my dll using this code: CREATE ASSEMBLY DWFWebService from Z:\dwng_broker\WebService1.dll' with permission_set = EXTERNAL_ACCESSI get: Msg 10327, Level 14, State 1, Line 12CREATE ASSEMBLY for assembly 'WebService1' failed because assembly 'WebService1' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.Is there something simple I need to do or do I have to jump through all the hoops in the error message?Any help would be appreciated, Aaron. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-27 : 17:36:16
|
Have you used the Surface Configuration Manager?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Aaron
Starting Member
20 Posts |
Posted - 2007-08-27 : 17:42:44
|
| I did, indeed, to enable CLR Integration. Is there anything else I should do? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-28 : 04:32:26
|
| so your database trustworthy option is set to on?if yes then you'll have to show us your code._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
Send XML/SOAP Envelope to Web Service using T-SQL