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 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