can I create a job to call a stored procedure?

Hello,

Can I create a job to call a stored procedure everyday? If yes, how to do it?

Thanks

I use sql server 2005.

Thanks

|||

Yes, refer to Books Online, Topic: SQL Agent, Jobs

Unless you are using SQL Express. With SQL Express, SQL Agent is not included. You can accomplish the same functionality using the Windows Scheduler service, SQLCmd.exe and a stored procedure.

|||

from sql agen job, I can't find stored procedure calling item from the drop down list. It only has maintanence plan etc.

Thanks

|||If you will explore Books Online, Topic: Jobs, Creating, you will find excellent walk-thoughs and examples.|||You have
1) create new job,
2) add a step to created job
3) Add name for this step, such as "Step1"
4) Choose type: Transact-SQL script
5) Choose actual database from list
6) Add command:

Code Snippet

EXEC sp_yourstored_proc_name

7) Go to schedules tab and set correct schedule plan|||

Easiest way to at least for me is to use isql wrapped in a bat file.

Open notepad or your favorite text editor.

Type the following, replacing where required.

@.echo off
isql -S SERVER_NAME_OR_IP -d DATABASE_NAME -Q "EXEC STORED_PROC_NAME" -U YOUR_USERNAME -P YOUR_PASSWORD -s , -o "OUTPUT_FILE"

Save the file with .bat as the extension.

Navigate to Start, All Programs, Control Panel, Scheduled Tasks, Add Scheduled Task.

Select next from the introduction screen.

Select browse from the next screen and find the .bat file created earlier.

Give the job a name and select interval from the next screen.

Enter the username and password to run as (helpful if using integrated security).

Select finish.

You can test your job by navigating to the scheduled jobs folder, right click on the job, select run

You can obtain the command arguments for isql using "isql /?" (w/o the quotes) at the command prompt.

Simple bat example (will execute sp_monitor and log the results to c:\sqlout.csv)

@.echo off

isql -S 192.168.1.250 -d master -Q "EXEC sp_monitor" -U test -P test -s , -o "c:\sqlout.csv"

Can i call Tigger in store procedure or vice versa and How?

Can i call Tigger in store procedure or vice versa in sql server 2000.

and How it implement ?

Your question has been replied in anthor post, please check it:

http://forums.asp.net/1318011/ShowPost.aspx

To call stored procedure in trigger, just call it like anywhere else you call it:

EXEC sp_mysp

sql

Can i call tigger in store procdure and vice versa?(urgent)

Can i call tigger in store procdure and vice versa??

if yes how it ???

I don't think you can. If you need the trigger functionality you could always copy it into a stored procedure and call that instead.|||you can not call trigger in stored procedure. but you do can call stored procedure from trigger.

Can I call stored procedure inside the case statement

