Can I avoid temp tables, etc.

I need help on two questions:
1. Is temp table the only way to pass recordsets from a nested stored
procedure to a calling stored procedure? Can we avoid temp tables in
this case?
2. Are operations in a stored procedure are treated as a transaction?

Any help will be greatly appreciated.

Background: We need to use temp table to pass recordsets from a nested
stored procedure to a calling stored procedure. Our understanding is
that in this case, we have no choice but to use temp tables. So, we
need to optimize the performance as much as possible. To do this, we
wanted to find out whether operations in a stored procedure are treated
as a transaction. We are using SQL 2000 SP4. I could not find any
answers so I did the following experiment.

Experiment 1:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[Wiz_SP_Transaction_Test]
GO

CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
AS

Update
Articles
SET
IsUpdate = 20
where
ArticlesId < 80000

SELECT * from Articles

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

"SELECT * from Articles" takes a long time (about 40 seconds) to
complete

Before executing the SP, the IsUpdate attribute for all articles is 30.
Then I executed this SP. Before the SP is finished, I end the SP
manually. I checked the IsUpdate attribute again, and found that all
Articles's (ArticlesId < 80000) Isupdate attribute is now 20. The
operations did not rollback. I interpret this to mean that the whole SP
is not treated as a transaction.

Then, I did experiment 2 below. This time, I explicitly declared the
transaction.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[Wiz_SP_Transaction_Test]
GO

CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
AS
BEGIN TRANSACTION
Update
Articles
SET
IsUpdate = 50
where
ArticlesId < 80000

SELECT * from Articles

IF @.@.ERROR <0 ROLLBACK TRANSACTION
COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Before this second SP, the IsUpdate attribute is 20 (set in the first
experiment). I run this second SP and ended it manually before it
finished. I checked the IsUpdate attributes for all Articles's
(ArticlesId < 80000), but their Isupdate attribute is 50. So the
operation did not rollback either. But we have declared the transaction
explicitly. Does this mean that the SP is still not treated as a
transaction?(betbubble@.gmail.com) writes:

Quote:

Originally Posted by

I need help on two questions:
1. Is temp table the only way to pass recordsets from a nested stored
procedure to a calling stored procedure? Can we avoid temp tables in
this case?

No, there are more alternative: use a process-keyed table. As long
as the access is from T-SQL only, @.@.spid works fine. We use this
technique a lot in our shop.

There is also INSERT-EXEC, but I like this less.

I discuss these options in more detail in an article on my web site:
http://www.sommarskog.se/share_data.html

Quote:

Originally Posted by

2. Are operations in a stored procedure are treated as a transaction?

A procedure as such does not define any transaction scope. However,
each INSERT, UPDATE and DELETE statement defines a transaction if
there is no other transaction active. This transaction includs any
trigger that is fired the statement. And in case of INSERT EXEC, the
called procedure will operate in the context of the transaction
defined by the INSERT statement.

Note that this applies, regardless of the INSERT, UPDATE or DELETE
statement appears in a stored procedure or not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What are you using SET ANSI_NULLS OFF for?|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

What are you using SET ANSI_NULLS OFF for?

I would guess that betbubble uses Enterprise Manager to create his
procedures. Which is a very bad idea, for the precise reason Alexander
points out (thanks for catching it!): Enterprise Manager has incorrect
defaults for ANSI_NULLS and QUTOED_IDENTIFIERS. You have rarely reason
to have these options off (least of all ANSI_NULLS), but there are
features in SQL Server that requires these settings to be ON, so by
all means run with them.

If you use Query Analyzer to edit stored procedures, you get the
correct defaults.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland and Alexander,
Thanks a lot. I will experiment with them and report back.|||These SPs were generated using the Query Analyzer, ANSI_NULLS and
QUTOED_IDENTIFIERS are OFF. I turned them ON manually. Is there
something wrong with my Query Analyzer settings?

I read the article by Erland. Great information! Thanks. I am
experimenting with the Process-Keyed tables, which are very big tables.
I have many querys concurrentlly, they will need to use the same
Process-Keyed tables. Any advice on reducing locks will be appreciated.|||(betbubble@.gmail.com) writes:

