Increase Tooltip display time?

Hi All,

Is it possible to increase the amount of time a tooltip is displayed within report manager?

Regards,
JonNo I don′t think that this is customizable within the report.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hello

can u tell me

How to put tool tip in My report.

thanx

ki

|||There is a setting for controls naming tooltip which can define any tooltip displayed if you hover over the specified item.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Hi Ki,

In the properties box all objects, in the "General" tab is an option

for "Tooltip". You can put an expression in here to define a

tooltip.

Regards,
Jon

increase the speed of the report

Hello,

I am working on a report in SQL Server Reporting Services 2000.

[CODE]
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = case when @.callDate = '' then [Call Day] else @.callDate end
[/CODE]

>> I have apromt for the user to enter the date.
>> If the user does not enter any date, then the report will show all the first 200 records.
>> This query is running too slow.

To increase the speed of the report , could somebody help me build the where clause only when something is in the filters ?

Thank you,

This should do what you want:

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = @.callDate

|||

I tried using

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = @.callDate

>> Output is blank.

>> I need the top 200 records to be returned by default. If the @.callday is blank.

Thank you

|||

urpalshu wrote:

I tried using

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = @.callDate

>> Output is blank.

>> I need the top 200 records to be returned by default. If the @.callday is blank.

Thank you

You need to use boolean logic here, you stated in some cases no date is entered.

By the way I hope call day is of type date time...

In any even if you sometimes have a value for @.callDate and other times it is null the sproc should be this:

@.callDate datetime= NULL --do you need a default ?

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)

Make sure Call Day is of the right type (datetime). If it is varchar, you will need to change the data type. You can strip the day time month year using various date functions.

Jon

|||

Thank you,

I changed the Call Date to datetime,

if @.callDate IS NULL AND @.destNbr = '' AND @.origNbr = '' AND @.btn = '' AND @.invoiceNbr = '' AND @.destMobile = ''
BEGIN
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
END
ELSE
BEGIN
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)
AND [Dest Nbr] = case when @.destNbr = '' then [Dest Nbr] else @.destNbr end
AND [Orig Nbr] = case when @.origNbr = '' then [Orig Nbr] else @.origNbr end
AND [BTN] = case when @.btn = '' then [BTN] else @.btn end
AND [invoice nbr] = case when @.invoiceNbr = '' then [invoice nbr] else @.invoiceNbr end
AND [dest mobile] = case when @.destMobile = '' then [dest mobile] else @.destMobile end
END

Can we improve the speed on this query?

Please help

|||

This is a quite common type of query requirement when coding queries that do searches.

What you want to do is replicate the exact same logic you used for the @.callDate parameter for all the other parameters too:

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)
AND (@.destNbr is null or @.destNbr = '' or [Dest Nbr] = @.destNbr)
AND (@.origNbr is null or @.origNbr = '' or [Orig Nbr] = @.origNbr)
... etc...

Notice the bracketing to group the expressions (it won't work correctly without it), and the ordering of the expressions: we are relying on shortcutting to ensure that the field vs. parameter check is only evaluated if the parameter contains a legitimate value (ie isn't empty).

If you do it this way then you can also get rid of the outer if @.callDate is null and @.destNbr = '' etc...

sluggy

|||

sluggy wrote:

This is a quite common type of query requirement when coding queries that do searches.

What you want to do is replicate the exact same logic you used for the @.callDate parameter for all the other parameters too:

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)
AND (@.destNbr is null or @.destNbr = '' or [Dest Nbr] = @.destNbr)
AND (@.origNbr is null or @.origNbr = '' or [Orig Nbr] = @.origNbr)
... etc...

Notice the bracketing to group the expressions (it won't work correctly without it), and the ordering of the expressions: we are relying on shortcutting to ensure that the field vs. parameter check is only evaluated if the parameter contains a legitimate value (ie isn't empty).

If you do it this way then you can also get rid of the outer if @.callDate is null and @.destNbr = '' etc...

sluggy

Its amazing how people dont listen, did I not just post this like the third post ?

|||

You sure did, but the original poster was still stuck, so i expanded upon it for him. You will see i mentioned "what he had already done with the @.callDate parameter" - this acknowledges your post.

But this is not the place for a flame war, so let it go.

sluggy

Increase the Rendering Timing of Reports

Hi,

We are using SQL Server 2005 Reporting Services for creating Reports. But report execution is taking bit time to give results.

Is there any way around to increase the rendering timing ?

Thx

First step would be to find where the delay is. There is a table called ExecutionLog in the reportserver database catalog. you can query this table and look at columns - TimeDataRetrival, Time processing, time rendering for this report to find out where the delay is. If the delay is in TimeDataRetrival, it means that your SQL query performance is the one to be blamed. You can optimize the query which is used in the report and get over it. NOTE: Always open the ExecutionLog table with no lock hint.

Increase Paging

I want to display everything on my report on the same screen without having
to go to any other pages. How can I increase what is shown on the first page
of a report?You could increase the PageHeight/PageWidth settings for the report. If you
are using RS 2005, you could set the InteractiveHeight value to 0 and leave
the PageHeight unchanged. Note: the InteractiveHeight property only affects
so called interactive renderers (such as Preview and HTML).
You may also want to read this blog article:
http://blogs.msdn.com/bwelcker/archive/2005/08/19/454043.aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <David@.discussions.microsoft.com> wrote in message
news:BB5240E3-ED2D-4875-AB25-F57312B74428@.microsoft.com...
>I want to display everything on my report on the same screen without having
> to go to any other pages. How can I increase what is shown on the first
> page
> of a report?

Incorrect syntax near the keyword ELSE.

Hi,

I have written a stored procedure to add the records to the table in DB from the report I generate, but the sored procedure gives me this error:

Incorrect syntax near the keyword 'ELSE'.

I am using Sql Server 2005.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
DECLARE @.PKID INT
DECLARE @.TagID INT

SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode

SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID
END

DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT

SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens=@.UniqueUserOpens-UniqueUserOpens,@.UniqueUserMessageClicks=@.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks=@.UniqueUserLinkClicks-UniqueUserLinkClicks,@.TotalLinkClicks=@.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes=@.Unsubscribes-Unsubscribes,@.Bounces=@.Bounces-Bounces,@.SpamComplaints=@.SpamComplaints-SpamComplaints,
@.Cost=@.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost
WHERE ReportDate=@.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

ELSE
SET NOCOUNT ON

INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens, UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)

SELECT
@.ReportDate,
@.SiteID,
@.PKID,
@.Sent,
@.Delivered,
@.TotalOpens,
@.UniqueUserOpens,
@.UniqueUserMessageClicks,
@.UniqueUserLinkClicks,
@.TotalLinkClicks,
@.Unsubscribes,
@.Bounces,
@.SpamComplaints,
@.Cost,
@.DataFeedID,
@.TagID

SET NOCOUNT OFF

Hi,

I think you find that the End Statement must be immediately before the Else (IF... BEGIN...END ELSE). Certainly if you move the END to just before the ELSE the SP compiles OK.

Hope this helps,

Paul

|||

How could I miss that.

Thanks a lot!!!

|||

No problems, sometimes these things just need a fresh pair of eyes.

Cheers

|||Try
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
SET NOCOUNT ON -- moved this
DECLARE @.PKID INT
DECLARE @.TagID INT
SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode
SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID
DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT
SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens = @.UniqueUserOpens-UniqueUserOpens,
@.UniqueUserMessageClicks = @.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks = @.UniqueUserLinkClicks-UniqueUserLinkClicks,
@.TotalLinkClicks = @.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes = @.Unsubscribes-Unsubscribes,
@.Bounces = @.Bounces-Bounces,
@.SpamComplaints = @.SpamComplaints-SpamComplaints,
@.Cost = @.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate AND SiteID=@.SiteID AND TagPKID=@.PKID
UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost
WHERE ReportDate=@.ReportDate AND SiteID=@.SiteID AND TagPKID=@.PKID
END
ELSE
INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens,
UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)
VALUES ( -- Should be values
@.ReportDate, @.SiteID, @.PKID, @.Sent, @.Delivered, @.TotalOpens, @.UniqueUserOpens,
@.UniqueUserMessageClicks, @.UniqueUserLinkClicks, @.TotalLinkClicks, @.Unsubscribes,
@.Bounces, @.SpamComplaints, @.Cost, @.DataFeedID, @.TagID)
SET NOCOUNT OFF|||You have an ELSE with no matching IF.

Incorrect syntax near ',' using a Multi-value parameter

I created a report in Reporting Services 2005 where I added multi-value parameters. When I run my report, and try to select more than one parameter, I get an error: Incorrect syntax near ','I fixed it by changing my '=' signs to 'IN', so that it would search for the multi-value parameter within the values selected.

Incorrect Schedule Displayed

Hi,
I am creating a data driven subscription via code using a shared schedule
and it seems fine, but when I view the subscription in Report Manager it
shows the wrong schedule.
I have checked the MatchData field in the Subscriptions table and the
ScheduleID in the ReportSchedules table and both of these have the correct
ID.
any ideas?
thanks
MattOh, I should add that the subscription runs correctly. It just displays a
different schedule to the one I have assigned on the "Specify When the
subscription is processed." page. Note, if I set the schedule here, it
seems to remember it.
thanks again
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:O40uAJ2gEHA.712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am creating a data driven subscription via code using a shared schedule
> and it seems fine, but when I view the subscription in Report Manager it
> shows the wrong schedule.
> I have checked the MatchData field in the Subscriptions table and the
> ScheduleID in the ReportSchedules table and both of these have the correct
> ID.
> any ideas?
> thanks
> Matt
>|||Is it possible for you to share your code? You can send it directly to me
if you want and I can take a look. Just remove the online from my address.
--
-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:#NVQ#z2gEHA.1972@.TK2MSFTNGP09.phx.gbl...
> Oh, I should add that the subscription runs correctly. It just displays a
> different schedule to the one I have assigned on the "Specify When the
> subscription is processed." page. Note, if I set the schedule here, it
> seems to remember it.
> thanks again
>
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:O40uAJ2gEHA.712@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I am creating a data driven subscription via code using a shared
schedule
> > and it seems fine, but when I view the subscription in Report Manager it
> > shows the wrong schedule.
> >
> > I have checked the MatchData field in the Subscriptions table and the
> > ScheduleID in the ReportSchedules table and both of these have the
correct
> > ID.
> >
> > any ideas?
> >
> > thanks
> >
> > Matt
> >
> >
>

Incorrect printed pages in SQL Report-Imp. pls. reply asap

Hi
Can someone tell me why my printed pages from the print icon on SQL Report
2005
is different from the page # shown on the browser. Meaning, if I specify
that I want to print page # 10 of a 50 paged report, the printed output is a
different page and not the page #10 which is seen in the browser.
Any clues why this is happening?
--
Thanks,
SDRoyBecause on screen in your browser you are viewing it in pdf. If you want to
see exactly how your page will print you need to use print preview or export
to pdf.
"SDRoy" wrote:
> Hi
> Can someone tell me why my printed pages from the print icon on SQL Report
> 2005
> is different from the page # shown on the browser. Meaning, if I specify
> that I want to print page # 10 of a 50 paged report, the printed output is a
> different page and not the page #10 which is seen in the browser.
> Any clues why this is happening?
> --
> Thanks,
> SDRoy|||I'm sorry...I meant you are viewing it in html on screen in your
browser...forgive my typo please.
"KimB" wrote:
> Because on screen in your browser you are viewing it in pdf. If you want to
> see exactly how your page will print you need to use print preview or export
> to pdf.
> "SDRoy" wrote:
> > Hi
> >
> > Can someone tell me why my printed pages from the print icon on SQL Report
> > 2005
> > is different from the page # shown on the browser. Meaning, if I specify
> > that I want to print page # 10 of a 50 paged report, the printed output is a
> > different page and not the page #10 which is seen in the browser.
> >
> > Any clues why this is happening?
> >
> > --
> > Thanks,
> > SDRoy|||SDRoy,
Different renderers are being used, (html v. rtf) so the pagnation is
different.
thx
-jsh
"SDRoy" wrote:
> Hi
> Can someone tell me why my printed pages from the print icon on SQL Report
> 2005
> is different from the page # shown on the browser. Meaning, if I specify
> that I want to print page # 10 of a 50 paged report, the printed output is a
> different page and not the page #10 which is seen in the browser.
> Any clues why this is happening?
> --
> Thanks,
> SDRoy