Hi
Can I call stored procedure inside the case statement.small correction
Can I execute stored procedure inside the case statement
CASE t1.OPERATION WHEN 'I' THEN (EXEC [UPD_SEQ_GENERATOR_PROC_VEERU]
@.p_seq_name, @.p_next_value1 OUTPUT select @.p_next_value1 WHEN 'L' THEN EXEC
[UPD_SEQ_GENERATOR_PROC_VEERU] @.p_seq_name, @.p_next_value1 OUTPUT select
@.p_next_value1 END
I want to return the value from the stroed procedure based on the condition.
Regards
Veeru
"Veeru" wrote:

> Hi
> Can I call stored procedure inside the case statement.
>|||No. Why don't you use IF ?
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:4BCD8E67-FB16-46BF-9298-4242A70D05E2@.microsoft.com...
> small correction
> Can I execute stored procedure inside the case statement
>
> CASE t1.OPERATION WHEN 'I' THEN (EXEC [UPD_SEQ_GENERATOR_PROC_VEERU]
> @.p_seq_name, @.p_next_value1 OUTPUT select @.p_next_value1 WHEN 'L' THEN
> EXEC
> [UPD_SEQ_GENERATOR_PROC_VEERU] @.p_seq_name, @.p_next_value1 OUTPUT select
> @.p_next_value1 END
> I want to return the value from the stroed procedure based on the
> condition.
> Regards
> Veeru
>
>
> "Veeru" wrote:
>|||Hi veeru,
I have been following your posts for quite sometime. Can you give the
exact requirement, why you ned that sequence table and what are you trying t
o
achieve with an example and may be we can help you out.
-Omnibuzz
"Veeru" wrote:
> small correction
> Can I execute stored procedure inside the case statement
>
> CASE t1.OPERATION WHEN 'I' THEN (EXEC [UPD_SEQ_GENERATOR_PROC_VEERU]
> @.p_seq_name, @.p_next_value1 OUTPUT select @.p_next_value1 WHEN 'L' THEN EXE
C
> [UPD_SEQ_GENERATOR_PROC_VEERU] @.p_seq_name, @.p_next_value1 OUTPUT select
> @.p_next_value1 END
> I want to return the value from the stroed procedure based on the conditio
n.
> Regards
> Veeru
>
>
> "Veeru" wrote:
>|||Hi Omnibuzz,
I have the requirement to load the flat file data into tables. previously we
did this in Orale. there we created one control file to load the flat file
data into table. In the control file we did some validations like
DATA_STREAMER_ID "DATA_STREAMER_ID_SEQ.nextval",
KEY_2 CONSTANT 'PRES',
KEY_3 CONSTANT '-1',
KEY_4 "Data_Streamer_Id_Seq.Nextval",
OPERATION " DECODE(TRIM(:OPERATION),'L',1,'I',1,'U',
0)",
IDENTIFIER_15 "DECODE
(TRIM:OPERATION),'L',SEQ_IS_GLOBAL_IDENT
IFIER.nextval,'I',SEQ_IS_GLOBAL_IDEN
TIFIER.nextval)"
DATE_1 DATE "FXYYYY-MM-DD HH24:MI:SS" "DECODE(:OPERATION,'L',NVL
(:DATE_1,to_char(sysdate,'YYYY-MM-DD
HH24:MI:SS')),'I',NVL(:DATE_1,to_char(sy
sdate,'YYYY-MM-DD
HH24:MI:SS')),:DATE_1)", -- STATUS_CHANGE_DATE
I want to do the same thing now in SQL server. For that I have created
Format file to map the data file fields to table column.
we have CASE statement in SQL Server which works same as DECODE in Oracle.
I have posted some more info yesterday subject as "Can we update the table
in user defined function". Actually I have been waiting for your reply for
that. Can you please refer that also and suggest me the approach.
Regards
Veeru.
"Omnibuzz" wrote:
> Hi veeru,
> I have been following your posts for quite sometime. Can you give the
> exact requirement, why you ned that sequence table and what are you trying
to
> achieve with an example and may be we can help you out.
> -Omnibuzz
> "Veeru" wrote:
>|||Do you want the values to be autogenerated like this?
KEY_1 KEY_4
1 1
2 2
3 3
or like this
KEY_1 KEY_4
1 2
3 4
5 6|||I want values like
KEY_1 KEY_4
1 2
3 4
5 6
and
I want the below syntax in INSERT...OPENROWSER(BULK) in SQL Server
IDENTIFIER_15 "DECODE
(TRIM:OPERATION),'L',SEQ_IS_GLOBAL_IDENT
IFIER.nextval,'I',SEQ_IS_GLOBAL_IDEN
TIFIER.nextval)"
Regards
Veeru
"Omnibuzz" wrote:

> Do you want the values to be autogenerated like this?
> KEY_1 KEY_4
> 1 1
> 2 2
> 3 3
> or like this
> KEY_1 KEY_4
> 1 2
> 3 4
> 5 6
>|||then use a table variable. Insert into the table variable from the source.
And select from the table variable and insert into the destination.
The code is this...
--The table variable definition should be something like this.
declare @.tbl1 table (id1 int identity(1,2), source_col1,source_col2,...)
Insert into @.tbl1 (source_col1,source_col2,... ) select ... from source.
--Here id1 will be autogenerated into the table variable.
--use this for insert into destination
INSERT...OPENROWSER(BULK) select id1, source_col1...., case when blah..blah
then id1 + 1 else
... blah blah.. end
Sorry.. leaving for a party :)
Try to decipher this.. If not will help u (in detail :)
Bye.|||Thanks Omnibuzz. I will try. If any problem I will post my doubts
"Omnibuzz" wrote:

> then use a table variable. Insert into the table variable from the source.
> And select from the table variable and insert into the destination.
> The code is this...
> --The table variable definition should be something like this.
> declare @.tbl1 table (id1 int identity(1,2), source_col1,source_col2,...)
> Insert into @.tbl1 (source_col1,source_col2,... ) select ... from source.
> --Here id1 will be autogenerated into the table variable.
> --use this for insert into destination
> INSERT...OPENROWSER(BULK) select id1, source_col1...., case when blah..bl
ah
> then id1 + 1 else
> ... blah blah.. end
>
> Sorry.. leaving for a party :)
> Try to decipher this.. If not will help u (in detail :)
> Bye.
>|||Hi Omnibuzz,
I have multiple records in my flat file. I have to load all the records into
the database. Like this I have to load into so many tables and have one
interface fir each. the sequence number should be unique in the database not
in the table. So I have to generate sequence number for all the Interfaces
where evevr I need and should be unique. In Oracle we can create one sequenc
e
and generate the sequences. Like that I have to do. I think you can
understand what I need.
Can you suggest me.
Thanks in advance.
Regards
Veeru
"Omnibuzz" wrote:

