Showing posts with label web. Show all posts
Showing posts with label web. Show all posts

Tuesday, March 27, 2012

Calling SSRS web services from Java using Axis

Hi,

I'm working on a project where I'll need to call the SSRS web services from a java servlet. We are using the Axis implementation of the SOAP protocol.

I've previously used SSRS web services from C#, but not from Java. Before I start this endeavor does anyone have any bits of wisdom they'd like to share that's going to make this easier?

Also, I've searched for tutorials, samples, source code, etc.. for this situation (that is, using java to call SSRS web services), but can't find anything available. Does anyone know where I can find resources such as these for this particular technology pairing?

Thanks for the help.

-M

Try WSDL2Java tool to create a java proxy to the reporting services.

The WSDL is accessible from one of these url depending on the version(2000 or 2005) and flavor (management or execution) of the service

http://<server>/reportserver/reportservice.asmx?wsdl

http://<server>/reportserver/reportservice2005.asmx?wsdl

http://<server>/reportserver/reportexecution2005.asmx?wsdl

An example on accessing .NET web services (not SSRS specific) from Java is available here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/jdni_ch08.asp

Sunday, March 25, 2012

Calling SQL Native Web Service failed as unauthorized

I created a native HTTP end point in SQL Server 2005 Developer edition. I
also created a simple console application to call the web service by add web
reference to generate the proxy client code. It works on the same local
host.
However, I could not add web service reference if I develop the same console
application in on a different machine. Both machines are on the same domain
and logged in using the same account.
I tried to use the Windows authentication.
What shall be set on both SQL Server side and also client side to make the
web service accesible from a different machine?
When adding web reference, I was asked for use id and passwords as well as
domain. It kept asking even after I entered the information.
So I tried to manually generate the proxy code based on the wsdl. The
application threw an exception of "assembly does not allow partially trusted
callers" when the constructor of the proxy client class was called.
Any idea on how to develop client to a SQL native web service?
Thanks.
Hao
It is also interesting that when I type the url into the web browser on a
different machine.
http://hostname:9100/sqlNativeHTTPendpointTest/test?wsdl
I was asked to enter the crendentials. No matter what I entered, it does not
work.
Hao
"Hao" <Hao@.newsgroup.nospam> wrote in message
news:%23gR$A7hdHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I created a native HTTP end point in SQL Server 2005 Developer edition. I
>also created a simple console application to call the web service by add
>web reference to generate the proxy client code. It works on the same local
>host.
> However, I could not add web service reference if I develop the same
> console application in on a different machine. Both machines are on the
> same domain and logged in using the same account.
> I tried to use the Windows authentication.
> What shall be set on both SQL Server side and also client side to make the
> web service accesible from a different machine?
> When adding web reference, I was asked for use id and passwords as well as
> domain. It kept asking even after I entered the information.
> So I tried to manually generate the proxy code based on the wsdl. The
> application threw an exception of "assembly does not allow partially
> trusted callers" when the constructor of the proxy client class was
> called.
> Any idea on how to develop client to a SQL native web service?
> Thanks.
> Hao
>
|||Hello Hao,
Are you doing this on Vista by chance?
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hello Hao,
I understand that when you try to Add web reference to a sql http web
service, you are required to input domain credential. Though you have the
correct domain account/passowrd, you are repeated to prompt to input again.
If I'm off-base, please let me know.
Based on my experience, this issue might be caused by integration
authentication. Your development machine might not have the delegation
right for kerberbose authentication. I suggest that you try to use NTLM
authentication on SQL server side to work around the issue. For example"
CREATE ENDPOINT SQL_Products
STATE = STARTED
AS HTTP
(
PATH = '/AWproducts',
AUTHENTICATION = (NTLM),
PORTS = (CLEAR),
CLEAR_PORT = 9999
)
FOR SOAP
(
WEBMETHOD 'ProductList'
(NAME='AdventureWorks.dbo.prProductList'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://Adventure-Works/Products'
)
If you want to enable integrated authentication. You may try the following
method:
In the user Active Directory property, Account is sensitive and cannot be
delegated, must not be selected.
If you have any further questions or concerns on the issue, please feel
free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Peter Yang [MSFT],
This is why I asked if its Vista. the stuff off the Local Intranet zone there
with its "automatic login"
That said, the proxy generated would be told to PreAuthenticate and use the
DefaultCredentials from CredentialCache.
Sure, the account may not be delegation-enabled, but how frequently is that
encountered?
Thanks,
Kent
|||Thanks for the work around of setting NTLM. It worked. Both machines are
XPs.
What is the reason that it did not work if I wanted to enable all these
authentication mode?
AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED)
Thanks.
Hao
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:18f2bcb1a0f88c94459b94bcee8@.news.microsoft.co m...
> Hello Peter Yang [MSFT],
> This is why I asked if its Vista. the stuff off the Local Intranet zone
> there with its "automatic login"
> That said, the proxy generated would be told to PreAuthenticate and use
> the DefaultCredentials from CredentialCache.
> Sure, the account may not be delegation-enabled, but how frequently is
> that encountered?
> Thanks,
> Kent
>
|||Hello Hao,
As you know, Integrated is actually a negotiation of Kerberos/NTLM. It
seems that the issue is caused by that KERBEROS does not work for the
Webservice authentication. Please check the delegation property of the
logged on user in AD.
Also, When Kerberos authentication is used, the instance of SQL Server must
associate a Service Principal Name (SPN) with the account it will be
running on. Please make sure you have it registred in AD. For more
information, see Registering Kerberos Service Principal Names by Using
Http.sys.
Registering Kerberos Service Principal Names by Using Http.sys
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29054cc2-31c5-46d6-a381-d10a
1ebc05b7.htm
For setspn tool, please refer to:
http://technet2.microsoft.com/WindowsServer/en/library/b3a029a1-7ff0-4f6f-87
d2-f2e70294a5761033.mspx?mfr=true
If the issue persists, please try to check security event log both on
client/server to see if there is any errors.
Another very useful utility for troubleshooting Kerberos issues
is Kerbtray.exe, which is part of the Windows 2000 Resource Kit. By using
Kerbtray, you can see Kerberos tickets that were granted out of the local
cache. To download this utility, visit the following Microsoft Web site:
Kerbtray.exe: Kerberos Tray
http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/kerbtray
-o.asp
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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 Hao,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.

