Showing posts with label service. Show all posts
Showing posts with label service. Show all posts

Tuesday, March 27, 2012

Calling SSIS packages from a Service Broker Queue

I would like to call an SSIS package from a Service Broker Queue.

There is one way that I am aware of -

Using xp_cmdshell from within an activation stored procedure and using DTEXEC.

Is there a more elegant way of executing an SSIS package from within SSB?

Also, I am not interested in writing a .NET external activator to process my messages in the queue. I would like this operation to be strictly database oriented. Having said this, I am also trying to avoid triggers processing the messages in the queue.

Thank you!

SQLCLR with unsafe assembly (in order to call Process.Start()) would be slightly better option than xp_cmdshell. Even then, starting external processes from an activated stored procedure is limiting and dangerous. Limiting because you can only start the external application as the service account running sqlservr.exe. Dangerous because if the process fails to start (eg> out of memory), you may orphan messages in the queue since there will be no process to process them.

You might want to look at the External Activator sample (linked from www.sqlservicebroker.com) and adapt it or build something similar.

I don't know what you mean by "triggers processing the messages in the queue". SQL Server 2005 has no triggers for queues.

Rushi

|||

We use this mechanism to farm out data warehouse ETL mechanism and it works fairly well.

Once you start up SSIS, though, your package should be the one that reaches back into the DB and pulls messages off the queue. Make sure to put your transaction logic, error handling, etc. in the SSIS control flow, taking into account what should be done with messages in the context of your package.

There's a ton of SSIS overhead (at least compared with pulling a message off a queue), so if you're getting a fair number of messages, you don't want to start up the engine each time you want to process a message. We opted to call out to dtexec rather than have a separate service running, although I think that's a matter of preference (and your level of comfort/concern of running external processes from SQL vs. having to install/manage other services).

|||

Couldn't you just create a SQLCLR stored procedure to execute the package directly & with full transactional support? I.e. Have an activated stored proc, authored in C#, executing the SSIS package without doing any "shelling out".

Something like the link below implemented as a SQLCLR proc?

http://www.codeproject.com/useritems/CallSSISFromCSharp.asp?df=100&forumid=309846&exp=0&select=1519306

Calling SSIS packages from a Service Broker Queue

I would like to call an SSIS package from a Service Broker Queue.

There is one way that I am aware of -

Using xp_cmdshell from within an activation stored procedure and using DTEXEC.

Is there a more elegant way of executing an SSIS package from within SSB?

Also, I am not interested in writing a .NET external activator to process my messages in the queue. I would like this operation to be strictly database oriented. Having said this, I am also trying to avoid triggers processing the messages in the queue.

Thank you!

SQLCLR with unsafe assembly (in order to call Process.Start()) would be slightly better option than xp_cmdshell. Even then, starting external processes from an activated stored procedure is limiting and dangerous. Limiting because you can only start the external application as the service account running sqlservr.exe. Dangerous because if the process fails to start (eg> out of memory), you may orphan messages in the queue since there will be no process to process them.

You might want to look at the External Activator sample (linked from www.sqlservicebroker.com) and adapt it or build something similar.

I don't know what you mean by "triggers processing the messages in the queue". SQL Server 2005 has no triggers for queues.

Rushi

|||

We use this mechanism to farm out data warehouse ETL mechanism and it works fairly well.

Once you start up SSIS, though, your package should be the one that reaches back into the DB and pulls messages off the queue. Make sure to put your transaction logic, error handling, etc. in the SSIS control flow, taking into account what should be done with messages in the context of your package.

There's a ton of SSIS overhead (at least compared with pulling a message off a queue), so if you're getting a fair number of messages, you don't want to start up the engine each time you want to process a message. We opted to call out to dtexec rather than have a separate service running, although I think that's a matter of preference (and your level of comfort/concern of running external processes from SQL vs. having to install/manage other services).

|||

Couldn't you just create a SQLCLR stored procedure to execute the package directly & with full transactional support? I.e. Have an activated stored proc, authored in C#, executing the SSIS package without doing any "shelling out".

Something like the link below implemented as a SQLCLR proc?

http://www.codeproject.com/useritems/CallSSISFromCSharp.asp?df=100&forumid=309846&exp=0&select=1519306

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

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
>

Tuesday, March 20, 2012

Calling an SSIS package from a Service Broker Queue

Posted the same message to the Service Broker forum. But the board is neither responsive nor active. So I am trying this forum. Hope someone can help.

I would like to call an SSIS package from a Service Broker Queue.

There is one way that I am aware of -

Using xp_cmdshell from within an activation stored procedure and using DTEXEC.

Is there a more elegant way of executing an SSIS package from within SSB?

Also, I am not interested in writing a .NET external activator to process my messages in the queue. I would like this operation to be strictly database oriented. Having said this, I am also trying to avoid triggers processing the messages in the queue.

Thank you!You can schedule an SSIS package in SQL Server Agent, and then you can kick off that job via a system stored procedure.|||Not the solution I was looking for. I am aware of using sp_start_job system sp to do the job. There's gotto be a more elegant way to kick off an SSIS package from within a Service Broker Activation process.
|||

yosonu wrote:

...There's gotto be a more elegant way...

It's better than xm_cmdshell... And why must there be a more elegant way?

What do you envision as the "best" way to do this if in a perfect world?|||It hardly makes any sense for me to store SSIS packages as disabled jobs on the SQL Server Agent level.

But I do agree that this solution is still better than using xp_cmdshell for security reasons.

