EXECUTE AS question: report

Showing posts with label report. Show all posts

Monday, March 26, 2012

Executing an update command after subscription

I need to execute an update stored procedure once the subscription report
has run.
The only way I can find to do this is to add an extra TSQL step into the SQL
Agent job through SQL Management Studio. But whenever the subscription is
modified via report manager, the newly added TSQL step is deleted.
Is there a way to get around this?
Cheers
GrantHello Grant,
My suggestion is create a new SQL Job and in this job, you could first
execute the job for the subscription and then you could add your extra
taskes. The schedule of this new job could be configured as you wish.
Once you create a subsciption in the report manager, the sql server agent
will create a job which have a GUID.
You could run the following TSQL statement to run the subscription job:
exec msdb..sp_start_job @.job_name='<GUID of the subscription job>'
Hope my suggestion will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
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.)

Wednesday, March 7, 2012

Execute report and go directly to a save file option

We have a report that is basically a data file. It can have 2 rows or it can
have 100,000 rows. We are running into response time issues, memory issues,
etc. I know that RS is not the preferred method to deliver these sorts of
large data files. We need to do anything possible to satify the user at this
point and the amount of time it's currently taking to run this report with a
large amount of data, even when the report runs successfully, is unacceptable
to them. But, until we have time to re-design, is there any way to have the
report always prompt to save the file instead of waiting for the default HTML
format and then exporting to CSV? Any other short-term solutions?
StephanieFrom your own web page it is easy. Using URL integration you can specify the
format. Even if you are using Report Manager you could have a report that
has the appropriate parameters, they open it up and all you have in the
report is a link that says Export Data. Use the Jump to URL action (I also
make the link blue and underlined).
One other thing, perhaps you have seen this already.If you do this you will
want to change the CSV to use ASCII instead of unicode. Excel puts unicode
into a single column. To have RS export CSV in ASCII format you have to make
a change to a config file.
You only need to change in one place, rsreportserver.config. Reboot after
the change. The below shows commenting out the existing entry and putting in
the needed change to have CSV export as ASCII

<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:10819562-B6BC-4535-BE4A-4B5AA6BD2B8A@.microsoft.com...
> We have a report that is basically a data file. It can have 2 rows or it
> can
> have 100,000 rows. We are running into response time issues, memory
> issues,
> etc. I know that RS is not the preferred method to deliver these sorts of
> large data files. We need to do anything possible to satify the user at
> this
> point and the amount of time it's currently taking to run this report with
> a
> large amount of data, even when the report runs successfully, is
> unacceptable
> to them. But, until we have time to re-design, is there any way to have
> the
> report always prompt to save the file instead of waiting for the default
> HTML
> format and then exporting to CSV? Any other short-term solutions?
> Stephanie

execute RDL dynamically...