Incorrect page rotation when printing

I have two machines on my network, that when printing a RS2005 report,
rotate the page incorrectly. ie. Print a portrait report in landscape,
cutting off the bottom of the report.
I am printing by using the print button in the RS2005 control from a web
browser.Hello Grant,
I have tested on my side and do not get the same issue.
I tested on the Sample Report Product Line Sales of RS 2005.
Would you please do the same test on your server and let me know the
result? Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Incorrect page count on reports that use drill-down or have hidden

I am getting an incorrect page count on reports that use drill-down or where
report items are hidden. The Globals!TotalPages returns 1 on a report that
clearly has more than one page. On these reports, the Globals!TotalPages
returns the correct page count for the print preview. I get the same results
whether this function is in the header or footer. My goal is to display the
current page and total pages on each page.
If I remove the expression from the Hidden property I get the correct page
count.
I am using SQL 2005 and Reporting Services 2005.Hello mrScott,
Based on my test, the TotalPages will return correct result in my side when
never I use the hidden report item or drill down report.
Would you please send the report to me so that I can try to reproduce it on
my side? You could create a simple report based on the Sample database in
SQL 2005.
To reach me, please remove the ONLINE in my email address.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Scott,
I have tested on my side.
I think that you misunderstood this problem.
The pagecount you see in the preview is page count for the HTML layout.
When you click the Print Layout, since it will re pagenate the report
according to the page setting, you will get another page count.
If you zoom in the print preview in the Print Layout, you could see the
Pagecount in the up-right is 9.
This is the correct behavior.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Scott,
I have tested on my side.
I think that you misunderstood this problem.
The pagecount you see in the preview is page count for the HTML layout.
When you click the Print Layout, since it will re pagenate the report
according to the page setting, you will get another page count.
If you zoom in the print preview in the Print Layout, you could see the
Pagecount in the up-right is 9.
This is the correct behavior.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
If I understand you correctly, Globals!TotalPages returns one value for the
HTML layout and can return a different value for the Print Layout? Even
though the InteractiveSize and the PageSize are the same?
Is it possible to display Globals!TotalPages only for the Print Layout? Is
there a property or global variable that indicates whether the report is in
HTML or Print Layout? I don't need to display the page count for the HTML
layout but would like to display it when the report is printed.
Thanks again for you assistance.
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Scott,
When you print the report, the Globals!TotalPages will return the value for
the print layout.
You could ignore it when you get the HTML layout.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Wei Lu,
You were right in your second response to me, I misunderstood the problem.
The sections "Controlling Report Pagination" and " Understanding Report
Layout and Rendering" in " SQL Server 2005 Books Online" drove it home.
Since I can't get the page count to match in both layouts, ignoring it in
the HTML layout is the solution. I set the InteractiveSize Height to zero so
that the page count will always be one when the report is in the HTML layout
and greater than one in print layout. I can then hide the textbox containing
the page count in the HTML layout and show it in the print layout. If you
have a better idea for ignoring the page count in the HTML layout please let
me know.
Thanks again.
"Wei Lu [MSFT]" wrote:
> Hello Scott,
> When you print the report, the Globals!TotalPages will return the value for
> the print layout.
> You could ignore it when you get the HTML layout.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Scott,
For now I think ignorint the page count in the HTML layout is the only
thing you could do.
You could send your feedback to the product team if you have any concern.
http://connect.microsoft.com/sqlserver
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Incorrect Order in rendering report

Hi,

I have this problem on Reporting Services 2005 SP2:

There is a stored procedure that is the source of a dataset in report, this procedure return a recordset ordered by some fileds (es. order by fields1, fields2, ecc...). This procedure also have some parameters, but this isn't important.

If I launch the stored procedure in sql server management studio the data are returned in the correct order, instead, when I run the report, the data are showed in wrong order.

Some one have informations about this issue?

Kind Regards,

Elia.

Did you try sorting in the report? If so doesn't it still sort in the order that you selected.

If not try sorting in the report, within table properties you will see sorting within which you can specify the sort order

|||

Thanks,

I have resolved the problem.

Regards,

Elia.

Incorrect group sum problem