> then use a table variable. Insert into the table variable from the source.
> And select from the table variable and insert into the destination.
> The code is this...
> --The table variable definition should be something like this.
> declare @.tbl1 table (id1 int identity(1,2), source_col1,source_col2,...)
> Insert into @.tbl1 (source_col1,source_col2,... ) select ... from source.
> --Here id1 will be autogenerated into the table variable.
> --use this for insert into destination
> INSERT...OPENROWSER(BULK) select id1, source_col1...., case when blah..bl
ah
> then id1 + 1 else
> ... blah blah.. end
>
> Sorry.. leaving for a party :)
> Try to decipher this.. If not will help u (in detail :)
> Bye.
>

Can I call a web service from SQL?

I have two databases with two identical tables in seperate physical locations. I want database B tables to be updated automatically when database A tables change. Is there a way to call a web service from SQL to make this happen? Or is there a better way to do this? I would really like it to get the rows that were modified and then copy only those rows to the other database tables. If anyone knows if this can be done please let me know. Thank you.

Why do you need a webservice? Maybe it's better to archieve this using UPDATE Triggers? Although the performance of firing triggers is not so good in some case, but it should be easier and faster than calling webservice. So let's say you want to trace modification made in database1.dbo.Table1, and you want to copy the modified rows into database2.dbo.Table2, and the 2 tables have almost the same structure, except the Table2 has 1 more column used to record UPDATETIME. You can create an UPDATE/INSERT trigger on Table1 like this:

CREATE TRIGGER trg_TraceMod ON tempdb.dbo.tbl_Const1 FOR UPDATE
AS
IF(object_id('tempdb.dbo.tbl_Trace') IS NULL)
BEGIN
SELECT deleted.*,GETDATE() AS UpdTime INTO tempdb.dbo.tbl_Trace FROM deleted

END
ELSE
INSERT INTO tempdb.dbo.tbl_Trace
SELECT deleted.*,GETDATE() FROM deleted
go

To learn more about triggers, you can refer to:

Enforcing Business Rules with Triggers

|||Thank you very much for the reply. The reason I was think about a webservice is because the two databases are in two different physical locations and cannot access each other without a web service because they are not on the same network. Basically one database is in our office and the other one in downtown in a data center. The one in the data center needs to update the one in our office everytime it changes. Could I use triggers to do that?|||

Then you can tryLinked Servers. Connections between internet SQL Servers may be more complex than in the same network, there may be trouble in locating host SQL box, passing credentials, firewalls, and so on. You may take a look at this post if you fail to establish connections between the 2 SQL servers:

http://forums.asp.net/thread/1289341.aspx

And after the Linked Servers have been created (you can do this in Enterprise Manager->Security->Linked Servers), you can query the tables on the linked server using four part object name:

server.database.owner_name.object_name

Or you can useOPENQUERY.

|||

My question is somewhat related. I need to call a web service upon completion of a sql job. Is this possible? I'm using a product called Captaris Workflow. What I need to do is call a web service that creates a new workflow process and emails the person responsible for the first workflow task. The workflow part may sound foreign, nevertheless, I want to call a web service when a sql job completes. Any help would be much appreciated.

Thanks,

Jason

Can an SQL Server stored procedure write output to an excel formated file?

Hi,
To clarify the previous post.
I would like to write a stored procedure that a client can call. The
stored procedure will then output its result in an excel file.
Thanks,
chariaYou have things a little reversed. You should have the stored procedure
return the data. How you call the report will determine how it is rendered
(HTML, CSV, PDF, Excel etc). When integrating reports you can use URL
integration or webservices. Neither of which would be that easy from a
stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<cpeters5@.gmail.com> wrote in message
news:1120582350.023702.281780@.g43g2000cwa.googlegroups.com...
> Hi,
> To clarify the previous post.
> I would like to write a stored procedure that a client can call. The
> stored procedure will then output its result in an excel file.
> Thanks,
> charia
>

Can a stored procedure open excel and call a macro? or vice versa