I know you can create RDL programatically...but is it possible to execute
the RDL on a report server without having to publish it to the server? I
want to be able to generate some RDL on the fly and execute it on the report
server and display the report to a user. Thanks for any help!!
Sunish AbrahamNo, you cannot (with RS 2000). VS 2005 (controls ship with VS 2005 and work
with RS 2005) will have a web and winform control that can run in local
mode, you give it a dataset and the report and away it goes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sunish Abraham" <sunisha@.comcast.net> wrote in message
news:eTf3Ye7sFHA.904@.tk2msftngp13.phx.gbl...
>I know you can create RDL programatically...but is it possible to execute
>the RDL on a report server without having to publish it to the server? I
>want to be able to generate some RDL on the fly and execute it on the
>report server and display the report to a user. Thanks for any help!!
> Sunish Abraham
>|||Hi Bruce!
Thanks for the quick response! Is what you are saying is that you can
generate RDL and a dataset and execute the report using the dataset without
having to "publish" the report to the reporting server? Is this control
part of the SQL2005 RS samples (if so what is the name of the control)?
Thanks again!!
Sunish
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23JJWAx7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
> No, you cannot (with RS 2000). VS 2005 (controls ship with VS 2005 and
> work with RS 2005) will have a web and winform control that can run in
> local mode, you give it a dataset and the report and away it goes.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Sunish Abraham" <sunisha@.comcast.net> wrote in message
> news:eTf3Ye7sFHA.904@.tk2msftngp13.phx.gbl...
>>I know you can create RDL programatically...but is it possible to execute
>>the RDL on a report server without having to publish it to the server? I
>>want to be able to generate some RDL on the fly and execute it on the
>>report server and display the report to a user. Thanks for any help!!
>> Sunish Abraham
>>
>|||It is part of Widbey. They were there with the last beta but I am not sure
on whether or not there were any problems with it. Anyway, these controls do
not ship with RS, only with VS.
In local mode you don't even have to have a server around. The controls work
with a RS server if you have one but you also have the option of using the
controls in local mode.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sunish Abraham" <sunisha@.comcast.net> wrote in message
news:ODG8nv9sFHA.3460@.TK2MSFTNGP10.phx.gbl...
> Hi Bruce!
> Thanks for the quick response! Is what you are saying is that you can
> generate RDL and a dataset and execute the report using the dataset
> without having to "publish" the report to the reporting server? Is this
> control part of the SQL2005 RS samples (if so what is the name of the
> control)? Thanks again!!
> Sunish
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23JJWAx7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
>> No, you cannot (with RS 2000). VS 2005 (controls ship with VS 2005 and
>> work with RS 2005) will have a web and winform control that can run in
>> local mode, you give it a dataset and the report and away it goes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Sunish Abraham" <sunisha@.comcast.net> wrote in message
>> news:eTf3Ye7sFHA.904@.tk2msftngp13.phx.gbl...
>>I know you can create RDL programatically...but is it possible to execute
>>the RDL on a report server without having to publish it to the server? I
>>want to be able to generate some RDL on the fly and execute it on the
>>report server and display the report to a user. Thanks for any help!!
>> Sunish Abraham
>>
>>
>|||I see that there is a ReportViewer subdirectory under the VS.NET install
directory which contains compiled files (Microsoft.ReportViewer.Common.dll,
Microsoft.ReportViewer.WebForms.dll,
Microsoft.ReportViewer.WinForms.dll)...are these the controls that you are
referencing? Is there documentation on how to use these controls? Thanks!
Sunish
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uYFFMy9sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> It is part of Widbey. They were there with the last beta but I am not sure
> on whether or not there were any problems with it. Anyway, these controls
> do not ship with RS, only with VS.
> In local mode you don't even have to have a server around. The controls
> work with a RS server if you have one but you also have the option of
> using the controls in local mode.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Sunish Abraham" <sunisha@.comcast.net> wrote in message
> news:ODG8nv9sFHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi Bruce!
>> Thanks for the quick response! Is what you are saying is that you can
>> generate RDL and a dataset and execute the report using the dataset
>> without having to "publish" the report to the reporting server? Is this
>> control part of the SQL2005 RS samples (if so what is the name of the
>> control)? Thanks again!!
>> Sunish
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23JJWAx7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
>> No, you cannot (with RS 2000). VS 2005 (controls ship with VS 2005 and
>> work with RS 2005) will have a web and winform control that can run in
>> local mode, you give it a dataset and the report and away it goes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Sunish Abraham" <sunisha@.comcast.net> wrote in message
>> news:eTf3Ye7sFHA.904@.tk2msftngp13.phx.gbl...
>>I know you can create RDL programatically...but is it possible to
>>execute the RDL on a report server without having to publish it to the
>>server? I want to be able to generate some RDL on the fly and execute
>>it on the report server and display the report to a user. Thanks for
>>any help!!
>> Sunish Abraham
>>
>>
>>
>|||Sorry, I don't know much more that what I have told you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sunish Abraham" <sunisha@.comcast.net> wrote in message
news:OtOVKJ%23sFHA.664@.tk2msftngp13.phx.gbl...
>I see that there is a ReportViewer subdirectory under the VS.NET install
>directory which contains compiled files (Microsoft.ReportViewer.Common.dll,
>Microsoft.ReportViewer.WebForms.dll,
>Microsoft.ReportViewer.WinForms.dll)...are these the controls that you are
>referencing? Is there documentation on how to use these controls? Thanks!
> Sunish
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uYFFMy9sFHA.1568@.TK2MSFTNGP10.phx.gbl...
>> It is part of Widbey. They were there with the last beta but I am not
>> sure on whether or not there were any problems with it. Anyway, these
>> controls do not ship with RS, only with VS.
>> In local mode you don't even have to have a server around. The controls
>> work with a RS server if you have one but you also have the option of
>> using the controls in local mode.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Sunish Abraham" <sunisha@.comcast.net> wrote in message
>> news:ODG8nv9sFHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi Bruce!
>> Thanks for the quick response! Is what you are saying is that you can
>> generate RDL and a dataset and execute the report using the dataset
>> without having to "publish" the report to the reporting server? Is this
>> control part of the SQL2005 RS samples (if so what is the name of the
>> control)? Thanks again!!
>> Sunish
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23JJWAx7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
>> No, you cannot (with RS 2000). VS 2005 (controls ship with VS 2005 and
>> work with RS 2005) will have a web and winform control that can run in
>> local mode, you give it a dataset and the report and away it goes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Sunish Abraham" <sunisha@.comcast.net> wrote in message
>> news:eTf3Ye7sFHA.904@.tk2msftngp13.phx.gbl...
>>I know you can create RDL programatically...but is it possible to
>>execute the RDL on a report server without having to publish it to the
>>server? I want to be able to generate some RDL on the fly and execute
>>it on the report server and display the report to a user. Thanks for
>>any help!!
>> Sunish Abraham
>>
>>
>>
>>
>