Hi all,
I have encountered a very strange problem in RS2005. In my report I
show real estate transactions. When I have more than one record with
the same TR_ID (transaction id) I need to popolate nulls into certain
money fields like AGC_amt and some other fields. These amounts are the
same in all transactions with the same TR_ID and should be treated as
one value not a repeating value. Let's say $1,000 in AGC_amt should
remain $1,000 even for every transaction with the same TR_ID it
repeats. I conditionally place nulls into those texboxes that have the
same TR_ID in the previous record so only the first record has the
value.
=iif(Fields!TR_id.Value = Previous(Fields!TR_id.Value), nothing,
Fields!AGC_amt.Value)
I need to sum up all these values in my group footer. What I have in my
report is the amount equal to total as if I did not place nulls! It is
easy to show in example:
tr_id AGC_am
----
12345 $1,000
same tr_id I place null here although it is $1,000 in dataset
same tr_id also null for the same reason
group sum: $3,000 (instead of $1,000 what I expect!)
formula for the sum in group footer:
=Sum(Fields!AGC_amt.Value)
What is wrong here? I tried to place zeros in place of nulls and get
the same incorrect number, i.e. I have $1,000, $0 and $0 totaling to
$3,000 in group footer!!!
I am going insane!
Please, help!
Thanks,
StanNever mind, everyone! My mistake. There was another field in my dataset
I was supposed to use in my group totals. I just did not know about it.
Stan
suslikovich wrote:
> Hi all,
> I have encountered a very strange problem in RS2005. In my report I
> show real estate transactions. When I have more than one record with
> the same TR_ID (transaction id) I need to popolate nulls into certain
> money fields like AGC_amt and some other fields. These amounts are the
> same in all transactions with the same TR_ID and should be treated as
> one value not a repeating value. Let's say $1,000 in AGC_amt should
> remain $1,000 even for every transaction with the same TR_ID it
> repeats. I conditionally place nulls into those texboxes that have the
> same TR_ID in the previous record so only the first record has the
> value.
> =iif(Fields!TR_id.Value = Previous(Fields!TR_id.Value), nothing,
> Fields!AGC_amt.Value)
> I need to sum up all these values in my group footer. What I have in my
> report is the amount equal to total as if I did not place nulls! It is
> easy to show in example:
> tr_id AGC_am
> ----
> 12345 $1,000
> same tr_id I place null here although it is $1,000 in dataset
> same tr_id also null for the same reason
> group sum: $3,000 (instead of $1,000 what I expect!)
> formula for the sum in group footer:
> =Sum(Fields!AGC_amt.Value)
> What is wrong here? I tried to place zeros in place of nulls and get
> the same incorrect number, i.e. I have $1,000, $0 and $0 totaling to
> $3,000 in group footer!!!
> I am going insane!
> Please, help!
> Thanks,
> Stan

incorrect format of date report parameters via datepicker

OK - truck loads of folk have reported this problem - but I cannot solve it - so apologies for repeating the question...

My SQL Server 2005 Report is developed on the same server as it is run.

I have SQL Server 2005 SP2 installed. The report language is set as en-GB.

The browser language setting is en-NZ.

The report previews fine - I pick May 22 from the picker and it appears as 22/05/2007 in the report parameter text box - which since I am in NZ is exactly as I want it.

I deploy the report - If I run it from the report manager virtual directory the date picker performs as above i.e correctly.

I run the deployed report from the report server virtual directory and here is where the problem occurs - selecting May 22 from the date picker produces 05/22/2007 in the report parameter text box and produces the error

"The value provided for the report parameter 'StartScheduledDate' is not valid for its type. (rsReportParameterTypeMismatch)"

thanks for any help here - this problem has made this a ridiculously expensive report!

honestbob wrote:

I deploy the report - If I run it from the report manager virtual directory the date picker performs as above i.e correctly.

I run the deployed report from the report server virtual directory and here is where the problem occurs

Pardon me, what's the difference between the 2 sentenses? They both sound like you run from http://localhost/Reports?

I'd suggest looking at the Regional settings in your machine, and server, to make sure they're the same

e.g. Canada default is DD/MM/YYYY, while USA default is MM/DD/YYYY

|||

bob,

what i have done with this in the past is always do a CDate() on the output of the calendar control before trying to use that output, then i can Format() it any way i like. I also found when trying to force it to a non-US date format i couldn't then use the Preview tab in the IDE to check my report, as that version of the control insists on rendering/outputting everything in US format, regardless of what culture you have your report/IE/server set to.

So in summary, i find the web version of that control works fine if you CDate everything before using it. And like the previous poster said, check the regional settings on your report server.

|||

I was having the same problem but i managed to fix it.

edit the file "ReportViewer.aspx" on the reporting services machine.

it's in the "C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\Pages" by default,

and add the highlighted part with the desired culture value.

It solved the problem in my case.

<%@. Register TagPrefix="RS" Namespace="Microsoft.ReportingServices.WebServer" Assembly="ReportingServicesWebServer" %>

<%@. Page Language="C#" AutoEventWireup="true" Inherits="Microsoft.ReportingServices.WebServer.ReportViewerPage" Culture="pt-PT"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head id="headID" runat="server">

<title>Report Viewer</title>

</head>

<body style="margin: 0px; overflow: auto">

<form runat="server" ID="ReportViewerForm">

<RS:ReportViewerHost ID="ReportViewerControl" runat="server" />

</form>

</body>

</html>

Hope this will solve your problem too.

Tiago Certal

|||

yes, that solved my problem too. I, too have tried fiddling with the regional settings on the database, the server, the local, the client computers etc. I would keep having the problem in IE, but not in Firefox. Firefox was fine.

so I tried to add the culture thing in, like Tiago suggested. only problem is that I think PT is portugese, so I had to use "en-CA" instead. Which then magically made the reports displayable in IE. Thank you Tiago!

problem is that now Firefox is having the same error. WHAT IS GOING ON?

Incorrect Data appearing in report

I have setup a report. I am calling the report from a page which passes parameters in a query string to the report.
The parameters are to be used in a stored procedure which the "Object Data Source" of the report refers to.
The stored procedure queries the data ina table and this data should be displayed in the report.

What actually happens is that the report appears but it has only the first record in the table but this is not the record
specified by the parameters passed to the stored procedure. It look as if the report is attaching to the correct table but
the query is not being executed properly.

Anyone any ideas how to fix this?
macca

Does ur storeprocedure return the right data when u run in SSMS or in the Data layout of ur report?

|||

Thanks for the reply Karenros but how do I test what you are suggesting.

Thanks,

macca

|||

Go to sql server ManagementStudio and then go to the database u wanna access and then click on new query.. in the new query window type in ur sproc name and give the parameters for ur sproc and u should see the results in the same window...

Or u can go to ur report in VS2005 and then click on the data part of ur report and u will see the name of the dataset and the sproc name.. next to the sproc click the exceute button and it will prompt u for parameters and after u enter them they should display the results...

Hope this helps.