I googled it but found nothing.
I've seen where excel can call a stored procedure to return a record set but
what if my stored procedure returns several record sets, how does excel
handle that?
Thanks1. Within SQL Server you can call xp_cmdshell, to OPEN excel file
2. Enabling xp_cmdshell has some drawbacks (SQL Injection,
Security...), watch out for that
3. Macro is a Part of Excel, which runs when you open the excel file so
I doubt SQL has anything to do with that
4. Opening excel file will happen @. server rather than client, might
need to look for that
5. Once Excel is OPEN SQL has no reference pointer to excel file, its
like I opened the file & I'm done
HTH
PP
Mike wrote:
> I googled it but found nothing.
> I've seen where excel can call a stored procedure to return a record set b
ut
> what if my stored procedure returns several record sets, how does excel
> handle that?
> Thanks

Can a stored procedure open excel and call a macro? or vice versa

I googled it but found nothing.
I've seen where excel can call a stored procedure to return a record set but
what if my stored procedure returns several record sets, how does excel
handle that?
Thanks1. Within SQL Server you can call xp_cmdshell, to OPEN excel file
2. Enabling xp_cmdshell has some drawbacks (SQL Injection,
Security...), watch out for that
3. Macro is a Part of Excel, which runs when you open the excel file so
I doubt SQL has anything to do with that
4. Opening excel file will happen @. server rather than client, might
need to look for that
5. Once Excel is OPEN SQL has no reference pointer to excel file, its
like I opened the file & I'm done :)
HTH
PP
Mike wrote:
> I googled it but found nothing.
> I've seen where excel can call a stored procedure to return a record set but
> what if my stored procedure returns several record sets, how does excel
> handle that?
> Thanks

Can a Stored Procedure called from an Inline Table-Valued Function

Hi,

I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks

Hi,

you cannot call a stored procedure from a function. however you can call that function from a view.

|||No stored procedure you cannot call inside a query|||Thanks for the reply guys I really appreciate that. But I was just wondering if there is a way I can access the stored proc results from a view.

callinga web service?