Quote:

Originally Posted by

These SPs were generated using the Query Analyzer, ANSI_NULLS and
QUTOED_IDENTIFIERS are OFF. I turned them ON manually. Is there
something wrong with my Query Analyzer settings?

You can change the connection settings under Tools->Options->Cononection
Properties. The default settings is that all settings for indexed views
are on, but you might have changed that at some point.

Also, if the SP was originally created by EM, and you scripted it from
QA, QA will include the original settings in the script, so you will
actively have to change them - or just remove them-

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Calling XML web service from a reporting services report (RDL)

Hi,

I wanted to display data returned by xml webservice in a sql reporting service report(RDL).

How I can implement the same, Please let me know the steps.

Thanks,

Anees

Check this: http://msdn.microsoft.com/library/en-us/dnsql90/html/RepServXMLDS.asp

Calling XML web service from a reporting services report (RDL)

Hi,

I wanted to display data returned by xml webservice in a sql reporting service report(RDL).

How I can implement the same, Please let me know the steps.

Thanks,

Anees

Check this: http://msdn.microsoft.com/library/en-us/dnsql90/html/RepServXMLDS.asp

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'

Calling webservice from a trigger

Is it possible to call/fire a method in a webservice (.asmx) from a trigger in MS SQL 2005? I would like to send out a notification to all the admins whenever a new row is inserted into a table in our db. If possible, can someone show me an example of how to?

Take a look at this post discussion - http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=57193&SiteID=17

Hope this helps

Calling Webservice