Calling SQL Native Web Service failed as unauthorized

I created a native HTTP end point in SQL Server 2005 Developer edition. I
also created a simple console application to call the web service by add web
reference to generate the proxy client code. It works on the same local
host.
However, I could not add web service reference if I develop the same console
application in on a different machine. Both machines are on the same domain
and logged in using the same account.
I tried to use the Windows authentication.
What shall be set on both SQL Server side and also client side to make the
web service accesible from a different machine?
When adding web reference, I was asked for use id and passwords as well as
domain. It kept asking even after I entered the information.
So I tried to manually generate the proxy code based on the wsdl. The
application threw an exception of "assembly does not allow partially trusted
callers" when the constructor of the proxy client class was called.
Any idea on how to develop client to a SQL native web service?
Thanks.
HaoIt is also interesting that when I type the url into the web browser on a
different machine.
http://hostname:9100/sqlNativeHTTPe...tTest/test?wsdl
I was asked to enter the crendentials. No matter what I entered, it does not
work.
Hao
"Hao" <Hao@.newsgroup.nospam> wrote in message
news:%23gR$A7hdHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I created a native HTTP end point in SQL Server 2005 Developer edition. I
>also created a simple console application to call the web service by add
>web reference to generate the proxy client code. It works on the same local
>host.
> However, I could not add web service reference if I develop the same
> console application in on a different machine. Both machines are on the
> same domain and logged in using the same account.
> I tried to use the Windows authentication.
> What shall be set on both SQL Server side and also client side to make the
> web service accesible from a different machine?
> When adding web reference, I was asked for use id and passwords as well as
> domain. It kept asking even after I entered the information.
> So I tried to manually generate the proxy code based on the wsdl. The
> application threw an exception of "assembly does not allow partially
> trusted callers" when the constructor of the proxy client class was
> called.
> Any idea on how to develop client to a SQL native web service?
> Thanks.
> Hao
>|||Hello Hao,
Are you doing this on Vista by chance?
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hello Hao,
I understand that when you try to Add web reference to a sql http web
service, you are required to input domain credential. Though you have the
correct domain account/passowrd, you are repeated to prompt to input again.
If I'm off-base, please let me know.
Based on my experience, this issue might be caused by integration
authentication. Your development machine might not have the delegation
right for kerberbose authentication. I suggest that you try to use NTLM
authentication on SQL server side to work around the issue. For example"
CREATE ENDPOINT SQL_Products
STATE = STARTED
AS HTTP
(
PATH = '/AWproducts',
AUTHENTICATION = (NTLM),
PORTS = (CLEAR),
CLEAR_PORT = 9999
)
FOR SOAP
(
WEBMETHOD 'ProductList'
(NAME='AdventureWorks.dbo.prProductList'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://Adventure-Works/Products'
)
If you want to enable integrated authentication. You may try the following
method:
In the user Active Directory property, Account is sensitive and cannot be
delegated, must not be selected.
If you have any further questions or concerns on the issue, please feel
free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter Yang [MSFT],
This is why I asked if its Vista. the stuff off the Local Intranet zone ther
e
with its "automatic login"
That said, the proxy generated would be told to PreAuthenticate and use the
DefaultCredentials from CredentialCache.
Sure, the account may not be delegation-enabled, but how frequently is that
encountered?
Thanks,
Kent|||Thanks for the work around of setting NTLM. It worked. Both machines are
XPs.
What is the reason that it did not work if I wanted to enable all these
authentication mode?
AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED)
Thanks.
Hao
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:18f2bcb1a0f88c94459b94bcee8@.news.microsoft.com...
> Hello Peter Yang [MSFT],
> This is why I asked if its Vista. the stuff off the Local Intranet zone
> there with its "automatic login"
> That said, the proxy generated would be told to PreAuthenticate and use
> the DefaultCredentials from CredentialCache.
> Sure, the account may not be delegation-enabled, but how frequently is
> that encountered?
> Thanks,
> Kent
>|||Hello Hao,
As you know, Integrated is actually a negotiation of Kerberos/NTLM. It
seems that the issue is caused by that KERBEROS does not work for the
Webservice authentication. Please check the delegation property of the
logged on user in AD.
Also, When Kerberos authentication is used, the instance of SQL Server must
associate a Service Principal Name (SPN) with the account it will be
running on. Please make sure you have it registred in AD. For more
information, see Registering Kerberos Service Principal Names by Using
Http.sys.
Registering Kerberos Service Principal Names by Using Http.sys
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29054cc2-31c5-46d6-a381-d10a
1ebc05b7.htm
For setspn tool, please refer to:
http://technet2.microsoft.com/Windo...a1-7ff0-4f6f-87
d2-f2e70294a5761033.mspx?mfr=true
If the issue persists, please try to check security event log both on
client/server to see if there is any errors.
Another very useful utility for troubleshooting Kerberos issues
is Kerbtray.exe, which is part of the Windows 2000 Resource Kit. By using
Kerbtray, you can see Kerberos tickets that were granted out of the local
cache. To download this utility, visit the following Microsoft Web site:
Kerbtray.exe: Kerberos Tray
http://www.microsoft.com/windows200...isting/kerbtray
-o.asp
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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 Hao,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.sql