Hi All
can i in call a web service from SP/function
(without using COM SP's) in any way ?
and represent the returned xml as a resultset ?
TIA
Danny
I don't think this is possible in SQL Server 2000 without using the sp_OA
procs or writing an extended stored proc.
In Yukon, you can write a CLR UDF.
Best regards
Michael
"Danny" <danny.ravid@.ness.com> wrote in message
news:eb2d01c43cee$7257dba0$a301280a@.phx.gbl...
> Hi All
> can i in call a web service from SP/function
> (without using COM SP's) in any way ?
> and represent the returned xml as a resultset ?
> TIA
> Danny

calling XACT_ABORT before/after BEGIN TRAN... What's the difference?

Is it necessary to execute "SET XACT_ABORT ON" before "BEGIN TRANSACTION"?

I have tried to call "SET XACT_ABORT ON" after "BEGIN TRANSACTION" and it works fine over a distributed transaction which includes a linked server.

XACT_ABORT is used to control the flow the current transaction.
If it is ON the Entire Transaction will be rollbacked and the Transaction Batch will be terminated immediately.

If it is OFF when the runtime error the current statement only rollbacked and the transaction batch will be executed with out any termination.

It is up to you what kind of behavior you need on your transaction.

But as per the Books online they are not recommending to use this option on Distributed Transactions.

See more on Books Online Under SET XACT_ABORT.

Calling WS from Sproc

The client wants to update Active Directory from a Sproc via a Web Service.
I found an example in Google Archives, but it won't work...the call to a method
generates the error listed below. Please note that a straight disco call does work.
Wondering if it's a versioning issue.
Thanks for any suggestions...
SQL Server: Microsoft SQL Server 2000 - 8.00.76
MSXML: 6.0
-- WS Code --
<WebMethod()> _
Public Function Hello(ByVal Name As String)
Return "Hello World " & Name
End Function
-- Sproc Code --
CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
AS
DECLARE @.Url varchar(1000)
, @.obj int
, @.hr int
, @.status int
, @.src varchar(4000)
, @.desc varchar(4000)
, @.errmsg varchar(4000)
-- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
-- Generates error in post
SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'send'
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OADestroy @.obj
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
-- Error --
[InvalidOperationException: Request format is unrecognized.]
System.Web.Services.Protocols.WebServiceHandlerFac tory.CoreGetHandler(Type type, HttpContext context, HttpRequest request,
HttpResponse response) +388
System.Web.Services.Protocols.WebServiceHandlerFac tory.GetHandler(HttpContext context, String verb, String url, String filePath)
+94
System.Web.HttpApplication.MapHttpHandler(HttpCont ext context, String requestType, String path, String pathTranslated, Boolean
useAppConfig) +699
System.Web.MapHandlerExecutionStep.System.Web.Http Application+IExecutionStep.Execute() +95
System.Web.HttpApplication.ExecuteStep(IExecutionS tep step, Boolean& completedSynchronously) +173
</pre></code>
There are a couple different ways to do this in SQL Server 2005 but there
aren't ant really good ways to do this in SQL Server 2000. Several people
have used external com objects called through the sp_OA* stored procedures
but that's not a very scaleable solution. I would suggest writing the
information you want to add to AD into a table and then writing an
application that occasionally reads the table and does the AD update. AD
updates are somewhat asynchronous anyway so the delay is probably tolerable.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Garth Wells" <nobody@.ishome.com> wrote in message
news:e5ln2oeYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> The client wants to update Active Directory from a Sproc via a Web
> Service.
> I found an example in Google Archives, but it won't work...the call to a
> method
> generates the error listed below. Please note that a straight disco call
> does work.
> Wondering if it's a versioning issue.
> Thanks for any suggestions...
>
> SQL Server: Microsoft SQL Server 2000 - 8.00.76
> MSXML: 6.0
> -- WS Code --
> <WebMethod()> _
> Public Function Hello(ByVal Name As String)
> Return "Hello World " & Name
> End Function
> -- Sproc Code --
> CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
> AS
> DECLARE @.Url varchar(1000)
> , @.obj int
> , @.hr int
> , @.status int
> , @.src varchar(4000)
> , @.desc varchar(4000)
> , @.errmsg varchar(4000)
> -- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
> -- Generates error in post
> SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'send'
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OADestroy @.obj
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> -- Error --
> [InvalidOperationException: Request format is unrecognized.]
> System.Web.Services.Protocols.WebServiceHandlerFac tory.CoreGetHandler(Type
> type, HttpContext context, HttpRequest request,
> HttpResponse response) +388
> System.Web.Services.Protocols.WebServiceHandlerFac tory.GetHandler(HttpContext
> context, String verb, String url, String filePath)
> +94
> System.Web.HttpApplication.MapHttpHandler(HttpCont ext context, String
> requestType, String path, String pathTranslated, Boolean
> useAppConfig) +699
> System.Web.MapHandlerExecutionStep.System.Web.Http Application+IExecutionStep.Execute()
> +95
> System.Web.HttpApplication.ExecuteStep(IExecutionS tep step, Boolean&
> completedSynchronously) +173
> </pre></code>
>
|||Adding the following to the web.config file fixed the problem..
<webServices>
<protocols>
<add name="HttpGet" />
</protocols>
</webServices>
"Garth Wells" <nobody@.ishome.com> wrote in message news:e5ln2oeYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> The client wants to update Active Directory from a Sproc via a Web Service.
> I found an example in Google Archives, but it won't work...the call to a method
> generates the error listed below. Please note that a straight disco call does work.
> Wondering if it's a versioning issue.
> Thanks for any suggestions...
>
> SQL Server: Microsoft SQL Server 2000 - 8.00.76
> MSXML: 6.0
> -- WS Code --
> <WebMethod()> _
> Public Function Hello(ByVal Name As String)
> Return "Hello World " & Name
> End Function
> -- Sproc Code --
> CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
> AS
> DECLARE @.Url varchar(1000)
> , @.obj int
> , @.hr int
> , @.status int
> , @.src varchar(4000)
> , @.desc varchar(4000)
> , @.errmsg varchar(4000)
> -- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
> -- Generates error in post
> SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'send'
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OADestroy @.obj
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> -- Error --
> [InvalidOperationException: Request format is unrecognized.]
> System.Web.Services.Protocols.WebServiceHandlerFac tory.CoreGetHandler(Type type, HttpContext context, HttpRequest request,
> HttpResponse response) +388
> System.Web.Services.Protocols.WebServiceHandlerFac tory.GetHandler(HttpContext context, String verb, String url, String
filePath)
> +94
> System.Web.HttpApplication.MapHttpHandler(HttpCont ext context, String requestType, String path, String pathTranslated, Boolean
> useAppConfig) +699
> System.Web.MapHandlerExecutionStep.System.Web.Http Application+IExecutionStep.Execute() +95
> System.Web.HttpApplication.ExecuteStep(IExecutionS tep step, Boolean& completedSynchronously) +173
> </pre></code>
>

Calling WS from Sproc

The client wants to update Active Directory from a Sproc via a Web Service.
I found an example in Google Archives, but it won't work...the call to a met
hod
generates the error listed below. Please note that a straight disco call doe
s work.
Wondering if it's a versioning issue.
Thanks for any suggestions...
SQL Server: Microsoft SQL Server 2000 - 8.00.76
MSXML: 6.0
-- WS Code --
<WebMethod()> _
Public Function Hello(ByVal Name As String)
Return "Hello World " & Name
End Function
-- Sproc Code --
CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
AS
DECLARE @.Url varchar(1000)
, @.obj int
, @.hr int
, @.status int
, @.src varchar(4000)
, @.desc varchar(4000)
, @.errmsg varchar(4000)
-- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
-- Generates error in post
SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'send'
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OADestroy @.obj
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
-- Error --
[InvalidOperationException: Request format is unrecognized.]
System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(Type t
ype, HttpContext context, HttpRequest request,
HttpResponse response) +388
System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpContex
t context, String verb, String url, String filePath)
+94
System.Web.HttpApplication.MapHttpHandler(HttpContext context, String reques
tType, String path, String pathTranslated, Boolean
useAppConfig) +699
System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep
.Execute() +95
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& com
pletedSynchronously) +173
</pre></code>Adding the following to the web.config file fixed the problem...
<webServices>
<protocols>
<add name="HttpGet" />
</protocols>
</webServices>
"Garth Wells" <nobody@.ishome.com> wrote in message news:uXC2hXTYFHA.4024@.TK2MSFTNGP10.phx.g
bl...
> The client wants to update Active Directory from a Sproc via a Web Service
.
> I found an example in Google Archives, but it won't work...the call to a m
ethod
> generates the error listed below. Please note that a straight disco call d
oes work.
> Wondering if it's a versioning issue.
> Thanks for any suggestions...
>
> SQL Server: Microsoft SQL Server 2000 - 8.00.76
> MSXML: 6.0
> -- WS Code --
> <WebMethod()> _
> Public Function Hello(ByVal Name As String)
> Return "Hello World " & Name
> End Function
> -- Sproc Code --
> CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
> AS
> DECLARE @.Url varchar(1000)
> , @.obj int
> , @.hr int
> , @.status int
> , @.src varchar(4000)
> , @.desc varchar(4000)
> , @.errmsg varchar(4000)
> -- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
> -- Generates error in post
> SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'send'
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OADestroy @.obj
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> -- Error --
> [InvalidOperationException: Request format is unrecognized.]
> System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(T
ype type, HttpContext context, HttpRequest request,
> HttpResponse response) +388
> System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpContext c
ontext, String verb, String url, String
filePath)
> +94
> System.Web.HttpApplication.MapHttpHandler(HttpContext context, String r
equestType, String path, String pathTranslated, Boolean
> useAppConfig) +699
> System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutio
nStep.Execute() +95
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
; completedSynchronously) +173
> </pre></code>
>
>