execute query for each row

I want to execute a query for each row in a report since a parameter in that query relies on a field in the row. Is there a way to do this?I used a subreport to do this.

Friday, February 24, 2012

EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', sc

I'm trying to create a new subscriptions on an existing report and get the following error.

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

Get Online Help

EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.

I ran the following that was suggested in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17774&SiteID=1. But still I get the same error. Do I need a reboot or restart of the services?

The only log file information I can find contains the following.

System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: An internal error occurred on the report server. See the error log for more details. > Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. > System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
End of inner exception stack trace
at Microsoft.ReportingServices.WebServer.ReportingService2005.ListSchedules(Schedule[]& Schedules)

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at Microsoft.SqlServer.ReportingServices2005.ReportingService2005.ListSchedules()

at Microsoft.SqlServer.ReportingServices2005.RSConnection.ListSchedules()

at Microsoft.ReportingServices.UI.SharedScheduleDropDown.EnsureSchedulesAreLoaded()

at Microsoft.ReportingServices.UI.SharedScheduleDropDown.SharedScheduleDropDown_Load(Object sender, EventArgs e)

at System.Web.UI.Control.OnLoad(EventArgs e)

at System.Web.UI.Control.LoadRecursive()

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
aspnet_wp!ui!1!17/10/2006-08:44:26:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
aspnet_wp!extensionfactory!e!17/10/2006-09:35:13:: w WARN: The extension Report Server Email does not have a LocalizedNameAttribute.
aspnet_wp!extensionfactory!e!17/10/2006-09:35:13:: w WARN: The extension Report Server FileShare does not have a LocalizedNameAttribute.
aspnet_wp!ui!e!17/10/2006-09:35:13:: e ERROR: System.Web.Services.Protocols.SoapException: An internal error occurred on the report server. See the error log for more details. > Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. > System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
End of inner exception stack trace
at Microsoft.ReportingServices.WebServer.ReportingService2005.ListSchedules(Schedule[]& Schedules)
aspnet_wp!ui!e!17/10/2006-09:35:13:: e ERROR: HTTP status code --> 200

I cannot find any other error log.

Can anybody help?

Sorry for the late reply. Try this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=662319&SiteID=1

EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource'