I have a parameter that is query based. I have created my own UI to list out
the text boxes per parameter required by the report. Now comes the problem of
a query based parameter. How to I get the queary based valid values to
populate my drop down list boxEddie,
If I understand your correctly I may have some ideas that will help you with
query based Parameters, but first please clarify the reason you built a UI
for users to pass parameters into the report?
thx.
"Eddie" wrote:
> I have a parameter that is query based. I have created my own UI to list out
> the text boxes per parameter required by the report. Now comes the problem of
> a query based parameter. How to I get the queary based valid values to
> populate my drop down list box|||I have several clients that use browsers which the report viewer doesn't
behave well.
I did figure it out. The valid values come back as array when you call the
GetReportParameter method and set the "ForRendering" to TRUE.
So all I did was add the values to ListItems and populated a drop down.
Also, a nifty thing was to find all the Date type parameters and lock the
text box and provide a calendar. This allowed me to use our application's
Localization methods to handle all the different Date formats.
"RS_CZAR" wrote:
> Eddie,
> If I understand your correctly I may have some ideas that will help you with
> query based Parameters, but first please clarify the reason you built a UI
> for users to pass parameters into the report?
> thx.
> "Eddie" wrote:
> > I have a parameter that is query based. I have created my own UI to list out
> > the text boxes per parameter required by the report. Now comes the problem of
> > a query based parameter. How to I get the queary based valid values to
> > populate my drop down list box
Friday, March 30, 2012
Reporting Services using NTLM Authentication
I need to have MSSRS send emails to external email addresses. Our Exchange
Server requires email to be authenticated. I am using SMTPAuthenticate value
of 2. My MSSRS server is in a different domain than my Exchange Mail server.
Currently I am testing before deployment into the field and my setup is
mirroring what the corporate desired setup is.
I receive the following error when I attempt to send a report to an external
email:
Failure sending mail: The message could not be sent to the SMTP server. The
transport error code was 0x80040217. The server response was not available
When I sniff the traffic during the report serving, my local Admin login
account is being authenticated by the Exchange Server in the mailer domain.
What type of setup or config changes can be made for the proper hand shake to
work? I must send authenticated email externally to our customers, but the
MSSRS Server and Exchange Servers will be in different domains.
Thanks,
RobertGood morning!
Did you ever find the solution to your question below? If so, what did you
do to resolve it?
Thank you.
"gilliatt via SQLMonster.com" wrote:
> I need to have MSSRS send emails to external email addresses. Our Exchange
> Server requires email to be authenticated. I am using SMTPAuthenticate value
> of 2. My MSSRS server is in a different domain than my Exchange Mail server.
> Currently I am testing before deployment into the field and my setup is
> mirroring what the corporate desired setup is.
> I receive the following error when I attempt to send a report to an external
> email:
> Failure sending mail: The message could not be sent to the SMTP server. The
> transport error code was 0x80040217. The server response was not available
> When I sniff the traffic during the report serving, my local Admin login
> account is being authenticated by the Exchange Server in the mailer domain.
> What type of setup or config changes can be made for the proper hand shake to
> work? I must send authenticated email externally to our customers, but the
> MSSRS Server and Exchange Servers will be in different domains.
> Thanks,
> Robert
>sql
Server requires email to be authenticated. I am using SMTPAuthenticate value
of 2. My MSSRS server is in a different domain than my Exchange Mail server.
Currently I am testing before deployment into the field and my setup is
mirroring what the corporate desired setup is.
I receive the following error when I attempt to send a report to an external
email:
Failure sending mail: The message could not be sent to the SMTP server. The
transport error code was 0x80040217. The server response was not available
When I sniff the traffic during the report serving, my local Admin login
account is being authenticated by the Exchange Server in the mailer domain.
What type of setup or config changes can be made for the proper hand shake to
work? I must send authenticated email externally to our customers, but the
MSSRS Server and Exchange Servers will be in different domains.
Thanks,
RobertGood morning!
Did you ever find the solution to your question below? If so, what did you
do to resolve it?
Thank you.
"gilliatt via SQLMonster.com" wrote:
> I need to have MSSRS send emails to external email addresses. Our Exchange
> Server requires email to be authenticated. I am using SMTPAuthenticate value
> of 2. My MSSRS server is in a different domain than my Exchange Mail server.
> Currently I am testing before deployment into the field and my setup is
> mirroring what the corporate desired setup is.
> I receive the following error when I attempt to send a report to an external
> email:
> Failure sending mail: The message could not be sent to the SMTP server. The
> transport error code was 0x80040217. The server response was not available
> When I sniff the traffic during the report serving, my local Admin login
> account is being authenticated by the Exchange Server in the mailer domain.
> What type of setup or config changes can be made for the proper hand shake to
> work? I must send authenticated email externally to our customers, but the
> MSSRS Server and Exchange Servers will be in different domains.
> Thanks,
> Robert
>sql
Reporting Services using IBM DB2 Data Provider for .NET framework 2.0
Hello, I have been testing the IBM DB2 .NET data provider the last couple of months with the intention too use it with Reporting Services. I am using the IBM DB2 Data Provider for .NET framework 2.0. Everything seems too work fine until I started too test the Cashing and Scheduling of reports in Reporting Services.
Too be able too cash or too schedule reports in Reporting Services you have too set the "Credientials stored securely in the report server". The problem is when I do this with the Data Connection using the IBM .Net Provider I get an error Message:
"The data source 'XXXX' uses a managed data provider which does not implement IDbConnectionExtension. Only Windows Integrated Security or No Credentials are supported."
Is it possible too write a wrapper around the IBM dataprovider too implement IdbConnectionExtension and thereby extend the SSRS security model or is this something IBM have too do in the provider.
You can implement the wrapper yourself. Use this documentation to get started: http://msdn2.microsoft.com/en-us/library/ms154655(SQL.90).aspx
-- Robert
|||Ok, thanks. I will look into this in a couple of days. Will this kind of wrapper couse much overhead or lack of performance?
I still wonder why IBM has not implemented this Interface in their release of the dataprovider.
B
|||The purpose of the wrapper is mainly to "link" the defined interfaces, properties and methods to the underlying managed DB2 provider.
Your data extension wrapper should not perform any real data processing - just passing through calls. Hence, there won't be any significant overhead.
-- Robert
|||Were you able to create this provider?Reporting Services using ASP
Is it possible to use SQL Server Repotring Services without using .net
framework? I want to use it using ASP.Depends on how you want to use it... You can call reports and pass
parameters via URL from ASP...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sumanta" <Sumanta@.discussions.microsoft.com> wrote in message
news:C07F6A9A-9C90-4213-AAB3-68E242E4213C@.microsoft.com...
> Is it possible to use SQL Server Repotring Services without using .net
> framework? I want to use it using ASP.
framework? I want to use it using ASP.Depends on how you want to use it... You can call reports and pass
parameters via URL from ASP...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sumanta" <Sumanta@.discussions.microsoft.com> wrote in message
news:C07F6A9A-9C90-4213-AAB3-68E242E4213C@.microsoft.com...
> Is it possible to use SQL Server Repotring Services without using .net
> framework? I want to use it using ASP.
Reporting Services User Problems...
What I learned this week is that Reporting Services security is annoying as
hell. :-)
I'm curious if there is any logical reason that you cannot create a
subscription out of
a report that references the User!UserID? Perhaps I can see why some other
values
in User! may not be available, but the subscription is clearly associated
with the username
that created it, so I cannot even conceieve why there would be a limitation
like that.
Second, I was curious about the context under which a scheduled report is
executed.
The restriction above seems to indicate that it is run under the context of
some unknown
generic user, but my experience below seems to indicate otherwise.
The nuts and bolts of it is that I am receiving the infamous
"ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files,
AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service
account doesn't
have rights to check domain user SIDs.". I started with the server running
as NETWORK
SERVICE, changed to LOCAL SYSTEM, and am finally using a domain account that
clearly has access to the domain controller to lookup SIDs.
My confusion here is that if *I* create a subscription (being an
administrator) the
subscription runs fine. If any other user creates the subscription, it will
fail with the error
above. I'd sure like some insight into how this authorization crap works to
help track
down the problem.
Thanks,
Joel
jdk6 at case dot eduJoel,
> I'm curious if there is any logical reason that you cannot create a
> subscription out of
> a report that references the User!UserID?
There is a good reason and it is that subscriptions are executed in an
unattended mode by the RS Windows service so any user context is irrelevant.
> Second, I was curious about the context under which a scheduled report is
> executed.
> The restriction above seems to indicate that it is run under the context
of
> some unknown
> generic user, but my experience below seems to indicate otherwise.
Under the context of the RS Windows service account.
Every user with Create Subscription rights should be able to create
subscriptions. From there, it is the RS Windows service responsibility to
handle the subscription.
--
Hope this helps.
----
Teo Lachev, 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/
----
"Joel D Kraft" <jdkraft2@.nospam.nospam> wrote in message
news:eaWoPv4mEHA.316@.TK2MSFTNGP10.phx.gbl...
> What I learned this week is that Reporting Services security is annoying
as
> hell. :-)
> I'm curious if there is any logical reason that you cannot create a
> subscription out of
> a report that references the User!UserID? Perhaps I can see why some
other
> values
> in User! may not be available, but the subscription is clearly associated
> with the username
> that created it, so I cannot even conceieve why there would be a
limitation
> like that.
> Second, I was curious about the context under which a scheduled report is
> executed.
> The restriction above seems to indicate that it is run under the context
of
> some unknown
> generic user, but my experience below seems to indicate otherwise.
> The nuts and bolts of it is that I am receiving the infamous
> "ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in
the
> log files,
> AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service
> account doesn't
> have rights to check domain user SIDs.". I started with the server
running
> as NETWORK
> SERVICE, changed to LOCAL SYSTEM, and am finally using a domain account
that
> clearly has access to the domain controller to lookup SIDs.
> My confusion here is that if *I* create a subscription (being an
> administrator) the
> subscription runs fine. If any other user creates the subscription, it
will
> fail with the error
> above. I'd sure like some insight into how this authorization crap works
to
> help track
> down the problem.
> Thanks,
> Joel
> jdk6 at case dot edu
>
>|||> Joel,
>> I'm curious if there is any logical reason that you cannot create a
>> subscription out of
>> a report that references the User!UserID?
> There is a good reason and it is that subscriptions are executed in an
> unattended mode by the RS Windows service so any user context is
> irrelevant.
> > Second, I was curious about the context under which a scheduled report
> > is
>> executed.
>> The restriction above seems to indicate that it is run under the context
> of
>> some unknown
>> generic user, but my experience below seems to indicate otherwise.
> Under the context of the RS Windows service account.
> Every user with Create Subscription rights should be able to create
> subscriptions. From there, it is the RS Windows service responsibility to
> handle the subscription.
Well at least your answers are consistent! :-)
Which is what I expected, though I cannot say that I agree.
My problem still remains then, is why when I create a subscription as an
administrator,
my subscription is delivered properly, but a subscription created by a
normal user
gets an AuthzInitializeContextFromSid error. This seems inconsistent with
the report
being solely run from the context of the RS Service account... if it had
permission
to process my subscription correctly, it should be able to do them all!!
This is RS 2000 SP1 under Windows 2003 and a Windows 2000 domain
functional level. "Authenticated Users" has the rights to read all of the
user and
group objects...
Joel|||Check the following thread:
http://groups.google.com/groups?q=AuthzInitializeContextFromSid&hl=en&lr=&ie=UTF-8&selm=O4qGzyL%24DHA.1700%40TK2MSFTNGP12.phx.gbl&rnum=7
--
Hope this helps.
----
Teo Lachev, 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/
----
"Joel D Kraft" <jdkraft2@.nospam.nospam> wrote in message
news:%23uO7VJ5mEHA.3296@.TK2MSFTNGP10.phx.gbl...
> > Joel,
> >
> >> I'm curious if there is any logical reason that you cannot create a
> >> subscription out of
> >> a report that references the User!UserID?
> >
> > There is a good reason and it is that subscriptions are executed in an
> > unattended mode by the RS Windows service so any user context is
> > irrelevant.
> > > Second, I was curious about the context under which a scheduled report
> > > is
> >> executed.
> >> The restriction above seems to indicate that it is run under the
context
> > of
> >> some unknown
> >> generic user, but my experience below seems to indicate otherwise.
> >
> > Under the context of the RS Windows service account.
> >
> > Every user with Create Subscription rights should be able to create
> > subscriptions. From there, it is the RS Windows service responsibility
to
> > handle the subscription.
> Well at least your answers are consistent! :-)
> Which is what I expected, though I cannot say that I agree.
> My problem still remains then, is why when I create a subscription as an
> administrator,
> my subscription is delivered properly, but a subscription created by a
> normal user
> gets an AuthzInitializeContextFromSid error. This seems inconsistent with
> the report
> being solely run from the context of the RS Service account... if it had
> permission
> to process my subscription correctly, it should be able to do them all!!
> This is RS 2000 SP1 under Windows 2003 and a Windows 2000 domain
> functional level. "Authenticated Users" has the rights to read all of the
> user and
> group objects...
> Joel
>
hell. :-)
I'm curious if there is any logical reason that you cannot create a
subscription out of
a report that references the User!UserID? Perhaps I can see why some other
values
in User! may not be available, but the subscription is clearly associated
with the username
that created it, so I cannot even conceieve why there would be a limitation
like that.
Second, I was curious about the context under which a scheduled report is
executed.
The restriction above seems to indicate that it is run under the context of
some unknown
generic user, but my experience below seems to indicate otherwise.
The nuts and bolts of it is that I am receiving the infamous
"ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files,
AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service
account doesn't
have rights to check domain user SIDs.". I started with the server running
as NETWORK
SERVICE, changed to LOCAL SYSTEM, and am finally using a domain account that
clearly has access to the domain controller to lookup SIDs.
My confusion here is that if *I* create a subscription (being an
administrator) the
subscription runs fine. If any other user creates the subscription, it will
fail with the error
above. I'd sure like some insight into how this authorization crap works to
help track
down the problem.
Thanks,
Joel
jdk6 at case dot eduJoel,
> I'm curious if there is any logical reason that you cannot create a
> subscription out of
> a report that references the User!UserID?
There is a good reason and it is that subscriptions are executed in an
unattended mode by the RS Windows service so any user context is irrelevant.
> Second, I was curious about the context under which a scheduled report is
> executed.
> The restriction above seems to indicate that it is run under the context
of
> some unknown
> generic user, but my experience below seems to indicate otherwise.
Under the context of the RS Windows service account.
Every user with Create Subscription rights should be able to create
subscriptions. From there, it is the RS Windows service responsibility to
handle the subscription.
--
Hope this helps.
----
Teo Lachev, 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/
----
"Joel D Kraft" <jdkraft2@.nospam.nospam> wrote in message
news:eaWoPv4mEHA.316@.TK2MSFTNGP10.phx.gbl...
> What I learned this week is that Reporting Services security is annoying
as
> hell. :-)
> I'm curious if there is any logical reason that you cannot create a
> subscription out of
> a report that references the User!UserID? Perhaps I can see why some
other
> values
> in User! may not be available, but the subscription is clearly associated
> with the username
> that created it, so I cannot even conceieve why there would be a
limitation
> like that.
> Second, I was curious about the context under which a scheduled report is
> executed.
> The restriction above seems to indicate that it is run under the context
of
> some unknown
> generic user, but my experience below seems to indicate otherwise.
> The nuts and bolts of it is that I am receiving the infamous
> "ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in
the
> log files,
> AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service
> account doesn't
> have rights to check domain user SIDs.". I started with the server
running
> as NETWORK
> SERVICE, changed to LOCAL SYSTEM, and am finally using a domain account
that
> clearly has access to the domain controller to lookup SIDs.
> My confusion here is that if *I* create a subscription (being an
> administrator) the
> subscription runs fine. If any other user creates the subscription, it
will
> fail with the error
> above. I'd sure like some insight into how this authorization crap works
to
> help track
> down the problem.
> Thanks,
> Joel
> jdk6 at case dot edu
>
>|||> Joel,
>> I'm curious if there is any logical reason that you cannot create a
>> subscription out of
>> a report that references the User!UserID?
> There is a good reason and it is that subscriptions are executed in an
> unattended mode by the RS Windows service so any user context is
> irrelevant.
> > Second, I was curious about the context under which a scheduled report
> > is
>> executed.
>> The restriction above seems to indicate that it is run under the context
> of
>> some unknown
>> generic user, but my experience below seems to indicate otherwise.
> Under the context of the RS Windows service account.
> Every user with Create Subscription rights should be able to create
> subscriptions. From there, it is the RS Windows service responsibility to
> handle the subscription.
Well at least your answers are consistent! :-)
Which is what I expected, though I cannot say that I agree.
My problem still remains then, is why when I create a subscription as an
administrator,
my subscription is delivered properly, but a subscription created by a
normal user
gets an AuthzInitializeContextFromSid error. This seems inconsistent with
the report
being solely run from the context of the RS Service account... if it had
permission
to process my subscription correctly, it should be able to do them all!!
This is RS 2000 SP1 under Windows 2003 and a Windows 2000 domain
functional level. "Authenticated Users" has the rights to read all of the
user and
group objects...
Joel|||Check the following thread:
http://groups.google.com/groups?q=AuthzInitializeContextFromSid&hl=en&lr=&ie=UTF-8&selm=O4qGzyL%24DHA.1700%40TK2MSFTNGP12.phx.gbl&rnum=7
--
Hope this helps.
----
Teo Lachev, 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/
----
"Joel D Kraft" <jdkraft2@.nospam.nospam> wrote in message
news:%23uO7VJ5mEHA.3296@.TK2MSFTNGP10.phx.gbl...
> > Joel,
> >
> >> I'm curious if there is any logical reason that you cannot create a
> >> subscription out of
> >> a report that references the User!UserID?
> >
> > There is a good reason and it is that subscriptions are executed in an
> > unattended mode by the RS Windows service so any user context is
> > irrelevant.
> > > Second, I was curious about the context under which a scheduled report
> > > is
> >> executed.
> >> The restriction above seems to indicate that it is run under the
context
> > of
> >> some unknown
> >> generic user, but my experience below seems to indicate otherwise.
> >
> > Under the context of the RS Windows service account.
> >
> > Every user with Create Subscription rights should be able to create
> > subscriptions. From there, it is the RS Windows service responsibility
to
> > handle the subscription.
> Well at least your answers are consistent! :-)
> Which is what I expected, though I cannot say that I agree.
> My problem still remains then, is why when I create a subscription as an
> administrator,
> my subscription is delivered properly, but a subscription created by a
> normal user
> gets an AuthzInitializeContextFromSid error. This seems inconsistent with
> the report
> being solely run from the context of the RS Service account... if it had
> permission
> to process my subscription correctly, it should be able to do them all!!
> This is RS 2000 SP1 under Windows 2003 and a Windows 2000 domain
> functional level. "Authenticated Users" has the rights to read all of the
> user and
> group objects...
> Joel
>
Reporting Services unstable.
I have been using reporting services for about 1 1/2 months, developing
reports in VS2003.
I have noticed that my changes don't get saved within the IDE (I have to go
out to notepad and edit the XML), as well as locking up when changing the
SQL statement in the report enviroment data window and changing the tab to
design or preview.
I have windows XP SP2 and SQL 2000 SP3a and Reporting Service SP1.
Unfortunately I don't know how long this has been happening.
Also when starting to apply changes to the report , VSS (Visual Source Safe)
doesn't prompt me to check out the report, it only does this when trying to
save it (which is doesn't do anyway).
Regards,
AdrianI too have been having very specific issues with changes to either the SQL
or the report layout format not being saved when integrated with VSS.
Neither the version in my working directory nor the version in VSS gets
updated.
Vivienne|||Sometimes it does not catch SQL changes. I always go to the layout mode
after I have made a change and then save it there. I have never seen a
problem with the report layout not being saved. Note that I do not use VSS
but the issue with changing the SQL is a problem regardless of whether VSS
is involved.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vivienne" <Vivienne@.discussions.microsoft.com> wrote in message
news:B57466D2-FFA7-4159-BE0F-4DCF07587C87@.microsoft.com...
> I too have been having very specific issues with changes to either the SQL
> or the report layout format not being saved when integrated with VSS.
> Neither the version in my working directory nor the version in VSS gets
> updated.
> Vivienne
reports in VS2003.
I have noticed that my changes don't get saved within the IDE (I have to go
out to notepad and edit the XML), as well as locking up when changing the
SQL statement in the report enviroment data window and changing the tab to
design or preview.
I have windows XP SP2 and SQL 2000 SP3a and Reporting Service SP1.
Unfortunately I don't know how long this has been happening.
Also when starting to apply changes to the report , VSS (Visual Source Safe)
doesn't prompt me to check out the report, it only does this when trying to
save it (which is doesn't do anyway).
Regards,
AdrianI too have been having very specific issues with changes to either the SQL
or the report layout format not being saved when integrated with VSS.
Neither the version in my working directory nor the version in VSS gets
updated.
Vivienne|||Sometimes it does not catch SQL changes. I always go to the layout mode
after I have made a change and then save it there. I have never seen a
problem with the report layout not being saved. Note that I do not use VSS
but the issue with changing the SQL is a problem regardless of whether VSS
is involved.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vivienne" <Vivienne@.discussions.microsoft.com> wrote in message
news:B57466D2-FFA7-4159-BE0F-4DCF07587C87@.microsoft.com...
> I too have been having very specific issues with changes to either the SQL
> or the report layout format not being saved when integrated with VSS.
> Neither the version in my working directory nor the version in VSS gets
> updated.
> Vivienne
Reporting Services under Windows Vista
Hello,
I tried to install SQL Server 2005 Express with Advanced Services under
Windows Vista RC1 x64. However I allways get the following Warning:
"ASP.Net Version Registration Requirement:
Failed to find the ASP.Net Version Registration with Microsoft Internet
Information Services (IIS)."
Having this warning it's not possible to install the Reporting Services.
Are there compatibility issues with IIS7 or am I doing sth. wrong?
MartinHello Martin,
Reporting Services won't be correctly supported until SP2 for SQL Server
2005, due to ship about the same time as Vista does. The IIS7 issue has to
due with the ISAPI filter RS uses. There is a KB article on this topic (920201,
see [0])
[0]: http://support.microsoft.com/kb/920201/en-us
I've tried getting working the RCs to not avail.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||I just think that is goddamn ridiculous
a) we can't use _ANY_ database on Vista until SQL 2005 SP2 comes out.
b) we can't get a DOCUMENTED release date on SP2.
c) we don't know what features are in SP2.
It's like-- all of a sudden; a month after vista ships; we're supposed
to flip the switch and have every dev box in the world using MSDE on
Windows XP on day one and SQL 2005 on Vista the next day.
I just dont understand WTF that company is thinking; it's goddamn
ridiculous
If you dont have enough programmers; hire more-- as it is; a company
that has 50,000 people and they're stressed about having 5,000
programmers working on Vista _AND_ Office 2007 combined?
No wonder google can out-develop you even though they only have 8,000
total employees-- I guess at Google; they don't require 45,000
redundant project managers / excel dorks
-Aaron
Kent Tegels wrote:
> Hello Martin,
> Reporting Services won't be correctly supported until SP2 for SQL Server
> 2005, due to ship about the same time as Vista does. The IIS7 issue has to
> due with the ISAPI filter RS uses. There is a KB article on this topic (920201,
> see [0])
> [0]: http://support.microsoft.com/kb/920201/en-us
> I've tried getting working the RCs to not avail.
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/|||I installed mine last week but not on x64. You have to go in to the Control
Panel / Programs and check all boxes for IIS. You can't rely on the top
check boxes for each subsection. You have to expand the hierarchy for IIS
and manually check ALL items.
Then it will install
--
"Everyone knows something you don't know"
"Martin W" wrote:
> Hello,
> I tried to install SQL Server 2005 Express with Advanced Services under
> Windows Vista RC1 x64. However I allways get the following Warning:
> "ASP.Net Version Registration Requirement:
> Failed to find the ASP.Net Version Registration with Microsoft Internet
> Information Services (IIS)."
> Having this warning it's not possible to install the Reporting Services.
> Are there compatibility issues with IIS7 or am I doing sth. wrong?
> Martin
>|||You have to go to Control Panel/Progams and manually expand the IIS section
so you can MANUALLY CHECK all IIS items. Just doing the top checkboxes won't
work.
--
"Everyone knows something you don't know"
"Martin W" wrote:
> Hello,
> I tried to install SQL Server 2005 Express with Advanced Services under
> Windows Vista RC1 x64. However I allways get the following Warning:
> "ASP.Net Version Registration Requirement:
> Failed to find the ASP.Net Version Registration with Microsoft Internet
> Information Services (IIS)."
> Having this warning it's not possible to install the Reporting Services.
> Are there compatibility issues with IIS7 or am I doing sth. wrong?
> Martin
>|||You have to go to Control Panel/Progams and manually expand the IIS section
so you can MANUALLY CHECK all IIS items. Just doing the top checkboxes won't
work.
--
"Everyone knows something you don't know"
"Martin W" wrote:
> Hello,
> I tried to install SQL Server 2005 Express with Advanced Services under
> Windows Vista RC1 x64. However I allways get the following Warning:
> "ASP.Net Version Registration Requirement:
> Failed to find the ASP.Net Version Registration with Microsoft Internet
> Information Services (IIS)."
> Having this warning it's not possible to install the Reporting Services.
> Are there compatibility issues with IIS7 or am I doing sth. wrong?
> Martin
>sql
I tried to install SQL Server 2005 Express with Advanced Services under
Windows Vista RC1 x64. However I allways get the following Warning:
"ASP.Net Version Registration Requirement:
Failed to find the ASP.Net Version Registration with Microsoft Internet
Information Services (IIS)."
Having this warning it's not possible to install the Reporting Services.
Are there compatibility issues with IIS7 or am I doing sth. wrong?
MartinHello Martin,
Reporting Services won't be correctly supported until SP2 for SQL Server
2005, due to ship about the same time as Vista does. The IIS7 issue has to
due with the ISAPI filter RS uses. There is a KB article on this topic (920201,
see [0])
[0]: http://support.microsoft.com/kb/920201/en-us
I've tried getting working the RCs to not avail.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||I just think that is goddamn ridiculous
a) we can't use _ANY_ database on Vista until SQL 2005 SP2 comes out.
b) we can't get a DOCUMENTED release date on SP2.
c) we don't know what features are in SP2.
It's like-- all of a sudden; a month after vista ships; we're supposed
to flip the switch and have every dev box in the world using MSDE on
Windows XP on day one and SQL 2005 on Vista the next day.
I just dont understand WTF that company is thinking; it's goddamn
ridiculous
If you dont have enough programmers; hire more-- as it is; a company
that has 50,000 people and they're stressed about having 5,000
programmers working on Vista _AND_ Office 2007 combined?
No wonder google can out-develop you even though they only have 8,000
total employees-- I guess at Google; they don't require 45,000
redundant project managers / excel dorks
-Aaron
Kent Tegels wrote:
> Hello Martin,
> Reporting Services won't be correctly supported until SP2 for SQL Server
> 2005, due to ship about the same time as Vista does. The IIS7 issue has to
> due with the ISAPI filter RS uses. There is a KB article on this topic (920201,
> see [0])
> [0]: http://support.microsoft.com/kb/920201/en-us
> I've tried getting working the RCs to not avail.
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/|||I installed mine last week but not on x64. You have to go in to the Control
Panel / Programs and check all boxes for IIS. You can't rely on the top
check boxes for each subsection. You have to expand the hierarchy for IIS
and manually check ALL items.
Then it will install
--
"Everyone knows something you don't know"
"Martin W" wrote:
> Hello,
> I tried to install SQL Server 2005 Express with Advanced Services under
> Windows Vista RC1 x64. However I allways get the following Warning:
> "ASP.Net Version Registration Requirement:
> Failed to find the ASP.Net Version Registration with Microsoft Internet
> Information Services (IIS)."
> Having this warning it's not possible to install the Reporting Services.
> Are there compatibility issues with IIS7 or am I doing sth. wrong?
> Martin
>|||You have to go to Control Panel/Progams and manually expand the IIS section
so you can MANUALLY CHECK all IIS items. Just doing the top checkboxes won't
work.
--
"Everyone knows something you don't know"
"Martin W" wrote:
> Hello,
> I tried to install SQL Server 2005 Express with Advanced Services under
> Windows Vista RC1 x64. However I allways get the following Warning:
> "ASP.Net Version Registration Requirement:
> Failed to find the ASP.Net Version Registration with Microsoft Internet
> Information Services (IIS)."
> Having this warning it's not possible to install the Reporting Services.
> Are there compatibility issues with IIS7 or am I doing sth. wrong?
> Martin
>|||You have to go to Control Panel/Progams and manually expand the IIS section
so you can MANUALLY CHECK all IIS items. Just doing the top checkboxes won't
work.
--
"Everyone knows something you don't know"
"Martin W" wrote:
> Hello,
> I tried to install SQL Server 2005 Express with Advanced Services under
> Windows Vista RC1 x64. However I allways get the following Warning:
> "ASP.Net Version Registration Requirement:
> Failed to find the ASP.Net Version Registration with Microsoft Internet
> Information Services (IIS)."
> Having this warning it's not possible to install the Reporting Services.
> Are there compatibility issues with IIS7 or am I doing sth. wrong?
> Martin
>sql
Reporting services unavailable after windows 2003 SP2 installed
I have installed windows server 2003 on one of our test boxes (prior to live) and found that reporting services is now unavailable.
The event log reports several times
When I browse to the report server I get
Service Unavailable
The event log reports several times
"A process serving application pool 'reportserver' terminated unexpectedly. The process id was '3500'. The process exit code was '0xffffffff'.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
before the worker process is disabled:
"Application pool 'reportserver' is being automatically disabled due to a series of failures in the process(es) serving that application pool.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp."
Has anyone seen similar behavior?
Stuart
"
A second reboot seems to have fixed this and (mysteriously enough) fixed terminal services too!
Reporting Services Tutorials Problem
Hi, I'm having a problem with the 6th tutorial of the Reporting Services: Using XML Data in a Report. Im using SQL Server Standard Edition on Windows server 2003.
In 'Lesson 1: Defining a Data Source and Dataset for XML Data' (http://msdn2.microsoft.com/en-us/library/ms345338.aspx), I can't seem to get steps 10 to 15 right.
In Step 10, it says you must type :
"<Query> <SoapAction>http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportservices/ListChildren</SoapAction> </Query>"
But in the diagram it has .../reporting/reportingservices/ListChildren...
Well I tried both(The namespace info from "http://localhost/reportserver/reportservice2005.asmx?wsdl" uses the latter.) but I get the error:
An error occurred while executing the query.
Failed to execute web request for the specified URL.
What am I doing wrong?
The schema information is wrong in the tutorial. We've fixed it for the next release. The following schema namespace should work for you:
Query>
<SoapAction>http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices/ListChildren</SoapAction>
</Query>
After typing the query as stated in the last post being:
<Query>
<SoapAction>http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices/ListChildren</SoapAction>
</Query>
I still get the same error. What is the correct syntax in order to get this to work?
Thanks
Reporting Services Tutorials Problem
Hi, I'm having a problem with the 6th tutorial of the Reporting Services: Using XML Data in a Report. Im using SQL Server Standard Edition on Windows server 2003.
In 'Lesson 1: Defining a Data Source and Dataset for XML Data' (http://msdn2.microsoft.com/en-us/library/ms345338.aspx), I can't seem to get steps 10 to 15 right.
In Step 10, it says you must type :
"<Query> <SoapAction>http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportservices/ListChildren</SoapAction> </Query>"
But in the diagram it has .../reporting/reportingservices/ListChildren...
Well I tried both(The namespace info from "http://localhost/reportserver/reportservice2005.asmx?wsdl" uses the latter.) but I get the error:
An error occurred while executing the query.
Failed to execute web request for the specified URL.
What am I doing wrong?
The schema information is wrong in the tutorial. We've fixed it for the next release. The following schema namespace should work for you:
Query>
<SoapAction>http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices/ListChildren</SoapAction>
</Query>
After typing the query as stated in the last post being:
<Query>
<SoapAction>http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices/ListChildren</SoapAction>
</Query>
I still get the same error. What is the correct syntax in order to get this to work?
Thanks
Reporting Services tutorial
Hi,
I am trying to step thru the June SQL 2005 CTP Book on Line Tutorial on Reporting Services (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqltut9/html/95c9c883-12e5-47af-8da1-b77c6ef7484c.htm)
However the steps don't seem to match what is in the GUI. Is there an updated version of this tutorial?
Thanks in advance.
Ned
Never mind, I selected REPORT MODEL instead of REPORT PROJECT.
Thanks,
Ned
Reporting Services Tuning
A couple of questions related to Reported Services tuning that I hope
someone might be able to give me a clue about....
1) In the Reporting Services Books Online, I have found a lot of
information on performance monitoring, but very little on performance
tuning. Is any additional information available, perhaps a whitepaper? Or
perhaps I am just missing something that is in the BOL?
2) An immediate question we have that perhaps someone can answer is, is
there any way to give On Demand reports a higher execution priority than
Scheduled reports? A particular concern is that a shared schedule, say "End
of month" processing, might become very popular and have a large number of
reports subscribed to it. These reports might then overload the server,
adversely effecting the execution time of On Demand reports.
Any enlightenment would be greatly appreciated.
Thanks,
BobOne thing to keep in mind. A snapshot of the report/data can be saved. So,
you can schedule to run at night every night and then save x days of
reports. Or you can run weekly and save 10 weeks of data (you specify how
many snapshots to save). This report or a link to it can be emailed out. To
me that is what makes the most sense, run the scheduled reports in off time
and then you will have no concern about the On Demand reports.
Bruce L-C
"Bob Hug" <rhug@.brassring.com> wrote in message
news:O6LK84QZEHA.3944@.tk2msftngp13.phx.gbl...
> A couple of questions related to Reported Services tuning that I hope
> someone might be able to give me a clue about....
> 1) In the Reporting Services Books Online, I have found a lot of
> information on performance monitoring, but very little on performance
> tuning. Is any additional information available, perhaps a whitepaper? Or
> perhaps I am just missing something that is in the BOL?
> 2) An immediate question we have that perhaps someone can answer is,
is
> there any way to give On Demand reports a higher execution priority than
> Scheduled reports? A particular concern is that a shared schedule, say
"End
> of month" processing, might become very popular and have a large number of
> reports subscribed to it. These reports might then overload the server,
> adversely effecting the execution time of On Demand reports.
> Any enlightenment would be greatly appreciated.
> Thanks,
> Bob
>sql
someone might be able to give me a clue about....
1) In the Reporting Services Books Online, I have found a lot of
information on performance monitoring, but very little on performance
tuning. Is any additional information available, perhaps a whitepaper? Or
perhaps I am just missing something that is in the BOL?
2) An immediate question we have that perhaps someone can answer is, is
there any way to give On Demand reports a higher execution priority than
Scheduled reports? A particular concern is that a shared schedule, say "End
of month" processing, might become very popular and have a large number of
reports subscribed to it. These reports might then overload the server,
adversely effecting the execution time of On Demand reports.
Any enlightenment would be greatly appreciated.
Thanks,
BobOne thing to keep in mind. A snapshot of the report/data can be saved. So,
you can schedule to run at night every night and then save x days of
reports. Or you can run weekly and save 10 weeks of data (you specify how
many snapshots to save). This report or a link to it can be emailed out. To
me that is what makes the most sense, run the scheduled reports in off time
and then you will have no concern about the On Demand reports.
Bruce L-C
"Bob Hug" <rhug@.brassring.com> wrote in message
news:O6LK84QZEHA.3944@.tk2msftngp13.phx.gbl...
> A couple of questions related to Reported Services tuning that I hope
> someone might be able to give me a clue about....
> 1) In the Reporting Services Books Online, I have found a lot of
> information on performance monitoring, but very little on performance
> tuning. Is any additional information available, perhaps a whitepaper? Or
> perhaps I am just missing something that is in the BOL?
> 2) An immediate question we have that perhaps someone can answer is,
is
> there any way to give On Demand reports a higher execution priority than
> Scheduled reports? A particular concern is that a shared schedule, say
"End
> of month" processing, might become very popular and have a large number of
> reports subscribed to it. These reports might then overload the server,
> adversely effecting the execution time of On Demand reports.
> Any enlightenment would be greatly appreciated.
> Thanks,
> Bob
>sql
Reporting Services trendline
Good day,
Can anyone help please?
I need to add a linear trendline to a chart in SSRS 2000 and it appears there is no option for this. If that is the case could someone help me in possibly manually working out the values for a linear trendline.
I know in Excel and so on it is done automatically but that does nit help and I am battling to find an equation to manually calculate the values.
Best regardsI am looking for the same answer. Were you able to resolve your issue?
Thank you,
Karen
Can anyone help please?
I need to add a linear trendline to a chart in SSRS 2000 and it appears there is no option for this. If that is the case could someone help me in possibly manually working out the values for a linear trendline.
I know in Excel and so on it is done automatically but that does nit help and I am battling to find an equation to manually calculate the values.
Best regardsI am looking for the same answer. Were you able to resolve your issue?
Thank you,
Karen
Reporting Services Toolbar & version
Hi All,
1. Whether I'm working on in Layout, Data or Preview (Reporting Services 2005) for any report
the toolbars I select disappear when I click on close. In Tools, Customize, I have check marks by: Formatting, Query designer, Layou,Build and Standard among a few others. I tried the reset button but no help as well.
What am I missing? Should they be visible and not grayed out?
2. Also, Is there an easy way to tell what verion/service pack level SQl server 2005 and of MSSRS 2005 I am running?
Thanks in Advance !
>Scott
http://i55.photobucket.com/albums/g121/Farsight38/toolbars.jpg
These should be all of your toolbars.
To get the versin of SQL server 2005, execute the following query:
select @.@.version
Reporting Services too slow.
Every time I log in for the first time in my Reporting Services by IIS it′s take forever.
Can I do something for increase the speed of this first connection to Reporting Services? After the first one the Reporting Services is much more faster.
Hello,
I believe the problem is your application pool is set to recycle too often. Take a look at this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1619105&SiteID=1
Hope this helps.
Jarret
|||Thanks... helped!Reporting Services too slow.
Every time I log in for the first time in my Reporting Services by IIS it′s take forever.
Can I do something for increase the speed of this first connection to Reporting Services? After the first one the Reporting Services is much more faster.
Hello,
I believe the problem is your application pool is set to recycle too often. Take a look at this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1619105&SiteID=1
Hope this helps.
Jarret
|||Thanks... helped!reporting services TOC
We need a way add a WORD style TOC to a report, an ideas out there...?Try a document map.
--
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/
---
"mike" <michael.w.i@.gmail.com> wrote in message
news:dbd5909c.0411121733.623da7b3@.posting.google.com...
> We need a way add a WORD style TOC to a report, an ideas out there...?sql
--
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/
---
"mike" <michael.w.i@.gmail.com> wrote in message
news:dbd5909c.0411121733.623da7b3@.posting.google.com...
> We need a way add a WORD style TOC to a report, an ideas out there...?sql
Reporting Services to MS Word Questions
Hello
I am using SQL RS to export a report to HTML with Office Web Components and then by clicking File->Edit with Microsoft Word, I get the report into Word format.
The report displays fine in the HTML with Office Web Components format, however the content gets condensed to about 3 inches in width and the page margins are off when I get this report into Word.
Is there a way to fix this formatting issues in Word?
Also if anyone has a better idea of how to get data from SQL RS to MS Word, please feel free to comment.
Thanks in advance for the help!We currently do not have a native export into Microsoft Word format. It is high on our list for a post-SQL 2005 release. You might take a look at SoftArtisans, a 3rd party that provides this functionality, http://officewriter.softartisans.com/officewriter-250.aspx.|||Is there any estimated date when a patch or release of SQL RS will be available that exports directly to Word format? This effects our decision and how we will view costs.|||
We have the same dillemma...
We are currently evaluating another reporting tool due to couple of reasons:
1)Mainly because SQL Server RS does not support direct export to MS Word /RTF.
2) No support for export of nested data regions to Excel?
Any idea when these features will be available?
Thanks a lot,
Panna Balaji
|||We have developed and just released Aspose.Words for Reporting Services that is currently the only solution available for exporting any RDL report to true DOC, RTF and WordML.
Reporting Services to MS Word Questions
Hello
I am using SQL RS to export a report to HTML with Office Web Components and then by clicking File->Edit with Microsoft Word, I get the report into Word format.
The report displays fine in the HTML with Office Web Components format, however the content gets condensed to about 3 inches in width and the page margins are off when I get this report into Word.
Is there a way to fix this formatting issues in Word?
Also if anyone has a better idea of how to get data from SQL RS to MS Word, please feel free to comment.
Thanks in advance for the help!We currently do not have a native export into Microsoft Word format. It is high on our list for a post-SQL 2005 release. You might take a look at SoftArtisans, a 3rd party that provides this functionality, http://officewriter.softartisans.com/officewriter-250.aspx.|||Is there any estimated date when a patch or release of SQL RS will be available that exports directly to Word format? This effects our decision and how we will view costs.|||
We have the same dillemma...
We are currently evaluating another reporting tool due to couple of reasons:
1)Mainly because SQL Server RS does not support direct export to MS Word /RTF.
2) No support for export of nested data regions to Excel?
Any idea when these features will be available?
Thanks a lot,
Panna Balaji
|||We have developed and just released Aspose.Words for Reporting Services that is currently the only solution available for exporting any RDL report to true DOC, RTF and WordML.
Reporting Services to MS Word Questions
Hello
I am using SQL RS to export a report to HTML with Office Web Components and then by clicking File->Edit with Microsoft Word, I get the report into Word format.
The report displays fine in the HTML with Office Web Components format, however the content gets condensed to about 3 inches in width and the page margins are off when I get this report into Word.
Is there a way to fix this formatting issues in Word?
Also if anyone has a better idea of how to get data from SQL RS to MS Word, please feel free to comment.
Thanks in advance for the help!We currently do not have a native export into Microsoft Word format. It is high on our list for a post-SQL 2005 release. You might take a look at SoftArtisans, a 3rd party that provides this functionality, http://officewriter.softartisans.com/officewriter-250.aspx.|||Is there any estimated date when a patch or release of SQL RS will be available that exports directly to Word format? This effects our decision and how we will view costs.|||
We have the same dillemma...
We are currently evaluating another reporting tool due to couple of reasons:
1)Mainly because SQL Server RS does not support direct export to MS Word /RTF.
2) No support for export of nested data regions to Excel?
Any idea when these features will be available?
Thanks a lot,
Panna Balaji
|||We have developed and just released Aspose.Words for Reporting Services that is currently the only solution available for exporting any RDL report to true DOC, RTF and WordML.
Reporting Services TIFF export quality
Hi
I've got a question regarding the TIFFs that are generated by Reporting
Services. The problem I am having right now, is that documents that
are exported into TIFFs are in bad quality - i.e. the text does not
seem to be 'ClearTyped'.
I installed the powertoy: ClearTypeTuner for the server, thinking
perhaps that would force the TIFFs to generate fonts with smoothed
edges.
Unfortunately this did not work. I know that cleartype is not
supported through remote desktop. I wonder if this is the case as well
for Reporting Services - when generating a TIFF image from the report
server, cleartype is not supported as well.
I could probably be totally wrong - and would like it if somebody could
just point me in the right direction.
Cheers.TIFF files default to 72 dpi. This makes for smaller files, and is legible
but not very nice looking. Change the device info settings to a higher dpi
and you should be happy with the smoothness of the fonts.
Ted
"sgt_sunshine" wrote:
> Hi
> I've got a question regarding the TIFFs that are generated by Reporting
> Services. The problem I am having right now, is that documents that
> are exported into TIFFs are in bad quality - i.e. the text does not
> seem to be 'ClearTyped'.
> I installed the powertoy: ClearTypeTuner for the server, thinking
> perhaps that would force the TIFFs to generate fonts with smoothed
> edges.
> Unfortunately this did not work. I know that cleartype is not
> supported through remote desktop. I wonder if this is the case as well
> for Reporting Services - when generating a TIFF image from the report
> server, cleartype is not supported as well.
> I could probably be totally wrong - and would like it if somebody could
> just point me in the right direction.
> Cheers.
>|||Yes I have done just that but unfortunately the quality is just not on
par with a PDF document, plus it now takes a longer time to generate a
report at 200 or 300 dpi.
I currently have the reports printing at 200 dpi but I suspect I will
need another workaround.
This problem is related to another I have been having - barcode
printing. If I can solve the barcode printing problem, I should solve
this as well.
Thanks anyway - I wasn't expecting any replies so soon.
I've got a question regarding the TIFFs that are generated by Reporting
Services. The problem I am having right now, is that documents that
are exported into TIFFs are in bad quality - i.e. the text does not
seem to be 'ClearTyped'.
I installed the powertoy: ClearTypeTuner for the server, thinking
perhaps that would force the TIFFs to generate fonts with smoothed
edges.
Unfortunately this did not work. I know that cleartype is not
supported through remote desktop. I wonder if this is the case as well
for Reporting Services - when generating a TIFF image from the report
server, cleartype is not supported as well.
I could probably be totally wrong - and would like it if somebody could
just point me in the right direction.
Cheers.TIFF files default to 72 dpi. This makes for smaller files, and is legible
but not very nice looking. Change the device info settings to a higher dpi
and you should be happy with the smoothness of the fonts.
Ted
"sgt_sunshine" wrote:
> Hi
> I've got a question regarding the TIFFs that are generated by Reporting
> Services. The problem I am having right now, is that documents that
> are exported into TIFFs are in bad quality - i.e. the text does not
> seem to be 'ClearTyped'.
> I installed the powertoy: ClearTypeTuner for the server, thinking
> perhaps that would force the TIFFs to generate fonts with smoothed
> edges.
> Unfortunately this did not work. I know that cleartype is not
> supported through remote desktop. I wonder if this is the case as well
> for Reporting Services - when generating a TIFF image from the report
> server, cleartype is not supported as well.
> I could probably be totally wrong - and would like it if somebody could
> just point me in the right direction.
> Cheers.
>|||Yes I have done just that but unfortunately the quality is just not on
par with a PDF document, plus it now takes a longer time to generate a
report at 200 or 300 dpi.
I currently have the reports printing at 200 dpi but I suspect I will
need another workaround.
This problem is related to another I have been having - barcode
printing. If I can solve the barcode printing problem, I should solve
this as well.
Thanks anyway - I wasn't expecting any replies so soon.
Reporting Services Third Party Utilities
I was wondering if anyone knows of a report utility that works with Reporting Services to enable end users to create their own reports? A utility that is user-friendly enough for end users to modify reports without the aid of a developer.SQL Server 2005 ships with an application called Report Builder that does this. It is bundled with every edition (except Express, Mobile, or Anywhere). It is easy to install on end-user machines, it even deploys with clickonce!|||I have tried Report Builder, and it seems to do everything that I've needed. Thanks for your post and help.sql
Reporting Services Templates
I have been successful in using a Template for my Reports, however if I
decide to change my template after a few reports have been created the old
reports do not 'automatically' pick up the changes to the Template. Is there
a way to 'Refer' to a template as opposed to 'Using' a template? Will this
be available in the next release?I don't know about the next release but with the current release there is no
way to do this.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KDogg" <KDogg@.discussions.microsoft.com> wrote in message
news:983A3BD2-BF11-4FF4-A314-7091DC632F4A@.microsoft.com...
>I have been successful in using a Template for my Reports, however if I
> decide to change my template after a few reports have been created the old
> reports do not 'automatically' pick up the changes to the Template. Is
> there
> a way to 'Refer' to a template as opposed to 'Using' a template? Will
> this
> be available in the next release?|||Thanks Bruce,
Do you know of any way to obtain any information regarding Reporting
Services templates in SQL Server 2005 Beta? We have just started
implementing Reporting Services and if the benefits of SQL2005's
functionality outweigh our options in SQL2000 we can switch as we are just
working on a pilot project for proof of concept.
"Bruce L-C [MVP]" wrote:
> I don't know about the next release but with the current release there is no
> way to do this.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KDogg" <KDogg@.discussions.microsoft.com> wrote in message
> news:983A3BD2-BF11-4FF4-A314-7091DC632F4A@.microsoft.com...
> >I have been successful in using a Template for my Reports, however if I
> > decide to change my template after a few reports have been created the old
> > reports do not 'automatically' pick up the changes to the Template. Is
> > there
> > a way to 'Refer' to a template as opposed to 'Using' a template? Will
> > this
> > be available in the next release?
>
>
decide to change my template after a few reports have been created the old
reports do not 'automatically' pick up the changes to the Template. Is there
a way to 'Refer' to a template as opposed to 'Using' a template? Will this
be available in the next release?I don't know about the next release but with the current release there is no
way to do this.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KDogg" <KDogg@.discussions.microsoft.com> wrote in message
news:983A3BD2-BF11-4FF4-A314-7091DC632F4A@.microsoft.com...
>I have been successful in using a Template for my Reports, however if I
> decide to change my template after a few reports have been created the old
> reports do not 'automatically' pick up the changes to the Template. Is
> there
> a way to 'Refer' to a template as opposed to 'Using' a template? Will
> this
> be available in the next release?|||Thanks Bruce,
Do you know of any way to obtain any information regarding Reporting
Services templates in SQL Server 2005 Beta? We have just started
implementing Reporting Services and if the benefits of SQL2005's
functionality outweigh our options in SQL2000 we can switch as we are just
working on a pilot project for proof of concept.
"Bruce L-C [MVP]" wrote:
> I don't know about the next release but with the current release there is no
> way to do this.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KDogg" <KDogg@.discussions.microsoft.com> wrote in message
> news:983A3BD2-BF11-4FF4-A314-7091DC632F4A@.microsoft.com...
> >I have been successful in using a Template for my Reports, however if I
> > decide to change my template after a few reports have been created the old
> > reports do not 'automatically' pick up the changes to the Template. Is
> > there
> > a way to 'Refer' to a template as opposed to 'Using' a template? Will
> > this
> > be available in the next release?
>
>
Reporting Services Template
How can I setup Reporting Service so the report wizard uses a default
template we have created?Hi,
Basically you can save at this folder to access the template.
C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
But for the wizard, you need to change the .vsz file with some scripts, it
is basically a text file. I would suggest you go through MSDN for changing
the scripts and search for "Creating a .vsz File" in MSDN.
Amarnath
"Chris Townsend" wrote:
> How can I setup Reporting Service so the report wizard uses a default
> template we have created?
>
>
template we have created?Hi,
Basically you can save at this folder to access the template.
C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
But for the wizard, you need to change the .vsz file with some scripts, it
is basically a text file. I would suggest you go through MSDN for changing
the scripts and search for "Creating a .vsz File" in MSDN.
Amarnath
"Chris Townsend" wrote:
> How can I setup Reporting Service so the report wizard uses a default
> template we have created?
>
>
reporting services table formatting
When you have a table that continues onto another page, how do you get
the table headers to show on the 2nd page? I just get a continuation of
the table with no headers on subsequent pages.
Anybody know how to do this?
*** Sent via Developersdex http://www.examnotes.net ***You can set the property RepeatOnNewPage property of the header to True in
order to get table headers on each page. It is False by default.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike P" <mike.parr@.gmail.com> wrote in message
news:uLW3bBMeGHA.3388@.TK2MSFTNGP05.phx.gbl...
> When you have a table that continues onto another page, how do you get
> the table headers to show on the 2nd page? I just get a continuation of
> the table with no headers on subsequent pages.
> Anybody know how to do this?
>
> *** Sent via Developersdex http://www.examnotes.net ***
the table headers to show on the 2nd page? I just get a continuation of
the table with no headers on subsequent pages.
Anybody know how to do this?
*** Sent via Developersdex http://www.examnotes.net ***You can set the property RepeatOnNewPage property of the header to True in
order to get table headers on each page. It is False by default.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike P" <mike.parr@.gmail.com> wrote in message
news:uLW3bBMeGHA.3388@.TK2MSFTNGP05.phx.gbl...
> When you have a table that continues onto another page, how do you get
> the table headers to show on the 2nd page? I just get a continuation of
> the table with no headers on subsequent pages.
> Anybody know how to do this?
>
> *** Sent via Developersdex http://www.examnotes.net ***
reporting services table formatting
When you have a table that continues onto another page, how do you get
the table headers to show on the 2nd page? I just get a continuation of
the table with no headers on subsequent pages.
the table headers to show on the 2nd page? I just get a continuation of
the table with no headers on subsequent pages.
Anybody know how to do this?
Are you using Reporting Services 2005?
If so... Select the table and right click to get the pop-up menu. Select properties and on the first tab (General) there is a checkbox "Repeat header rows on each page"
mike72:
When you have a table that continues onto another page, how do you get
the table headers to show on the 2nd page? I just get a continuation of
the table with no headers on subsequent pages.Anybody know how to do this?
Reporting Services suggestions/wishes
So far, Microsoft has put out log of seminars, webcasts, conference
sessions, whitepapers, etc on Reporting Services. But they have been heavy
on using RS or developing with RS. I have not seen a lot focusing on
managing Reporting Services in an enterprise. The RS capacity planning and
performance tuning whitepaper is an exception. I think there is a real need
for much more coverage on topics such as the following (just a sample list):
* Monitoring Reporting Services
* Managing Reporing Services disaster and recovery
* RS.exe scripting for automation
LinchiAt the loadfest I attended, the suggest to send an e-mail - something
sqlwish@.microsoft.com - (not sure...).
Our company has been evaluting RS to replace Crystal - so far - it "ain't
gonna happen". RS isn't ready for complex reporting. For anyone that says
it is, you aren't righting complex reports. We have created many simple
reports - which is does very well (thank you).
Don't misunderstand me - I am looking forward to the next version - which I
will expect to see some big improvements. MS has been very "hush" about
what type of enhancements will be included (maybe they know - maybe they
don't). I don't want to beat up on MS too bad - this is a very good idea
with a lot of promise - but it isn't for eveyone (yet!).
My wish list:
- Aggregate functions on textbox values (a must!)
- Preview with edit/formatting ability (if you don't understand - look
at Crystal)
- Intellisense!!!
- Some type of interface to assist with coding (again - look at Crystal,
building functions/custom code)
- Ability to create variables at the report level that can be access by
functions (...Crystal...again)
OK - I am a Crystal user - so what?!? I am trying to use RS and will
actively support RS when it can compare to Crystal.
...take this as "constructive criticism". To be honest - I am anxious to
see Business Objects "whine" about MS monopolizing the report-writing
market!! Go MS!
-KB
"Linchi Shea" <linchi_shea@.NOSPAM.ml.com> wrote in message
news:%23ltaDWAhEHA.3236@.TK2MSFTNGP10.phx.gbl...
> So far, Microsoft has put out log of seminars, webcasts, conference
> sessions, whitepapers, etc on Reporting Services. But they have been heavy
> on using RS or developing with RS. I have not seen a lot focusing on
> managing Reporting Services in an enterprise. The RS capacity planning and
> performance tuning whitepaper is an exception. I think there is a real
need
> for much more coverage on topics such as the following (just a sample
list):
> * Monitoring Reporting Services
> * Managing Reporing Services disaster and recovery
> * RS.exe scripting for automation
> Linchi
>|||Kevin,
I am not trying to be a guardian angel but as they say "truth is born in
discussions", so please find my comments inline for whatever they are worth
for.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Kevin B" <kevin@.misnet.info> wrote in message
news:#AozSnAhEHA.3928@.TK2MSFTNGP11.phx.gbl...
> At the loadfest I attended, the suggest to send an e-mail - something
> sqlwish@.microsoft.com - (not sure...).
> Our company has been evaluting RS to replace Crystal - so far - it "ain't
> gonna happen". RS isn't ready for complex reporting. For anyone that
says
> it is, you aren't righting complex reports. We have created many simple
> reports - which is does very well (thank you).
> Don't misunderstand me - I am looking forward to the next version - which
I
> will expect to see some big improvements. MS has been very "hush" about
> what type of enhancements will be included (maybe they know - maybe they
> don't). I don't want to beat up on MS too bad - this is a very good idea
> with a lot of promise - but it isn't for eveyone (yet!).
> My wish list:
> - Aggregate functions on textbox values (a must!)
Teo: Not sure what you mean here, can you explain or better give an
example?
> - Preview with edit/formatting ability (if you don't understand - look
> at Crystal)
Teo: By definition, preview is read-only but again I don't know Crystal...
> - Intellisense!!!
Teo: Use external assemblies and you will have the whole VS.NET enchilada.
My recommendation is to use embedded functions for simple logic only.
> - Some type of interface to assist with coding (again - look at
Crystal,
> building functions/custom code)
Teo: Again use VS.NET again and external assemblies.
> - Ability to create variables at the report level that can be access
by
> functions (...Crystal...again)
Teo: Can you accomplish this by using class-level variables in instance
classes?
> OK - I am a Crystal user - so what?!? I am trying to use RS and will
> actively support RS when it can compare to Crystal.
> ...take this as "constructive criticism". To be honest - I am anxious to
> see Business Objects "whine" about MS monopolizing the report-writing
> market!! Go MS!
> -KB
>
> "Linchi Shea" <linchi_shea@.NOSPAM.ml.com> wrote in message
> news:%23ltaDWAhEHA.3236@.TK2MSFTNGP10.phx.gbl...
> > So far, Microsoft has put out log of seminars, webcasts, conference
> > sessions, whitepapers, etc on Reporting Services. But they have been
heavy
> > on using RS or developing with RS. I have not seen a lot focusing on
> > managing Reporting Services in an enterprise. The RS capacity planning
and
> > performance tuning whitepaper is an exception. I think there is a real
> need
> > for much more coverage on topics such as the following (just a sample
> list):
> >
> > * Monitoring Reporting Services
> > * Managing Reporing Services disaster and recovery
> > * RS.exe scripting for automation
> >
> > Linchi
> >
> >
>sql
sessions, whitepapers, etc on Reporting Services. But they have been heavy
on using RS or developing with RS. I have not seen a lot focusing on
managing Reporting Services in an enterprise. The RS capacity planning and
performance tuning whitepaper is an exception. I think there is a real need
for much more coverage on topics such as the following (just a sample list):
* Monitoring Reporting Services
* Managing Reporing Services disaster and recovery
* RS.exe scripting for automation
LinchiAt the loadfest I attended, the suggest to send an e-mail - something
sqlwish@.microsoft.com - (not sure...).
Our company has been evaluting RS to replace Crystal - so far - it "ain't
gonna happen". RS isn't ready for complex reporting. For anyone that says
it is, you aren't righting complex reports. We have created many simple
reports - which is does very well (thank you).
Don't misunderstand me - I am looking forward to the next version - which I
will expect to see some big improvements. MS has been very "hush" about
what type of enhancements will be included (maybe they know - maybe they
don't). I don't want to beat up on MS too bad - this is a very good idea
with a lot of promise - but it isn't for eveyone (yet!).
My wish list:
- Aggregate functions on textbox values (a must!)
- Preview with edit/formatting ability (if you don't understand - look
at Crystal)
- Intellisense!!!
- Some type of interface to assist with coding (again - look at Crystal,
building functions/custom code)
- Ability to create variables at the report level that can be access by
functions (...Crystal...again)
OK - I am a Crystal user - so what?!? I am trying to use RS and will
actively support RS when it can compare to Crystal.
...take this as "constructive criticism". To be honest - I am anxious to
see Business Objects "whine" about MS monopolizing the report-writing
market!! Go MS!
-KB
"Linchi Shea" <linchi_shea@.NOSPAM.ml.com> wrote in message
news:%23ltaDWAhEHA.3236@.TK2MSFTNGP10.phx.gbl...
> So far, Microsoft has put out log of seminars, webcasts, conference
> sessions, whitepapers, etc on Reporting Services. But they have been heavy
> on using RS or developing with RS. I have not seen a lot focusing on
> managing Reporting Services in an enterprise. The RS capacity planning and
> performance tuning whitepaper is an exception. I think there is a real
need
> for much more coverage on topics such as the following (just a sample
list):
> * Monitoring Reporting Services
> * Managing Reporing Services disaster and recovery
> * RS.exe scripting for automation
> Linchi
>|||Kevin,
I am not trying to be a guardian angel but as they say "truth is born in
discussions", so please find my comments inline for whatever they are worth
for.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Kevin B" <kevin@.misnet.info> wrote in message
news:#AozSnAhEHA.3928@.TK2MSFTNGP11.phx.gbl...
> At the loadfest I attended, the suggest to send an e-mail - something
> sqlwish@.microsoft.com - (not sure...).
> Our company has been evaluting RS to replace Crystal - so far - it "ain't
> gonna happen". RS isn't ready for complex reporting. For anyone that
says
> it is, you aren't righting complex reports. We have created many simple
> reports - which is does very well (thank you).
> Don't misunderstand me - I am looking forward to the next version - which
I
> will expect to see some big improvements. MS has been very "hush" about
> what type of enhancements will be included (maybe they know - maybe they
> don't). I don't want to beat up on MS too bad - this is a very good idea
> with a lot of promise - but it isn't for eveyone (yet!).
> My wish list:
> - Aggregate functions on textbox values (a must!)
Teo: Not sure what you mean here, can you explain or better give an
example?
> - Preview with edit/formatting ability (if you don't understand - look
> at Crystal)
Teo: By definition, preview is read-only but again I don't know Crystal...
> - Intellisense!!!
Teo: Use external assemblies and you will have the whole VS.NET enchilada.
My recommendation is to use embedded functions for simple logic only.
> - Some type of interface to assist with coding (again - look at
Crystal,
> building functions/custom code)
Teo: Again use VS.NET again and external assemblies.
> - Ability to create variables at the report level that can be access
by
> functions (...Crystal...again)
Teo: Can you accomplish this by using class-level variables in instance
classes?
> OK - I am a Crystal user - so what?!? I am trying to use RS and will
> actively support RS when it can compare to Crystal.
> ...take this as "constructive criticism". To be honest - I am anxious to
> see Business Objects "whine" about MS monopolizing the report-writing
> market!! Go MS!
> -KB
>
> "Linchi Shea" <linchi_shea@.NOSPAM.ml.com> wrote in message
> news:%23ltaDWAhEHA.3236@.TK2MSFTNGP10.phx.gbl...
> > So far, Microsoft has put out log of seminars, webcasts, conference
> > sessions, whitepapers, etc on Reporting Services. But they have been
heavy
> > on using RS or developing with RS. I have not seen a lot focusing on
> > managing Reporting Services in an enterprise. The RS capacity planning
and
> > performance tuning whitepaper is an exception. I think there is a real
> need
> > for much more coverage on topics such as the following (just a sample
> list):
> >
> > * Monitoring Reporting Services
> > * Managing Reporing Services disaster and recovery
> > * RS.exe scripting for automation
> >
> > Linchi
> >
> >
>sql
Reporting Services Subscriptions Stop arbitrarily
Hi,
periodically, our Reporting Service seems to fall over with respect to
Subscriptions (visiting subscriptions reveal they are in a state of
pending). We then restart the SQL Agent and the reporting services service
which seems to resolve the problem until it falls over again. Sometimes
this happens several times in a day, other times it may be a week.
Can anyone gives us suggestions as to what might be the cause of this?
thanks
MattYou best bet is to look in the log files and see if there is anything in
there that might lead you to the cause of the problem.
Is the report your rendering a large report? Perhaps it is using up all of
the available machine resources and causing RS to shut down. You may need
to look into using report execution snapshots for the report to lessen the
load on RS.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:OWUT%23qs8EHA.3700@.tk2msftngp13.phx.gbl...
> Hi,
> periodically, our Reporting Service seems to fall over with respect to
> Subscriptions (visiting subscriptions reveal they are in a state of
> pending). We then restart the SQL Agent and the reporting services
> service
> which seems to resolve the problem until it falls over again. Sometimes
> this happens several times in a day, other times it may be a week.
> Can anyone gives us suggestions as to what might be the cause of this?
> thanks
> Matt
>
periodically, our Reporting Service seems to fall over with respect to
Subscriptions (visiting subscriptions reveal they are in a state of
pending). We then restart the SQL Agent and the reporting services service
which seems to resolve the problem until it falls over again. Sometimes
this happens several times in a day, other times it may be a week.
Can anyone gives us suggestions as to what might be the cause of this?
thanks
MattYou best bet is to look in the log files and see if there is anything in
there that might lead you to the cause of the problem.
Is the report your rendering a large report? Perhaps it is using up all of
the available machine resources and causing RS to shut down. You may need
to look into using report execution snapshots for the report to lessen the
load on RS.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:OWUT%23qs8EHA.3700@.tk2msftngp13.phx.gbl...
> Hi,
> periodically, our Reporting Service seems to fall over with respect to
> Subscriptions (visiting subscriptions reveal they are in a state of
> pending). We then restart the SQL Agent and the reporting services
> service
> which seems to resolve the problem until it falls over again. Sometimes
> this happens several times in a day, other times it may be a week.
> Can anyone gives us suggestions as to what might be the cause of this?
> thanks
> Matt
>
Reporting Services Subscriptions / Sharepoint question
I'm trying to create a subscription that places a file (excel or PDF) in sharepoint. I haven't been able to find a path that works. I was wondering if anyone else had had this issue and how they overcame it.
Thanks.Answered my own question.
http://technet.microsoft.com/en-us/library/bb326364.aspx
Reporting Services Subscriptions - Help!
Hi, I have two questions regarding subscriptions in Report Services 2000.
1) Is there a way to view all the subscriptions in one centralised place. I know that you can view all your own subscriptions, but I did not create all of them. What I need is to be able to see all the subscriptions for all the reports without having to go into each report and see if it has subscriptions associated with it (we have about three hundred reports)
2) How can you disable a subscription, for example, my warehouse may be down one day and I dont want my daily reports to get sent to my clients so I just want to disable the subscription, not delete it. Anyway to get around this?
Thanks in advance
Look in Subscriptions in the ReportServer Db. I've created reports pulling data from the ReportServer database.
To disable a Subscription I'd set the end date before today.
|||Thanks very much for the help. Have decided to go that route nowReporting Services Subscriptions - Help!
Hi, I have two questions regarding subscriptions in Report Services 2000.
1) Is there a way to view all the subscriptions in one centralised place. I know that you can view all your own subscriptions, but I did not create all of them. What I need is to be able to see all the subscriptions for all the reports without having to go into each report and see if it has subscriptions associated with it (we have about three hundred reports)
2) How can you disable a subscription, for example, my warehouse may be down one day and I dont want my daily reports to get sent to my clients so I just want to disable the subscription, not delete it. Anyway to get around this?
Thanks in advance
Look in Subscriptions in the ReportServer Db. I've created reports pulling data from the ReportServer database.
To disable a Subscription I'd set the end date before today.
|||Thanks very much for the help. Have decided to go that route nowReporting Services subscriptions
I've created a report and I did subscribe to it using the subscriptions on this report. I get a mail with the data from that report.
So everything works fine but some times the reports results in an empty report because there is nothing. This is correct but then the mail is send anyway.
So I want to prevent that the mail of the subscription is send when the result of the report is empty (recordset of the report returns 0 rows).
Is this posibel and if so how do I do this.
Hi,
Based on my knowledge, I'm afraid you can't achieve that via some built-in options in reporting service.
The email delivering extension service is specially for each requesting to the report database, and send the produced reports to users and groups, the schedule always runs no matter what result it is.
Thanks.
Reporting Services Subscriptions
Hi All,
I'm testing RS with the Samples Reports, and especially the Product
Catalog report, which has no parameters, and is not a linked report.
I'm trying to make a subscription on this report, and I always get the
classical message shown bellow :
"Subscriptions cannot be created because the
credentials used to run the report are not stored,the report is using
user-defined para values,or if a linked report,the link is no longer
work."
So, as the report as no parameters, and is not a linked report, the
problem is with credentials.
I've changed the AdventureWorks datasource to make the credential
stored. The report is generated well.
But when I want to make a subscription? same message again.
I've tried with the "sa" account !!! Same message.
Am I wrong in my settings ?
Thanks for help.
DAThis report uses the user property which makes it unusable for
subscriptions.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Death Angel" <lionel.valero@.scam.fr> wrote in message
news:d114e6a.0407290555.3734e70e@.posting.google.com...
> Hi All,
>
> I'm testing RS with the Samples Reports, and especially the Product
> Catalog report, which has no parameters, and is not a linked report.
> I'm trying to make a subscription on this report, and I always get the
> classical message shown bellow :
> "Subscriptions cannot be created because the
> credentials used to run the report are not stored,the report is using
> user-defined para values,or if a linked report,the link is no longer
> work."
> So, as the report as no parameters, and is not a linked report, the
> problem is with credentials.
> I've changed the AdventureWorks datasource to make the credential
> stored. The report is generated well.
> But when I want to make a subscription. same message again.
> I've tried with the "sa" account !!! Same message.
> Am I wrong in my settings ?
>
> Thanks for help.
>
> DA|||I am running into the same problem with my own custom
report. What is this 'user property' that is being
referred to and what needs to change?
Thanks for any assistance!!!
>--Original Message--
>This report uses the user property which makes it
unusable for
>subscriptions.
>--
>-Daniel
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"Death Angel" <lionel.valero@.scam.fr> wrote in message
>news:d114e6a.0407290555.3734e70e@.posting.google.com...
>> Hi All,
>>
>> I'm testing RS with the Samples Reports, and especially
the Product
>> Catalog report, which has no parameters, and is not a
linked report.
>> I'm trying to make a subscription on this report, and I
always get the
>> classical message shown bellow :
>> "Subscriptions cannot be created because the
>> credentials used to run the report are not stored,the
report is using
>> user-defined para values,or if a linked report,the link
is no longer
>> work."
>> So, as the report as no parameters, and is not a linked
report, the
>> problem is with credentials.
>> I've changed the AdventureWorks datasource to make the
credential
>> stored. The report is generated well.
>> But when I want to make a subscription. same message
again.
>> I've tried with the "sa" account !!! Same message.
>> Am I wrong in my settings ?
>>
>> Thanks for help.
>>
>> DA
>
>.
>|||The user property is a property made available by RS. It contains
information such as the current user name (user running the report), their
language, etc. It is used in expression.
This link should help you:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPORTAL/HTM/rs_gts_portal_3vqd.asp
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"jb" <Jill.Barrett@.accesspointinc.com> wrote in message
news:044d01c47eec$716543a0$7d02280a@.phx.gbl...
> I am running into the same problem with my own custom
> report. What is this 'user property' that is being
> referred to and what needs to change?
> Thanks for any assistance!!!
> >--Original Message--
> >This report uses the user property which makes it
> unusable for
> >subscriptions.
> >
> >--
> >-Daniel
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> >
> >"Death Angel" <lionel.valero@.scam.fr> wrote in message
> >news:d114e6a.0407290555.3734e70e@.posting.google.com...
> >> Hi All,
> >>
> >>
> >> I'm testing RS with the Samples Reports, and especially
> the Product
> >> Catalog report, which has no parameters, and is not a
> linked report.
> >>
> >> I'm trying to make a subscription on this report, and I
> always get the
> >> classical message shown bellow :
> >>
> >> "Subscriptions cannot be created because the
> >> credentials used to run the report are not stored,the
> report is using
> >> user-defined para values,or if a linked report,the link
> is no longer
> >> work."
> >>
> >> So, as the report as no parameters, and is not a linked
> report, the
> >> problem is with credentials.
> >>
> >> I've changed the AdventureWorks datasource to make the
> credential
> >> stored. The report is generated well.
> >>
> >> But when I want to make a subscription. same message
> again.
> >>
> >> I've tried with the "sa" account !!! Same message.
> >>
> >> Am I wrong in my settings ?
> >>
> >>
> >> Thanks for help.
> >>
> >>
> >> DA
> >
> >
> >.
> >|||Thanks for the reference I was able to figure out what's happening. Now, of
course, I have another question.
Is there any way to allow for subscriptions while using integrated security
on the data source?
jb
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:OLfyslvfEHA.3932@.TK2MSFTNGP09.phx.gbl...
> The user property is a property made available by RS. It contains
> information such as the current user name (user running the report), their
> language, etc. It is used in expression.
> This link should help you:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPORTAL/HTM/rs_gts_portal_3vqd.asp
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "jb" <Jill.Barrett@.accesspointinc.com> wrote in message
> news:044d01c47eec$716543a0$7d02280a@.phx.gbl...
> > I am running into the same problem with my own custom
> > report. What is this 'user property' that is being
> > referred to and what needs to change?
> >
> > Thanks for any assistance!!!
> >
> > >--Original Message--
> > >This report uses the user property which makes it
> > unusable for
> > >subscriptions.
> > >
> > >--
> > >-Daniel
> > >This posting is provided "AS IS" with no warranties, and
> > confers no rights.
> > >
> > >
> > >"Death Angel" <lionel.valero@.scam.fr> wrote in message
> > >news:d114e6a.0407290555.3734e70e@.posting.google.com...
> > >> Hi All,
> > >>
> > >>
> > >> I'm testing RS with the Samples Reports, and especially
> > the Product
> > >> Catalog report, which has no parameters, and is not a
> > linked report.
> > >>
> > >> I'm trying to make a subscription on this report, and I
> > always get the
> > >> classical message shown bellow :
> > >>
> > >> "Subscriptions cannot be created because the
> > >> credentials used to run the report are not stored,the
> > report is using
> > >> user-defined para values,or if a linked report,the link
> > is no longer
> > >> work."
> > >>
> > >> So, as the report as no parameters, and is not a linked
> > report, the
> > >> problem is with credentials.
> > >>
> > >> I've changed the AdventureWorks datasource to make the
> > credential
> > >> stored. The report is generated well.
> > >>
> > >> But when I want to make a subscription. same message
> > again.
> > >>
> > >> I've tried with the "sa" account !!! Same message.
> > >>
> > >> Am I wrong in my settings ?
> > >>
> > >>
> > >> Thanks for help.
> > >>
> > >>
> > >> DA
> > >
> > >
> > >.
> > >
>
I'm testing RS with the Samples Reports, and especially the Product
Catalog report, which has no parameters, and is not a linked report.
I'm trying to make a subscription on this report, and I always get the
classical message shown bellow :
"Subscriptions cannot be created because the
credentials used to run the report are not stored,the report is using
user-defined para values,or if a linked report,the link is no longer
work."
So, as the report as no parameters, and is not a linked report, the
problem is with credentials.
I've changed the AdventureWorks datasource to make the credential
stored. The report is generated well.
But when I want to make a subscription? same message again.
I've tried with the "sa" account !!! Same message.
Am I wrong in my settings ?
Thanks for help.
DAThis report uses the user property which makes it unusable for
subscriptions.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Death Angel" <lionel.valero@.scam.fr> wrote in message
news:d114e6a.0407290555.3734e70e@.posting.google.com...
> Hi All,
>
> I'm testing RS with the Samples Reports, and especially the Product
> Catalog report, which has no parameters, and is not a linked report.
> I'm trying to make a subscription on this report, and I always get the
> classical message shown bellow :
> "Subscriptions cannot be created because the
> credentials used to run the report are not stored,the report is using
> user-defined para values,or if a linked report,the link is no longer
> work."
> So, as the report as no parameters, and is not a linked report, the
> problem is with credentials.
> I've changed the AdventureWorks datasource to make the credential
> stored. The report is generated well.
> But when I want to make a subscription. same message again.
> I've tried with the "sa" account !!! Same message.
> Am I wrong in my settings ?
>
> Thanks for help.
>
> DA|||I am running into the same problem with my own custom
report. What is this 'user property' that is being
referred to and what needs to change?
Thanks for any assistance!!!
>--Original Message--
>This report uses the user property which makes it
unusable for
>subscriptions.
>--
>-Daniel
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"Death Angel" <lionel.valero@.scam.fr> wrote in message
>news:d114e6a.0407290555.3734e70e@.posting.google.com...
>> Hi All,
>>
>> I'm testing RS with the Samples Reports, and especially
the Product
>> Catalog report, which has no parameters, and is not a
linked report.
>> I'm trying to make a subscription on this report, and I
always get the
>> classical message shown bellow :
>> "Subscriptions cannot be created because the
>> credentials used to run the report are not stored,the
report is using
>> user-defined para values,or if a linked report,the link
is no longer
>> work."
>> So, as the report as no parameters, and is not a linked
report, the
>> problem is with credentials.
>> I've changed the AdventureWorks datasource to make the
credential
>> stored. The report is generated well.
>> But when I want to make a subscription. same message
again.
>> I've tried with the "sa" account !!! Same message.
>> Am I wrong in my settings ?
>>
>> Thanks for help.
>>
>> DA
>
>.
>|||The user property is a property made available by RS. It contains
information such as the current user name (user running the report), their
language, etc. It is used in expression.
This link should help you:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPORTAL/HTM/rs_gts_portal_3vqd.asp
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"jb" <Jill.Barrett@.accesspointinc.com> wrote in message
news:044d01c47eec$716543a0$7d02280a@.phx.gbl...
> I am running into the same problem with my own custom
> report. What is this 'user property' that is being
> referred to and what needs to change?
> Thanks for any assistance!!!
> >--Original Message--
> >This report uses the user property which makes it
> unusable for
> >subscriptions.
> >
> >--
> >-Daniel
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> >
> >"Death Angel" <lionel.valero@.scam.fr> wrote in message
> >news:d114e6a.0407290555.3734e70e@.posting.google.com...
> >> Hi All,
> >>
> >>
> >> I'm testing RS with the Samples Reports, and especially
> the Product
> >> Catalog report, which has no parameters, and is not a
> linked report.
> >>
> >> I'm trying to make a subscription on this report, and I
> always get the
> >> classical message shown bellow :
> >>
> >> "Subscriptions cannot be created because the
> >> credentials used to run the report are not stored,the
> report is using
> >> user-defined para values,or if a linked report,the link
> is no longer
> >> work."
> >>
> >> So, as the report as no parameters, and is not a linked
> report, the
> >> problem is with credentials.
> >>
> >> I've changed the AdventureWorks datasource to make the
> credential
> >> stored. The report is generated well.
> >>
> >> But when I want to make a subscription. same message
> again.
> >>
> >> I've tried with the "sa" account !!! Same message.
> >>
> >> Am I wrong in my settings ?
> >>
> >>
> >> Thanks for help.
> >>
> >>
> >> DA
> >
> >
> >.
> >|||Thanks for the reference I was able to figure out what's happening. Now, of
course, I have another question.
Is there any way to allow for subscriptions while using integrated security
on the data source?
jb
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:OLfyslvfEHA.3932@.TK2MSFTNGP09.phx.gbl...
> The user property is a property made available by RS. It contains
> information such as the current user name (user running the report), their
> language, etc. It is used in expression.
> This link should help you:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPORTAL/HTM/rs_gts_portal_3vqd.asp
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "jb" <Jill.Barrett@.accesspointinc.com> wrote in message
> news:044d01c47eec$716543a0$7d02280a@.phx.gbl...
> > I am running into the same problem with my own custom
> > report. What is this 'user property' that is being
> > referred to and what needs to change?
> >
> > Thanks for any assistance!!!
> >
> > >--Original Message--
> > >This report uses the user property which makes it
> > unusable for
> > >subscriptions.
> > >
> > >--
> > >-Daniel
> > >This posting is provided "AS IS" with no warranties, and
> > confers no rights.
> > >
> > >
> > >"Death Angel" <lionel.valero@.scam.fr> wrote in message
> > >news:d114e6a.0407290555.3734e70e@.posting.google.com...
> > >> Hi All,
> > >>
> > >>
> > >> I'm testing RS with the Samples Reports, and especially
> > the Product
> > >> Catalog report, which has no parameters, and is not a
> > linked report.
> > >>
> > >> I'm trying to make a subscription on this report, and I
> > always get the
> > >> classical message shown bellow :
> > >>
> > >> "Subscriptions cannot be created because the
> > >> credentials used to run the report are not stored,the
> > report is using
> > >> user-defined para values,or if a linked report,the link
> > is no longer
> > >> work."
> > >>
> > >> So, as the report as no parameters, and is not a linked
> > report, the
> > >> problem is with credentials.
> > >>
> > >> I've changed the AdventureWorks datasource to make the
> > credential
> > >> stored. The report is generated well.
> > >>
> > >> But when I want to make a subscription. same message
> > again.
> > >>
> > >> I've tried with the "sa" account !!! Same message.
> > >>
> > >> Am I wrong in my settings ?
> > >>
> > >>
> > >> Thanks for help.
> > >>
> > >>
> > >> DA
> > >
> > >
> > >.
> > >
>
Reporting Services Subscription Table DataSettings field cut off at 256 characters!
Does anybody know why when i create a subscription using rs.CreateDataDrivenSubscription, the datasettings that is saved in the subscriptions table is cut off at 256 characters.
can anyone help?
Is the column defined as an ntext? If so then somehow your DB is corrupt. Otherwise, I have never seen this issue before.-Daniel
Reporting Services Subscription Problem
I have a subscription set up in reporting services. When SQL Agent attempts to send out the subscription it reports
the following error:
[298] SQLServer Error: 8198, Could not obtain information about Windows NT group/user 'DOMAIN\MACHINE$'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Where DOMAIN is the domain name and MACHINE is the name of the machine Reporting Service is running on.
Reporting services runs on a separate machine from SQL server. Both machine run Windows 2003 Server. Any suggestion about how to fix this?
--Tom
What is your sql server account running as? You may need to use sql auth to
connect to the report server database. You can use rsconfig.exe to change
this setting.
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"tom777@.newsgroups.nospam" <anonymous@.discussions.microsoft.com> wrote in
message news:DF3DA2CA-BC34-471D-BDB0-E476CA043577@.microsoft.com...
> I have a subscription set up in reporting services. When SQL Agent
attempts to send out the subscription it reports
> the following error:
> [298] SQLServer Error: 8198, Could not obtain information about Windows NT
group/user 'DOMAIN\MACHINE$'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
> Where DOMAIN is the domain name and MACHINE is the name of the machine
Reporting Service is running on.
> Reporting services runs on a separate machine from SQL server. Both
machine run Windows 2003 Server. Any suggestion about how to fix this?
> --Tom
|||Look also to services MSSQLSERVER AND SQLSERVERAGENT... If they have been started with an other profile you can have this problem...when you try to use database Maintenance plan...
the following error:
[298] SQLServer Error: 8198, Could not obtain information about Windows NT group/user 'DOMAIN\MACHINE$'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Where DOMAIN is the domain name and MACHINE is the name of the machine Reporting Service is running on.
Reporting services runs on a separate machine from SQL server. Both machine run Windows 2003 Server. Any suggestion about how to fix this?
--Tom
What is your sql server account running as? You may need to use sql auth to
connect to the report server database. You can use rsconfig.exe to change
this setting.
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"tom777@.newsgroups.nospam" <anonymous@.discussions.microsoft.com> wrote in
message news:DF3DA2CA-BC34-471D-BDB0-E476CA043577@.microsoft.com...
> I have a subscription set up in reporting services. When SQL Agent
attempts to send out the subscription it reports
> the following error:
> [298] SQLServer Error: 8198, Could not obtain information about Windows NT
group/user 'DOMAIN\MACHINE$'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
> Where DOMAIN is the domain name and MACHINE is the name of the machine
Reporting Service is running on.
> Reporting services runs on a separate machine from SQL server. Both
machine run Windows 2003 Server. Any suggestion about how to fix this?
> --Tom
|||Look also to services MSSQLSERVER AND SQLSERVERAGENT... If they have been started with an other profile you can have this problem...when you try to use database Maintenance plan...
Reporting Services Subscription Problem
I have a subscription set up in reporting services. When SQL Agent attempts
to send out the subscription it reports
the following error:
[298] SQLServer Error: 8198, Could not obtain information about Windows
NT group/user 'DOMAIN\MACHINE$'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Where DOMAIN is the domain name and MACHINE is the name of the machine Repor
ting Service is running on.
Reporting services runs on a separate machine from SQL server. Both machine
run Windows 2003 Server. Any suggestion about how to fix this?
--TomWhat is your sql server account running as? You may need to use sql auth to
connect to the report server database. You can use rsconfig.exe to change
this setting.
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"tom777@.newsgroups.nospam" <anonymous@.discussions.microsoft.com> wrote in
message news:DF3DA2CA-BC34-471D-BDB0-E476CA043577@.microsoft.com...
> I have a subscription set up in reporting services. When SQL Agent
attempts to send out the subscription it reports
> the following error:
> [298] SQLServer Error: 8198, Could not obtain information about Windows NT[/co
lor]
group/user 'DOMAIN\MACHINE$'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
> Where DOMAIN is the domain name and MACHINE is the name of the machine
Reporting Service is running on.
> Reporting services runs on a separate machine from SQL server. Both
machine run Windows 2003 Server. Any suggestion about how to fix this?
> --Tom|||Look also to services MSSQLSERVER AND SQLSERVERAGENT... If they have been s
tarted with an other profile you can have this problem...when you try to use
database Maintenance plan...
to send out the subscription it reports
the following error:
[298] SQLServer Error: 8198, Could not obtain information about Windows
NT group/user 'DOMAIN\MACHINE$'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Where DOMAIN is the domain name and MACHINE is the name of the machine Repor
ting Service is running on.
Reporting services runs on a separate machine from SQL server. Both machine
run Windows 2003 Server. Any suggestion about how to fix this?
--TomWhat is your sql server account running as? You may need to use sql auth to
connect to the report server database. You can use rsconfig.exe to change
this setting.
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"tom777@.newsgroups.nospam" <anonymous@.discussions.microsoft.com> wrote in
message news:DF3DA2CA-BC34-471D-BDB0-E476CA043577@.microsoft.com...
> I have a subscription set up in reporting services. When SQL Agent
attempts to send out the subscription it reports
> the following error:
> [298] SQLServer Error: 8198, Could not obtain information about Windows NT[/co
lor]
group/user 'DOMAIN\MACHINE$'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
> Where DOMAIN is the domain name and MACHINE is the name of the machine
Reporting Service is running on.
> Reporting services runs on a separate machine from SQL server. Both
machine run Windows 2003 Server. Any suggestion about how to fix this?
> --Tom|||Look also to services MSSQLSERVER AND SQLSERVERAGENT... If they have been s
tarted with an other profile you can have this problem...when you try to use
database Maintenance plan...
Reporting Services subscription
Hi,
Is it possible to create new subscription for reporting services in sql 2005
standard edition? I get New subscription and New data driven subscription
greyed out.
GSMy understanding is that Data Driven Subscriptions are available only
in Enterprise edition (and up).sql
Is it possible to create new subscription for reporting services in sql 2005
standard edition? I get New subscription and New data driven subscription
greyed out.
GSMy understanding is that Data Driven Subscriptions are available only
in Enterprise edition (and up).sql
Reporting services Subreport
Hi,
Can I show my subreport under the "master" reports calling column.
I also want to show my subreport in a new window.
Please help me!the subreport in New window is called Linked report
Just create another report and Link it on Navigation Tab for specific
Column. check Jump to Report.
In order to open it in new window you need to put LinkTarget="_blank"
in the URl for the first report.
Mary wrote:
> *Hi,
> Can I show my subreport under the "master" reports calling column.
> I also want to show my subreport in a new window.
> Please help me! *
scorpion509
---
Posted via http://www.codecomments.com
---
Can I show my subreport under the "master" reports calling column.
I also want to show my subreport in a new window.
Please help me!the subreport in New window is called Linked report
Just create another report and Link it on Navigation Tab for specific
Column. check Jump to Report.
In order to open it in new window you need to put LinkTarget="_blank"
in the URl for the first report.
Mary wrote:
> *Hi,
> Can I show my subreport under the "master" reports calling column.
> I also want to show my subreport in a new window.
> Please help me! *
scorpion509
---
Posted via http://www.codecomments.com
---
reporting services stored procedure help
Hi all,
I need help using stored procedures in Reporting Services. I have a
dataset set to type stored procedure and when I run the sp it doesn't
return all the data that it should. When I run it in query analyzer it
returns approxiamately 4500 rows, but in RS it only returns around
1000. Is there a maximum number of rows that RS can return? Does
anyone have any idea what the problem could be? I've looked everywhere
and can't seem to find any answers.
Thanks in advance.
MissyAre you sure you are running the same sproc? from the same environement? do a
test by adding a top 10 to your sproc and check the results.|||Kyriakos wrote:
> Are you sure you are running the same sproc? from the same environement? do a
> test by adding a top 10 to your sproc and check the results.
The results are correct that I am getting, I am just not getting all
the results. I am also having trouble using the parameters with the
sproc. I am using temp tables in my sproc, would that mess everything
up?|||There is not a maximum number of rows (if rendering in PDF or Excel there
might be a practical limit, Excel has a limit of 65K).
First, no problem with temp tables. I use them all the time
One thing that could be happening is an artifact of the development
environment. If you don't change the values of the parameter (at least as
far as the preview tab, I'm not sure about the dataset tab) it uses data
that it caches. Look in the directory where you .rdl files are. You will see
a file called filename.rdl.data. You can delete the file and make sure that
it is requerying the database.
You mentioned that you are having trouble with passing parameters. You can
hard code the parameters. You can either click on ..., parameters tab, set
the mapping to an expression, and put in the value. OR you can do this:
pr_myprocedurename 'somevalue',1.0,'some'
I.e. you can put exactly the same in as you do in query analyzer. In this
way you can make sure that it isn't a parameter mapping/passing problem.
Doing this you might want to switch to generic query design mode. There is a
button to the right of the ... that allows you to do this.
And, you might have to switch from stored procedure to text to hard code the
parameters.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156427092.932270.196010@.75g2000cwc.googlegroups.com...
> Hi all,
> I need help using stored procedures in Reporting Services. I have a
> dataset set to type stored procedure and when I run the sp it doesn't
> return all the data that it should. When I run it in query analyzer it
> returns approxiamately 4500 rows, but in RS it only returns around
> 1000. Is there a maximum number of rows that RS can return? Does
> anyone have any idea what the problem could be? I've looked everywhere
> and can't seem to find any answers.
> Thanks in advance.
> Missy
>|||Thanks Bruce. In my sproc do I need to specify the parameters after I
create the sproc and before the if and select statements? If so, when I
try to run the sproc in RS it still is only pulling the records that
have a district (one of my fields) name what begins with the letter A.
So it is missing tons of records. Any ideas? Thanks for your help.
Missy|||Does your stored procedure have parameters? Is RS recognizing the
parameters? When you execute from the dataset you should be prompted to fill
in the query parameters.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156435417.812409.21310@.m79g2000cwm.googlegroups.com...
> Thanks Bruce. In my sproc do I need to specify the parameters after I
> create the sproc and before the if and select statements? If so, when I
> try to run the sproc in RS it still is only pulling the records that
> have a district (one of my fields) name what begins with the letter A.
> So it is missing tons of records. Any ideas? Thanks for your help.
> Missy
>|||yes it is recognizing the parameters in my sproc. I'm going to post my
sproc, this is my first time ever using a sproc, so I could have it all
wrong. :)
CREATE PROCEDURE [dbo].[meterquery]
@.district char
as
BEGIN
IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
BEGIN
DROP TABLE #TmpTable1
END
IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
BEGIN
DROP TABLE #ZonesInDelSys
END
IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
BEGIN
DROP TABLE #DelSysHier
END
IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
BEGIN
DROP TABLE #MetersInDelSys
END
IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
BEGIN
DROP TABLE #TempAttr
END
/******************* BEGIN CREATE TABLES **************************/
SELECT Root.DelSysHID AS DelSysHID
, H.HdrCode AS DelSysCode
, H.HdrName AS DelSysName
, CfgHist.DelSysTID
, Mp.MeasPtHID AS ZoneID
INTO #TmpTable1
FROM pdMasDelSys Root (NOLOCK)
INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
Root.DelSysTID = CfgHist.DelSysTID
LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
CfgHist.DsConfigTID = Mp.DsConfigTID
LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID =Mp1.HdrHID
LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID =Typ1.HdrTypeTID
WHERE 1=0
CREATE CLUSTERED INDEX IX1_#TmpTable1 ON
#TmpTable1(DelSysTID,DelSysHID,DelSysCode,DelSysName)
SELECT DISTINCT
ZR.District
, ZR.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, ZR.ZoneID
, ZR.ZoneName
, ZR.PropertyNumber
, ZR.Area
, ZR.Route
, ZR.Operator
INTO #ZonesInDelSys
FROM productioncache..cache_zonerelations ZR
INNER JOIN #TmpTable1 T ON T.ZoneID = ZR.ZoneID
WHERE 1=0
--select * from #ZonesInDelSys
SELECT DISTINCT DelSysID, District, FieldOffice
INTO #DelSysHier
FROM #ZonesInDelSys
WHERE 1=0
CREATE CLUSTERED INDEX IX1_#DelSysHier ON #DelSysHier (DelSysID,
District, FieldOffice)
SELECT ZD.District
, ZD.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, T.ZoneID
, R.MpDestHID AS GasMeterHID
, H.HdrCode as MeterCode
, HT.HdrTypeName as MeterType
, HA.hdrattrcode as MeterAttr
INTO #MetersInDelSys
FROM #TmpTable1 T (TABLOCKX)
INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
T.DelSysTID = R.DsConfigTID
INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID =T.DelSysHID
INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
AND H.HdrTypeTID=150005
INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
H.HdrTypeTID=HT.HdrTypeTID
INNER JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
HA.HdrHID=R.MpDestHID
WHERE 1=0
SELECT District
, FieldOffice
, DelSysID
, DelSysCode
, DelSysName
, GasMeterHID
, MeterCode
, HA.hdrattrcode
INTO #TempAttr
FROM #MetersInDelSys A
INNER JOIN chk..fbmashdrattribute ha (NOLOCK) ON
ha.HdrHID=A.GasMeterHID
WHERE 1=0
/************* END CREATE TABLES ***********************************/
INSERT INTO #TmpTable1 WITH (TABLOCKX)
SELECT Root.DelSysHID AS DelSysHID
, H.HdrCode AS DelSysCode
, H.HdrName AS DelSysName
, CfgHist.DelSysTID
, Mp.MeasPtHID AS ZoneID
FROM pdMasDelSys Root (NOLOCK)
INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
Root.DelSysTID = CfgHist.DelSysTID
LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
CfgHist.DsConfigTID = Mp.DsConfigTID
LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID = Mp1.HdrHID
LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID =Typ1.HdrTypeTID
WHERE 1=1
AND (Typ1.HdrTypeCode = 'Zone')
AND (H.HdrInactive = 0)
AND (Mp1.HdrInactive = 0)
AND (CfgHist.DsConfigEffEnd = '12/31/2078')
AND CfgHist.DsObsolete=0
--and root.DelSysHID in(1754810,1711525)
GROUP BY Root.DelSysHID, H.HdrCode, H.HdrName, Mp.MeasPtHID,
CfgHist.DelSysTID
ORDER BY H.HdrName
--Zones in DelSys
INSERT INTO #ZonesInDelSys WITH (TABLOCKX)
SELECT DISTINCT
ZR.District
, ZR.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, ZR.ZoneID
, ZR.ZoneName
, ZR.PropertyNumber
, ZR.Area
, ZR.Route
, ZR.Operator
FROM productioncache..cache_zonerelations ZR (NOLOCK)
INNER JOIN #TmpTable1 T (TABLOCKX) ON T.ZoneID = ZR.ZoneID
WHERE getdate() BETWEEN ZR.OperationBeginDate and ZR.OperationEndDate
--DelSys Enertia Hierarchy
INSERT INTO #DelSysHier WITH (TABLOCKX)
SELECT DISTINCT
DelSysID
, District
, FieldOffice
FROM #ZonesInDelSys (TABLOCKX)
truncate table #MetersInDelSys
--Gas Meters in DelSys
INSERT INTO #MetersInDelSys WITH (TABLOCKX)
SELECT DISTINCT
ZD.District
, ZD.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, T.ZoneID
, R.MpDestHID AS GasMeterHID
, H.HdrCode as MeterCode
, HT.HdrTypeName as MeterType
, HA.hdrattrcode as MeterAttr
FROM #TmpTable1 T (TABLOCKX)
INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
T.DelSysTID = R.DsConfigTID
INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID =T.DelSysHID
INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
AND H.HdrTypeTID=150005
INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
H.HdrTypeTID=HT.HdrTypeTID
LEFT JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
HA.HdrHID=R.MpDestHID
WHERE HA.HdrAttrCode in
('Transp','Fuel','Alloc','Check','Custody','Custdy'
,'ChCalc','Irrig','Deduct')
INSERT INTO #TempAttr WITH (TABLOCKX)
SELECT District
, FieldOffice
, DelSysID
, DelSysCode
, DelSysName
, GasMeterHID
, MeterCode
, HA.hdrattrcode
FROM #MetersInDelSys A (TABLOCKX)
INNER JOIN chk..fbMasHdrAttribute HA (NOLOCK) ON
HA.HdrHID=A.GasMeterHID
WHERE HA.HdrAttrCode in
('Transp','Fuel','Alloc','Check','Custody','Custdy'
,'ChCalc','Irrig','Deduct')
--SELECT where only 1 meter in DelSys
SELECT A.District, @.district as districtname
, A.FieldOffice
, A.DelSysID
, A.DelSysCode
, A.DelSysName
, A.ZoneID
, HZ.HdrName
, A.GasMeterHID
, HG.HdrName as MeterName
, A.MeterCode
, A.MeterType
, A.MeterAttr
FROM #MetersInDelSys A (TABLOCKX)
INNER JOIN CHK..fbMasHdr HZ (NOLOCK) ON HZ.HdrHID=A.ZoneID
INNER JOIN CHK..fbMasHdr HG (NOLOCK) ON
HG.HdrHID=A.GasMeterHID
INNER JOIN (SELECT DelSysID
, count(*) as num
FROM #MetersInDelSys
GROUP BY DelSysID
HAVING count(*) = 1) G1 ON
G1.DelSysID=A.DelSysID
where @.district=a.district
/**************** CLEAN UP TEMP TABLES**********************/
IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
BEGIN
DROP TABLE #TmpTable1
END
IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
BEGIN
DROP TABLE #ZonesInDelSys
END
IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
BEGIN
DROP TABLE #DelSysHier
END
IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
BEGIN
DROP TABLE #MetersInDelSys
END
IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
BEGIN
DROP TABLE #TempAttr
END
END
Then inside RS I select a dataset and set the command to stored
procedure. I then go to the layout tab and specify a parameter called
district and it pulls from the sproc query district name. if that
makes sense...|||Several things are problematic. First, my guess is you first wrote this in
query analyzer and then turned it into a stored procedure. SQL Server cleans
up for you. You never ever have to drop temp tables manually. Let SQL Server
handle that. Remove all of that at the beginning of your stored procedure.
Next, do not manually drop your temp tables at the end of your stored
procedure. That can definitely cause problems with RS. You should just let
it fall out of scope and let SQL Server manage the temp tables lifespan.
So, the drop tables at the beginning are just unnecessary. The drop tables
at the end are dangerous (from the perspective of Reporting Services).
Remove every single drop table and see what happens. The rest of it looks
good.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156437458.749343.239350@.m79g2000cwm.googlegroups.com...
> yes it is recognizing the parameters in my sproc. I'm going to post my
> sproc, this is my first time ever using a sproc, so I could have it all
> wrong. :)
>
> CREATE PROCEDURE [dbo].[meterquery]
> @.district char
> as
> BEGIN
> IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
> BEGIN
> DROP TABLE #TmpTable1
> END
>
> IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #ZonesInDelSys
> END
>
> IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
> BEGIN
> DROP TABLE #DelSysHier
> END
>
> IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #MetersInDelSys
> END
>
> IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
> BEGIN
> DROP TABLE #TempAttr
> END
> /******************* BEGIN CREATE TABLES **************************/
> SELECT Root.DelSysHID AS DelSysHID
> , H.HdrCode AS DelSysCode
> , H.HdrName AS DelSysName
> , CfgHist.DelSysTID
> , Mp.MeasPtHID AS ZoneID
> INTO #TmpTable1
> FROM pdMasDelSys Root (NOLOCK)
> INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
> LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
> Root.DelSysTID = CfgHist.DelSysTID
> LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
> CfgHist.DsConfigTID = Mp.DsConfigTID
> LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID => Mp1.HdrHID
> LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID => Typ1.HdrTypeTID
> WHERE 1=0
>
> CREATE CLUSTERED INDEX IX1_#TmpTable1 ON
> #TmpTable1(DelSysTID,DelSysHID,DelSysCode,DelSysName)
>
> SELECT DISTINCT
> ZR.District
> , ZR.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , ZR.ZoneID
> , ZR.ZoneName
> , ZR.PropertyNumber
> , ZR.Area
> , ZR.Route
> , ZR.Operator
> INTO #ZonesInDelSys
> FROM productioncache..cache_zonerelations ZR
> INNER JOIN #TmpTable1 T ON T.ZoneID = ZR.ZoneID
> WHERE 1=0
> --select * from #ZonesInDelSys
> SELECT DISTINCT DelSysID, District, FieldOffice
> INTO #DelSysHier
> FROM #ZonesInDelSys
> WHERE 1=0
>
> CREATE CLUSTERED INDEX IX1_#DelSysHier ON #DelSysHier (DelSysID,
> District, FieldOffice)
>
> SELECT ZD.District
> , ZD.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , T.ZoneID
> , R.MpDestHID AS GasMeterHID
> , H.HdrCode as MeterCode
> , HT.HdrTypeName as MeterType
> , HA.hdrattrcode as MeterAttr
> INTO #MetersInDelSys
> FROM #TmpTable1 T (TABLOCKX)
> INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
> T.DelSysTID = R.DsConfigTID
> INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID => T.DelSysHID
> INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
> AND H.HdrTypeTID=150005
> INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
> H.HdrTypeTID=HT.HdrTypeTID
> INNER JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
> HA.HdrHID=R.MpDestHID
> WHERE 1=0
>
> SELECT District
> , FieldOffice
> , DelSysID
> , DelSysCode
> , DelSysName
> , GasMeterHID
> , MeterCode
> , HA.hdrattrcode
> INTO #TempAttr
> FROM #MetersInDelSys A
> INNER JOIN chk..fbmashdrattribute ha (NOLOCK) ON
> ha.HdrHID=A.GasMeterHID
> WHERE 1=0
> /************* END CREATE TABLES ***********************************/
> INSERT INTO #TmpTable1 WITH (TABLOCKX)
> SELECT Root.DelSysHID AS DelSysHID
> , H.HdrCode AS DelSysCode
> , H.HdrName AS DelSysName
> , CfgHist.DelSysTID
> , Mp.MeasPtHID AS ZoneID
> FROM pdMasDelSys Root (NOLOCK)
> INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
> LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
> Root.DelSysTID = CfgHist.DelSysTID
> LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
> CfgHist.DsConfigTID = Mp.DsConfigTID
> LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID = Mp1.HdrHID
> LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID => Typ1.HdrTypeTID
> WHERE 1=1
> AND (Typ1.HdrTypeCode = 'Zone')
> AND (H.HdrInactive = 0)
> AND (Mp1.HdrInactive = 0)
> AND (CfgHist.DsConfigEffEnd = '12/31/2078')
> AND CfgHist.DsObsolete=0
> --and root.DelSysHID in(1754810,1711525)
> GROUP BY Root.DelSysHID, H.HdrCode, H.HdrName, Mp.MeasPtHID,
> CfgHist.DelSysTID
> ORDER BY H.HdrName
>
> --Zones in DelSys
> INSERT INTO #ZonesInDelSys WITH (TABLOCKX)
> SELECT DISTINCT
> ZR.District
> , ZR.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , ZR.ZoneID
> , ZR.ZoneName
> , ZR.PropertyNumber
> , ZR.Area
> , ZR.Route
> , ZR.Operator
> FROM productioncache..cache_zonerelations ZR (NOLOCK)
> INNER JOIN #TmpTable1 T (TABLOCKX) ON T.ZoneID = ZR.ZoneID
> WHERE getdate() BETWEEN ZR.OperationBeginDate and ZR.OperationEndDate
>
> --DelSys Enertia Hierarchy
> INSERT INTO #DelSysHier WITH (TABLOCKX)
> SELECT DISTINCT
> DelSysID
> , District
> , FieldOffice
> FROM #ZonesInDelSys (TABLOCKX)
> truncate table #MetersInDelSys
> --Gas Meters in DelSys
> INSERT INTO #MetersInDelSys WITH (TABLOCKX)
> SELECT DISTINCT
> ZD.District
> , ZD.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , T.ZoneID
> , R.MpDestHID AS GasMeterHID
> , H.HdrCode as MeterCode
> , HT.HdrTypeName as MeterType
> , HA.hdrattrcode as MeterAttr
> FROM #TmpTable1 T (TABLOCKX)
> INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
> T.DelSysTID = R.DsConfigTID
> INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID => T.DelSysHID
> INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
> AND H.HdrTypeTID=150005
> INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
> H.HdrTypeTID=HT.HdrTypeTID
> LEFT JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
> HA.HdrHID=R.MpDestHID
> WHERE HA.HdrAttrCode in
> ('Transp','Fuel','Alloc','Check','Custody','Custdy'
> ,'ChCalc','Irrig','Deduct')
>
> INSERT INTO #TempAttr WITH (TABLOCKX)
> SELECT District
> , FieldOffice
> , DelSysID
> , DelSysCode
> , DelSysName
> , GasMeterHID
> , MeterCode
> , HA.hdrattrcode
> FROM #MetersInDelSys A (TABLOCKX)
> INNER JOIN chk..fbMasHdrAttribute HA (NOLOCK) ON
> HA.HdrHID=A.GasMeterHID
> WHERE HA.HdrAttrCode in
> ('Transp','Fuel','Alloc','Check','Custody','Custdy'
> ,'ChCalc','Irrig','Deduct')
>
>
> --SELECT where only 1 meter in DelSys
> SELECT A.District, @.district as districtname
> , A.FieldOffice
> , A.DelSysID
> , A.DelSysCode
> , A.DelSysName
> , A.ZoneID
> , HZ.HdrName
> , A.GasMeterHID
> , HG.HdrName as MeterName
> , A.MeterCode
> , A.MeterType
> , A.MeterAttr
> FROM #MetersInDelSys A (TABLOCKX)
> INNER JOIN CHK..fbMasHdr HZ (NOLOCK) ON HZ.HdrHID=A.ZoneID
> INNER JOIN CHK..fbMasHdr HG (NOLOCK) ON
> HG.HdrHID=A.GasMeterHID
> INNER JOIN (SELECT DelSysID
> , count(*) as num
> FROM #MetersInDelSys
> GROUP BY DelSysID
> HAVING count(*) = 1) G1 ON
> G1.DelSysID=A.DelSysID
> where @.district=a.district
>
> /**************** CLEAN UP TEMP TABLES**********************/
> IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
> BEGIN
> DROP TABLE #TmpTable1
> END
>
> IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #ZonesInDelSys
> END
>
> IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
> BEGIN
> DROP TABLE #DelSysHier
> END
>
> IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #MetersInDelSys
> END
>
> IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
> BEGIN
> DROP TABLE #TempAttr
> END
> END
>
> Then inside RS I select a dataset and set the command to stored
> procedure. I then go to the layout tab and specify a parameter called
> district and it pulls from the sproc query district name. if that
> makes sense...
>|||It's working yay!!! The only thing I have left to do is figure out the
report parameters. Do I need to specify them in my sproc? Or can I
create them through RS?
Thanks so much!|||Your stored procedure has a parameter. This is the query parameter. If you
are using SQL Server then RS recognizes the parameter and creates a
corresponding report parameter and maps to it. So when you create a dataset
based on the stored procedure you will then have a report parameter called
district created. I notice you had a parameter question in another posting.
I suggest starting with a new report so you don't have problems left over
from before.
Your query parameters have this mapping occur automatically but you can also
change the mappng to an expression by clicking on the ..., parameters tab.
Then if the report parameter is not needed you go to the layout tab, report
menu->report parameters and delete the now unused parameter (this is not
necessary in your case).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156441135.234902.131430@.i3g2000cwc.googlegroups.com...
> It's working yay!!! The only thing I have left to do is figure out the
> report parameters. Do I need to specify them in my sproc? Or can I
> create them through RS?
> Thanks so much!
>
I need help using stored procedures in Reporting Services. I have a
dataset set to type stored procedure and when I run the sp it doesn't
return all the data that it should. When I run it in query analyzer it
returns approxiamately 4500 rows, but in RS it only returns around
1000. Is there a maximum number of rows that RS can return? Does
anyone have any idea what the problem could be? I've looked everywhere
and can't seem to find any answers.
Thanks in advance.
MissyAre you sure you are running the same sproc? from the same environement? do a
test by adding a top 10 to your sproc and check the results.|||Kyriakos wrote:
> Are you sure you are running the same sproc? from the same environement? do a
> test by adding a top 10 to your sproc and check the results.
The results are correct that I am getting, I am just not getting all
the results. I am also having trouble using the parameters with the
sproc. I am using temp tables in my sproc, would that mess everything
up?|||There is not a maximum number of rows (if rendering in PDF or Excel there
might be a practical limit, Excel has a limit of 65K).
First, no problem with temp tables. I use them all the time
One thing that could be happening is an artifact of the development
environment. If you don't change the values of the parameter (at least as
far as the preview tab, I'm not sure about the dataset tab) it uses data
that it caches. Look in the directory where you .rdl files are. You will see
a file called filename.rdl.data. You can delete the file and make sure that
it is requerying the database.
You mentioned that you are having trouble with passing parameters. You can
hard code the parameters. You can either click on ..., parameters tab, set
the mapping to an expression, and put in the value. OR you can do this:
pr_myprocedurename 'somevalue',1.0,'some'
I.e. you can put exactly the same in as you do in query analyzer. In this
way you can make sure that it isn't a parameter mapping/passing problem.
Doing this you might want to switch to generic query design mode. There is a
button to the right of the ... that allows you to do this.
And, you might have to switch from stored procedure to text to hard code the
parameters.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156427092.932270.196010@.75g2000cwc.googlegroups.com...
> Hi all,
> I need help using stored procedures in Reporting Services. I have a
> dataset set to type stored procedure and when I run the sp it doesn't
> return all the data that it should. When I run it in query analyzer it
> returns approxiamately 4500 rows, but in RS it only returns around
> 1000. Is there a maximum number of rows that RS can return? Does
> anyone have any idea what the problem could be? I've looked everywhere
> and can't seem to find any answers.
> Thanks in advance.
> Missy
>|||Thanks Bruce. In my sproc do I need to specify the parameters after I
create the sproc and before the if and select statements? If so, when I
try to run the sproc in RS it still is only pulling the records that
have a district (one of my fields) name what begins with the letter A.
So it is missing tons of records. Any ideas? Thanks for your help.
Missy|||Does your stored procedure have parameters? Is RS recognizing the
parameters? When you execute from the dataset you should be prompted to fill
in the query parameters.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156435417.812409.21310@.m79g2000cwm.googlegroups.com...
> Thanks Bruce. In my sproc do I need to specify the parameters after I
> create the sproc and before the if and select statements? If so, when I
> try to run the sproc in RS it still is only pulling the records that
> have a district (one of my fields) name what begins with the letter A.
> So it is missing tons of records. Any ideas? Thanks for your help.
> Missy
>|||yes it is recognizing the parameters in my sproc. I'm going to post my
sproc, this is my first time ever using a sproc, so I could have it all
wrong. :)
CREATE PROCEDURE [dbo].[meterquery]
@.district char
as
BEGIN
IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
BEGIN
DROP TABLE #TmpTable1
END
IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
BEGIN
DROP TABLE #ZonesInDelSys
END
IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
BEGIN
DROP TABLE #DelSysHier
END
IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
BEGIN
DROP TABLE #MetersInDelSys
END
IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
BEGIN
DROP TABLE #TempAttr
END
/******************* BEGIN CREATE TABLES **************************/
SELECT Root.DelSysHID AS DelSysHID
, H.HdrCode AS DelSysCode
, H.HdrName AS DelSysName
, CfgHist.DelSysTID
, Mp.MeasPtHID AS ZoneID
INTO #TmpTable1
FROM pdMasDelSys Root (NOLOCK)
INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
Root.DelSysTID = CfgHist.DelSysTID
LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
CfgHist.DsConfigTID = Mp.DsConfigTID
LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID =Mp1.HdrHID
LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID =Typ1.HdrTypeTID
WHERE 1=0
CREATE CLUSTERED INDEX IX1_#TmpTable1 ON
#TmpTable1(DelSysTID,DelSysHID,DelSysCode,DelSysName)
SELECT DISTINCT
ZR.District
, ZR.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, ZR.ZoneID
, ZR.ZoneName
, ZR.PropertyNumber
, ZR.Area
, ZR.Route
, ZR.Operator
INTO #ZonesInDelSys
FROM productioncache..cache_zonerelations ZR
INNER JOIN #TmpTable1 T ON T.ZoneID = ZR.ZoneID
WHERE 1=0
--select * from #ZonesInDelSys
SELECT DISTINCT DelSysID, District, FieldOffice
INTO #DelSysHier
FROM #ZonesInDelSys
WHERE 1=0
CREATE CLUSTERED INDEX IX1_#DelSysHier ON #DelSysHier (DelSysID,
District, FieldOffice)
SELECT ZD.District
, ZD.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, T.ZoneID
, R.MpDestHID AS GasMeterHID
, H.HdrCode as MeterCode
, HT.HdrTypeName as MeterType
, HA.hdrattrcode as MeterAttr
INTO #MetersInDelSys
FROM #TmpTable1 T (TABLOCKX)
INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
T.DelSysTID = R.DsConfigTID
INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID =T.DelSysHID
INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
AND H.HdrTypeTID=150005
INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
H.HdrTypeTID=HT.HdrTypeTID
INNER JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
HA.HdrHID=R.MpDestHID
WHERE 1=0
SELECT District
, FieldOffice
, DelSysID
, DelSysCode
, DelSysName
, GasMeterHID
, MeterCode
, HA.hdrattrcode
INTO #TempAttr
FROM #MetersInDelSys A
INNER JOIN chk..fbmashdrattribute ha (NOLOCK) ON
ha.HdrHID=A.GasMeterHID
WHERE 1=0
/************* END CREATE TABLES ***********************************/
INSERT INTO #TmpTable1 WITH (TABLOCKX)
SELECT Root.DelSysHID AS DelSysHID
, H.HdrCode AS DelSysCode
, H.HdrName AS DelSysName
, CfgHist.DelSysTID
, Mp.MeasPtHID AS ZoneID
FROM pdMasDelSys Root (NOLOCK)
INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
Root.DelSysTID = CfgHist.DelSysTID
LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
CfgHist.DsConfigTID = Mp.DsConfigTID
LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID = Mp1.HdrHID
LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID =Typ1.HdrTypeTID
WHERE 1=1
AND (Typ1.HdrTypeCode = 'Zone')
AND (H.HdrInactive = 0)
AND (Mp1.HdrInactive = 0)
AND (CfgHist.DsConfigEffEnd = '12/31/2078')
AND CfgHist.DsObsolete=0
--and root.DelSysHID in(1754810,1711525)
GROUP BY Root.DelSysHID, H.HdrCode, H.HdrName, Mp.MeasPtHID,
CfgHist.DelSysTID
ORDER BY H.HdrName
--Zones in DelSys
INSERT INTO #ZonesInDelSys WITH (TABLOCKX)
SELECT DISTINCT
ZR.District
, ZR.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, ZR.ZoneID
, ZR.ZoneName
, ZR.PropertyNumber
, ZR.Area
, ZR.Route
, ZR.Operator
FROM productioncache..cache_zonerelations ZR (NOLOCK)
INNER JOIN #TmpTable1 T (TABLOCKX) ON T.ZoneID = ZR.ZoneID
WHERE getdate() BETWEEN ZR.OperationBeginDate and ZR.OperationEndDate
--DelSys Enertia Hierarchy
INSERT INTO #DelSysHier WITH (TABLOCKX)
SELECT DISTINCT
DelSysID
, District
, FieldOffice
FROM #ZonesInDelSys (TABLOCKX)
truncate table #MetersInDelSys
--Gas Meters in DelSys
INSERT INTO #MetersInDelSys WITH (TABLOCKX)
SELECT DISTINCT
ZD.District
, ZD.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, T.ZoneID
, R.MpDestHID AS GasMeterHID
, H.HdrCode as MeterCode
, HT.HdrTypeName as MeterType
, HA.hdrattrcode as MeterAttr
FROM #TmpTable1 T (TABLOCKX)
INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
T.DelSysTID = R.DsConfigTID
INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID =T.DelSysHID
INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
AND H.HdrTypeTID=150005
INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
H.HdrTypeTID=HT.HdrTypeTID
LEFT JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
HA.HdrHID=R.MpDestHID
WHERE HA.HdrAttrCode in
('Transp','Fuel','Alloc','Check','Custody','Custdy'
,'ChCalc','Irrig','Deduct')
INSERT INTO #TempAttr WITH (TABLOCKX)
SELECT District
, FieldOffice
, DelSysID
, DelSysCode
, DelSysName
, GasMeterHID
, MeterCode
, HA.hdrattrcode
FROM #MetersInDelSys A (TABLOCKX)
INNER JOIN chk..fbMasHdrAttribute HA (NOLOCK) ON
HA.HdrHID=A.GasMeterHID
WHERE HA.HdrAttrCode in
('Transp','Fuel','Alloc','Check','Custody','Custdy'
,'ChCalc','Irrig','Deduct')
--SELECT where only 1 meter in DelSys
SELECT A.District, @.district as districtname
, A.FieldOffice
, A.DelSysID
, A.DelSysCode
, A.DelSysName
, A.ZoneID
, HZ.HdrName
, A.GasMeterHID
, HG.HdrName as MeterName
, A.MeterCode
, A.MeterType
, A.MeterAttr
FROM #MetersInDelSys A (TABLOCKX)
INNER JOIN CHK..fbMasHdr HZ (NOLOCK) ON HZ.HdrHID=A.ZoneID
INNER JOIN CHK..fbMasHdr HG (NOLOCK) ON
HG.HdrHID=A.GasMeterHID
INNER JOIN (SELECT DelSysID
, count(*) as num
FROM #MetersInDelSys
GROUP BY DelSysID
HAVING count(*) = 1) G1 ON
G1.DelSysID=A.DelSysID
where @.district=a.district
/**************** CLEAN UP TEMP TABLES**********************/
IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
BEGIN
DROP TABLE #TmpTable1
END
IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
BEGIN
DROP TABLE #ZonesInDelSys
END
IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
BEGIN
DROP TABLE #DelSysHier
END
IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
BEGIN
DROP TABLE #MetersInDelSys
END
IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
BEGIN
DROP TABLE #TempAttr
END
END
Then inside RS I select a dataset and set the command to stored
procedure. I then go to the layout tab and specify a parameter called
district and it pulls from the sproc query district name. if that
makes sense...|||Several things are problematic. First, my guess is you first wrote this in
query analyzer and then turned it into a stored procedure. SQL Server cleans
up for you. You never ever have to drop temp tables manually. Let SQL Server
handle that. Remove all of that at the beginning of your stored procedure.
Next, do not manually drop your temp tables at the end of your stored
procedure. That can definitely cause problems with RS. You should just let
it fall out of scope and let SQL Server manage the temp tables lifespan.
So, the drop tables at the beginning are just unnecessary. The drop tables
at the end are dangerous (from the perspective of Reporting Services).
Remove every single drop table and see what happens. The rest of it looks
good.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156437458.749343.239350@.m79g2000cwm.googlegroups.com...
> yes it is recognizing the parameters in my sproc. I'm going to post my
> sproc, this is my first time ever using a sproc, so I could have it all
> wrong. :)
>
> CREATE PROCEDURE [dbo].[meterquery]
> @.district char
> as
> BEGIN
> IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
> BEGIN
> DROP TABLE #TmpTable1
> END
>
> IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #ZonesInDelSys
> END
>
> IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
> BEGIN
> DROP TABLE #DelSysHier
> END
>
> IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #MetersInDelSys
> END
>
> IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
> BEGIN
> DROP TABLE #TempAttr
> END
> /******************* BEGIN CREATE TABLES **************************/
> SELECT Root.DelSysHID AS DelSysHID
> , H.HdrCode AS DelSysCode
> , H.HdrName AS DelSysName
> , CfgHist.DelSysTID
> , Mp.MeasPtHID AS ZoneID
> INTO #TmpTable1
> FROM pdMasDelSys Root (NOLOCK)
> INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
> LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
> Root.DelSysTID = CfgHist.DelSysTID
> LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
> CfgHist.DsConfigTID = Mp.DsConfigTID
> LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID => Mp1.HdrHID
> LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID => Typ1.HdrTypeTID
> WHERE 1=0
>
> CREATE CLUSTERED INDEX IX1_#TmpTable1 ON
> #TmpTable1(DelSysTID,DelSysHID,DelSysCode,DelSysName)
>
> SELECT DISTINCT
> ZR.District
> , ZR.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , ZR.ZoneID
> , ZR.ZoneName
> , ZR.PropertyNumber
> , ZR.Area
> , ZR.Route
> , ZR.Operator
> INTO #ZonesInDelSys
> FROM productioncache..cache_zonerelations ZR
> INNER JOIN #TmpTable1 T ON T.ZoneID = ZR.ZoneID
> WHERE 1=0
> --select * from #ZonesInDelSys
> SELECT DISTINCT DelSysID, District, FieldOffice
> INTO #DelSysHier
> FROM #ZonesInDelSys
> WHERE 1=0
>
> CREATE CLUSTERED INDEX IX1_#DelSysHier ON #DelSysHier (DelSysID,
> District, FieldOffice)
>
> SELECT ZD.District
> , ZD.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , T.ZoneID
> , R.MpDestHID AS GasMeterHID
> , H.HdrCode as MeterCode
> , HT.HdrTypeName as MeterType
> , HA.hdrattrcode as MeterAttr
> INTO #MetersInDelSys
> FROM #TmpTable1 T (TABLOCKX)
> INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
> T.DelSysTID = R.DsConfigTID
> INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID => T.DelSysHID
> INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
> AND H.HdrTypeTID=150005
> INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
> H.HdrTypeTID=HT.HdrTypeTID
> INNER JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
> HA.HdrHID=R.MpDestHID
> WHERE 1=0
>
> SELECT District
> , FieldOffice
> , DelSysID
> , DelSysCode
> , DelSysName
> , GasMeterHID
> , MeterCode
> , HA.hdrattrcode
> INTO #TempAttr
> FROM #MetersInDelSys A
> INNER JOIN chk..fbmashdrattribute ha (NOLOCK) ON
> ha.HdrHID=A.GasMeterHID
> WHERE 1=0
> /************* END CREATE TABLES ***********************************/
> INSERT INTO #TmpTable1 WITH (TABLOCKX)
> SELECT Root.DelSysHID AS DelSysHID
> , H.HdrCode AS DelSysCode
> , H.HdrName AS DelSysName
> , CfgHist.DelSysTID
> , Mp.MeasPtHID AS ZoneID
> FROM pdMasDelSys Root (NOLOCK)
> INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
> LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
> Root.DelSysTID = CfgHist.DelSysTID
> LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
> CfgHist.DsConfigTID = Mp.DsConfigTID
> LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID = Mp1.HdrHID
> LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID => Typ1.HdrTypeTID
> WHERE 1=1
> AND (Typ1.HdrTypeCode = 'Zone')
> AND (H.HdrInactive = 0)
> AND (Mp1.HdrInactive = 0)
> AND (CfgHist.DsConfigEffEnd = '12/31/2078')
> AND CfgHist.DsObsolete=0
> --and root.DelSysHID in(1754810,1711525)
> GROUP BY Root.DelSysHID, H.HdrCode, H.HdrName, Mp.MeasPtHID,
> CfgHist.DelSysTID
> ORDER BY H.HdrName
>
> --Zones in DelSys
> INSERT INTO #ZonesInDelSys WITH (TABLOCKX)
> SELECT DISTINCT
> ZR.District
> , ZR.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , ZR.ZoneID
> , ZR.ZoneName
> , ZR.PropertyNumber
> , ZR.Area
> , ZR.Route
> , ZR.Operator
> FROM productioncache..cache_zonerelations ZR (NOLOCK)
> INNER JOIN #TmpTable1 T (TABLOCKX) ON T.ZoneID = ZR.ZoneID
> WHERE getdate() BETWEEN ZR.OperationBeginDate and ZR.OperationEndDate
>
> --DelSys Enertia Hierarchy
> INSERT INTO #DelSysHier WITH (TABLOCKX)
> SELECT DISTINCT
> DelSysID
> , District
> , FieldOffice
> FROM #ZonesInDelSys (TABLOCKX)
> truncate table #MetersInDelSys
> --Gas Meters in DelSys
> INSERT INTO #MetersInDelSys WITH (TABLOCKX)
> SELECT DISTINCT
> ZD.District
> , ZD.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , T.ZoneID
> , R.MpDestHID AS GasMeterHID
> , H.HdrCode as MeterCode
> , HT.HdrTypeName as MeterType
> , HA.hdrattrcode as MeterAttr
> FROM #TmpTable1 T (TABLOCKX)
> INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
> T.DelSysTID = R.DsConfigTID
> INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID => T.DelSysHID
> INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
> AND H.HdrTypeTID=150005
> INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
> H.HdrTypeTID=HT.HdrTypeTID
> LEFT JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
> HA.HdrHID=R.MpDestHID
> WHERE HA.HdrAttrCode in
> ('Transp','Fuel','Alloc','Check','Custody','Custdy'
> ,'ChCalc','Irrig','Deduct')
>
> INSERT INTO #TempAttr WITH (TABLOCKX)
> SELECT District
> , FieldOffice
> , DelSysID
> , DelSysCode
> , DelSysName
> , GasMeterHID
> , MeterCode
> , HA.hdrattrcode
> FROM #MetersInDelSys A (TABLOCKX)
> INNER JOIN chk..fbMasHdrAttribute HA (NOLOCK) ON
> HA.HdrHID=A.GasMeterHID
> WHERE HA.HdrAttrCode in
> ('Transp','Fuel','Alloc','Check','Custody','Custdy'
> ,'ChCalc','Irrig','Deduct')
>
>
> --SELECT where only 1 meter in DelSys
> SELECT A.District, @.district as districtname
> , A.FieldOffice
> , A.DelSysID
> , A.DelSysCode
> , A.DelSysName
> , A.ZoneID
> , HZ.HdrName
> , A.GasMeterHID
> , HG.HdrName as MeterName
> , A.MeterCode
> , A.MeterType
> , A.MeterAttr
> FROM #MetersInDelSys A (TABLOCKX)
> INNER JOIN CHK..fbMasHdr HZ (NOLOCK) ON HZ.HdrHID=A.ZoneID
> INNER JOIN CHK..fbMasHdr HG (NOLOCK) ON
> HG.HdrHID=A.GasMeterHID
> INNER JOIN (SELECT DelSysID
> , count(*) as num
> FROM #MetersInDelSys
> GROUP BY DelSysID
> HAVING count(*) = 1) G1 ON
> G1.DelSysID=A.DelSysID
> where @.district=a.district
>
> /**************** CLEAN UP TEMP TABLES**********************/
> IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
> BEGIN
> DROP TABLE #TmpTable1
> END
>
> IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #ZonesInDelSys
> END
>
> IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
> BEGIN
> DROP TABLE #DelSysHier
> END
>
> IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #MetersInDelSys
> END
>
> IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
> BEGIN
> DROP TABLE #TempAttr
> END
> END
>
> Then inside RS I select a dataset and set the command to stored
> procedure. I then go to the layout tab and specify a parameter called
> district and it pulls from the sproc query district name. if that
> makes sense...
>|||It's working yay!!! The only thing I have left to do is figure out the
report parameters. Do I need to specify them in my sproc? Or can I
create them through RS?
Thanks so much!|||Your stored procedure has a parameter. This is the query parameter. If you
are using SQL Server then RS recognizes the parameter and creates a
corresponding report parameter and maps to it. So when you create a dataset
based on the stored procedure you will then have a report parameter called
district created. I notice you had a parameter question in another posting.
I suggest starting with a new report so you don't have problems left over
from before.
Your query parameters have this mapping occur automatically but you can also
change the mappng to an expression by clicking on the ..., parameters tab.
Then if the report parameter is not needed you go to the layout tab, report
menu->report parameters and delete the now unused parameter (this is not
necessary in your case).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156441135.234902.131430@.i3g2000cwc.googlegroups.com...
> It's working yay!!! The only thing I have left to do is figure out the
> report parameters. Do I need to specify them in my sproc? Or can I
> create them through RS?
> Thanks so much!
>
Reporting Services Stored Procedure Dataset
I have a big SQL Stored Procedure which works with a cursor inside of it. During the procedure the data is inserted into a table and at the end is a SELECT statement from that table. The problem is that when i create a dataset with that stored procedure and i run it in the Data tab i get the correct select, but in the Fields section of the Report I don't get the fields from the last SELECT, but the fields from the cursor. Am I doing something wrong or is this a bug and how can i fix it.
Thanks!does it return the correct result set when you run it from QA ?|||Yes, it does return the correct dataset. I am using sql 2005 and if I have the procedure like his:
Thanks!does it return the correct result set when you run it from QA ?|||Yes, it does return the correct dataset. I am using sql 2005 and if I have the procedure like his:
DECLARE Mycusor CURSOR FOR SELECT Field1, Field2, ...
OPEN CURSOR ...
FETCH NEXT ...
WHILE @.@.FETCH_STATUS > 0 ...
INSERT INTO tmp ...
SELECT * FROM tmp
If the procedure is like this then in Sql 2005 when i run it i get the data from tmp and in Reporting Services i get a dataset containg Field1, Field2 with no rows in it.
Do you have any idea?|||dont you have a close/deallocate cursor ? heres a template for a cursor.
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT
OPEN rs
fetch next from rs into
WHILE ( @.@.FETCH_STATUS = 0 )
begin
-- your DML stmts.FETCH NEXT FROM rs INTO
ENDclose rs
deallocate rs
and you would need to do a select after deallocating the cursor.
hth
Reporting Services stability and bugs
We're considering adopting Reporting Services, but we're concerned about the
short history of RS.
Are you having issues with stability or bugs?
Thanks!
-- EspenThere are a few known bugs and then there are a few
features that you would want but arene there in RS.
So I would suggest that you do something like a POC on RS
before deciding on RS.
We also had the same issue in our organization but after
working on RS for sometime I feel that its worth atleast
exploring it and seeing if it suits your needs.
>--Original Message--
>We're considering adopting Reporting Services, but we're
concerned about the
>short history of RS.
>Are you having issues with stability or bugs?
>Thanks!
>-- Espen
>.
>|||We haven't done a full-scale deploy yet, but from our perspective, the
stability and/or bugs have not been as much of an issue as the
shortage of features. There are some other threads in this forum that
might interest you in that regard.
Brad.
On Fri, 3 Sep 2004 11:15:03 -0700, "Espen"
<Espen@.discussions.microsoft.com> wrote:
>We're considering adopting Reporting Services, but we're concerned about the
>short history of RS.
>Are you having issues with stability or bugs?
>Thanks!
>-- Espen
short history of RS.
Are you having issues with stability or bugs?
Thanks!
-- EspenThere are a few known bugs and then there are a few
features that you would want but arene there in RS.
So I would suggest that you do something like a POC on RS
before deciding on RS.
We also had the same issue in our organization but after
working on RS for sometime I feel that its worth atleast
exploring it and seeing if it suits your needs.
>--Original Message--
>We're considering adopting Reporting Services, but we're
concerned about the
>short history of RS.
>Are you having issues with stability or bugs?
>Thanks!
>-- Espen
>.
>|||We haven't done a full-scale deploy yet, but from our perspective, the
stability and/or bugs have not been as much of an issue as the
shortage of features. There are some other threads in this forum that
might interest you in that regard.
Brad.
On Fri, 3 Sep 2004 11:15:03 -0700, "Espen"
<Espen@.discussions.microsoft.com> wrote:
>We're considering adopting Reporting Services, but we're concerned about the
>short history of RS.
>Are you having issues with stability or bugs?
>Thanks!
>-- Espen
Reporting Services SQL 2005 x64 standard edition
Can reporting services be upgraded to include data driven subscriptions or must SQL server be upgraded to enterprise edition. Currently I am using SQL 2000 standard edition 32bit with reporting services and data driven subscription. Has the features been changed from 2000 to 2005?
Thanks
Francois
Hello Francois,
SQL Server Reporting Services 2005 is a part of SQL Server 2005 and Data Driven Subscriptions are part of 2005 Enterprise Edition(http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx). From Books On Line (BOL) "Server components require a SQL Server license. You must have a valid SQL Server license for any computer on which you install a Reporting Services server component (http://technet.microsoft.com/en-us/library/ms143238.aspx)." So the Microsoft solution is to get Enterprise Edition.
However, I came accross this article by Jason Selburg which claims to allow you to build your own data driven subscriptions using Standard Edition: http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp
Good luck,
Larry
|||Oops, found a more recent article by the same author:
http://www.sqlservercentral.com/columnists/jselburg/2824.asp
that appears to be much more robust.
Larry
|||Thank you for your reply. Aplogies for only responding now.
sqlReporting Services SQL 2005 x64 standard edition
Can reporting services be upgraded to include data driven subscriptions or must SQL server be upgraded to enterprise edition. Currently I am using SQL 2000 standard edition 32bit with reporting services and data driven subscription. Has the features been changed from 2000 to 2005?
Thanks
Francois
Hello Francois,
SQL Server Reporting Services 2005 is a part of SQL Server 2005 and Data Driven Subscriptions are part of 2005 Enterprise Edition(http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx). From Books On Line (BOL) "Server components require a SQL Server license. You must have a valid SQL Server license for any computer on which you install a Reporting Services server component (http://technet.microsoft.com/en-us/library/ms143238.aspx)." So the Microsoft solution is to get Enterprise Edition.
However, I came accross this article by Jason Selburg which claims to allow you to build your own data driven subscriptions using Standard Edition: http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp
Good luck,
Larry
|||Oops, found a more recent article by the same author:
http://www.sqlservercentral.com/columnists/jselburg/2824.asp
that appears to be much more robust.
Larry
|||Thank you for your reply. Aplogies for only responding now.
Subscribe to:
Posts (Atom)