Calling WS from Sproc

The client wants to update Active Directory from a Sproc via a Web Service.
I found an example in Google Archives, but it won't work...the call to a met
hod
generates the error listed below. Please note that a straight disco call doe
s work.
Wondering if it's a versioning issue.
Thanks for any suggestions...
SQL Server: Microsoft SQL Server 2000 - 8.00.76
MSXML: 6.0
-- WS Code --
<WebMethod()> _
Public Function Hello(ByVal Name As String)
Return "Hello World " & Name
End Function
-- Sproc Code --
CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
AS
DECLARE @.Url varchar(1000)
, @.obj int
, @.hr int
, @.status int
, @.src varchar(4000)
, @.desc varchar(4000)
, @.errmsg varchar(4000)
-- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
-- Generates error in post
SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'send'
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OADestroy @.obj
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
-- Error --
[InvalidOperationException: Request format is unrecognized.]
System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(Type t
ype, HttpContext context, HttpRequest request,
HttpResponse response) +388
System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpContex
t context, String verb, String url, String filePath)
+94
System.Web.HttpApplication.MapHttpHandler(HttpContext context, String reques
tType, String path, String pathTranslated, Boolean
useAppConfig) +699
System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep
.Execute() +95
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& com
pletedSynchronously) +173
</pre></code>There are a couple different ways to do this in SQL Server 2005 but there
aren't ant really good ways to do this in SQL Server 2000. Several people
have used external com objects called through the sp_OA* stored procedures
but that's not a very scaleable solution. I would suggest writing the
information you want to add to AD into a table and then writing an
application that occasionally reads the table and does the AD update. AD
updates are somewhat asynchronous anyway so the delay is probably tolerable.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Garth Wells" <nobody@.ishome.com> wrote in message
news:e5ln2oeYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> The client wants to update Active Directory from a Sproc via a Web
> Service.
> I found an example in Google Archives, but it won't work...the call to a
> method
> generates the error listed below. Please note that a straight disco call
> does work.
> Wondering if it's a versioning issue.
> Thanks for any suggestions...
>
> SQL Server: Microsoft SQL Server 2000 - 8.00.76
> MSXML: 6.0
> -- WS Code --
> <WebMethod()> _
> Public Function Hello(ByVal Name As String)
> Return "Hello World " & Name
> End Function
> -- Sproc Code --
> CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
> AS
> DECLARE @.Url varchar(1000)
> , @.obj int
> , @.hr int
> , @.status int
> , @.src varchar(4000)
> , @.desc varchar(4000)
> , @.errmsg varchar(4000)
> -- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
> -- Generates error in post
> SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'send'
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OADestroy @.obj
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> -- Error --
> [InvalidOperationException: Request format is unrecognized.]
> System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(Type
> type, HttpContext context, HttpRequest request,
> HttpResponse response) +388
> System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpCont
ext
> context, String verb, String url, String filePath)
> +94
> System.Web.HttpApplication.MapHttpHandler(HttpContext context, String
> requestType, String path, String pathTranslated, Boolean
> useAppConfig) +699
> System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutionSt
ep.Execute()
> +95
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
> completedSynchronously) +173
> </pre></code>
>|||Adding the following to the web.config file fixed the problem..
<webServices>
<protocols>
<add name="HttpGet" />
</protocols>
</webServices>
"Garth Wells" <nobody@.ishome.com> wrote in message news:e5ln2oeYFHA.3320@.TK2MSFTNGP12.phx.g
bl...
> The client wants to update Active Directory from a Sproc via a Web Service
.
> I found an example in Google Archives, but it won't work...the call to a m
ethod
> generates the error listed below. Please note that a straight disco call d
oes work.
> Wondering if it's a versioning issue.
> Thanks for any suggestions...
>
> SQL Server: Microsoft SQL Server 2000 - 8.00.76
> MSXML: 6.0
> -- WS Code --
> <WebMethod()> _
> Public Function Hello(ByVal Name As String)
> Return "Hello World " & Name
> End Function
> -- Sproc Code --
> CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
> AS
> DECLARE @.Url varchar(1000)
> , @.obj int
> , @.hr int
> , @.status int
> , @.src varchar(4000)
> , @.desc varchar(4000)
> , @.errmsg varchar(4000)
> -- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
> -- Generates error in post
> SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'send'
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OADestroy @.obj
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> -- Error --
> [InvalidOperationException: Request format is unrecognized.]
> System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(T
ype type, HttpContext context, HttpRequest request,
> HttpResponse response) +388
> System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpContext c
ontext, String verb, String url, String
filePath)
> +94
> System.Web.HttpApplication.MapHttpHandler(HttpContext context, String r
equestType, String path, String pathTranslated, Boolean
> useAppConfig) +699
> System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutio
nStep.Execute() +95
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
; completedSynchronously) +173
> </pre></code>
>