Regards

Karen

|||Thanks for that Karen that works.I am getting another problem though. I am calling the report via a Response.redirect but am getting an error with the dates formats. The error message is as follows:"An error has occurred during report processing.· String was not recognized as a valid DateTime." I am calling the report and the url looks like this:· · "/PaymentReport.aspx?Section=107&DateFrom=02/10/2007&DateTo=19/10/2007". When I try the method you suggested above I must put in the DateFrom as 10/2/2007 and DateTo as 10/19/2007. The report is obviously not getting the dates in the query string in the correct format and therefore the error.

|||

How are u storing ur date time in ur database. looks like the format you are using is dd/mm/yyyy... and by any chance is the date in ur database stored as mm/dd/yyyy?

Regards

Karen

|||

Macca,

while u are giving ur date Parameter try putting it in Single quote... take a look at this code.

Declare @.reqDeldatedatetimeSet @.reqDeldate ='1/31/2007'Select OrderId, OrderDate, PlanId, RequiredDeliveryDateFrom [Order]Where RequiredDeliveryDate = @.reqDeldate

If i just @.reqDeldate as 1/31/2007 instead of '1/31/2007' nothing shows up.

 So try giving it quotes..

Regards

Karen

|||

Thanks Karen that worked.

I now have another problem. The report is not bringing back the correct results from the query. It appears to just bring back one record multiple times.

But if I run the query it brings back the correct results.

I don't know why it is doing this.

Any ideas?

macca

|||

can u pls post ur store procedure code? and have u tried refreshing the data in VS studio.. cause sometimes i have seen display the old data... and after i refresh it works fine... and try deploying it to the report server to see if it gives the correct results.

Hope this helps.

Regards

Karen

|||

Here is my Stored procedure:

CREATE PROCEDURE sproc_PaymentReports

(
@.DateFrom Datetime,
@.DateTo Datetime,
@.Status Int,
@.Section Int
)

AS

BEGIN
SELECT cheq_id, cheq_daterec, cheq_pername, cheq_amt
FROM RecCheqs
WHERE cheq_DateRec >= coalesce(nullif(@.DateFrom, ' '),cheq_DateRec) AND cheq_DateRec <= coalesce(nullif(@.DateTo, ' '),cheq_DateRec)
AND cheq_dept = coalesce(nullif(@.Section , ' '),cheq_dept)
AND cheq_added = coalesce(nullif(@.Status , ' '),cheq_added)
END
GO

How do you deploy to the report server?

macca

|||

Do u have a report server setup? if yes

then in VS go to Project and then click on properties and in the window that comes up Give a

Name for the TargetReportFolder. and in the TargetServerURL ... give http:/Either localhost or an ipaddress/ReportServer

and click ok ... for the first time the overwriteDatasources to false...but sometimes i would have to make changes to the report to the dataset so i just set it to true.

Once u set ur project properties... Go to Build -- Deploy Project name and it should prompt for a user name and password if required...

and to run ur report from the report server... go to the address that u specified in the TargetReportServerURL and then navigate to the folder name and click on the main report that runs it... and then its like .... runing it from Visual studio

Hope this helps...

Regards

Karen

|||

I don't have a report server setup so can't do any of that. This is not doing it for me at all.

In .net 1.1 I used to create reports using a Repeater and I would run the stored procedure and it would "Bind" the data to the repeater and it would work fine.

I have tried this with the ReportViewer using the following code but cannot get it to Bind:

Dim oDSAs DataSet

Dim oChequeAsNew Cheque

Try

oDS =New DataSet

oDS = oCheque.GPAssignedReport(dateFrom, dateTo, Section, Status)

If oDS.Tables(0).Rows.Count > 0Then

rptVwPayment.DataBind()

rptVwPayment.Visible =True

This code works for the repeaters in 1.1 but cannot get it to bind to report.

Any ideas, as I am ready to abandon Reports as a pack of rubbish.

macca

|||

i have used the report viewer control... but only done it using remote processing cause all my reports are located in the report server.

|||

Karen,

Thanks for all your help I got that resolved.

macca

Incorporating Form Authentication with Report Services

Hi all,

I am new to reporting services in SQL SERVER 2005. I configured my reporting server which is currently runnung when i type //localhost/reportserver in the URL it give me results. Is there anyway of incorporating security in ASP.NET 2.0 using Form Authentication So long as the user clicks on the link having been authenticated he can access the reports on the reporting server. For example I am working on an academic project whereby when user logins, I create a link that him to report server as in

//localhost/reportserver and when user clicks on that link, he can be able to download whatever report he might need.

I am running all these one local machine.

Could someone help me out

Try one of these links. I am beginning to think Russell is the SSRS god.

http://blogs.msdn.com/bimusings/archive/2005/11/29/497848.aspx

http://blogs.msdn.com/bimusings/archive/2005/12/05/500195.aspx

http://blogs.msdn.com/bimusings/archive/2005/09/08/462544.aspx

Ron

Inconsistent Excel Exports

Hi
We've got a oddity when exporting to Excel. If you export the same
report to Excel twice in sucession (don't refresh between - just
export) some report give different results between exports, with some
columns being shifted. Subsequent exports seem to be conistent.
The problem may be related to images on the reports - not sure yet.
Has anyone else encountered this, and is there a workround (other than
not using images in our standard header format).
Chloe Crowder
The British LibraryOn Jun 28, 6:15 am, Chloe C <c...@.mcrowdd.plus.com> wrote:
> Hi
> We've got a oddity when exporting to Excel. If you export the same
> report to Excel twice in sucession (don't refresh between - just
> export) some report give different results between exports, with some
> columns being shifted. Subsequent exports seem to be conistent.
> The problem may be related to images on the reports - not sure yet.
> Has anyone else encountered this, and is there a workround (other than
> not using images in our standard header format).
> Chloe Crowder
> The British Library
The first thing you should check is to make sure that you have the
latest MS Office updates and the latest SQL Server SP. Also, I would
look into index tuning for better performance of the query/stored
procedure that is used in the report. If you are using SQL Server
2005, run the query/stored procedure in the Database Engine Tuning
Advisor, if you are using SQL Server 2000, run it against the Index
Tuning Advisor/Wizard. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Inconsistent behaviour when setting up parameters

Hi guys,

i am having an issue setting up two parameters in the report designer (SSRS 2005). The report is calling a stored proc, these two parameters are optional, when the user decides to run a report i don't want them to input anything for these parameters, they should both be set to null when calling the stored proc (changing the signature of the sproc is not an option).

So, in the Report Parameter dialog, i set both parameters to "Allow null value", "Internal", and default value of null. This is when the first issue strikes: if i "OK" out of the dialog, then save, then go back into the dialog, the designer has "forgotten" that one of the params was internal (the check box is not checked) - but the other one is still okay. How can i prevent the designer forgetting things like this?

The second problem is that even though (at least one of) the parameters is set to internal, when using IE to connect to the report server and run the report the input fields for those params still show up - i don't want the user seeing them. These param input fields are both hidden if i run the report through the reporting services control (ie just right click on the report in the solution explorer and select "run"). Why the inconsistent behaviour? How can i prevent the user seeing those fields when using IE? (if i just set them to hidden then i get an error message "

The 'poolList' parameter is missing a value" when i try to run the report).

The rdl xml for these two paramaters is:

<ReportParameter Name="poolList">

<DataType>String</DataType>

<Nullable>true</Nullable>

</ReportParameter>

<ReportParameter Name="poolGroupList">

<DataType>String</DataType>

<Nullable>true</Nullable>

</ReportParameter>

Nowhere in there do i see that the parameter is internal - where does the designer stick that sort of information?

Thanks for any answers!

sluggy

A report parameter being internal means that it does not have a prompt. Hence, in the underlying RDL file, there is no <Prompt> element under the <ReportParameter> element. So, from the RDL snippet shown in your posting everything looks correct.

Regarding report server: I recommend that you delete the already published report from the report server before republishing the report with updated parameter information. Otherwise, the old parameter settings and the new parameter settings may get merged (because the administrator on the report server could have decided to change the default value etc. for the published report).

-- Robert

|||

Hi Robert,

thanks for the heads-up regarding the merging of the reports, i will watch out for it when this project goes live as we won't have control over that report server.

I have fixed one of my problems. To get rid of the "The 'blah' parameter is missing a value" message all i had to do was remove those parameters in the parameters tab of the configure dataset dialog. As they are named params and they are assigned default values in the sproc i didn't even need to try to pass them. This also means i don't have those input fields showing up in IE but not the control.

Although this still doesn't explain why the designer was persistently forgetting settings :)

sluggy

Inconsistency Report

I have a SQL Server 6.5 installation that I want to
migrate to sql 2000. I've installed sql 2000 on the same
machine, and next I'm starting the database upgrade.
Just before starting this process, the installation gives
me a "Warning" :
****************
Inconsistency Report for database : yumyum
****************
dbo.sp_ImporterEnteteCommande
Procedure
Text Not found
--
What can I do for that?Thanks for your Answer.
To bypass this problems here is what i've done (but not
sure if it's the best idea)
-generated a sql script of every stored proc in 6.5
-deleted every stored proc in 6.5
-migrating database to 2000
-creating every stored proc from the sql script of my
stored proc.
The migrating process gives me no warning and seems
succesfull. Then I just goes to the login section and re-
enter every password.
The problem is that on a client computer there is a c++
programm connecting to the sql server through odbc. He
is crashing at startup. After debugging this c++
programm, I've seen that the faulty lines is this one:
PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
CRecordset::none);
If you have any clue about this issue, let me know..
thanks!
Note: I have already tryed some version of Mdac component
in the client machine (running nt4) but no sucess.
>--Original Message--
>The upgrade needs the text to recreate the stored
procedure. As it is, this
>proc will not upgrade successfully. You can drop it and
the error will go
>away.
>For your new database, you should go to wherever you
keep the source of you
>stored procedures and recreate it from source code.
>(FWIW - In SQL 6.5 I believe that the text of a stored
procedure could be
>deleted as a 'security' measure to keep the code
hidden. From 7.0 forward
>this no longer works.)
>Russell Fields
>"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
wrote in message
>news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
>> I have a SQL Server 6.5 installation that I want to
>> migrate to sql 2000. I've installed sql 2000 on the
same
>> machine, and next I'm starting the database upgrade.
>> Just before starting this process, the installation
gives
>> me a "Warning" :
>> ****************
>> Inconsistency Report for database : yumyum
>> ****************
>> dbo.sp_ImporterEnteteCommande
>> Procedure
>> Text Not found
>> --
>> What can I do for that?
>
>.
>|||Jean-Francois,
Well, I am not a C++ guy, so I am not much help here. Is the C++ crashing
on its own? (Could it be an ODBC or OLE DB error in setup?) Or is the SQL
Server failing to return it the expected result and crashing because of
that?
I would be interested in what is inside strSQL. It would be interesting if
it was a call to the stored procedure that had no text. (If, when you say
"generated a sql script of every stored proc in 6.5" you mean that you
generated them from the database, any procedure that had no text in
syscomments would not be recreated.)
Russell Fields
"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com> wrote in message
news:0b2d01c35db8$108122d0$a301280a@.phx.gbl...
> Thanks for your Answer.
> To bypass this problems here is what i've done (but not
> sure if it's the best idea)
> -generated a sql script of every stored proc in 6.5
> -deleted every stored proc in 6.5
> -migrating database to 2000
> -creating every stored proc from the sql script of my
> stored proc.
> The migrating process gives me no warning and seems
> succesfull. Then I just goes to the login section and re-
> enter every password.
> The problem is that on a client computer there is a c++
> programm connecting to the sql server through odbc. He
> is crashing at startup. After debugging this c++
> programm, I've seen that the faulty lines is this one:
> PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
> CRecordset::none);
> If you have any clue about this issue, let me know..
> thanks!
> Note: I have already tryed some version of Mdac component
> in the client machine (running nt4) but no sucess.
>
> >--Original Message--
> >The upgrade needs the text to recreate the stored
> procedure. As it is, this
> >proc will not upgrade successfully. You can drop it and
> the error will go
> >away.
> >
> >For your new database, you should go to wherever you
> keep the source of you
> >stored procedures and recreate it from source code.
> >
> >(FWIW - In SQL 6.5 I believe that the text of a stored
> procedure could be
> >deleted as a 'security' measure to keep the code
> hidden. From 7.0 forward
> >this no longer works.)
> >
> >Russell Fields
> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> wrote in message
> >news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
> >> I have a SQL Server 6.5 installation that I want to
> >> migrate to sql 2000. I've installed sql 2000 on the
> same
> >> machine, and next I'm starting the database upgrade.
> >>
> >> Just before starting this process, the installation
> gives
> >> me a "Warning" :
> >>
> >> ****************
> >> Inconsistency Report for database : yumyum
> >> ****************
> >> dbo.sp_ImporterEnteteCommande
> >> Procedure
> >> Text Not found
> >> --
> >>
> >> What can I do for that?
> >
> >
> >.
> >|||If you are getting an unhandled SQL exception, you might try a Profiler
trace to spot the errant SQL statement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com> wrote in message
news:0b2d01c35db8$108122d0$a301280a@.phx.gbl...
> Thanks for your Answer.
> To bypass this problems here is what i've done (but not
> sure if it's the best idea)
> -generated a sql script of every stored proc in 6.5
> -deleted every stored proc in 6.5
> -migrating database to 2000
> -creating every stored proc from the sql script of my
> stored proc.
> The migrating process gives me no warning and seems
> succesfull. Then I just goes to the login section and re-
> enter every password.
> The problem is that on a client computer there is a c++
> programm connecting to the sql server through odbc. He
> is crashing at startup. After debugging this c++
> programm, I've seen that the faulty lines is this one:
> PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
> CRecordset::none);
> If you have any clue about this issue, let me know..
> thanks!
> Note: I have already tryed some version of Mdac component
> in the client machine (running nt4) but no sucess.
>
> >--Original Message--
> >The upgrade needs the text to recreate the stored
> procedure. As it is, this
> >proc will not upgrade successfully. You can drop it and
> the error will go
> >away.
> >
> >For your new database, you should go to wherever you
> keep the source of you
> >stored procedures and recreate it from source code.
> >
> >(FWIW - In SQL 6.5 I believe that the text of a stored
> procedure could be
> >deleted as a 'security' measure to keep the code
> hidden. From 7.0 forward
> >this no longer works.)
> >
> >Russell Fields
> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> wrote in message
> >news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
> >> I have a SQL Server 6.5 installation that I want to
> >> migrate to sql 2000. I've installed sql 2000 on the
> same
> >> machine, and next I'm starting the database upgrade.
> >>
> >> Just before starting this process, the installation
> gives
> >> me a "Warning" :
> >>
> >> ****************
> >> Inconsistency Report for database : yumyum
> >> ****************
> >> dbo.sp_ImporterEnteteCommande
> >> Procedure
> >> Text Not found
> >> --
> >>
> >> What can I do for that?
> >
> >
> >.
> >|||Jean-Francois,
The texts for stored procedures are stored in the syscomments table.
Without the texts, it is not possible to create a script for the stored
procedures in question since the scripting tool cannot create the TSQL
needed.
Regarding your problem, if the stored procedure exists and the things it
references exist, then I have no idea. Dan's recommendation to trace your
efforts with Profiler is the best idea I have.
Russell Fields
"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com> wrote in message
news:06af01c36003$b4bf4c30$a301280a@.phx.gbl...
> Sorry I didn't paste the good c++ line that is crashing..
> here it is:
> PointeurSecuriteSet->Open(CRecordset::dynaset, "{call
> sp_SelectSecurite()}", CRecordset::none);
> It is a call to a simple stored procedure with no params.
> About the stored proc, the way i have transfer them
> between sql 6.5 to sql 2000 is:
> -I just generated a sql script from 6.5;
> -in sql 2000, I copied the content of the sql script in
> the query analyser and run it.
> Like you said, the "no text" error may not be solved this
> way.. Where are defined those "text" and what are they?
> Thanks!!
>
> >--Original Message--
> >Jean-Francois,
> >
> >Well, I am not a C++ guy, so I am not much help here.
> Is the C++ crashing
> >on its own? (Could it be an ODBC or OLE DB error in
> setup?) Or is the SQL
> >Server failing to return it the expected result and
> crashing because of
> >that?
> >
> >I would be interested in what is inside strSQL. It
> would be interesting if
> >it was a call to the stored procedure that had no text.
> (If, when you say
> >"generated a sql script of every stored proc in 6.5" you
> mean that you
> >generated them from the database, any procedure that had
> no text in
> >syscomments would not be recreated.)
> >
> >Russell Fields
> >
> >
> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> wrote in message
> >news:0b2d01c35db8$108122d0$a301280a@.phx.gbl...
> >> Thanks for your Answer.
> >> To bypass this problems here is what i've done (but not
> >> sure if it's the best idea)
> >> -generated a sql script of every stored proc in 6.5
> >> -deleted every stored proc in 6.5
> >> -migrating database to 2000
> >> -creating every stored proc from the sql script of my
> >> stored proc.
> >>
> >> The migrating process gives me no warning and seems
> >> succesfull. Then I just goes to the login section and
> re-
> >> enter every password.
> >>
> >> The problem is that on a client computer there is a c++
> >> programm connecting to the sql server through odbc. He
> >> is crashing at startup. After debugging this c++
> >> programm, I've seen that the faulty lines is this one:
> >>
> >> PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
> >> CRecordset::none);
> >>
> >> If you have any clue about this issue, let me know..
> >> thanks!
> >>
> >> Note: I have already tryed some version of Mdac
> component
> >> in the client machine (running nt4) but no sucess.
> >>
> >>
> >> >--Original Message--
> >> >The upgrade needs the text to recreate the stored
> >> procedure. As it is, this
> >> >proc will not upgrade successfully. You can drop it
> and
> >> the error will go
> >> >away.
> >> >
> >> >For your new database, you should go to wherever you
> >> keep the source of you
> >> >stored procedures and recreate it from source code.
> >> >
> >> >(FWIW - In SQL 6.5 I believe that the text of a stored
> >> procedure could be
> >> >deleted as a 'security' measure to keep the code
> >> hidden. From 7.0 forward
> >> >this no longer works.)
> >> >
> >> >Russell Fields
> >> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> >> wrote in message
> >> >news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
> >> >> I have a SQL Server 6.5 installation that I want to
> >> >> migrate to sql 2000. I've installed sql 2000 on the
> >> same
> >> >> machine, and next I'm starting the database upgrade.
> >> >>
> >> >> Just before starting this process, the installation
> >> gives
> >> >> me a "Warning" :
> >> >>
> >> >> ****************
> >> >> Inconsistency Report for database : yumyum
> >> >> ****************
> >> >> dbo.sp_ImporterEnteteCommande
> >> >> Procedure
> >> >> Text Not found
> >> >> --
> >> >>
> >> >> What can I do for that?
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||The c++ programm was not working because the call to a
stored procedure in SQL 2000 must be without () if you
don't have parameters.
sp_SelectSecurite() must be :
sp_SelectSecurite
It's working well now, even with the inconsistency error.
Thanks guys

