This is in sql 2005
I have a query that is returning a set of rows each with one xml field
row1 -<apple></apple>
row3 -<orange></orange>
row2-<grape></grape>
I want the output to look like this
<fruits>
<apple></apple>
<orange></orange>
<grape></grape>
</fruits>
However when I use FOR XML AUTO, root(''Fruits'')
<fruits>
<fruit><apple></apple></fruit>
<fruit><orange></orange></fruit>
<fruit> <grape></grape></fruit>
</fruits>
How do I remove the extra fruit element?
ENDHello Hyper,
Try using a FOR XML PATH query instead, ala for xml path (''),root('fruits')
,type
Thanks!
Kent
> This is in sql 2005
> I have a query that is returning a set of rows each with one xml field
> row1 -<apple></apple>
> row3 -<orange></orange>
> row2-<grape></grape>
> I want the output to look like this
> <fruits>
> <apple></apple>
> <orange></orange>
> <grape></grape>
> </fruits>
> However when I use FOR XML AUTO, root(''Fruits'')
> <fruits>
> <fruit><apple></apple></fruit>
> <fruit><orange></orange></fruit>
> <fruit> <grape></grape></fruit>
> </fruits>
> How do I remove the extra fruit element?
> END
>
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
Showing posts with label extra. Show all posts
Showing posts with label extra. Show all posts
Tuesday, March 27, 2012
Extra white space in table
I've got a table with a header, group, and detail row. The detail row
is collaspable however when it's collasped there is several lines of
white space between the parent groups. How do I get rid of this white
space?
Thanks in advance for the helpIs it possible that the white space is really the detail row, and it is
not shrinking as expected? (I've had this same problem before...) If
you are supressing just a column or two, but not hiding the entire
detail row (such as setting visibility --> hidden property based upon
conditional expression) then you will get the spacing issue as
described.|||Thanks Matt
That was the problem. I was setting the visibility at the cell level
when i should have been setting it on the over all row. thanks for the
help
Mathiassql
is collaspable however when it's collasped there is several lines of
white space between the parent groups. How do I get rid of this white
space?
Thanks in advance for the helpIs it possible that the white space is really the detail row, and it is
not shrinking as expected? (I've had this same problem before...) If
you are supressing just a column or two, but not hiding the entire
detail row (such as setting visibility --> hidden property based upon
conditional expression) then you will get the spacing issue as
described.|||Thanks Matt
That was the problem. I was setting the visibility at the cell level
when i should have been setting it on the over all row. thanks for the
help
Mathiassql
Extra White Space
Hi All,
I've got a report that is set up with the Report Properties layout as
follows: page width = 8.5in; page height = 11in; left/right margins = .25in;
top/bottom margins = .50in.
When the report displays in IE7, there is a large white area (blank) to the
right of the report and it's full length. When I export as a PDF document,
this area seems to cause extra pages to be created in the PDF output.
Since I'm not sure "why" this extra white space is appearing, I thought I'd
post this here.
Thanks in advance!
Kind regards - FredHi Fred,
I've found, that meters of extra white space on the right side, before all
when viewed in HTML, are created when there are overlapping visible textboxes.
When textboxes overlap where one is invisible, there are also minor layout
differences, but never so dramatically.
Yours, Henry
I've got a report that is set up with the Report Properties layout as
follows: page width = 8.5in; page height = 11in; left/right margins = .25in;
top/bottom margins = .50in.
When the report displays in IE7, there is a large white area (blank) to the
right of the report and it's full length. When I export as a PDF document,
this area seems to cause extra pages to be created in the PDF output.
Since I'm not sure "why" this extra white space is appearing, I thought I'd
post this here.
Thanks in advance!
Kind regards - FredHi Fred,
I've found, that meters of extra white space on the right side, before all
when viewed in HTML, are created when there are overlapping visible textboxes.
When textboxes overlap where one is invisible, there are also minor layout
differences, but never so dramatically.
Yours, Henry
Extra Trailing Whitespace Doesn't Violate Foreign Key Constrai
To check whether it was off, try this run this and see if the value is 1. If
so, then the trailing blank spaces are lost
select ColumnProperty(object_id('ProductTable')
, 'Product One',
'UsesAnsiTrim')On Tue, 30 May 2006 07:43:03 -0700, Omnibuzz wrote:
> To check whether it was off, try this run this and see if the value is 1.
If
> so, then the trailing blank spaces are lost
> select ColumnProperty(object_id('ProductTable')
, 'Product One',
> 'UsesAnsiTrim')
Yes, the value of this is 1, so ANSI_PADDING is on. As far as I can tell,
ANSI_PADDING is ON per the default and I have never done anything to
change this.
Thanks,
Josh|||It would have been set at the time of the table creation.
Its set at runtime.
"Josh T" wrote:
> On Tue, 30 May 2006 07:43:03 -0700, Omnibuzz wrote:
>
>
> Yes, the value of this is 1, so ANSI_PADDING is on. As far as I can tell,
> ANSI_PADDING is ON per the default and I have never done anything to
> change this.
> Thanks,
> Josh
>
so, then the trailing blank spaces are lost
select ColumnProperty(object_id('ProductTable')
, 'Product One',
'UsesAnsiTrim')On Tue, 30 May 2006 07:43:03 -0700, Omnibuzz wrote:
> To check whether it was off, try this run this and see if the value is 1.
If
> so, then the trailing blank spaces are lost
> select ColumnProperty(object_id('ProductTable')
, 'Product One',
> 'UsesAnsiTrim')
Yes, the value of this is 1, so ANSI_PADDING is on. As far as I can tell,
ANSI_PADDING is ON per the default and I have never done anything to
change this.
Thanks,
Josh|||It would have been set at the time of the table creation.
Its set at runtime.
"Josh T" wrote:
> On Tue, 30 May 2006 07:43:03 -0700, Omnibuzz wrote:
>
>
> Yes, the value of this is 1, so ANSI_PADDING is on. As far as I can tell,
> ANSI_PADDING is ON per the default and I have never done anything to
> change this.
> Thanks,
> Josh
>
Extra Space....
Hello!
I have written a report that is being displayed in a table. For some reason all the way to the right of the table has a HUGE amount of space......I checked to make sure there was no layers over it and I looked in the properties and saw no padding....ANY IDEAS? I am so frusterated because this defies all logic!!! Has anybody experienced this?
TIA!
That happened to me and I checked the width of the object that contained the table. In my case, the width of the rectangle that contained my table was way bigger than it should have been.|||Oh Thank you for responding!! Are you talking about the width of the table itself....or the width of the page?
Thanks Again!
|||Hi,
Check what the parent of the table is. If it says "body", then that is what you would adjust the width of.
|||Oh great! thanks!
Extra space at bottom of page
I keep getting extra space at the bottom of each page on my report when it is deployed to the web server but I don't get this extra space on the report server. Does anyone know if this is in the report layout or if the problem is in the web page itself?
Thanks,
messed around with report printer properties and re-deployed.sqlextra space at bottom
Hi;
i want to add extra separation between lines when a field has certain
value... right now, i tried bottom padding and Lineheight but none seems to
work...
this is the expression i had on my table detail's line bottom pading :
=iif(fields!UTIL.value="T",10,2)
Any advice?found the problem...
i has to be: =iif(fields!UTIL.value="T","10pt","2pt")
"Willo" <willoberto@.yahoo.com.mx> wrote in message
news:%235Wc$9FgHHA.668@.TK2MSFTNGP05.phx.gbl...
> Hi;
> i want to add extra separation between lines when a field has certain
> value... right now, i tried bottom padding and Lineheight but none seems
> to work...
> this is the expression i had on my table detail's line bottom pading :
> =iif(fields!UTIL.value="T",10,2)
> Any advice?
>
>
i want to add extra separation between lines when a field has certain
value... right now, i tried bottom padding and Lineheight but none seems to
work...
this is the expression i had on my table detail's line bottom pading :
=iif(fields!UTIL.value="T",10,2)
Any advice?found the problem...
i has to be: =iif(fields!UTIL.value="T","10pt","2pt")
"Willo" <willoberto@.yahoo.com.mx> wrote in message
news:%235Wc$9FgHHA.668@.TK2MSFTNGP05.phx.gbl...
> Hi;
> i want to add extra separation between lines when a field has certain
> value... right now, i tried bottom padding and Lineheight but none seems
> to work...
> this is the expression i had on my table detail's line bottom pading :
> =iif(fields!UTIL.value="T",10,2)
> Any advice?
>
>
Extra Row Needed
Here is the basic sql I am trying to implement:
select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123' and classid = 'A1'
group by rmpropid, classid, dtmready
order by dtmready;
Here is my result set:
A1 3 2006-07-01 00:00:00.000
A1 10 2006-08-15 00:00:00.000
A1 11 2006-09-15 00:00:00.000
A1 10 2006-10-15 00:00:00.000
A1 10 2006-11-01 00:00:00.000
A1 10 2006-11-30 00:00:00.000
If you notice, the earliest dtmready is 7/1/2006. What I need is to return an additional row when the earliest dtmready is after today. The desired row would be:
A1 0 (today's date)
Background: I am running SQL Server 2000 SP4 and the results of the query are returned to a java program at a level where I do not have the ability to create a new row. So, it would be ideal if I could create the sql that returns a row with a dtmready of today with a count of 0.Try this:
select classid, count(*) as [COUNT], dtmready
into #tempunit
from unit
where rmpropid = '123' and classid = 'A1'
group by rmpropid, classid, dtmready
order by dtmready;
if ((select min(dtmready) from #tempunit)>getdate())
insert into #tempunit values ('a1','0',getdate())
select * from #tempunit
This should work?|||Thanks for the response.
I don't think I will be allowed to create a temp table (production database bureaucracy etc.) to solve this problem. Do you have any other ideas?
Thanks, Mike|||I don't think I will be allowed to create a temp table (production database bureaucracy etc.) to solve this problem.No temp tables? That is bogus. I can see them not wanting you to create permanent tables "temporarily", but there should be nothing wrong with creating true "temp" tables.
Regardless, here is another method:select classid,
count(*) as [COUNT],
dtmready
from unit
where rmpropid = '123'
and classid = 'A1'
group by rmpropid,
classid,
dtmready
UNION
select classid,
0 as [COUNT],
getdate() as dtmready
from unit
group by classid
having min(dtmready) > getdate()
order by dtmready;|||That did it! Thanks so much for your help!
Mike
select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123' and classid = 'A1'
group by rmpropid, classid, dtmready
order by dtmready;
Here is my result set:
A1 3 2006-07-01 00:00:00.000
A1 10 2006-08-15 00:00:00.000
A1 11 2006-09-15 00:00:00.000
A1 10 2006-10-15 00:00:00.000
A1 10 2006-11-01 00:00:00.000
A1 10 2006-11-30 00:00:00.000
If you notice, the earliest dtmready is 7/1/2006. What I need is to return an additional row when the earliest dtmready is after today. The desired row would be:
A1 0 (today's date)
Background: I am running SQL Server 2000 SP4 and the results of the query are returned to a java program at a level where I do not have the ability to create a new row. So, it would be ideal if I could create the sql that returns a row with a dtmready of today with a count of 0.Try this:
select classid, count(*) as [COUNT], dtmready
into #tempunit
from unit
where rmpropid = '123' and classid = 'A1'
group by rmpropid, classid, dtmready
order by dtmready;
if ((select min(dtmready) from #tempunit)>getdate())
insert into #tempunit values ('a1','0',getdate())
select * from #tempunit
This should work?|||Thanks for the response.
I don't think I will be allowed to create a temp table (production database bureaucracy etc.) to solve this problem. Do you have any other ideas?
Thanks, Mike|||I don't think I will be allowed to create a temp table (production database bureaucracy etc.) to solve this problem.No temp tables? That is bogus. I can see them not wanting you to create permanent tables "temporarily", but there should be nothing wrong with creating true "temp" tables.
Regardless, here is another method:select classid,
count(*) as [COUNT],
dtmready
from unit
where rmpropid = '123'
and classid = 'A1'
group by rmpropid,
classid,
dtmready
UNION
select classid,
0 as [COUNT],
getdate() as dtmready
from unit
group by classid
having min(dtmready) > getdate()
order by dtmready;|||That did it! Thanks so much for your help!
Mike
Extra parameters being added onto SP call with ADO?
We are running into problems on our development environment with extra
parameters being added on to a stored procedure call. Instead of the
expected stored procedure call, the trace shows something like this:
parameters being added on to a stored procedure call. Instead of the
expected stored procedure call, the trace shows something like this:
declare @.P1 int
set @.P1=NULL
<<expected stored procedure call>> , @.P1 output, <<repeat of first
three sp parms>>
select @.P1
The developer has checked the code, and I have checked the SP - both
seem to match what is in production (which works fine). The databases
are on the same server, and the apps are running on seperate web
servers.
I'm guessing this may be some sort of configuration issue with ADO,
SQL, or something else - has anyone run into something similar to
this? Thanks!
DaveSo, is the sp running at all when called with that additional parameter? It
should fail if that parameter (the one you think is additional) is not
declared in the sp.
What happens if you paste the output from Profiler into Query Analyzer and
run it?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dokter Z" <dzahn@.execpc.com> wrote in message
news:7e6a5a6.0402250644.4ef950eb@.posting.google.co m...
We are running into problems on our development environment with extra
parameters being added on to a stored procedure call. Instead of the
expected stored procedure call, the trace shows something like this:
declare @.P1 int
set @.P1=NULL
<<expected stored procedure call>> , @.P1 output, <<repeat of first
three sp parms>>
select @.P1
The developer has checked the code, and I have checked the SP - both
seem to match what is in production (which works fine). The databases
are on the same server, and the apps are running on seperate web
servers.
I'm guessing this may be some sort of configuration issue with ADO,
SQL, or something else - has anyone run into something similar to
this? Thanks!
Dave|||Vyas -
Thanks for the response. Actually, we have discovered that the initial
problem was caused by
1) Some "sub-optimal" Paramaters.Refresh code
2) A stored procedure call issued by user aaaaa ended up calling
bbbbb.stored_proc_name instead of dbo.stored_proc_name. I'm currently
researching that issue...
Dave
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Extra Pages with Landscape
I have a report that is landscape (11w x 8.5h). The left margin is .2 and the
right margin is .2 and the body is 10.149w. The total (10.549) is under the
11 inches of the width but when I go to print preview, the report is 15 pages
with only 10 containing data. The last five pages are blank.
I then go to print it and the printer still sees the report a portrait
document instead of landscape so I have to manually adjust for this each
time.
Any ideas or suggestions?
I thought that this would be a good challenge for Peter Blackburn...In Visual Studio report designer make sure that the white canvas page is at
minimum size.
If the canvas (The white page you start with when designing a report) is
greater than the width of your paper then the print out will strech more
pagers...
"Kipp C" wrote:
> I have a report that is landscape (11w x 8.5h). The left margin is .2 and the
> right margin is .2 and the body is 10.149w. The total (10.549) is under the
> 11 inches of the width but when I go to print preview, the report is 15 pages
> with only 10 containing data. The last five pages are blank.
> I then go to print it and the printer still sees the report a portrait
> document instead of landscape so I have to manually adjust for this each
> time.
> Any ideas or suggestions?
> I thought that this would be a good challenge for Peter Blackburn...
right margin is .2 and the body is 10.149w. The total (10.549) is under the
11 inches of the width but when I go to print preview, the report is 15 pages
with only 10 containing data. The last five pages are blank.
I then go to print it and the printer still sees the report a portrait
document instead of landscape so I have to manually adjust for this each
time.
Any ideas or suggestions?
I thought that this would be a good challenge for Peter Blackburn...In Visual Studio report designer make sure that the white canvas page is at
minimum size.
If the canvas (The white page you start with when designing a report) is
greater than the width of your paper then the print out will strech more
pagers...
"Kipp C" wrote:
> I have a report that is landscape (11w x 8.5h). The left margin is .2 and the
> right margin is .2 and the body is 10.149w. The total (10.549) is under the
> 11 inches of the width but when I go to print preview, the report is 15 pages
> with only 10 containing data. The last five pages are blank.
> I then go to print it and the printer still sees the report a portrait
> document instead of landscape so I have to manually adjust for this each
> time.
> Any ideas or suggestions?
> I thought that this would be a good challenge for Peter Blackburn...
Extra page in print preview and .pdf
I have tried and tried to get my reports to quit printing an extra blank page but have been unsuccessful in my efforts.
I've set the margins in my report properties to (all in inches):
.75 - top
.5 - left, right, bottom
My page is landscape so the width is 11 and the height is 8.5
I've made sure my table is not wider than 9.5 - I've even made it as small as 8" wide to see if that was the problem . (I've done this for the header/footer as well.)
My report prints all on one page but I still get a 2nd page with header/footer information and a blank body.
I read a post that said to try making the right margin 0 - that didn't work either.
Additional information: I just saw a post suggesting borders around everything to see what might be bleeding onto a 2nd page.
One page 1, the full borders are there, so nothing should be bleeding onto page 2. However, when I go to page 2, there are no borders around the header, but the left edge shows on the right side of the page. Also, the footer is missing it's left edge, but the right shows. I've tried making both the header and footer smaller, as well as moving them further right - still get the exact issue.
I'm using the reporting services for Visual Studio .NET 2003.
Any help would be greatly appreciated! Thanks!
Well, turns out it was the layout. Although my report elements were the right size, the layout was too big, forcing the margins over.|||It is COOL that your posted your results!
|||
Hello - just a general comment!
I have eliminated the capability to export to PDF within RS in that PDF NEVER renders any ledgeable characters in my part of the world which includes:
Thailand, Cambodia, Laos, Mayamar (Burma), Vietnam, Pakistan and Tibet and this also includes any dialect in Mandarin!
We just stick to a TIF and everything is always perfect -
|||
When you say your layout was too big, what did you set it too?
Just curious, since I was having an issue similar to yours although I might not have worded it as clearly
![Wink [;)]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_uDnuGedb8rYvEw5KMFtbkxfovoUqV_zJ58BkfexAKJe_V1td9cWKImMyI4mAiGZ_3oKITVtXmIe4orAbgLzQLa6LTvcCcw9lo_4Htx-nPFPyfQSgtgBw=s0-d)
http://forums.asp.net/1139722/ShowPost.aspx
Thanks
sqlExtra Page in Adobe Export
I also have the same problem. When I export the report out in pdf format,
there is an extra page.
I am running RSR2 and adobe 7.0.2
Is there any solution to this?yes,
Make sure there is no extra space in the report body after a table and chart
"qjlee" wrote:
> I also have the same problem. When I export the report out in pdf format,
> there is an extra page.
> I am running RSR2 and adobe 7.0.2
> Is there any solution to this?
there is an extra page.
I am running RSR2 and adobe 7.0.2
Is there any solution to this?yes,
Make sure there is no extra space in the report body after a table and chart
"qjlee" wrote:
> I also have the same problem. When I export the report out in pdf format,
> there is an extra page.
> I am running RSR2 and adobe 7.0.2
> Is there any solution to this?
Extra Page in Adobe Export
Using SRS SP2 and Adobe 7.0.2
When my report renders in the preview pane it it limited to one page but
when exported to Adobe it prints an extra page. The report has Page
Header/Body/Page Footer sections.
Is this a bug or am I missing something in the config?
ThanksI have the same problem. Would like to know if there is a solution.
"Mike Harbinger" wrote:
> Using SRS SP2 and Adobe 7.0.2
> When my report renders in the preview pane it it limited to one page but
> when exported to Adobe it prints an extra page. The report has Page
> Header/Body/Page Footer sections.
> Is this a bug or am I missing something in the config?
> Thanks
>
>|||make sure there is no extra space after the table and chart
"Mike Harbinger" wrote:
> Using SRS SP2 and Adobe 7.0.2
> When my report renders in the preview pane it it limited to one page but
> when exported to Adobe it prints an extra page. The report has Page
> Header/Body/Page Footer sections.
> Is this a bug or am I missing something in the config?
> Thanks
>
>|||That was it! I had moved a field in from the right margin and after
tightening it up the extra page was gone.
Many thanks!
"RP" <RP@.discussions.microsoft.com> wrote in message
news:94E1BCC1-F540-46DE-95F7-56F242097113@.microsoft.com...
> make sure there is no extra space after the table and chart
> "Mike Harbinger" wrote:
>> Using SRS SP2 and Adobe 7.0.2
>> When my report renders in the preview pane it it limited to one page but
>> when exported to Adobe it prints an extra page. The report has Page
>> Header/Body/Page Footer sections.
>> Is this a bug or am I missing something in the config?
>> Thanks
>>
When my report renders in the preview pane it it limited to one page but
when exported to Adobe it prints an extra page. The report has Page
Header/Body/Page Footer sections.
Is this a bug or am I missing something in the config?
ThanksI have the same problem. Would like to know if there is a solution.
"Mike Harbinger" wrote:
> Using SRS SP2 and Adobe 7.0.2
> When my report renders in the preview pane it it limited to one page but
> when exported to Adobe it prints an extra page. The report has Page
> Header/Body/Page Footer sections.
> Is this a bug or am I missing something in the config?
> Thanks
>
>|||make sure there is no extra space after the table and chart
"Mike Harbinger" wrote:
> Using SRS SP2 and Adobe 7.0.2
> When my report renders in the preview pane it it limited to one page but
> when exported to Adobe it prints an extra page. The report has Page
> Header/Body/Page Footer sections.
> Is this a bug or am I missing something in the config?
> Thanks
>
>|||That was it! I had moved a field in from the right margin and after
tightening it up the extra page was gone.
Many thanks!
"RP" <RP@.discussions.microsoft.com> wrote in message
news:94E1BCC1-F540-46DE-95F7-56F242097113@.microsoft.com...
> make sure there is no extra space after the table and chart
> "Mike Harbinger" wrote:
>> Using SRS SP2 and Adobe 7.0.2
>> When my report renders in the preview pane it it limited to one page but
>> when exported to Adobe it prints an extra page. The report has Page
>> Header/Body/Page Footer sections.
>> Is this a bug or am I missing something in the config?
>> Thanks
>>
Extra Page for a Key or Legend
I would like to know if I can create an extra page in a dynamic report that
will appear as the final page in a report and house the Key Legend and
glossary for the contained report.
I can think of two ways in theory that it can be done one by being able to
manipulate the page break to creak a final page and another to conbine two
seperate reports at runtime.
This is however a theory and I by no means think either of these is possible.
Does anyone have any Ideas at all on this?
Please and thank you.How about putting this information in its own table at the end of what you
currently have and then going into the properties of the table and choose
page break before this table? I've done this before.
"pcrtrg" wrote:
> I would like to know if I can create an extra page in a dynamic report that
> will appear as the final page in a report and house the Key Legend and
> glossary for the contained report.
> I can think of two ways in theory that it can be done one by being able to
> manipulate the page break to creak a final page and another to conbine two
> seperate reports at runtime.
> This is however a theory and I by no means think either of these is possible.
> Does anyone have any Ideas at all on this?
> Please and thank you.
will appear as the final page in a report and house the Key Legend and
glossary for the contained report.
I can think of two ways in theory that it can be done one by being able to
manipulate the page break to creak a final page and another to conbine two
seperate reports at runtime.
This is however a theory and I by no means think either of these is possible.
Does anyone have any Ideas at all on this?
Please and thank you.How about putting this information in its own table at the end of what you
currently have and then going into the properties of the table and choose
page break before this table? I've done this before.
"pcrtrg" wrote:
> I would like to know if I can create an extra page in a dynamic report that
> will appear as the final page in a report and house the Key Legend and
> glossary for the contained report.
> I can think of two ways in theory that it can be done one by being able to
> manipulate the page break to creak a final page and another to conbine two
> seperate reports at runtime.
> This is however a theory and I by no means think either of these is possible.
> Does anyone have any Ideas at all on this?
> Please and thank you.
Extra nextrecordset for sp_help in ASP
I'm running "sp_help mytable1" from ASP against SQL Server 2000 over ADODB. Other T-SQL commands work fine, including commands giving more than one result set. But, for this very command, the 6th and 7th record sets give:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
when I try to fetch the data.
I have a loop that, for any returned record sets, displays all data.
If I, instead of looping over all record sets, code one data-displaying loop per record set (i.e. a "manual" variant), the sp_help call works provided that I, before displaying the 6th and 7th record set, code an extra "set rs = rs.nextrecordset". I.e., for those record sets giving the above error, I have to do an extra nextrecordset.
So far, it's only with sp_help tablename this has occurred.
The general loop works fine in VB6 including the two problem recordsets. It's only in ASP that record set no. 6 and 7 are inaccessible without extra Next's.
Any hints?Can you post your asp code ?|||ASP code (the general one) follows below.
Enter sp_help mytable1 in a HTML form calling it.
<html>
<%
REM *************************
REM Subrutiner
REM *************************
REM Print a recordset's field details
sub printfields
FOR EACH FIELD IN rs.FIELDS
response.write("<p>Field name: " & FIELD.NAME)
response.write("<br>Type: " & LTRIM(cSTR(FIELD.TYPE)))
response.write("<br>Defined size: " & LTRIM(cSTR(FIELD.DEFINEDSIZE)))
response.write("<br>Actual size: " & LTRIM(cSTR(FIELD.ACTUALSIZE)))
NEXT
end sub
REM *************************
REM Huvudrutinen
REM *************************
REM *** Create connection ***
dim conn
set conn = createobject("ADODB.Connection.2.6")
conn.provider = "SQLOLEDB"
conn.connectionstring = "data source=myserver1;
initial catalog=mydatabase1;Integrated Security=SSPI;Trusted_Connection=Yes;"
REM *** Open and run ***
conn.open
set rs = conn.execute( request.form("sqlcmd"))
REM *** Output to web client ***
response.write("<u>" & request.form("sqlcmd") & "</u><br>")
REM printfields
do until rs is Nothing
do until rs.eof
for each field in rs.fields
response.write("<br>" & field.name & " = " & rs(field.name))
next
response.write("<br>----------")
rs.movenext
loop
set rs = rs.nextrecordset
loop
REM *** Close connection ***
conn.close
set conn = nothing
%>
</html>|||I *think* I've solved it by checking if the next recordset is closed - if so, do an extra next recordset. Code included below. Still don't understand why the recordset get a State value of "closed".
(How do I write an "if object is NOT nothing then..." ?)
<html>
<%
REM *** Create connection ***
dim conn
set conn = createobject("ADODB.Connection.2.6")
conn.provider = "SQLOLEDB"
conn.connectionstring = "data source=myserver1;
initial catalog=mydatabase1;
Integrated Security=SSPI;Trusted_Connection=Yes;"
REM *** Open and run ***
conn.open
set rs = conn.execute( request.form("sqlcmd"))
REM *** Output to web client ***
response.write("<u>" & request.form("sqlcmd") & "</u><br>")
REM do until rs.state = adStateClosed
do until rs is Nothing
do until rs.eof
for each field in rs.fields
response.write("<br>" & field.name & " = " & rs(field.name))
next
response.write("<br>----------")
rs.movenext
loop
REM next recordset
set rs = rs.nextrecordset
REM if there is a next recordset, check if it's closed and if so, go to next after this
REM Don't know why it's needed.
if rs is nothing then
response.write("nop")
else
if rs.state = adStateClosed then
set rs = rs.nextrecordset
end if
end if
loop
REM *** Close connection ***
conn.close
set conn = nothing
%>
</html>sql
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
when I try to fetch the data.
I have a loop that, for any returned record sets, displays all data.
If I, instead of looping over all record sets, code one data-displaying loop per record set (i.e. a "manual" variant), the sp_help call works provided that I, before displaying the 6th and 7th record set, code an extra "set rs = rs.nextrecordset". I.e., for those record sets giving the above error, I have to do an extra nextrecordset.
So far, it's only with sp_help tablename this has occurred.
The general loop works fine in VB6 including the two problem recordsets. It's only in ASP that record set no. 6 and 7 are inaccessible without extra Next's.
Any hints?Can you post your asp code ?|||ASP code (the general one) follows below.
Enter sp_help mytable1 in a HTML form calling it.
<html>
<%
REM *************************
REM Subrutiner
REM *************************
REM Print a recordset's field details
sub printfields
FOR EACH FIELD IN rs.FIELDS
response.write("<p>Field name: " & FIELD.NAME)
response.write("<br>Type: " & LTRIM(cSTR(FIELD.TYPE)))
response.write("<br>Defined size: " & LTRIM(cSTR(FIELD.DEFINEDSIZE)))
response.write("<br>Actual size: " & LTRIM(cSTR(FIELD.ACTUALSIZE)))
NEXT
end sub
REM *************************
REM Huvudrutinen
REM *************************
REM *** Create connection ***
dim conn
set conn = createobject("ADODB.Connection.2.6")
conn.provider = "SQLOLEDB"
conn.connectionstring = "data source=myserver1;
initial catalog=mydatabase1;Integrated Security=SSPI;Trusted_Connection=Yes;"
REM *** Open and run ***
conn.open
set rs = conn.execute( request.form("sqlcmd"))
REM *** Output to web client ***
response.write("<u>" & request.form("sqlcmd") & "</u><br>")
REM printfields
do until rs is Nothing
do until rs.eof
for each field in rs.fields
response.write("<br>" & field.name & " = " & rs(field.name))
next
response.write("<br>----------")
rs.movenext
loop
set rs = rs.nextrecordset
loop
REM *** Close connection ***
conn.close
set conn = nothing
%>
</html>|||I *think* I've solved it by checking if the next recordset is closed - if so, do an extra next recordset. Code included below. Still don't understand why the recordset get a State value of "closed".
(How do I write an "if object is NOT nothing then..." ?)
<html>
<%
REM *** Create connection ***
dim conn
set conn = createobject("ADODB.Connection.2.6")
conn.provider = "SQLOLEDB"
conn.connectionstring = "data source=myserver1;
initial catalog=mydatabase1;
Integrated Security=SSPI;Trusted_Connection=Yes;"
REM *** Open and run ***
conn.open
set rs = conn.execute( request.form("sqlcmd"))
REM *** Output to web client ***
response.write("<u>" & request.form("sqlcmd") & "</u><br>")
REM do until rs.state = adStateClosed
do until rs is Nothing
do until rs.eof
for each field in rs.fields
response.write("<br>" & field.name & " = " & rs(field.name))
next
response.write("<br>----------")
rs.movenext
loop
REM next recordset
set rs = rs.nextrecordset
REM if there is a next recordset, check if it's closed and if so, go to next after this
REM Don't know why it's needed.
if rs is nothing then
response.write("nop")
else
if rs.state = adStateClosed then
set rs = rs.nextrecordset
end if
end if
loop
REM *** Close connection ***
conn.close
set conn = nothing
%>
</html>sql
Extra logging in SQL error log
Hi All,
Hoping someone can assist with this. Is there an SQL trace flag, or
other method, where I can get the SQL server error logs (2000 and
2005) to log database detachements, and changes in DB status (eg to
read only from online etc).
I thought about a trigger on sysdatabases (sys.databases) but am
highly skeptical of triggers on system tables.
Why am I doing this? I need to monitor the up time of individual
databases on an instance, and third party tools are not an option.
thanks in advance.Hi
If you are on SQL Server 2005 you can create TRIGGER on database level (see
BOL for details)
To see error log
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6908c21a-65e3-458f-a272-fee256d86448.htm
<snafu66@.hotmail.co.uk> wrote in message
news:1188442379.790535.109850@.m37g2000prh.googlegroups.com...
> Hi All,
> Hoping someone can assist with this. Is there an SQL trace flag, or
> other method, where I can get the SQL server error logs (2000 and
> 2005) to log database detachements, and changes in DB status (eg to
> read only from online etc).
> I thought about a trigger on sysdatabases (sys.databases) but am
> highly skeptical of triggers on system tables.
> Why am I doing this? I need to monitor the up time of individual
> databases on an instance, and third party tools are not an option.
> thanks in advance.
>
Hoping someone can assist with this. Is there an SQL trace flag, or
other method, where I can get the SQL server error logs (2000 and
2005) to log database detachements, and changes in DB status (eg to
read only from online etc).
I thought about a trigger on sysdatabases (sys.databases) but am
highly skeptical of triggers on system tables.
Why am I doing this? I need to monitor the up time of individual
databases on an instance, and third party tools are not an option.
thanks in advance.Hi
If you are on SQL Server 2005 you can create TRIGGER on database level (see
BOL for details)
To see error log
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6908c21a-65e3-458f-a272-fee256d86448.htm
<snafu66@.hotmail.co.uk> wrote in message
news:1188442379.790535.109850@.m37g2000prh.googlegroups.com...
> Hi All,
> Hoping someone can assist with this. Is there an SQL trace flag, or
> other method, where I can get the SQL server error logs (2000 and
> 2005) to log database detachements, and changes in DB status (eg to
> read only from online etc).
> I thought about a trigger on sysdatabases (sys.databases) but am
> highly skeptical of triggers on system tables.
> Why am I doing this? I need to monitor the up time of individual
> databases on an instance, and third party tools are not an option.
> thanks in advance.
>
Extra Lines in ReportViewer (lots of em)
Any idea where these extra lines are coming from in the detail? The data is being summarized correctly. I just need to dump all the extra space. I'm one stumped kahuna.
Extra empty pages in PDF report
Report looks great in Visual studio and in a Browser, but when I convert it
to pdf for printing, I am getting extra pages after every good page with just
a header printed on those pages.
I tried decrease the size of the report and make sure that it fits into 8
1/2 x 11 inches, but it did not help.
I still have those annoying empty pages in the printed report.
Any other suggestions on how can I fix it?
Thank you.Simon if are you having problem with client side printing then you will want
to try the following.
Try setting body width to 8.46 if you are using letter/portrait. If you are
using letter/landscape then set the width to 10.96.
I hope that helps.
This is an issue Microsorft is now aware of.
"Simon Gold" wrote:
> Report looks great in Visual studio and in a Browser, but when I convert it
> to pdf for printing, I am getting extra pages after every good page with just
> a header printed on those pages.
> I tried decrease the size of the report and make sure that it fits into 8
> 1/2 x 11 inches, but it did not help.
> I still have those annoying empty pages in the printed report.
> Any other suggestions on how can I fix it?
> Thank you.
>|||Thanks Kevin. I figured it out about one hour before I got your e-mail. :-)
"Kevin Weir" wrote:
> Simon if are you having problem with client side printing then you will want
> to try the following.
> Try setting body width to 8.46 if you are using letter/portrait. If you are
> using letter/landscape then set the width to 10.96.
> I hope that helps.
> This is an issue Microsorft is now aware of.
> "Simon Gold" wrote:
> > Report looks great in Visual studio and in a Browser, but when I convert it
> > to pdf for printing, I am getting extra pages after every good page with just
> > a header printed on those pages.
> > I tried decrease the size of the report and make sure that it fits into 8
> > 1/2 x 11 inches, but it did not help.
> > I still have those annoying empty pages in the printed report.
> > Any other suggestions on how can I fix it?
> > Thank you.
> >
to pdf for printing, I am getting extra pages after every good page with just
a header printed on those pages.
I tried decrease the size of the report and make sure that it fits into 8
1/2 x 11 inches, but it did not help.
I still have those annoying empty pages in the printed report.
Any other suggestions on how can I fix it?
Thank you.Simon if are you having problem with client side printing then you will want
to try the following.
Try setting body width to 8.46 if you are using letter/portrait. If you are
using letter/landscape then set the width to 10.96.
I hope that helps.
This is an issue Microsorft is now aware of.
"Simon Gold" wrote:
> Report looks great in Visual studio and in a Browser, but when I convert it
> to pdf for printing, I am getting extra pages after every good page with just
> a header printed on those pages.
> I tried decrease the size of the report and make sure that it fits into 8
> 1/2 x 11 inches, but it did not help.
> I still have those annoying empty pages in the printed report.
> Any other suggestions on how can I fix it?
> Thank you.
>|||Thanks Kevin. I figured it out about one hour before I got your e-mail. :-)
"Kevin Weir" wrote:
> Simon if are you having problem with client side printing then you will want
> to try the following.
> Try setting body width to 8.46 if you are using letter/portrait. If you are
> using letter/landscape then set the width to 10.96.
> I hope that helps.
> This is an issue Microsorft is now aware of.
> "Simon Gold" wrote:
> > Report looks great in Visual studio and in a Browser, but when I convert it
> > to pdf for printing, I am getting extra pages after every good page with just
> > a header printed on those pages.
> > I tried decrease the size of the report and make sure that it fits into 8
> > 1/2 x 11 inches, but it did not help.
> > I still have those annoying empty pages in the printed report.
> > Any other suggestions on how can I fix it?
> > Thank you.
> >
Extra columns in Excel with SP2
I would like to upgrade to Reporting Services service pack 2 so that I can
specify report colours and specify to move the header into Excel's header.
However when we upgrade to Service Pack 2 it causes extra totals columns to
appear at the end of the months in the generated reports. These columns
could possibly acceptable if they were titled as Total however they are not.
Can anybody make any suggestions in how to generate the report in excel
without the additional columns appearing in the excel output? Many thanks.Does anybody have any ideas about how to do this - any guesses even would be
appreciated. Thanks
"Nicola Jones" wrote:
> I would like to upgrade to Reporting Services service pack 2 so that I can
> specify report colours and specify to move the header into Excel's header.
> However when we upgrade to Service Pack 2 it causes extra totals columns to
> appear at the end of the months in the generated reports. These columns
> could possibly acceptable if they were titled as Total however they are not.
> Can anybody make any suggestions in how to generate the report in excel
> without the additional columns appearing in the excel output? Many thanks.
specify report colours and specify to move the header into Excel's header.
However when we upgrade to Service Pack 2 it causes extra totals columns to
appear at the end of the months in the generated reports. These columns
could possibly acceptable if they were titled as Total however they are not.
Can anybody make any suggestions in how to generate the report in excel
without the additional columns appearing in the excel output? Many thanks.Does anybody have any ideas about how to do this - any guesses even would be
appreciated. Thanks
"Nicola Jones" wrote:
> I would like to upgrade to Reporting Services service pack 2 so that I can
> specify report colours and specify to move the header into Excel's header.
> However when we upgrade to Service Pack 2 it causes extra totals columns to
> appear at the end of the months in the generated reports. These columns
> could possibly acceptable if they were titled as Total however they are not.
> Can anybody make any suggestions in how to generate the report in excel
> without the additional columns appearing in the excel output? Many thanks.
Extra column in matrix
Hello,Month Quarter Year Mar-06 Mar-07 Q1 2006 Q1 2007 2006 2007 Company Product $1,000 $2,000 $3,000 $6,000 $12,000 $24,000 Month Quarter Year Mar-06 Mar-07 % Change Q1 2006 Q1 2007 % Change 2006 2007 % Change Company Product $1,000 $2,000 $3,000 $6,000 $12,000 $24,000 Month Quarter Year Mar-06 Mar-07 Q1 2006 Q1 2007 2006 2007 Company Product 1000 2000 3000 6000 12000 24000 Daily Data Commission (GroupData cell 1) Interest (GroupData CELL 2) GroupData etc … SUBTOTAL (DTD) MTD TOTAL YTD TOTAL Book Currency =sum(Fields!DTD.VALUE) ........ =sum(Fields!MTD.VALUE) =sum(Fields!YTD.VALUE) Book CCY GroupedData DTD MTD YTD ABC GBP Commission ￡0.01 ￡0.09 -￡0.10 ABC GBP Interest ￡0.02 ￡0.29 ￡0.11 ABC GBP Brokerage ￡0.12 ￡0.06 ￡0.20 Daily Data Grouped Data CELL 1 Grouped Data CELL 2 Book Name Currency DTD TOTAL MTD TOTAL YTD TOTAL DTD TOTAL MTD TOTAL YTD TOTAL =sum(Fields!DTD.VALUE)
I'm trying to make a report with the following layout:
Car Sales 2004 2005 Var %
Total 10 20 50%
Green 7 14 50%
Red 3 6 50%
I'm using a matrix and the data is coming from a cube. One dimension called 'Years' is used to fill the matrix columns and the data corresponding to the car sales is filled by a measure called 'Sales'.
I've built part of the example but I can't add the final column ( the VAR% column).
If I try to add a static column in the end it apears only one year.
The behavior that I would like to have is simillar to the situation when we add the Subtotal column. Although, instead the sum() made by subtotal I would like to calculate the variation percentage between years.
Is there any way to add a final column into a matrix avoiding the problem that I'm having or maybe change the behavior of Subtotal column?
Thanks and best regards.
vjn
I have a very similar need. I have two levels of columns and two row groups. I need to calculate a percentage difference between the 2nd level column values:
Change above to this:
I need to calculate a % Change column. I can't seem to find a way to do this. I can add a subtotal easily enough but how can I do a custom calculation? This is based on a sql query not an analysis cube. I had to use a matrix to "cross tab" the data. The data is not in cross tab format at the source otherwise I would just calculate the % in the query.
Can anyone help?
Thanks!
|||Right click in the cell where you need to add the extra column to its right and select Add Column from the popup menu. HTH.|||Thanks for the reply however that does not get me to what I need. Adding a column simply changes it to this:
I need the % change from Mar-06 to Mar-07, Q1 2006 to Q1 2007, and 2006 to 2007.
|||I also need to be able to add an extra column to my matrix - I've searched high and low on the net and I cant seem to find the answer anywhere.
I have grouped data which displays as it should and I can get the SubTotal part for my Dailys to run - I need to add a total for MTD and YTD (which is a part of the SQL Data) after the subtotal - is there a straight forward way of doing this?
This is an example of how I want my report to look (I can already achieve the bits highlighted bold)
and this is an example of the data being returned by my Sproc
Like the person above - if i use the "Add Column" functionality - I get a repeat of the MTD and YTD under each of the groupData cells - where as i only need it as a summary after the subtotal.
Does anyone out there know how to do it?
|||Hi Everyone,
I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.
=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))
NOTE: matrix1_FiscalMonth is my row group name
To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%
Regard,
AA
"Small things done consistently in strategic places create major impact"
sql
Subscribe to:
Posts (Atom)