Showing posts with label aspnet. Show all posts
Showing posts with label aspnet. Show all posts

Sunday, March 25, 2012

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

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

Tuesday, March 20, 2012

Calling DTS via ASP.NET works locally but not when published to intranet

I have a DTS package that I am calling in my code-behind (vb) of one of my asp.net pages on my intranet. The package takes a FoxPro table that is on our network and inserts it into a SQL Server table in one of our databases. The package gets called and executed properly when I am working on it in development on my local workstation, but when I publish it to our intranet, which is in the same domain and behind the same firewall, the package does not execute. No error shows up on the published page, but also nothing gets executed. Has anyone encountered this? Here is my code, which is pretty straightforward:

oPkg = Server.CreateObject(

"DTS.Package")
oPkg.LoadFromSQLServer("[servername]","sa","[password]", DTSSQLStgFlag_Default,"","","","Invoice Import")
oPkg.Execute()

Any help would be appreciated. Thanks.

The problem is you are using the wrong user to run the DTS package because when Asp.net is running the package it runs under the Agent so the better security model is to clone a valid Admin level Windows and SQL Server account to run the package. I have covered it in the thread below, if you run into problem try again it will work. Hope this helps.

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

|||

Isn't asp.net running the package when I am going through my browser on localhost? Could you elaborate on how asp.net would use sa effectively when I'm using a browser in VS and it wouldn't when I publish? That doesn't make much sense.

|||

I have explained in detail in the thread I posted earlier it is not SA(system Admin) related you need an account with Network permissions for the SQL Server Agent service which is reponsible for automations in SQL Server. You could be using browser in VS but this is something only SQL Server control and I have wondered why it is barely documented but running DTS in web applications was created by SQL Server users not Microsoft so documentation is not comprehensive. What I know is if you don't give the Agent the right permissions it will not work.

So you need a domain account with Admin level permissions create Admin level SQL Server permissions then use that account to create a proxy account for SQL Server Agent to run your package with xp_cmdshell. You are not the first to ask this question and will not be the last but read that thread do everything correctly and it will work. Hope this helps.

Sunday, March 11, 2012

Calling a DTS Stored Procedure from ASP.NET

I have a stored procedure that calls a DTS package to grab a text file that has been uploaded to the server and merge it with a table on the database. The DTS package works woderfully in SQL, as does the the file upload. The problem arrises when I create a stored procedure to run the DTS package. I know that you have to shell out and do a command line on the SQL server (and I think that I got the syntax correct) but its calling the Stored Procedure in the ASP.NET app that is causing me hardship. Here is the code that I have so far:

Stored Procedure:

CREATE PROCEDURE spSampleData AS exec master..xp_cmdshell 'dtsrun /SZEUS\sqlServer113 /NdtsPackage /UuserID /Ppassword'
GO

VB to run DTS:

Dim myCommand As SqlCommand
myCommand.CommandType = CommandType.StoredProcedure
myCommand.CommandText = "spSampleData"
myCommand.ExecuteNonQuery()

I'm not sure what I am doing wrong but any help would be great.

Thanks!Your code seems ok on the face of it. What is the error message that is generated? Or does just nothing happen?

These links might be helpful:

DTS Cookbook for .Net

Execute a package from T-SQL This one describes your current method but offers another alternative too.|||Thank you so much for the links - I'll check them out. So here are the errors...

When running in Query Analyzer:

-"DTSRun: Cannot create COM Server to load and execute DTS Package. Error -2147024769 (8007007F): The specified procedure could not be found. "

I thought this was because some of the DLLs weren't registered but that isn't it.

-"I tried to schedule it as a job, before I got this just to see, and it gave me a a long hex dec error that was in a windows alert box, so I didn't even worry with that. But it was:"

The procedure entry point ?SQLUIUpdateRegistryFromResource could not be located in the dynamic link library SQLRESLD.dll

In the Webform, it was your typical:

"Object reference not set to an instance of an object" error... So I'm at a loss.|||I hate those sorts of problems. I Googled the error message and ended up with this link.

Wednesday, March 7, 2012

call SQL job in ASP.NET application

is it possible to call a SQL job from an ASP.NET application? if so, how? I'm using VS.net2003 and am programming in C#

I've been searching for a while, but haven't found anything. It doesn't help that one of my keywords when searching is "job(s)."

Look at the stored procedure sp_start_job in SQL Server Books online.

call custom SQL function in ASP.NET

I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function

SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_lengthYou may try to use the full name of that function, like dbo.GetHeatEmission().