Thanks
sql

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)

Sunday, March 11, 2012

Calling a custom web service from my package to perform heavy-duty tasks

As part of my SSIS package, a list of sites is created that need to be created on a remote machine. let's say 1000 sites. I need to pass this list to a web service so web service sitting on that machine creates these sites for me. MY SSIS package does not run frequently so I can sacrifice time a little bit to get better functionality.

I need to move the sites that are not created (for any reasons) by web service to another table and successfully created sites to another table, so I need to get confirmation for each site from the web service.

Which option is better?

1) Calling web service for every single record (site) and get the confirmation and then based on the confirmation I move the records accordingly. I know this might be very time consuming, but as I said my SSIS package might only run every six months

OR

2) Sending records to a web service in a batch and get the result. I don’t know how to do this though.

Any help or better solution is appreciated.

If the web service is already created, you might be stuck with whatever it supports. Often, web services have either a single record interface or a batch interface, but not both. If you have control over how it is implemented, then either option will work.

If the web sevice is capable of handling a batch, you just need to make sure your message conforms to what the web service expects. That could be by concatenating a large string together.

|||

Web Service is not implemented yet.Actually I have to implement the web service.Any clues on how to implement batch web services?

Thanks

|||

The message that the web service accepts needs to contain multiple items, and handle processing them in a batch.

For example, your message payload might contain:

<Websites>
<Website>A</Website>
<Website>B</Website>
<Website>C</Website>
</Websites>

Thursday, March 8, 2012

calling a C# assembly from SQL Server 2005

Hi,

I have got a simple C# assembly which call a ASP.net web service and executes a webmethod, now i want to call this assembly from sql server 2005 within the sql server function. can any body let me know how to do it.

Thanks

I suggest you get a copy of "Professional SQL Server 2005 CLR Programming" published by WROX - ISBN 0-470-05403-4. You should be able to read a copy on Safari.

The rules on what may and may not be called are lengthy. It would have to be used with a permission set of EXTERNAL_ACCESS as it access the network. Not all DBA's will allow this.

Have you rigoursly unit tested the application? Do you tests reach at least 80-90% code coverage?

Depending on how exactly you wrote your application, you might have to rewrite some of it. For example the SQL access methods are in a different namespace within the SQL CLR.

Called Web Services from a stored prcedure

Hello,
Does anyone know if it is possible to call a web service from a stored
procedure? Failing that is it possible to use exterbal object? I'm thinking
along the lines of:
@.obj = CreateObject("xyzzy.f")
@.obj.j = 1
call @.obj
Is this wishful thinking ?
Many thanks...
MartinI'm certain there are multiple methods of doing exactly what you ask, and
each method will impact the performance and reliability of your database to
one degree or another. The best option would be to make the procedure call
and web service call in tandem from the application or a DTS package.
"Martin Waller" <martinwaller@.beeb.net> wrote in message
news:eDWb629MGHA.1716@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Does anyone know if it is possible to call a web service from a stored
> procedure? Failing that is it possible to use exterbal object? I'm
> thinking along the lines of:
> @.obj = CreateObject("xyzzy.f")
> @.obj.j = 1
> call @.obj
> Is this wishful thinking ?
> Many thanks...
> Martin
>|||Thanks for the reply. Performance is not an issue for me. I need to unit
test some web services that directly effect database tables and I was
thinking of a simple .SQL file that does something like:
set-up precondition
call web service
test data is in the expected state
exit
Then I could log the output to a file and, once happy, use it to compare
against in further tests as and when the web serices are tested.
Martin
"JT" <someone@.microsoft.com> wrote in message
news:O$O3O$9MGHA.3932@.TK2MSFTNGP09.phx.gbl...
> I'm certain there are multiple methods of doing exactly what you ask, and
> each method will impact the performance and reliability of your database
> to one degree or another. The best option would be to make the procedure
> call and web service call in tandem from the application or a DTS package.
> "Martin Waller" <martinwaller@.beeb.net> wrote in message
> news:eDWb629MGHA.1716@.TK2MSFTNGP10.phx.gbl...
>|||i recommend you use a sql statement to call a dts, (plenty of examples how t
o
do this). Let the dts call the web service and log whatever it needs to log

Call webservice from trigger/stored proc

Is it possible to call an external web service from a SQL Server trigger or stored procedure?You can sort of call a web service from a stored procedure, but it takes a lot of bubble gum and bailing wire to make it work. Can you describe what you want in a bit more detail, and one of us might have an idea or three that might help.

-PatP|||Hi
I want to call the web services which is developed in .net from sqlserver 2000.So please do help me.|||Here is a sample code (without error handling):

declare @.objRef int
,@.resultcode int
,@.responseText varchar(8000)

EXEC @.resultcode = sp_OACreate 'MSXML2.ServerXMLHTTP', @.objRef OUT,4
EXEC @.resultcode = sp_OAMethod @.objRef, 'Open', NULL,'GET','http://myurl.com', False
EXEC @.resultcode = sp_OAMethod @.objRef, 'Send',null
EXEC @.resultcode = sp_OAGetProperty @.objRef, 'responseText',@.responseText output

print(@.response)

EXEC sp_OADestroy @.objRef

Call Web Services from Sql Notification services

Hello -

Can someone please let me know if we can call a Web Service from Sql Notification services. If yes can a sample code be provided.

I figured out the way to call a web service from NTS. Basically we need to implement iHttpProvider interface to call a web method.