Calling WebServices Asynchronously from SQL CLR

I'm assuming it's possible to call a web service asynchronously from a SQL CLR function, is this correct or is there a fundamental flaw in calling the asynchronous methods?

I've tried coding a sample and despite creating a new thread, which in turn calls the web service asynchronously and using newthread.join I can never get the webmethodasync_completed sub to be called.

There is a fundamental flaw in using SQL CLR in this fashion. For starters, the thread you spawn is not visible through any management tools for SQL Server. So if the thread runs amuck, there is no way (that I have found yet) to kill that thread. Of course, you will need to deploy an UNSAFE assembly.

As for asynchronous call from SQL CLR, I would be willing to bet a very small amount of money (since I haven't ried it myself, mind you) that it isn't possible.

So can I ask, what exactly is it that you are trying to do? Perhaps there is another way to find a solution to your issue that doesn't involve the techniques you have already tried doing.

Calling WebServices Asynchronously from SQL CLR

I'm assuming it's possible to call a web service asynchronously from a SQL CLR function, is this correct or is there a fundamental flaw in calling the asynchronous methods?

I've tried coding a sample and despite creating a new thread, which in turn calls the web service asynchronously and using newthread.join I can never get the webmethodasync_completed sub to be called.

There is a fundamental flaw in using SQL CLR in this fashion. For starters, the thread you spawn is not visible through any management tools for SQL Server. So if the thread runs amuck, there is no way (that I have found yet) to kill that thread. Of course, you will need to deploy an UNSAFE assembly.

As for asynchronous call from SQL CLR, I would be willing to bet a very small amount of money (since I haven't ried it myself, mind you) that it isn't possible.

So can I ask, what exactly is it that you are trying to do? Perhaps there is another way to find a solution to your issue that doesn't involve the techniques you have already tried doing.

Calling webservice from CLR Stored Procedure

I would like to be able to call an webservice from a SP, I'm able to add a
webreference to the webservice in the database project, but how to call it i
n
code, and how to set up the code access illudes me?
any suggestions?
.LPHi LP,
What version of SQL Server are you using?
In SQL Server 2005 you can write a CLR to consume the webservice, check out
my article and samples here: http://sqlserverfaq.com?eid=58 there are
samples and even a blogcast of me showing exactly how to do it.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
".LP" <.LP@.discussions.microsoft.com> wrote in message
news:3DF46544-C919-4C90-B101-2CC906A248E8@.microsoft.com...
>I would like to be able to call an webservice from a SP, I'm able to add a
> webreference to the webservice in the database project, but how to call it
> in
> code, and how to set up the code access illudes me?
> any suggestions?
> .LP|||I am using Sql Server 2005, but want to call an extarnal webservice (asmx)
from the clr-code Sp.
The purpose beeing; to send an notification from the database, from an
trigger with the ID of the Inserted/updated record
and the code I'm looking for is to call an webservice, not just consume xml.
.LP
"Tony Rogerson" wrote:

> Hi LP,
> What version of SQL Server are you using?
> In SQL Server 2005 you can write a CLR to consume the webservice, check ou
t
> my article and samples here: http://sqlserverfaq.com?eid=58 there are
> samples and even a blogcast of me showing exactly how to do it.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> ".LP" <.LP@.discussions.microsoft.com> wrote in message
> news:3DF46544-C919-4C90-B101-2CC906A248E8@.microsoft.com...
>
>|||The code samples does call a webservice - the msn search webservice for one.
Basically, you need to use CLR to encapsulate the web service call.
Tony
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
".LP" <LP@.discussions.microsoft.com> wrote in message
news:8FAC07F5-81CE-468F-B1BC-DE91C9F8BDF8@.microsoft.com...
>I am using Sql Server 2005, but want to call an extarnal webservice (asmx)
> from the clr-code Sp.
> The purpose beeing; to send an notification from the database, from an
> trigger with the ID of the Inserted/updated record
> and the code I'm looking for is to call an webservice, not just consume
> xml.
> .LP
> "Tony Rogerson" wrote:
>

Calling webservice from CLR

I have a webservice that I would like to call from SQL Server 2005. I have done alot of testing and am pretty familiar with how CLR works and how to create assemblies in SQL. My problem is that I don't know much about .net and am not sure what kind of project I need to create in Visual Studios to accomplish calling the webservice. I've tried googling and have not found much that is of use to me.

Any help would be appreciated

Aaron
You need to

1) creat SQL Server Project and create CRL stored Procedure.

2) add your wsdl file to web reference that points to your web service