Hi Scott,
Thanks for the input, but this user has been made a member of all of
the roles defined within Reporting services. i.e "Browser",
"Publisher","My Reports", "Content Manager". I've tried all
combinations of these. No joy. The user has to be a member of the
"Administrators Group" on the machine itself (it appears?).
Scott Allen wrote:
> Hi Amy:
>
> Reporting services uses roles based authorization, and the default
> setup only places the local administrator into a role.
>
> While RSAdminUser might have rights to access all the files and
> folders in the reporting services installation, you still have to put
> the user into a "role". Roles are how RS determines who can browse a
> folder, who can view a report, who can manage a data source etc.
>
> From your reports home page you can go to Site Settings -> Configure
> site wide security (link is at the bottom). Perhaps you should define
> a new role for RSAdminUser and grant just enough authorization for
the
> user to get the things done you need.
>
> Hope this helps,
>
> --
> Scott
> http://www.OdeToCode.com
>
> On 3 Aug 2004 05:04:48 -0700, amydudley@.webmail.co.za (Amy) wrote:
>
> >Hi,
> >
> >I have set the Reporting WebServices WebService Directory Security
in
> >IIS to Windows Authentication. The authentication setting in the
> >config file is set to "Windows" with Impersonation set to "true".
> >
> >I created an account called "RSAdminUser" with which to call the
> >webservice.
> >When calling the "ListReportsUsingDataSource" method, after setting
> >the credentials to this "RSAdminUser", I get the following error:
> >
> >"System.Web.Services.Protocols.SoapException: The permissions
granted
> >to user 'SERVER\RSAdminUser' are insufficient for performing this
> >operation. -->
Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
> >The permissions granted to user 'SERVER\RSAdminUser' are
insufficient
> >for performing this operation. at
>
>Microsoft.ReportingServices.Library.RSService.ListReportsUsingDataSource(String
> >path) at
Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataSource(String
> >DataSource, CatalogItem[]& Reports) -- End of inner exception stack
> >trace -- at
Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataSource(String
> >DataSource, CatalogItem[]& Reports)"
> >
> >I have methodically given this user permissions to all of the
relevant
> >folders and files that I can think of, but the only thing that makes
> >this work is making "RSAdminUser" a member of the "Administrators"
> >group. Is it possible that only members of the "Administrators"
group
> >are allowed to execute this method?
> >
> >Is there an alternative solution? What permissions does this
account
> >need to execute this method?
> >
> >Thanks in advance,
> >
> >AmyWhat is the path of the data source in the namespace. Is it nested several
folders deep and have you given the user rights to all items in the path to
the data source?
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"ALD" <amydudley@.webmail.co.za> wrote in message
news:cesoec$hlm@.odak26.prod.google.com...
> Hi Scott,
> Thanks for the input, but this user has been made a member of all of
> the roles defined within Reporting services. i.e "Browser",
> "Publisher","My Reports", "Content Manager". I've tried all
> combinations of these. No joy. The user has to be a member of the
> "Administrators Group" on the machine itself (it appears?).
> Scott Allen wrote:
> > Hi Amy:
> >
> > Reporting services uses roles based authorization, and the default
> > setup only places the local administrator into a role.
> >
> > While RSAdminUser might have rights to access all the files and
> > folders in the reporting services installation, you still have to put
> > the user into a "role". Roles are how RS determines who can browse a
> > folder, who can view a report, who can manage a data source etc.
> >
> > From your reports home page you can go to Site Settings -> Configure
> > site wide security (link is at the bottom). Perhaps you should define
> > a new role for RSAdminUser and grant just enough authorization for
> the
> > user to get the things done you need.
> >
> > Hope this helps,
> >
> > --
> > Scott
> > http://www.OdeToCode.com
> >
> > On 3 Aug 2004 05:04:48 -0700, amydudley@.webmail.co.za (Amy) wrote:
> >
> > >Hi,
> > >
> > >I have set the Reporting WebServices WebService Directory Security
> in
> > >IIS to Windows Authentication. The authentication setting in the
> > >config file is set to "Windows" with Impersonation set to "true".
> > >
> > >I created an account called "RSAdminUser" with which to call the
> > >webservice.
> > >When calling the "ListReportsUsingDataSource" method, after setting
> > >the credentials to this "RSAdminUser", I get the following error:
> > >
> > >"System.Web.Services.Protocols.SoapException: The permissions
> granted
> > >to user 'SERVER\RSAdminUser' are insufficient for performing this
> > >operation. -->
> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
> > >The permissions granted to user 'SERVER\RSAdminUser' are
> insufficient
> > >for performing this operation. at
> >
>Microsoft.ReportingServices.Library.RSService.ListReportsUsingDataSource(St
ring
> > >path) at
>
Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataS
ource(String
> > >DataSource, CatalogItem[]& Reports) -- End of inner exception stack
> > >trace -- at
>
Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataS
ource(String
> > >DataSource, CatalogItem[]& Reports)"
> > >
> > >I have methodically given this user permissions to all of the
> relevant
> > >folders and files that I can think of, but the only thing that makes
> > >this work is making "RSAdminUser" a member of the "Administrators"
> > >group. Is it possible that only members of the "Administrators"
> group
> > >are allowed to execute this method?
> > >
> > >Is there an alternative solution? What permissions does this
> account
> > >need to execute this method?
> > >
> > >Thanks in advance,
> > >
> > >Amy
>

Calling Webservice

