Showing posts with label aspx. Show all posts
Showing posts with label aspx. Show all posts

Tuesday, March 27, 2012

calling store procedure from aspx

hi , I would appreicate your help, I have created a store procedure in MS sql server 2000 to be called from aspx page.
The store procedure will have multiple functions:
checking if a file exist in c:\data
if file exist, it will use zip file command line to unzip the file and then run DTS package to upload the database.

The issue I am having is when I run the store procedure from MS query analyzer,I get a confirmation that the whole procedure ran successfully, but when I call the same procedure from aspx, althought the store procedure ran successfully, but I did not get any confirmation to the user in the aspx page to notify the user that the process successed... here is my code in where I think is not working,
CREATE PROCEDURE p_on_demand_dts_sales_option_price_report_3_ftest
@.id int , @.msg_output varchar(28) output
as
declare @.varcmd varchar(255),@.FileExist int
select @.varcmd = null
select @.varcmd ='dir :\Data_on_demand\sales_option_price_report.csv' -- check if file exist
EXEC @.FileExist = master..xp_cmdshell @.varcmd
set @.msg_output = 'The file exists'
Return 44

+++ The above store procedure use command line to check if the csv file exist, its suppose will return a output parameter and a return # BUT it did not. To my surprise, when I modify the above store procedure like this, it work
--
CREATE PROCEDURE p_on_demand_dts_sales_option_price_report_3_ftest
@.id int , @.msg_output varchar(28) output
as
set @.msg_output = 'The file exists'
Return 44

The second procedure does not do anything, except take input parameter and return a message output and return, this work.

since the second procedure works, it means that the code in aspx is correct, but I have no clue why it does not return output parameter and return # in the first procedure.

I would really appreciate anyone who could help.
thanks

ehx5what value are you trying to retuen form the stored proc ? the msg? or the value 44 ?
try :

set @.msg_output = 'The file exists'
return @.msg_output

hth|||What is your code in aspx to catch the output value?|||hi, thanks for your reply, the store procedure should get back to the client the following:
output message
and a return #

The aspx will get those values and present them to the user in label.

hope this will help.
thanks
Al|||then you would need to set up both as output parameters..


CREATE PROCEDURE p_on_demand_dts_sales_option_price_report_3_ftest
@.id int , @.msg_output varchar(28)OUTPUT, @.retid intOUTPUT as

and set the values to both the parameters..


set @.msg_output="the file exists"
set @.retid=44

return @.msg_output
return @.retid

hth

Sunday, March 25, 2012

calling report from asp.net page times out

Hello,
I built an aspx page to call a report from reportserver. The report takes
about 2 minutes to run and I am getting a timeout error from reporting web
services. Here is the error:
System.Net.WebException: The operation has timed-out.
at
System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest
request)
at
System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebReques
t request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
methodName, Object[] parameters)
at Time.ReportServiceWS.ReportingService.Render(String Report, String
Format, String HistoryID, String DeviceInfo, ParameterValue[] Parameters,
DataSourceCredentials[] Credentials, String ShowHideToggle, String&
Encoding, String& MimeType, ParameterValue[]& ParametersUsed, Warning[]&
Warnings, String[]& StreamIds) in c:\inetpub\wwwroot\Time\Web
References\ReportServiceWS\Reference.vb:line 554
at Time.ReportLib.GenerateReport(String rptPath, String rptFormat,
Hashtable param) in c:\inetpub\wwwroot\Time\ReportLib.vb:line 50
The reprotw orks just fine from the report manager and from Report designer.
I am pretty sure it is a config issue somewhere. Does anyone know what
setting I need to change so that report does not timeout?
Thanks,
BivaBiva,
Wnen you instantiate a new RS object in your code, set the time out
property. Use rs.timeout=XXXXX. The should help,
Or, you should look at the IIS timeout param.
-rwiethorn
"Biva" wrote:
> Hello,
> I built an aspx page to call a report from reportserver. The report takes
> about 2 minutes to run and I am getting a timeout error from reporting web
> services. Here is the error:
>
> System.Net.WebException: The operation has timed-out.
> at
> System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest
> request)
> at
> System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebReques
> t request)
> at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
> methodName, Object[] parameters)
> at Time.ReportServiceWS.ReportingService.Render(String Report, String
> Format, String HistoryID, String DeviceInfo, ParameterValue[] Parameters,
> DataSourceCredentials[] Credentials, String ShowHideToggle, String&
> Encoding, String& MimeType, ParameterValue[]& ParametersUsed, Warning[]&
> Warnings, String[]& StreamIds) in c:\inetpub\wwwroot\Time\Web
> References\ReportServiceWS\Reference.vb:line 554
> at Time.ReportLib.GenerateReport(String rptPath, String rptFormat,
> Hashtable param) in c:\inetpub\wwwroot\Time\ReportLib.vb:line 50
> The reprotw orks just fine from the report manager and from Report designer.
> I am pretty sure it is a config issue somewhere. Does anyone know what
> setting I need to change so that report does not timeout?
> Thanks,
> Biva
>
>