Calling sp_helptext from ASPNET

Hi,

I am executing a call to "sp_helptext" from a web service, in order to
obtain the text of a stored procedure. The call itself seems to be ok but
eventually it throws an exception saying that the specified object does not
exist in the database - which is a false statement, i've checked carefully.

I probably do not have enough rights to access something from the ASPNET
account.
Do i have to login as "sa"? Is this the only solution available?

Bogdan.Bogdan Rechi (bogdan_rechi@.hotmail.com) writes:
> I am executing a call to "sp_helptext" from a web service, in order to
> obtain the text of a stored procedure. The call itself seems to be ok
> but eventually it throws an exception saying that the specified object
> does not exist in the database - which is a false statement, i've
> checked carefully.

What sort of object is it? Not all object types have text.

> I probably do not have enough rights to access something from the ASPNET
> account.

Is this account owner of the database? If not, how do you specify the
object names? Do you include owner, as in

exec sp_helptext 'dho.my_proc'

In SQL 2000, access rights are not likely to be an issue, as all users
have rights to see metadata. SQL2005 is very different in that regard.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

calling report in asp.net app

Hello,
I created a few reports by using RS2000, now I want to call these reports in
my asp.net web application through a selection box. What is the best and
easiest way to handle this.
Thanks,
Jim.if you want a dynamic list (a list constructs from the reports available) so
you can access RS through its webservice and retrieve the list of available
reports
Or if the list is limited , then you can fill manually the selection box
with "static" values.
To open and render the report, if the ASP.NET application is on the same
server as RS, then simply open the report with URL access:
/reportserver?/folder/report name
You can use the sample ASP.net control provided with RS to disply the
report.
"JIM.H." <JIMH@.discussions.microsoft.com> a écrit dans le message de news:
185467F1-14DD-4658-A5EE-A2024FC6290D@.microsoft.com...
> Hello,
> I created a few reports by using RS2000, now I want to call these reports
> in
> my asp.net web application through a selection box. What is the best and
> easiest way to handle this.
> Thanks,
> Jim.
>|||Jim,
Easiest may not always be the best way since it depends on your report
requirements. But in general, you should gravitate whenever possible to URL
addressability. The ReportViewer sample that comes with RS makes this
easier.
If you want to be able to switch between URL and SOAP addressibility you may
find my AwReportViewer control (an enhanced version of ReportViewer useful
(http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45). Enjoy
it!
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:185467F1-14DD-4658-A5EE-A2024FC6290D@.microsoft.com...
> Hello,
> I created a few reports by using RS2000, now I want to call these reports
in
> my asp.net web application through a selection box. What is the best and
> easiest way to handle this.
> Thanks,
> Jim.
>

Thursday, March 22, 2012

Calling my web service from the custom code is failing

This is what I'm trying to achieve. I want to call into my custom
assembly from the reports, and that assembly in turn calls a web
service. The reason I have to do that is because I think we can't
directly call a web service from the reports. To complicate the things
further I also have Forms Authentication enabled on Reporting
services.
Forms authentication enables me to use User!UserID which I pass on to
my custom assembly. The assembly in turn passes the parameter to web
service. To test the functionality I was trying to return simply
"Hello " + parameter value from the web service.
I have copied the custom assembly code to report manager's bin and
report server's bin directories. I have also added CodeGroup to
rssvrpolicy.config file to give a "FullTrust" permission on my custom
assembly.
The reports works as expected in Preview tab in VS.NET designer, but
when accessed from report manager or from the report server via URL,
the expression in text box shows #Error.
Please help to explain how to debug and fix the problem. Thanks much.
YogeshI have finally managed to get web service call working. I was able to
do this by asserting System.Net.WebPermission. Here's code snippet:
//Add following at the top of the class file
using System.Net;
using System.Security.Permissions;
//Add these lines just before instantiating web service proxy class
WebPermission webPerm = new
WebPermission(PermissionState.Unrestricted);
webPerm.Assert();
Remember to add exception handling code otherwise when functions in
custom code fails you will get "#Error" in the textbox in the report
without explaining what went wrong. If your methods return strings
then for debugging purposes you can return ex.ToString() which will at
least let you know what went wrong.
Yogesh|||Hi Yogesh,
Help..
Tried all of the above and still getting the #error on my report - I can
call a different function in the dll (not using a web service) and that is
fine..
Do I need to set up any policies in the rssvrpolicy.config file ?
I have added the reference in my code Web.Assert code - so it looks like I
still have problems with the config files ..
"Yogesh" wrote:
> This is what I'm trying to achieve. I want to call into my custom
> assembly from the reports, and that assembly in turn calls a web
> service. The reason I have to do that is because I think we can't
> directly call a web service from the reports. To complicate the things
> further I also have Forms Authentication enabled on Reporting
> services.
> Forms authentication enables me to use User!UserID which I pass on to
> my custom assembly. The assembly in turn passes the parameter to web
> service. To test the functionality I was trying to return simply
> "Hello " + parameter value from the web service.
> I have copied the custom assembly code to report manager's bin and
> report server's bin directories. I have also added CodeGroup to
> rssvrpolicy.config file to give a "FullTrust" permission on my custom
> assembly.
> The reports works as expected in Preview tab in VS.NET designer, but
> when accessed from report manager or from the report server via URL,
> the expression in text box shows #Error.
> Please help to explain how to debug and fix the problem. Thanks much.
> Yogesh
>

calling my connection from pages

I have been looking everywhere and cant seem to find out how to do this using 2.0

I would like to have my connection strign in my web.config file being decralred publicly in my pages so that I dont have to repeat lines of code throughout pages.

In my web.config I have this.
<connectionStrings>
<add name="myconn" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

then in my pages I have

Imports System.Data
Imports System.Data.SqlClient

Public sqlconnection As New SqlConnection(ConfigurationSettings.AppSettings("myconn"))
Public sqlcommand As New SqlCommand
Public sqldataAdapter As New SqlDataAdapter
Public sSQL as String

Function getdata()
sSQL = "INSERT INTO tbl_Idea (Status) value (@.Status)
Dim dbcommand As New System.Data.SqlClient.SqlCommand(sSQL, sqlconnection)

Dim statusparam As New SqlParameter("@.Status", SqlDbType.NVarChar)
statusparam.Value = "PENDING"
dbcommand.Parameters.Add(statusparam)

sqlconnection.Open()
dbcommand.ExecuteNonQuery()
sqlconnection.Close()

for some reason this code does not seem to work. It keeps telling me The ConnectionString Property has not been initialized.

I do the same thing in my asp.net 1.1 pages using vs 2003. Can some one help or atleast point in the right direction.

thanks

Hi,

Here is the simple explanation:

Your connection string in the web.config is not in the AppSettings section but in the connectionStrings section.

so instead of reading it like in your code:
Public sqlconnection As New SqlConnection(ConfigurationSettings.AppSettings("myconn"))

you just read it from the connectionStrings section like so:
Public

Dim sqlconnectionAsNew SqlConnection(ConfigurationManager.ConnectionStrings("myconn").ConnectionString)

This will do the job

Have fun

G.

|||dude ur the bomb. thanks a lot.

Calling Function From Stored Procdure ??

I'm a total novice with SQL, so please bare with me.
I'm trying to write a simple forum for my web site, i've done it before using Access but struggling to use the same idea with SQL. In acces i had one table for all the post and replies, i then had several querys that filtered off just the main topics and the number of replies for each thread. I did this by using multiple queries joined together, i've now found out that in SQL you cant call one strored procdure from another, so you have to use functions instead.

I have a stored procdure that filters off just the starter threads, but i want to also show how many replies ther has been for each topic and put then in date order of when they were posted.

this is my main stored procdure

CREATE PROCEDURE forum2 AS
SELECT forum.ForumID, forum.ID, forum.partID, forum.subject, forum.author, forum.message, forum.responder, forum.dtDate, forum.IpAddress, forum.PicID
FROM forum
WHERE forum.partID=0
ORDER BY dtDate DESC

this works ok and shows the main threads, but i also want to list the number of posts for each thread, i've got a function

CREATE FUNCTION dbo.CountofUsers()
RETURNS TABLE
AS RETURN
SELECT Count(*) AS CountOfID, forum.partID
FROM forum
WHERE forum.partID<>0
GROUP BY forum.partID

this should count the number of posts for each thread, but how do i combined the 2 together

i've messed about with adding "countofusers()" to the FROM clause in the procdure but i'm struggling!!! In acesses it something like "JOIN INNER..."
Please can anyone help!!In order to use table functions, assuming you are using SQL 2005 and not SQL 2000 in which case you are stuck and would have to either use cursors or else create an empty column which you update with the count afterwards, you need to join using either CROSS APPLY (like an inner join) or OUTER APPLY (like a left outer join).

E.g.

SELECT forum.ForumID, forum.ID, forum.partID, forum.subject, forum.author, forum.message, forum.responder, forum.dtDate, forum.IpAddress, forum.PicID,
CountofUsers.CountOfID
FROM forum
OUTER APPLY dbo.CountofUsers() CountofUsers
WHERE forum.partID=0
AND CountofUsers.partID = forum.partID
ORDER BY dtDate DESC

I would probably re-write the function to take a parameter for partID for optimal efficiency:

CREATE FUNCTION dbo.CountofUsers() (@.partID int)
RETURNS @.CountOfUsers TABLE (
CountOfID int,
partID int
)
AS
BEGIN
INSERT INTO @.CountOfUsers (
CountOfID
)
SELECT Count(*) AS CountOfID
FROM forum
WHERE forum.partID=(@.partID
RETURN
END

and then use the select like this:

SELECT forum.ForumID, forum.ID, forum.partID, forum.subject, forum.author, forum.message, forum.responder, forum.dtDate, forum.IpAddress, forum.PicID,
CountofUsers.CountOfID
FROM forum
OUTER APPLY dbo.CountofUsers(forum.partID) CountofUsers
WHERE forum.partID=0
ORDER BY dtDate DESC

Hope that helps.

Alternatively, I have found a way to be able to pass table results from one SQL stored procedure up to a caller via XML datatype output parameters. This simulates MARS behaviour with ADO.NET, allowing more than one resultset to be passed up as well. Check Books Online for more info.

Quote:

Originally Posted by Milkstr

I'm a total novice with SQL, so please bare with me.
I'm trying to write a simple forum for my web site, i've done it before using Access but struggling to use the same idea with SQL. In acces i had one table for all the post and replies, i then had several querys that filtered off just the main topics and the number of replies for each thread. I did this by using multiple queries joined together, i've now found out that in SQL you cant call one strored procdure from another, so you have to use functions instead.

I have a stored procdure that filters off just the starter threads, but i want to also show how many replies ther has been for each topic and put then in date order of when they were posted.

this is my main stored procdure

CREATE PROCEDURE forum2 AS
SELECT forum.ForumID, forum.ID, forum.partID, forum.subject, forum.author, forum.message, forum.responder, forum.dtDate, forum.IpAddress, forum.PicID
FROM forum
WHERE forum.partID=0
ORDER BY dtDate DESC

this works ok and shows the main threads, but i also want to list the number of posts for each thread, i've got a function

CREATE FUNCTION dbo.CountofUsers()
RETURNS TABLE
AS RETURN
SELECT Count(*) AS CountOfID, forum.partID
FROM forum
WHERE forum.partID<>0
GROUP BY forum.partID

this should count the number of posts for each thread, but how do i combined the 2 together

i've messed about with adding "countofusers()" to the FROM clause in the procdure but i'm struggling!!! In acesses it something like "JOIN INNER..."
Please can anyone help!!

|||Thanks for you explaination, very usefull, i am indeed using SQL 2000, i i guest i'm a little stuck. I thought what i was doing was quite straight forward but it apppears not to be!
I've tried using a sub query but didn't have much joy.
Can you suggest anything else that will get me going?? i'm only wrting a simple forum. (i dont have to use the table function, this was only a suggestion to me, if there is a better way for the fuction to work i can try it)|||Probably the easiest method is to create a temp table and insert the results of the SELECT statement from your function into it (either directly as a select or from the function or a stored procedure, if you want the piece of code to be reusable). Then perform a join to your previous result on partID to get the count value.

Tuesday, March 20, 2012

Calling DTS from ASP throught ISA Firewall

I am trying to execute a DTS package from our external web site. The package resides on an SQL Server inside the ISA firewall. I can connect to the SQL Server via ADO but everything else is blocked so the following code will not work since I believe it uses Windows Integrated Security:

Dim oPkg
Dim objCID
Dim objSID
Dim objOrderID

Set oPkg = CreateObject("DTS.Package")

Set objCID = oPkg.GlobalVariables.New("@.CID")
Set objSID = oPkg.GlobalVariables.New("@.SID")
Set objOrderID = oPkg.GlobalVariables.New("@.OrderID")

oPkg.LoadFromSQLServer "ServerName", "UserName", "Password", , , , , "MSIOrder"

objCID.Value = "2303"
objSID.Value = "822105614"
objOrderID.Value = "323"
oPkg.GlobalVariables.Add objCID
oPkg.GlobalVariables.Add objSID
oPkg.GlobalVariables.Add objOrderID

oPkg.Execute
Set oPkg = Nothing

Is there a way to call this DTS package via an ADO connection? And of course, to complicate things, I need to pass the DTS package three global variables.

Lastly, modifying the ISA config is not an option unless I can be absolutely sure that it would not alter our current level of security.

Thanks in advance,
JimDTS? Ick. Anyway, are you sure you have the DTS DLLs installed on the client machine (this case the web server)? I believe you need to install Enterprise Manager on the machine for those DLLs to get installed. No separate installation as far as I know.

Also, what error message are you getting?
~Mattsql

Monday, March 19, 2012

Calling a Web Service with data from a SQL Server table one row at a time

Hello

I am trying to call a Web Servie within a SSIS package. I want to pull a row at a time from a SQL Server 2005 table and pass this data values onto the web service.

Any help is highly appreciated!

Kind Regards

Gayatri

I guess it rather depends on what you want to do with the Web Service. Can you give us more details?

You can certainly call a web service row-by-row from a script component in the data flow. This may work for you, but it rather depends on what you want to do once you have called the service.

Donald

|||

Hi Donald

Many thanks for your reply. Web Service is written to accept fields of the records from the table I am reading. I've gone as far as accessing record fields in a script task whlie the records are being read. Where I am lost is assigning those field values to the web service input fields.

My Web service interface is as follows:

Service: PersonWebserviceService

Method: CreatePersonRecord

CreatePersonRecord Method accepts as input a structure to hold fields data values as follows:

(On the input tab)

Name: Key

Type: LegacysystemData

Values: each value in turn have a Name, Type and Value components (eg: Name: ClientNo Type: String and Value). Value is the field I need to access in the script task to assign the record field value I read in)

Your help is much appreciated.

Gayatri

|||

Hi Donald

During the course of the day I managed to Create a web service proxy for my web service and reference it in the script task. But when I run the SSIS Package I get the following error:

Could not load file or assembly 'PersonWebserviceService, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

Hope you could shed some light

many thanks


Gayatri

Calling a web service from RS

Is it possible to call a web service in a reporting services report?
I was thinking along the lines of somehow adding a web reference like you
would for a normal project and then using it in the Code part of the report.
Cheers, Dune.You might be able to create a custom assembly that calls a web service.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Dune" <Dune@.discussions.microsoft.com> wrote in message
news:19CE2430-1EA4-4669-B346-A31230030F9F@.microsoft.com...
> Is it possible to call a web service in a reporting services report?
> I was thinking along the lines of somehow adding a web reference like you
> would for a normal project and then using it in the Code part of the
> report.
> Cheers, Dune.

Calling a Web Service from DTS?

I have a DTS that will cycle though records coming from a FTP file. As it
inserts some of the records into the database, it will need a call a web
service to get photo information for each record it inserts to also write as
a blob to the database.
How/Can I have a DTS call a WS to get additonal data'
JP
.NET Software DeveloperThe first hit should get you started.
http://groups.google.co.uk/groups?h...rver+oj+xmlhttp
-oj
"JP" <JP@.discussions.microsoft.com> wrote in message
news:BFB437D7-D5FB-47B3-AEDD-0272C224B34B@.microsoft.com...
>I have a DTS that will cycle though records coming from a FTP file. As it
> inserts some of the records into the database, it will need a call a web
> service to get photo information for each record it inserts to also write
> as
> a blob to the database.
> How/Can I have a DTS call a WS to get additonal data'
> --
> JP
> .NET Software Developer|||Now you can all a Webservices from SQL (DTS, triggers, SQL Analizar,
Function, Procedure)
Call a WebService from SQL 2000
http://www.rdlcomponents.com/EXSP/default.aspx
Thanks
Jerry
"oj" wrote:

> The first hit should get you started.
> http://groups.google.co.uk/groups?h...rver+oj+xmlhttp
> --
> -oj
>
> "JP" <JP@.discussions.microsoft.com> wrote in message
> news:BFB437D7-D5FB-47B3-AEDD-0272C224B34B@.microsoft.com...
>
>

Calling a Web Service from DTS

Hi,
I created a DTS Package that is running a Java Script:
var xmlHttp=3Dnew ActiveXObject("Microsoft.XMLHT=ADTP")
xmlHttp.open("POST","http://crm01/InstallName/Ch=ADeckContractExpire.asmx/C=
heckCo=ADntracts",false,"DOMAINNAME\USERNAME",=AD"PWD")
xmlHttp.send()
When running the script from the package it runs fine, and processes
the data as expected. When I convert this to a scheduled job, it fails
with the following error:
Executed as user: CRM02\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
OnError: DTSStep_DTSActiveScriptTask_1, Error =3D -2147220482 (800403FE)
Error string: Error Code: 0 Error Source=3D msxml3.dll Error
Description: The download of the specified resource has failed.
Error on Line 9 Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
4500 Error Detail Records: Error: -2147220482 (800403FE);
Provider Error: 0 (0) Error string: Error Code: 0 Error
Source=3D msxml3.dll Error Description: The download of the specified
resource has failed. Error on Line 9 Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 4500 DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_1 DTSRun: Package execution complete.
Process Exit Code 1. The step failed.
Any Suggestions? =20
Thanks=20
BillHi All,
I deleted the step out of the Job, and recreated a new step and
change the type to ActiveX, and selected the JScript. I dropped the
code that I generated from the Packaged and executed the Job with No
Errors. I then scheduled the Job and it works fine. Not sure what's
going on, but I have it working.
Bill

Calling a web service from Custom Code Block

Is it possible to make a web service call from a report's custom code
section? Or is it neccesary to wrtie a custom assembly to do it?
Thanks.It will be necessary to write a custom assembly to do it - unless you
unsecure the permissions of the Report Expressions - which is not a good
idea.
You'll need to learn about Code Access Security to do this properly and our
book's Chapter 9 will give you a great deal of information to get you up and
running with writing Custom Assemblies and Securing them for use in
Reporting Services.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"billd" <billd@.discussions.microsoft.com> wrote in message
news:C7AC6A11-97BC-4648-9130-8A6AA196C488@.microsoft.com...
> Is it possible to make a web service call from a report's custom code
> section? Or is it neccesary to wrtie a custom assembly to do it?
> Thanks.|||Peter,
Doing something similar to Chapter 9 - but getting stuck on what I need to
put into the policy file - I have read Yogesh's link - but still having
problems.
Any help appreciated.
"Peter Blackburn (www.sqlreportingservice" wrote:
> It will be necessary to write a custom assembly to do it - unless you
> unsecure the permissions of the Report Expressions - which is not a good
> idea.
> You'll need to learn about Code Access Security to do this properly and our
> book's Chapter 9 will give you a great deal of information to get you up and
> running with writing Custom Assemblies and Securing them for use in
> Reporting Services.
> Peter Blackburn
> Hitchhiker's Guide to SQL Server 2000 Reporting Services
> http://www.sqlreportingservices.net
>
> "billd" <billd@.discussions.microsoft.com> wrote in message
> news:C7AC6A11-97BC-4648-9130-8A6AA196C488@.microsoft.com...
> > Is it possible to make a web service call from a report's custom code
> > section? Or is it neccesary to wrtie a custom assembly to do it?
> >
> > Thanks.
>
>

Calling a web service from a CLR SQL stored procedure

Hi,
I am trying to call a remote webservice from within SQl server using CLR.
However, SQL Server gives me an error saying that load(), loadModule() etc
are disabled. Of what I can tell, C# needs to dynamically generate and load
the XML serialization for the web service call. Does anyone know how I can
get around this problem?
Platform: SQL Server 2005 + C# (.net 2.0)Please use the beta newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Krishna" <Krishna@.discussions.microsoft.com> wrote in message
news:B5643087-4F83-4EB6-AA7E-12D7CF6C6DEF@.microsoft.com...
> Hi,
> I am trying to call a remote webservice from within SQl server using CLR.
> However, SQL Server gives me an error saying that load(), loadModule() etc
> are disabled. Of what I can tell, C# needs to dynamically generate and
> load
> the XML serialization for the web service call. Does anyone know how I can
> get around this problem?
> Platform: SQL Server 2005 + C# (.net 2.0)

Calling a web service from a CLR SQL stored procedure

Hi,
I am trying to call a remote webservice from within SQl server using CLR.
However, SQL Server gives me an error saying that load(), loadModule() etc
are disabled. Of what I can tell, C# needs to dynamically generate and load
the XML serialization for the web service call. Does anyone know how I can
get around this problem?
Platform: SQL Server 2005 + C# (.net 2.0)Please use the beta newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Krishna" <Krishna@.discussions.microsoft.com> wrote in message
news:B5643087-4F83-4EB6-AA7E-12D7CF6C6DEF@.microsoft.com...
> Hi,
> I am trying to call a remote webservice from within SQl server using CLR.
> However, SQL Server gives me an error saying that load(), loadModule() etc
> are disabled. Of what I can tell, C# needs to dynamically generate and
> load
> the XML serialization for the web service call. Does anyone know how I can
> get around this problem?
> Platform: SQL Server 2005 + C# (.net 2.0)

Calling a web service from a CLR SQL stored procedure

Hi,
I am trying to call a remote webservice from within SQl server using CLR.
However, SQL Server gives me an error saying that load(), loadModule() etc
are disabled. Of what I can tell, C# needs to dynamically generate and load
the XML serialization for the web service call. Does anyone know how I can
get around this problem?
Platform: SQL Server 2005 + C# (.net 2.0)
Please use the beta newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Krishna" <Krishna@.discussions.microsoft.com> wrote in message
news:B5643087-4F83-4EB6-AA7E-12D7CF6C6DEF@.microsoft.com...
> Hi,
> I am trying to call a remote webservice from within SQl server using CLR.
> However, SQL Server gives me an error saying that load(), loadModule() etc
> are disabled. Of what I can tell, C# needs to dynamically generate and
> load
> the XML serialization for the web service call. Does anyone know how I can
> get around this problem?
> Platform: SQL Server 2005 + C# (.net 2.0)

Calling a web page from stored procedure

Hello,
Don't know if this is possible.
I have a website on a shared hosting. I can't put any components on the
server,
but I want to schedule a task to run regularly.
My idea was to schedule a stored procedure to run on a regular basis, that
will call
an ASP or ASP.NET page to perform tasks. Is something like this possible?
Are there alternative routes I may take?
ThanksHi
What is the operation that you would like to perform?
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"A_X_L_X" wrote:

> Hello,
> Don't know if this is possible.
> I have a website on a shared hosting. I can't put any components on the
> server,
> but I want to schedule a task to run regularly.
> My idea was to schedule a stored procedure to run on a regular basis, that
> will call
> an ASP or ASP.NET page to perform tasks. Is something like this possible?
> Are there alternative routes I may take?
> Thanks
>
>|||Chandra,
I'd like to retrieve data from the internet, either in XML or RSS or HTML
format.
Regards
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:54EF875B-18B6-4575-8FFC-A35AF441573F@.microsoft.com...
> Hi
> What is the operation that you would like to perform?
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "A_X_L_X" wrote:
>
that
possible?|||Would this data be stored in your SQL Server (you mentioned it's your
website)? If so, then contact your ISP and ask for the IP address and port
of the server hosting your database. You can connect via DSN using IP as the
server name, and use DTS to export the data.
"A_X_L_X" <aleksl at aol dot com> wrote in message
news:uCBPDV3VFHA.1148@.tk2msftngp13.phx.gbl...
> Chandra,
> I'd like to retrieve data from the internet, either in XML or RSS or HTML
> format.
> Regards
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:54EF875B-18B6-4575-8FFC-A35AF441573F@.microsoft.com...
the
> that
> possible?
>|||Thanks JT, but no, the data may be not in SQL Server, it maybe just
someone's RSS feed, or a WebService, or a web page.
Here's the structure:
Our Server -- Other Web Server
DB, Webserver - Some data
In General, every other platform but Microsoft's has this feature - like a
Java Servlet, that is actually web-server specific, and can be made to run
on a scheduled interval to pull data from the web and insert into the
database.
I've been programming a Windows Scheduler event in the past, when I had
control over the server, but not in this instance. So the only available
"scheduling" mechanizm is SQL Server jobs. Also, it is possible to make an
external machine do this, and then just send data back to generic "XML
gateway" i.e. to program an ASP / ASP.NET page to accept and parse data and
insert into SQL Server. But this is (a) too long and (b) too error-prone.
Has done this in the past also, even with distributed structure (for
fail-safe), still a maintenance nightmare.
This may not be the right newsgroup to ask, and yet I'm interested in
learning the answer.
Regards
Alex
"JT" <someone@.microsoft.com> wrote in message
news:Ob7QVh7VFHA.3572@.TK2MSFTNGP12.phx.gbl...
> Would this data be stored in your SQL Server (you mentioned it's your
> website)? If so, then contact your ISP and ask for the IP address and port
> of the server hosting your database. You can connect via DSN using IP as
the
> server name, and use DTS to export the data.
> "A_X_L_X" <aleksl at aol dot com> wrote in message
> news:uCBPDV3VFHA.1148@.tk2msftngp13.phx.gbl...
HTML
> the
basis,
>

calling a web page from scheduled job

hi all,
how can i call a web page from within a sql server 2000 scheduled job ?
thanks in advance
cosetta
See an example here
http://www.sqlteam.com/forums/topic...00&whichpage=2
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||thank you
your link opens new scenarios in my life!!!!!!
hi
cosetta
"Vaxman" wrote:

> See an example here
> http://www.sqlteam.com/forums/topic...00&whichpage=2
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

calling a web page from scheduled job

hi all,
how can i call a web page from within a sql server 2000 scheduled job ?
thanks in advance
cosettaSee an example here
http://www.sqlteam.com/forums/topic...900&whichpage=2
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||thank you
your link opens new scenarios in my life!!!!!!
hi
cosetta
"Vaxman" wrote:

> See an example here
> http://www.sqlteam.com/forums/topic...900&whichpage=2
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>