Hi,
I have set the Reporting WebServices WebService Directory Security in
IIS to Windows Authentication. The authentication setting in the
config file is set to "Windows" with Impersonation set to "true".
I created an account called "RSAdminUser" with which to call the
webservice.
When calling the "ListReportsUsingDataSource" method, after setting
the credentials to this "RSAdminUser", I get the following error:
"System.Web.Services.Protocols.SoapException: The permissions granted
to user 'SERVER\RSAdminUser' are insufficient for performing this
operation. --> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
The permissions granted to user 'SERVER\RSAdminUser' are insufficient
for performing this operation. at
Microsoft.ReportingServices.Library.RSService.ListReportsUsingDataSource(String
path) at Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataSource(String
DataSource, CatalogItem[]& Reports) -- End of inner exception stack
trace -- at Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataSource(String
DataSource, CatalogItem[]& Reports)"
I have methodically given this user permissions to all of the relevant
folders and files that I can think of, but the only thing that makes
this work is making "RSAdminUser" a member of the "Administrators"
group. Is it possible that only members of the "Administrators" group
are allowed to execute this method?
Is there an alternative solution? What permissions does this account
need to execute this method?
Thanks in advance,
AmyHi Amy:
Reporting services uses roles based authorization, and the default
setup only places the local administrator into a role.
While RSAdminUser might have rights to access all the files and
folders in the reporting services installation, you still have to put
the user into a "role". Roles are how RS determines who can browse a
folder, who can view a report, who can manage a data source etc.
From your reports home page you can go to Site Settings -> Configure
site wide security (link is at the bottom). Perhaps you should define
a new role for RSAdminUser and grant just enough authorization for the
user to get the things done you need.
Hope this helps,
--
Scott
http://www.OdeToCode.com
On 3 Aug 2004 05:04:48 -0700, amydudley@.webmail.co.za (Amy) wrote:
>Hi,
>I have set the Reporting WebServices WebService Directory Security in
>IIS to Windows Authentication. The authentication setting in the
>config file is set to "Windows" with Impersonation set to "true".
>I created an account called "RSAdminUser" with which to call the
>webservice.
>When calling the "ListReportsUsingDataSource" method, after setting
>the credentials to this "RSAdminUser", I get the following error:
>"System.Web.Services.Protocols.SoapException: The permissions granted
>to user 'SERVER\RSAdminUser' are insufficient for performing this
>operation. --> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
>The permissions granted to user 'SERVER\RSAdminUser' are insufficient
>for performing this operation. at
>Microsoft.ReportingServices.Library.RSService.ListReportsUsingDataSource(String
>path) at Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataSource(String
>DataSource, CatalogItem[]& Reports) -- End of inner exception stack
>trace -- at Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataSource(String
>DataSource, CatalogItem[]& Reports)"
>I have methodically given this user permissions to all of the relevant
>folders and files that I can think of, but the only thing that makes
>this work is making "RSAdminUser" a member of the "Administrators"
>group. Is it possible that only members of the "Administrators" group
>are allowed to execute this method?
>Is there an alternative solution? What permissions does this account
>need to execute this method?
>Thanks in advance,
>Amy

Calling web services from Query Analyzer