3) call web method in your CRL stored Procedure

The following link might be useful to you.

http://davidhayden.com/blog/dave/archive/2006/04/25/2924.aspx

Calling webservice from CLR

I have a webservice that I would like to call from SQL Server 2005. I have done alot of testing and am pretty familiar with how CLR works and how to create assemblies in SQL. My problem is that I don't know much about .net and am not sure what kind of project I need to create in Visual Studios to accomplish calling the webservice. I've tried googling and have not found much that is of use to me.

Any help would be appreciated

Aaron
You need to

1) creat SQL Server Project and create CRL stored Procedure.

2) add your wsdl file to web reference that points to your web service

3) call web method in your CRL stored Procedure

The following link might be useful to you.

http://davidhayden.com/blog/dave/archive/2006/04/25/2924.aspx

Calling webservice from a T-SQl stored Procedure

Hi,

I need to call a web service (consume a webservice)from a T-SQL stored procedure. Is there a way to do this. If not is there a way to make a simple http request, something like a utl_http in oracle.

At the moment iam using a MSSOAP30.SOAPCLIENT object created using sp_OACreate to make this call. However this means that the soap toolkit be installed on the pc on which SQL server is installed. I was hoping to find a completely independent way.

Also when i call sp_OACreate where does sqlserver 2005 look to find that object. Iam thinking of putting the MSSOAP30.dll on that machine, if all else fails.

Ahmad

Hi,

you can create an object of XMLHTTP using sp_oacreate as

exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp', @.obj OUT

exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.sUrl, false

exec @.hr = sp_OAMethod @.obj, 'send'