Inconsisten Crystal Report

Hiya,

Been putting a report together in Crystal Reports X, and its giving inconsisten results.

I have 3 charts that are being created over a set of data, 2 are fine, but 1 is being inconsistent.

Its using a formula with a global array to store sales figures for the next 12 months (its something with the dataset), doing a bunch of calcs for current months sales, and rolling those figures forwards.

When you first run the report, it comes up with a set of values that bear no relation to the actual data.

If you then go in and just add a blank line to the formula and refresh it, the report runs exactly as expected.

Very strange, and totally repeatable, kinda clueless on what to do with it.Can you post the formula you used?
Also make sure you verify database everytime the table schema changes

Incomplete Exports

Hello,
I hope that someone will be able to help us with a perplexing issue. When
generating a reporting in either Excel or PDF every 5th report or so, we will
get a corrupt / damaged file. The issue is repeatable but not reproducible.
The same report will fail a random number of times in a row, but will then
execute correctly. It appears that the corrupt file is really truncated.
The reports are big, but we don't have any very long columns or many rows.
However, they do contain quite a few charts. The final file size in excel
is ~1.4 MBs.
We have been unable to locate any errors in the logs. The web site
behaves like it successfully created the report. I've watched the memory
usage and it does not appear to be memory bound or disk bound.
I'm hoping for any suggestions on avenues to try or finding someone with a
similar problem.
Thank You,
--Chris Swinefurth
MID Technologies, Inc.You may want to try this:
http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
--
Adrian M.
MCP
"Chris Swinefurth" <Chris Swinefurth@.discussions.microsoft.com> wrote in
message news:241B48D8-8163-4952-920D-1FE019DB92D9@.microsoft.com...
> Hello,
> I hope that someone will be able to help us with a perplexing issue.
> When
> generating a reporting in either Excel or PDF every 5th report or so, we
> will
> get a corrupt / damaged file. The issue is repeatable but not
> reproducible.
> The same report will fail a random number of times in a row, but will then
> execute correctly. It appears that the corrupt file is really truncated.
> The reports are big, but we don't have any very long columns or many
> rows.
> However, they do contain quite a few charts. The final file size in excel
> is ~1.4 MBs.
> We have been unable to locate any errors in the logs. The web site
> behaves like it successfully created the report. I've watched the memory
> usage and it does not appear to be memory bound or disk bound.
> I'm hoping for any suggestions on avenues to try or finding someone with
> a
> similar problem.
> Thank You,
> --Chris Swinefurth
> MID Technologies, Inc.
>|||Adrian,
Thank you for your reply. Unfortuntely, we are unable to utilize ether
suggesting in the KB article. 1) Sending a link to a report will not work as
we're pulling data from a non-database provider and we need a point-in-time
report. I believe that sending a link will produce another execution of the
report when the link is clicked and not when the subscription email is sent.
2) We've tried web archives, but unfortunately, printing becomes an issue.
IE renders extra pages and cuts off parts of the graphs.
Thanks again for your help. Any more suggestions would be greatly
appreciated.
--Chris
"Adrian M." wrote:
> You may want to try this:
> http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> --
> Adrian M.
> MCP
>
> "Chris Swinefurth" <Chris Swinefurth@.discussions.microsoft.com> wrote in
> message news:241B48D8-8163-4952-920D-1FE019DB92D9@.microsoft.com...
> > Hello,
> > I hope that someone will be able to help us with a perplexing issue.
> > When
> > generating a reporting in either Excel or PDF every 5th report or so, we
> > will
> > get a corrupt / damaged file. The issue is repeatable but not
> > reproducible.
> > The same report will fail a random number of times in a row, but will then
> > execute correctly. It appears that the corrupt file is really truncated.
> > The reports are big, but we don't have any very long columns or many
> > rows.
> > However, they do contain quite a few charts. The final file size in excel
> > is ~1.4 MBs.
> > We have been unable to locate any errors in the logs. The web site
> > behaves like it successfully created the report. I've watched the memory
> > usage and it does not appear to be memory bound or disk bound.
> > I'm hoping for any suggestions on avenues to try or finding someone with
> > a
> > similar problem.
> > Thank You,
> > --Chris Swinefurth
> > MID Technologies, Inc.
> >
>
>