I've made a database change to a table to add home and
operational amounts. In an upgrade path, I need to
populate these columns with values. There is already C#
code that does the currency conversion based on comparing
transactional currency to both the home and operational
currency codes. Right now, our upgrade scripts are run
via Query Analyzer. Is there a way for me to call web
services and execute methods that have already been
written to do the currency conversion for me? I'd hate to
reinvent the wheel and rewrite code for something that's
already been in done C#.
We are running SQL Server 2000.
Thanks in advance,
BettinaSQL Server's T-SQL language doesn't have any in-built features that can
perform any kind of http calls - soap or otherwise.
T-SQL does provide ability to call external libraries and operating system
commands via things such as sp_OACreate (COM) and xp_cmdshell (console
commands), so it's arguable that your could conceivably call a web-service
from within the Query Analyser, consume it with sp_xml_preparedocument etc
but this is really getting into the zen mastery area & not really what Query
Analyser was designed to perform.
You might do better writing a VBScript or JScript & running that as a DTS
package, or perhaps just coding something in a VS.Net project (with
whatever's your favourite language flavour..)
Regards,
Greg Linwood
SQL Server MVP
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:cb4301c3ef43$84912d60$a101280a@.phx.gbl...
> I've made a database change to a table to add home and
> operational amounts. In an upgrade path, I need to
> populate these columns with values. There is already C#
> code that does the currency conversion based on comparing
> transactional currency to both the home and operational
> currency codes. Right now, our upgrade scripts are run
> via Query Analyzer. Is there a way for me to call web
> services and execute methods that have already been
> written to do the currency conversion for me? I'd hate to
> reinvent the wheel and rewrite code for something that's
> already been in done C#.
> We are running SQL Server 2000.
> Thanks in advance,
> Bettina
>|||Hi Greg,
Thank you so much for your response. Unfortunately, I'm
not a database programmer (I only have experience as an
Oracle Forms programmer) and I am not knowledgeable in
creating VBScripts, JScript, or writing something up using
VS .NET. I was hoping to find an easier answer.
Thanks again for your response.
Bettina

>--Original Message--
>SQL Server's T-SQL language doesn't have any in-built
features that can
>perform any kind of http calls - soap or otherwise.
>T-SQL does provide ability to call external libraries and
operating system
>commands via things such as sp_OACreate (COM) and
xp_cmdshell (console
>commands), so it's arguable that your could conceivably
call a web-service
>from within the Query Analyser, consume it with
sp_xml_preparedocument etc
>but this is really getting into the zen mastery area &
not really what Query
>Analyser was designed to perform.
>You might do better writing a VBScript or JScript &
running that as a DTS
>package, or perhaps just coding something in a VS.Net
project (with
>whatever's your favourite language flavour..)
>Regards,
>Greg Linwood
>SQL Server MVP
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:cb4301c3ef43$84912d60$a101280a@.phx.gbl...
comparing
to
>
>.
>|||Sorry there's not an easier answer.. :c/
This (xml, soap etc) is an area where things are improving in SQL Server but
for now it's not a simple task to perform in QA & is better suited to
language development..
Regards,
Greg Linwood
SQL Server MVP
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:d76901c3ef5c$3bad51f0$a401280a@.phx.gbl...
> Hi Greg,
> Thank you so much for your response. Unfortunately, I'm
> not a database programmer (I only have experience as an
> Oracle Forms programmer) and I am not knowledgeable in
> creating VBScripts, JScript, or writing something up using
> VS .NET. I was hoping to find an easier answer.
> Thanks again for your response.
> Bettina
>
> features that can
> operating system
> xp_cmdshell (console
> call a web-service
> sp_xml_preparedocument etc
> not really what Query
> running that as a DTS
> project (with
> message
> comparing
> to

Calling web services from Query Analyzer

I've made a database change to a table to add home and
operational amounts. In an upgrade path, I need to
populate these columns with values. There is already C#
code that does the currency conversion based on comparing
transactional currency to both the home and operational
currency codes. Right now, our upgrade scripts are run
via Query Analyzer. Is there a way for me to call web
services and execute methods that have already been
written to do the currency conversion for me? I'd hate to
reinvent the wheel and rewrite code for something that's
already been in done C#.
We are running SQL Server 2000.
Thanks in advance,
BettinaSQL Server's T-SQL language doesn't have any in-built features that can
perform any kind of http calls - soap or otherwise.
T-SQL does provide ability to call external libraries and operating system
commands via things such as sp_OACreate (COM) and xp_cmdshell (console
commands), so it's arguable that your could conceivably call a web-service
from within the Query Analyser, consume it with sp_xml_preparedocument etc
but this is really getting into the zen mastery area & not really what Query
Analyser was designed to perform.
You might do better writing a VBScript or JScript & running that as a DTS
package, or perhaps just coding something in a VS.Net project (with
whatever's your favourite language flavour..)
Regards,
Greg Linwood
SQL Server MVP
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:cb4301c3ef43$84912d60$a101280a@.phx.gbl...
> I've made a database change to a table to add home and
> operational amounts. In an upgrade path, I need to
> populate these columns with values. There is already C#
> code that does the currency conversion based on comparing
> transactional currency to both the home and operational
> currency codes. Right now, our upgrade scripts are run
> via Query Analyzer. Is there a way for me to call web
> services and execute methods that have already been
> written to do the currency conversion for me? I'd hate to
> reinvent the wheel and rewrite code for something that's
> already been in done C#.
> We are running SQL Server 2000.
> Thanks in advance,
> Bettina
>|||Hi Greg,
Thank you so much for your response. Unfortunately, I'm
not a database programmer (I only have experience as an
Oracle Forms programmer) and I am not knowledgeable in
creating VBScripts, JScript, or writing something up using
VS .NET. I was hoping to find an easier answer.
Thanks again for your response.
Bettina
>--Original Message--
>SQL Server's T-SQL language doesn't have any in-built
features that can
>perform any kind of http calls - soap or otherwise.
>T-SQL does provide ability to call external libraries and
operating system
>commands via things such as sp_OACreate (COM) and
xp_cmdshell (console
>commands), so it's arguable that your could conceivably
call a web-service
>from within the Query Analyser, consume it with
sp_xml_preparedocument etc
>but this is really getting into the zen mastery area &
not really what Query
>Analyser was designed to perform.
>You might do better writing a VBScript or JScript &
running that as a DTS
>package, or perhaps just coding something in a VS.Net
project (with
>whatever's your favourite language flavour..)
>Regards,
>Greg Linwood
>SQL Server MVP
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:cb4301c3ef43$84912d60$a101280a@.phx.gbl...
>> I've made a database change to a table to add home and
>> operational amounts. In an upgrade path, I need to
>> populate these columns with values. There is already C#
>> code that does the currency conversion based on
comparing
>> transactional currency to both the home and operational
>> currency codes. Right now, our upgrade scripts are run
>> via Query Analyzer. Is there a way for me to call web
>> services and execute methods that have already been
>> written to do the currency conversion for me? I'd hate
to
>> reinvent the wheel and rewrite code for something that's
>> already been in done C#.
>> We are running SQL Server 2000.
>> Thanks in advance,
>> Bettina
>
>.
>|||Sorry there's not an easier answer.. :c/
This (xml, soap etc) is an area where things are improving in SQL Server but
for now it's not a simple task to perform in QA & is better suited to
language development..
Regards,
Greg Linwood
SQL Server MVP
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:d76901c3ef5c$3bad51f0$a401280a@.phx.gbl...
> Hi Greg,
> Thank you so much for your response. Unfortunately, I'm
> not a database programmer (I only have experience as an
> Oracle Forms programmer) and I am not knowledgeable in
> creating VBScripts, JScript, or writing something up using
> VS .NET. I was hoping to find an easier answer.
> Thanks again for your response.
> Bettina
> >--Original Message--
> >SQL Server's T-SQL language doesn't have any in-built
> features that can
> >perform any kind of http calls - soap or otherwise.
> >
> >T-SQL does provide ability to call external libraries and
> operating system
> >commands via things such as sp_OACreate (COM) and
> xp_cmdshell (console
> >commands), so it's arguable that your could conceivably
> call a web-service
> >from within the Query Analyser, consume it with
> sp_xml_preparedocument etc
> >but this is really getting into the zen mastery area &
> not really what Query
> >Analyser was designed to perform.
> >
> >You might do better writing a VBScript or JScript &
> running that as a DTS
> >package, or perhaps just coding something in a VS.Net
> project (with
> >whatever's your favourite language flavour..)
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"bpdee" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:cb4301c3ef43$84912d60$a101280a@.phx.gbl...
> >> I've made a database change to a table to add home and
> >> operational amounts. In an upgrade path, I need to
> >> populate these columns with values. There is already C#
> >> code that does the currency conversion based on
> comparing
> >> transactional currency to both the home and operational
> >> currency codes. Right now, our upgrade scripts are run
> >> via Query Analyzer. Is there a way for me to call web
> >> services and execute methods that have already been
> >> written to do the currency conversion for me? I'd hate
> to
> >> reinvent the wheel and rewrite code for something that's
> >> already been in done C#.
> >>
> >> We are running SQL Server 2000.
> >>
> >> Thanks in advance,
> >> Bettina
> >>
> >
> >
> >.
> >

Calling web service without .Net API

Is there an example of how to call the report server web services without
using the .Net API that it comes with? I need to call the web services from
a non .Net client.
Thanks.
--
McGeeky
http://mcgeeky.blogspot.comWhat you are really looking for then is an example of a SOAP call. Don't
know what you are working in, but a Google on your client language and SOAP
may get you what you need.
"McGeeky" <anon@.anon.com> wrote in message
news:uskxdJViFHA.328@.tk2msftngp13.phx.gbl...
> Is there an example of how to call the report server web services without
> using the .Net API that it comes with? I need to call the web services
> from a non .Net client.
> Thanks.
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>

Calling web service onclick of report column

We have a web service that reads a PDF file into binary and inserts the
binary text in a database, then returns an ID to the record. Is there a way
to call this web service from a Reporting Service Report column? The column
will have an Adobe image in the column and when clicked with call the web
service and read the record from the Database streaming the binary to a
browser window.
Thanks in advance!
RickOn Nov 27, 7:55 am, "Rick" <rfem...@.newsgroups.nospam> wrote:
> We have a web service that reads a PDF file into binary and inserts the
> binary text in a database, then returns an ID to the record. Is there a way
> to call this web service from a Reporting Service Report column? The column
> will have an Adobe image in the column and when clicked with call the web
> service and read the record from the Database streaming the binary to a
> browser window.
> Thanks in advance!
> Rick
You might try looking into using the Custom Code section of the report
(via: Layout tab >> Report drop-down tab >> Report Properties... >>
Code tab). Another long shot might be to use Jump to URL as part of
the Navigation Properties and pass the Web Service parameters as part
of an expression that calls the web service via URL. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Ok, I created a class library that makes a call to the web service and
referenced it in the report.
The web service uses system.web.httpresponse to stream the file to a
browser, so in order to make the class library work it required me to pass
in the httpresponse from the calling web page. So I tested that with a test
web site and it works as expected. I tried adding the same code to call the
class library using Custom Code and Navigation URL. The problem I have is
how to get the Httpresponse from the report to pass into the class library,
I made a reference to System.Web.Httpresponse in the report, but when I call
the code or use the navigation url it does not recognize HTTPResponse.
Any suggestions?
Error:
The Hyperlink expression for the image 'image1' contains an error: [BC30691]
'HttpResponse' is a type in 'Web' and cannot be used as an expression.
Navigation URL:
=webservicecall.webservicecall.getpdffile("filepath\filename.pdf",System.Web.HTTPResponse)
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:b5c663d9-9c91-4b50-8b56-bae9ef9f4788@.w34g2000hsg.googlegroups.com...
> On Nov 27, 7:55 am, "Rick" <rfem...@.newsgroups.nospam> wrote:
>> We have a web service that reads a PDF file into binary and inserts the
>> binary text in a database, then returns an ID to the record. Is there a
>> way
>> to call this web service from a Reporting Service Report column? The
>> column
>> will have an Adobe image in the column and when clicked with call the web
>> service and read the record from the Database streaming the binary to a
>> browser window.
>> Thanks in advance!
>> Rick
>
> You might try looking into using the Custom Code section of the report
> (via: Layout tab >> Report drop-down tab >> Report Properties... >>
> Code tab). Another long shot might be to use Jump to URL as part of
> the Navigation Properties and pass the Web Service parameters as part
> of an expression that calls the web service via URL. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I could use some help. Here is what I have, I created a Class Library that
makes a call to a web service and referenced the class library in the
report, I setup code in the report properties code, The Public function
calls CallWebService2, CallWebService2 makes a call to a the class library
with the web service passing in a pdffiname, an ID Parameter and the
HTTPResponse, the web service in return streams the PDF to the browser, I
put a column on the report that has an adobe icon image, in this column in
the Jump to URL, I put =code.CallWebService(): When I do this the Jump To
URL does nothing. I'm not sure if I'm getting the HTTPResponse correctly.
Report Properties Code:
Public Function CallWebService() as String
CallWebService2()
Return "True"
End Function
Private Function CallWebService2() as Boolean
Dim nservice As New WebServiceCall.WebServiceCall
Dim response1 As System.Web.HttpResponse
WebServiceCall.WebServiceCall.GetPDFFile("filepath\filename",
"parameter1", Response1)
Return True
End Function
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:Ks7X3AzMIHA.6940@.TK2MSFTNGHUB02.phx.gbl...
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Rick,
You could not use the Code in the Jump to URL directly.
You may add a new report which is call the web service to show the
information. And you may use the Jump to URL to the report.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.