Calling normal stored procedures from Extended Procedures

Hi

I want to call a database stored procedure from an extended procedure. Can some one provide snippet of code (either in C or C++) to achieve the same ?

Thanks & Regards
ImtiazYou have to make connection to server using ODBC or OLEDB to do this. Please take a look at the call escape sequence topic in Books Online. The url is mk:@.MSITStore:D:\Program%20Files\Microsoft%20SQL%20Server%20Books%20Online\1033\odbcsql.chm::/od_6_035_5dnp.htm. You may have to change the path depending on your installation. You can also find samples in the SQL Server CD.

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 .net assembly in DTS package

Hi everyone,

I want to call the .net assembly(DLL) in a DTS package. Can anyone help me as to how to achieve this. I read numerous articles on internet, but couldn't find the one that can help me with this problem.

Any help or directing me to an article will be greatly appreciated.

Thanks.

Vinki

Edit

You need full edition like the developer edition because you need to use SQL Server Agent to run xp_cmdshell to run it within your application. Try the thread below for all you need do to get it to work. It works post again if you still have questions. Wrong link originally posted now corrected. Hope this helps.

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

|||

The article that you told me about is discussing something about nullable values. I was more looking for some active x script that I can call in my DTS package that in turns call my .net dll. I am using sql server 2000 ( full professionsal version) and visual studio.net ( full professional version 2003) .

Thanks.

|||

My apologies I gave you the wrong link and trust me and we will get you up and running.

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

|||

Hi,

I am not sure if we are talking about same thing. I have a dll from visual studio.net project and I want this dll to be called in my DTS. The link that you told me about, that person is trying to call the DTS package in .net project. Please advise.

Thanks.

|||

DTS(data transformation service) in the data business is a basic ETL(extraction transformation and loading) tool we have created other uses for it like calling it from web application and going anywhere on the enterprise to move data but your dll must be some form of data. So I need to know the content of your dll before I can tell you other options. If you are in the full version of SQL Server 2005 you have a full ETL tool SSIS (sql server integration service) that can do most things expensive ETL tools do. There is a site dedicated for it check it out in the link below.

http://www.sqlis.com/?15

|||

Hi,

My dll is justcreating a simple xml file and calling a web service to send the xml file to that web service. I get a response from the web service to show whether the xml file is accepted or not. I am using sql server 2000. I don't have sql server 2005 version. I looked at one article and it explains about calling the dll in dts package, but I am having lot of problems creating the strong name. Below is the link to the article.

http://www.c-sharpcorner.com/UploadFile/frankalonzo/NetComSqlJob09122005042555AM/NetComSqlJob.aspx?ArticleID=ee58ac62-a9fd-4957-95a6-8586c58a4dd3

When I am following this article, I am getting this error

Assembly generation failed -- Referenced assembly 'Microsoft.ApplicationBlocks.Data' does not have a strong name. Although I created the strong name for my project. I also tried creating the strong name for 'Microsoft.ApplicationBlocks.Data' .

Thanks.

|||You see that the article is not rated because when you get your dll into DTS you will still do all that was covered in my links because Jobs operate under SQL Server Agent context and must have admin or proxy to admin account to run as explained in my links but he did not cover it because until 2005 that was not even covered in the docs. So if you can get you dll into DTS then post again so I can help you call it in your application. Hope this helps.|||

Thanks Caddre. I will let you know, I will work on this today.

|||You know I did not tell that if you get it working it will run as a job if you run the job manually because it is running in the context of you permission but in Asp.net it must run in the context of SQL Server Agent, the automation part of SQL Server.