Users are receiving link-only subscriptions with broken links where the break
always starts with an exclamation mark. It looks like this in email source
view:
<table><tr><td><span>My comment text added to
subscription</span></td></tr></table><table><tr><td><span>The report is
accessible at the following address:</span></td><tr><td><a
href="http://links.10026.com/?link=http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&r!
s%3aParameterLanguage=en-US">http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&rs%3aParameterLanguage=en-US</a></td></tr></table>
Any suggestions how to fix or work around this?I have updated Reporting Services to version 9.00.2040.00 but I am still
getting broken hyperlinks in subscription emails.
"PeterG" wrote:
> Users are receiving link-only subscriptions with broken links where the break
> always starts with an exclamation mark. It looks like this in email source
> view:
> <table><tr><td><span>My comment text added to
> subscription</span></td></tr></table><table><tr><td><span>The report is
> accessible at the following address:</span></td><tr><td><a
> href="http://links.10026.com/?link=http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&r!
> s%3aParameterLanguage=en-US">http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&rs%3aParameterLanguage=en-US</a></td></tr></table>
> Any suggestions how to fix or work around this?
>
Showing posts with label break. Show all posts
Showing posts with label break. Show all posts
Sunday, February 19, 2012
Thursday, February 16, 2012
Breakup a group
Thanks in advance and Im new to RS.
I have a simple dataset of date records which can span 1 or more weeks up to
a month, I want to break the list up every time a Wed is encountered as this
is the end of a reporting week and some totals need to be printed at bottom
of the list.
I have been fiddling with a table, but dont seem to be quite able to nut it
out, has anyone else done this or similar at all, comments appreciated.
Thanks, Paul
DataSet...
Tue 2/11 $77 $99 $765
wed 3/11 $50 $450 $999
<Need a break here and total of each column)
Thu 4/11 $23 $888 $987
Fri 5/11 $98 $999 $999
Sat 6/11 $33 $ 99 $999
Sun 7/11 $999 $ 99 $999
Mon 8/11 $999 $ 99 $999
Tue 9/11 $999 $ 99 $999
Wed 10/11 $999 $ 99 $999
<Break here and total>
Thu
etc etc etcPlease ignore this, figured it out in the end. Just needed a grouping value uin
my sql and voila table grouped ok as desired.
"PaulQld" wrote:
> Thanks in advance and Im new to RS.
> I have a simple dataset of date records which can span 1 or more weeks up to
> a month, I want to break the list up every time a Wed is encountered as this
> is the end of a reporting week and some totals need to be printed at bottom
> of the list.
> I have been fiddling with a table, but dont seem to be quite able to nut it
> out, has anyone else done this or similar at all, comments appreciated.
> Thanks, Paul
> DataSet...
> Tue 2/11 $77 $99 $765
> wed 3/11 $50 $450 $999
> <Need a break here and total of each column)
> Thu 4/11 $23 $888 $987
> Fri 5/11 $98 $999 $999
> Sat 6/11 $33 $ 99 $999
> Sun 7/11 $999 $ 99 $999
> Mon 8/11 $999 $ 99 $999
> Tue 9/11 $999 $ 99 $999
> Wed 10/11 $999 $ 99 $999
> <Break here and total>
> Thu
> etc etc etc
>
I have a simple dataset of date records which can span 1 or more weeks up to
a month, I want to break the list up every time a Wed is encountered as this
is the end of a reporting week and some totals need to be printed at bottom
of the list.
I have been fiddling with a table, but dont seem to be quite able to nut it
out, has anyone else done this or similar at all, comments appreciated.
Thanks, Paul
DataSet...
Tue 2/11 $77 $99 $765
wed 3/11 $50 $450 $999
<Need a break here and total of each column)
Thu 4/11 $23 $888 $987
Fri 5/11 $98 $999 $999
Sat 6/11 $33 $ 99 $999
Sun 7/11 $999 $ 99 $999
Mon 8/11 $999 $ 99 $999
Tue 9/11 $999 $ 99 $999
Wed 10/11 $999 $ 99 $999
<Break here and total>
Thu
etc etc etcPlease ignore this, figured it out in the end. Just needed a grouping value uin
my sql and voila table grouped ok as desired.
"PaulQld" wrote:
> Thanks in advance and Im new to RS.
> I have a simple dataset of date records which can span 1 or more weeks up to
> a month, I want to break the list up every time a Wed is encountered as this
> is the end of a reporting week and some totals need to be printed at bottom
> of the list.
> I have been fiddling with a table, but dont seem to be quite able to nut it
> out, has anyone else done this or similar at all, comments appreciated.
> Thanks, Paul
> DataSet...
> Tue 2/11 $77 $99 $765
> wed 3/11 $50 $450 $999
> <Need a break here and total of each column)
> Thu 4/11 $23 $888 $987
> Fri 5/11 $98 $999 $999
> Sat 6/11 $33 $ 99 $999
> Sun 7/11 $999 $ 99 $999
> Mon 8/11 $999 $ 99 $999
> Tue 9/11 $999 $ 99 $999
> Wed 10/11 $999 $ 99 $999
> <Break here and total>
> Thu
> etc etc etc
>
Breaks in Min and Max
I have a report designed as follows:
ISBN DESC MIN -PKG MAX-PKG
Intentional break in PKG numbers
The output without suppression is in Output1.png file attached
The output with suppression is in Output2.png file attached
I am using crystal reports 9. How can I use the suppression and line up the min and max to be on the same row.
Any help is greatly appreciated.
Thanks
msimhaI have a report designed as follows:
ISBN DESC MIN -PKG MAX-PKG
Intentional break in PKG numbers
The output without suppression is in Output1.png file attached
The output with suppression is in Output2.png file attached
I am using crystal reports 9. How can I use the suppression and line up the min and max to be on the same row.
Any help is greatly appreciated.
Thanks
msimha
If you go into the section expert, go to the section above the area you want to align and click on 'Underlay Following Sections' This will bring things in line for you.
Brian
http://www.briankuipers.com
ISBN DESC MIN -PKG MAX-PKG
Intentional break in PKG numbers
The output without suppression is in Output1.png file attached
The output with suppression is in Output2.png file attached
I am using crystal reports 9. How can I use the suppression and line up the min and max to be on the same row.
Any help is greatly appreciated.
Thanks
msimhaI have a report designed as follows:
ISBN DESC MIN -PKG MAX-PKG
Intentional break in PKG numbers
The output without suppression is in Output1.png file attached
The output with suppression is in Output2.png file attached
I am using crystal reports 9. How can I use the suppression and line up the min and max to be on the same row.
Any help is greatly appreciated.
Thanks
msimha
If you go into the section expert, go to the section above the area you want to align and click on 'Underlay Following Sections' This will bring things in line for you.
Brian
http://www.briankuipers.com
Breakpoint in child-package triggered on first execution only.
I have a child package which is executed several times within the same SSIS ETL. I have placed a break point on one of the child package's tasks, set to trigger on a PreExecute() event. The first time the child package is invoked, the breakpoint is triggered. However, on each successive invocation the breakpoint is ignored. Does anybody know if this behaviour is normal? Thanks in advance!
This is a known issue and we are aware of the bug. ThanksBreakpoint doesn't work
I've got a breakpoint in a script task. The script task executes successfully but doesn't break.
Short and sweet this one. Anyone any idea why it might not?
-JamieHey Jamie, I believe I read somewhere in the pipe that breakpoints weren't going to work until the prod release...|||
JAson_scoobyjw wrote:
Hey Jamie, I believe I read somewhere in the pipe that breakpoints weren't going to work until the prod release...
Nah, I know this has worked in the past and I've read posts today from people that have had it working.
-Jamie|||We don't support breakpoints in script data flow component in this release.
The script task breakpoints should work, except when the package is executed using 64-bit runtime on x64 machines.
Jamie - are you using CTP 16? I remember in some older builds the script task breakpoints did not work if the PreCompile property of the task was true. I think it is fixed in CTP 16 (but it could be after CTP 16 - for RTM, not sure).|||
We don't support breakpoints in script data flow component in this release. The script task breakpoints should work, except when the package is executed using 64-bit runtime on x64 machines.
Jamie - are you using CTP 16? I remember in some older builds the script task breakpoints did not work if the PreCompile property of the task was true. I think it is fixed in CTP 16 (but it could be after CTP 16 - for RTM, not sure).
Hi Michael,
Yeah, I am using Sept CTP/IDW 16 and I do have PreCompile=TRUE.
The package is at the office and I am currently at home so I'll check this out (i.e. set PreCompile=FALSE) on monday.
-Jamie|||http://blogs.conchango.com/jamiethomson/archive/2005/10/15/2271.aspx|||I found that if you are in debug at a breakpoint on a looping bit of code. If you press F5 the code then continues and doesn't break on the break point again, even though the statement with the breakpoint is executed again.
I thought F5 ran the code and if a breakpoint is found it should stop?|||Is it true that breakpoints won't work in a script component?
I just wrote a script source component to reorder the columns in incoming CSV's based on the column names in the first row.
I set some break points to debug, but they get ignored every time I try to run it. My "precompile" flag is set to false per some earlier posts, but that doesn't seem to affect the issue.|||SSIS does not currently support breakpoints in script components.
You will need to put some logging information in your script component see my post http://www.sqljunkies.com/WebLog/simons/archive/2005/08/03/SSIS_Script_Component_Debugging.aspx
Tuesday, February 14, 2012
Breaking the 8kb barrier on UDT
Is it possible by any kind of workaround to break the 8kb limit on user-defines datatypes?
My datatype can contain an arbitrary number of double-precision points meaning that I in best case only can store 512 points (2 x 8 x 512). there's a few extra bytes used for something else, but this is roughly the maximum, which is far from what I in many cases need. I serialize the object myself to ensure that I only store what I really need.
Not really, if you still want to have your UDT in the database.
What you could do is to send down to the database and a SQLCLR proc/function a binary blob and insert that into the db in a varbinary(max) field. On the client you would then retrieve the binary and re-populate intio your type.
Niels
Breaking down goals
Hi,
we have a fact-table, containing sales-goales. Each goal relates to a year and a sales-person. We want to break down this annual goals as follows:
If the sales is $ 1000 for 2007 then the goal for 2007-12-31 is $ 1000; for 2007-12-30 the goal would be $ 1000 / 365 * 364; for 2007-12-29 $ 1000 / 365 * 363 and so on.
How to accomplish this with SSAS ?
Thx and Regards,
Manfred
you could do something like
Measures.[Sales Goal] * (count(YTD([Time].[Day].CurrentMember)) / 365)
You would probably want to scope such a calculation so that it only occurred at the day level as it would not work at higher levels.
Another approach might be to add a "Day of Year" attribute or even a "YTD proportion" to your time dimension which does this calculation in the DSV, then you don't have to dynamically count the days all the time and you could do something like.
Measures.[Sales Goal] * [Time].[YTD Proportion].MemberValue
Breaking down goals
Hi,
we have a fact-table, containing sales-goales. Each goal relates to a year and a sales-person. We want to break down this annual goals as follows:
If the sales is $ 1000 for 2007 then the goal for 2007-12-31 is $ 1000; for 2007-12-30 the goal would be $ 1000 / 365 * 364; for 2007-12-29 $ 1000 / 365 * 363 and so on.
How to accomplish this with SSAS ?
Thx and Regards,
Manfred
you could do something like
Measures.[Sales Goal] * (count(YTD([Time].[Day].CurrentMember)) / 365)
You would probably want to scope such a calculation so that it only occurred at the day level as it would not work at higher levels.
Another approach might be to add a "Day of Year" attribute or even a "YTD proportion" to your time dimension which does this calculation in the DSV, then you don't have to dynamically count the days all the time and you could do something like.
Measures.[Sales Goal] * [Time].[YTD Proportion].MemberValue
Breaking column data
DBA's
How do I break a column data into 2 or more columns.
Example, I have a 7 character column that I want to break into 3 columns of 3,2 and 2 character respectively. Let me know how the query would look like.
ThanksYou could use left(), substring() functions:
select left(column,2), substring(column,3,2),...|||Don't forget RIGHT
USE Northwind
GO
CREATE TABLE myTable99 (Col1 char(7))
GO
INSERT INTO myTable99(Col1)
SELECT '1234567' UNION ALL
SELECT 'abcdefg' UNION ALL
SELECT 'qwertyu' UNION ALL
SELECT 'lkjhgfd' UNION ALL
SELECT '1212312'
SELECT LEFT(Col1,2) AS [Left]
, SUBSTRING(Col1,3,3) AS Center
, RIGHT(Col1,2) AS [Right]
FROM myTable99
GO
DROP TABLE myTable99
GO|||Don't forget about your bol - check out string functions.
How do I break a column data into 2 or more columns.
Example, I have a 7 character column that I want to break into 3 columns of 3,2 and 2 character respectively. Let me know how the query would look like.
ThanksYou could use left(), substring() functions:
select left(column,2), substring(column,3,2),...|||Don't forget RIGHT
USE Northwind
GO
CREATE TABLE myTable99 (Col1 char(7))
GO
INSERT INTO myTable99(Col1)
SELECT '1234567' UNION ALL
SELECT 'abcdefg' UNION ALL
SELECT 'qwertyu' UNION ALL
SELECT 'lkjhgfd' UNION ALL
SELECT '1212312'
SELECT LEFT(Col1,2) AS [Left]
, SUBSTRING(Col1,3,3) AS Center
, RIGHT(Col1,2) AS [Right]
FROM myTable99
GO
DROP TABLE myTable99
GO|||Don't forget about your bol - check out string functions.
Breaking a SQL Cluster
Is there a way to break my Active/Active cluster back to have a single
stand-alone SQL server?
internal politics and a rogue admin who applies security patches willie
nillie to the cluster nodes is causing more downtime than the cluster is
worth!
Hopefully there is a way that this can be done fairly painlessly.
Thanks in advance,
Ron G
You can uninstall a virtual server by running the installation wizard again.
The books online have the exact steps listed.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Ron Griffin" <rgriffin@.lattestonetech.com> wrote in message
news:8A9Zd.19191$YD4.2485@.newssvr12.news.prodigy.c om...
> Is there a way to break my Active/Active cluster back to have a single
> stand-alone SQL server?
> internal politics and a rogue admin who applies security patches willie
> nillie to the cluster nodes is causing more downtime than the cluster is
> worth!
> Hopefully there is a way that this can be done fairly painlessly.
> Thanks in advance,
> Ron G
>
stand-alone SQL server?
internal politics and a rogue admin who applies security patches willie
nillie to the cluster nodes is causing more downtime than the cluster is
worth!
Hopefully there is a way that this can be done fairly painlessly.
Thanks in advance,
Ron G
You can uninstall a virtual server by running the installation wizard again.
The books online have the exact steps listed.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Ron Griffin" <rgriffin@.lattestonetech.com> wrote in message
news:8A9Zd.19191$YD4.2485@.newssvr12.news.prodigy.c om...
> Is there a way to break my Active/Active cluster back to have a single
> stand-alone SQL server?
> internal politics and a rogue admin who applies security patches willie
> nillie to the cluster nodes is causing more downtime than the cluster is
> worth!
> Hopefully there is a way that this can be done fairly painlessly.
> Thanks in advance,
> Ron G
>
Breakdown database by percent
I am in the Casino industry and would like to break down our database by
percent of win. For example:
lets say we have 100,000 rated players. I want to find the win the top 1%
of players contribute followed by
2%
3%
4%
.
.
100%
My fields are Player_ID, Win.
I need help writing a procdure to group/calculate this for me. The database
grows automatically so the procudre needs to automatically adjust based on
the growth.
ThanksI don't think I understand your question fully but on it's surface
it sounds like you can benefit from the TOP keyword in your
SELECT.
SELECT TOP n [PERCENT]
See BOL for more details
<brian.shannon@.diamondjo.com> wrote in message
news:OaRnZCgFFHA.2156@.TK2MSFTNGP09.phx.gbl...
> I am in the Casino industry and would like to break down our database by
> percent of win. For example:
> lets say we have 100,000 rated players. I want to find the win the top 1%
> of players contribute followed by
> 2%
> 3%
> 4%
> .
> .
> 100%
> My fields are Player_ID, Win.
> I need help writing a procdure to group/calculate this for me. The
database
> grows automatically so the procudre needs to automatically adjust based on
> the growth.
> Thanks
>|||>> I need help writing a procdure to group/calculate this for me.
Sure, it is easy, but many here won't have the inclination to create sample
tables, insert sample data & write up a query for you when you did not
bother to post required information for others to repro the problem. Read
www.aspfaq.com/5006 & provide table structures, sample data & expected
results so that others can suggest a tested solution.
Anith
percent of win. For example:
lets say we have 100,000 rated players. I want to find the win the top 1%
of players contribute followed by
2%
3%
4%
.
.
100%
My fields are Player_ID, Win.
I need help writing a procdure to group/calculate this for me. The database
grows automatically so the procudre needs to automatically adjust based on
the growth.
ThanksI don't think I understand your question fully but on it's surface
it sounds like you can benefit from the TOP keyword in your
SELECT.
SELECT TOP n [PERCENT]
See BOL for more details
<brian.shannon@.diamondjo.com> wrote in message
news:OaRnZCgFFHA.2156@.TK2MSFTNGP09.phx.gbl...
> I am in the Casino industry and would like to break down our database by
> percent of win. For example:
> lets say we have 100,000 rated players. I want to find the win the top 1%
> of players contribute followed by
> 2%
> 3%
> 4%
> .
> .
> 100%
> My fields are Player_ID, Win.
> I need help writing a procdure to group/calculate this for me. The
database
> grows automatically so the procudre needs to automatically adjust based on
> the growth.
> Thanks
>|||>> I need help writing a procdure to group/calculate this for me.
Sure, it is easy, but many here won't have the inclination to create sample
tables, insert sample data & write up a query for you when you did not
bother to post required information for others to repro the problem. Read
www.aspfaq.com/5006 & provide table structures, sample data & expected
results so that others can suggest a tested solution.
Anith
Break XML into Columns
Hi,
I have a XML of the structure,
<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello</Column>
<Column name="Header2">hello</Column>
</XMLSET>
in my database (SQL - 2k5). Now I want to split it in a manner that yields me this output;
Header Header1 Header2
Hello Hello Hello
Any pointers on how to go about this ?
Thanks in Advance,
declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'
select r.value('(./Column[@.name="Header"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Header1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Header2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)
Thanks for the reply....
A small query here..... Instead of hard coding the name of the column as Header, Header1 is there a way we can have the headers displayed dynamically... In short the following query will always return header, header1 and so. How to get it displayed dynamically?
select r.value('(./Column[@.name="Head"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Headr1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Headr2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)
Thanks again for the reply....
|||If you want to have dynamic column names you'll have to use dynamic SQL.
Note that you can use variables for your search criteria as below.
declare @.h1 varchar(10)
declare @.h2 varchar(10)
declare @.h3 varchar(10)
set @.h1='Header'
set @.h2='Header1'
set @.h3='Header2'
declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'
select r.value('(./Column[@.name=sql:variable("@.h1")])[1]','varchar(10)') as Header,
r.value('(./Column[@.name=sql:variable("@.h2")])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name=sql:variable("@.h3")])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)
|||Hi
I am processing xml in sql server 2005. The values are stored in a column as xml datatype. How to query the data that is in the column. This is the structure of the xml.
'<skill>
<Coach type="AA">false</Coach>
<CDT type="AA">false</CDT>
<DIT type="AA">false</DIT>
<FSA type="AA">false</FSA>
<LMR type="AA">false</LMR>
<ROSPA type="Additional">false</ROSPA>
<IAM type="Additional">false</IAM>
<Diamond type="Additional">false</Diamond>
<DipDI type="Additional">false</DipDI>
<BTEC type="Additional">false</BTEC>
<QEF type="Additional">
<member>false</member>
<certificatenumber />
<level />
</QEF>
<BTec type="Additional">false</BTec>
</skill>'
Thanks
Srini
|||Can you be bit specific here ?
You want the data in the XML as columns is it ?
|||The data is in the xml field of a table. The xml contains the skills of a candidate. I need to access the skill set values like CDT=false, DIT = false, and additional qualifications, if any, have to be accessed.
Thanks,
Srini
|||Not sure if I got ur requirement clear, but I infer you wanted to break open the XML nodes as columns. Create a table like this, insert the two values and then run the query.
create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)
Hope that helps...
Break XML into Columns
Hi,
I have a XML of the structure,
<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello</Column>
<Column name="Header2">hello</Column>
</XMLSET>
in my database (SQL - 2k5). Now I want to split it in a manner that yields me this output;
Header Header1 Header2
Hello Hello Hello
Any pointers on how to go about this ?
Thanks in Advance,
declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'
select r.value('(./Column[@.name="Header"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Header1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Header2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)
Thanks for the reply....
A small query here..... Instead of hard coding the name of the column as Header, Header1 is there a way we can have the headers displayed dynamically... In short the following query will always return header, header1 and so. How to get it displayed dynamically?
select r.value('(./Column[@.name="Head"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Headr1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Headr2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)
Thanks again for the reply....
|||If you want to have dynamic column names you'll have to use dynamic SQL.
Note that you can use variables for your search criteria as below.
declare @.h1 varchar(10)
declare @.h2 varchar(10)
declare @.h3 varchar(10)
set @.h1='Header'
set @.h2='Header1'
set @.h3='Header2'
declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'
select r.value('(./Column[@.name=sql:variable("@.h1")])[1]','varchar(10)') as Header,
r.value('(./Column[@.name=sql:variable("@.h2")])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name=sql:variable("@.h3")])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)
|||Hi
I am processing xml in sql server 2005. The values are stored in a column as xml datatype. How to query the data that is in the column. This is the structure of the xml.
'<skill>
<Coach type="AA">false</Coach>
<CDT type="AA">false</CDT>
<DIT type="AA">false</DIT>
<FSA type="AA">false</FSA>
<LMR type="AA">false</LMR>
<ROSPA type="Additional">false</ROSPA>
<IAM type="Additional">false</IAM>
<Diamond type="Additional">false</Diamond>
<DipDI type="Additional">false</DipDI>
<BTEC type="Additional">false</BTEC>
<QEF type="Additional">
<member>false</member>
<certificatenumber />
<level />
</QEF>
<BTec type="Additional">false</BTec>
</skill>'
Thanks
Srini
|||Can you be bit specific here ?
You want the data in the XML as columns is it ?
|||The data is in the xml field of a table. The xml contains the skills of a candidate. I need to access the skill set values like CDT=false, DIT = false, and additional qualifications, if any, have to be accessed.
Thanks,
Srini
|||Not sure if I got ur requirement clear, but I infer you wanted to break open the XML nodes as columns. Create a table like this, insert the two values and then run the query.
create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)
Hope that helps...
Break XML into Columns
Hi,
I have a XML of the structure,
<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello</Column>
<Column name="Header2">hello</Column>
</XMLSET>
in my database (SQL - 2k5). Now I want to split it in a manner that yields me this output;
Header Header1 Header2
Hello Hello Hello
Any pointers on how to go about this ?
Thanks in Advance,
declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'
select r.value('(./Column[@.name="Header"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Header1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Header2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)
Thanks for the reply....
A small query here..... Instead of hard coding the name of the column as Header, Header1 is there a way we can have the headers displayed dynamically... In short the following query will always return header, header1 and so. How to get it displayed dynamically?
select r.value('(./Column[@.name="Head"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Headr1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Headr2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)
Thanks again for the reply....
|||If you want to have dynamic column names you'll have to use dynamic SQL.
Note that you can use variables for your search criteria as below.
declare @.h1 varchar(10)
declare @.h2 varchar(10)
declare @.h3 varchar(10)
set @.h1='Header'
set @.h2='Header1'
set @.h3='Header2'
declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'
select r.value('(./Column[@.name=sql:variable("@.h1")])[1]','varchar(10)') as Header,
r.value('(./Column[@.name=sql:variable("@.h2")])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name=sql:variable("@.h3")])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)
|||Hi
I am processing xml in sql server 2005. The values are stored in a column as xml datatype. How to query the data that is in the column. This is the structure of the xml.
'<skill>
<Coach type="AA">false</Coach>
<CDT type="AA">false</CDT>
<DIT type="AA">false</DIT>
<FSA type="AA">false</FSA>
<LMR type="AA">false</LMR>
<ROSPA type="Additional">false</ROSPA>
<IAM type="Additional">false</IAM>
<Diamond type="Additional">false</Diamond>
<DipDI type="Additional">false</DipDI>
<BTEC type="Additional">false</BTEC>
<QEF type="Additional">
<member>false</member>
<certificatenumber />
<level />
</QEF>
<BTec type="Additional">false</BTec>
</skill>'
Thanks
Srini
|||Can you be bit specific here ?
You want the data in the XML as columns is it ?
|||The data is in the xml field of a table. The xml contains the skills of a candidate. I need to access the skill set values like CDT=false, DIT = false, and additional qualifications, if any, have to be accessed.
Thanks,
Srini
|||Not sure if I got ur requirement clear, but I infer you wanted to break open the XML nodes as columns. Create a table like this, insert the two values and then run the query.
create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)
Hope that helps...
Break up Record
Hi,
I have a table with only one record in it looks someting like this
Field Titles : Name Address PC
Data : Bert A House CL4
Is it possible to reformat this so that it looks like
Name
Bert
Address
A House
PC
CL4
Usually I am doing this the other way around and moving things up onto one
like, it's been a long morning not much fun being in work at 6.40am so any
help would be brilliant.
Thanks Phil, zzzzzzzzzzzzzzzzzzDont worry about this post, I have woken up and managed to sort it.
Thanks Phil
"Phil" wrote:
> Hi,
> I have a table with only one record in it looks someting like this
> Field Titles : Name Address PC
> Data : Bert A House CL4
> Is it possible to reformat this so that it looks like
> Name
> Bert
> Address
> A House
> PC
> CL4
> Usually I am doing this the other way around and moving things up onto one
> like, it's been a long morning not much fun being in work at 6.40am so any
> help would be brilliant.
> Thanks Phil, zzzzzzzzzzzzzzzzzz
I have a table with only one record in it looks someting like this
Field Titles : Name Address PC
Data : Bert A House CL4
Is it possible to reformat this so that it looks like
Name
Bert
Address
A House
PC
CL4
Usually I am doing this the other way around and moving things up onto one
like, it's been a long morning not much fun being in work at 6.40am so any
help would be brilliant.
Thanks Phil, zzzzzzzzzzzzzzzzzzDont worry about this post, I have woken up and managed to sort it.
Thanks Phil
"Phil" wrote:
> Hi,
> I have a table with only one record in it looks someting like this
> Field Titles : Name Address PC
> Data : Bert A House CL4
> Is it possible to reformat this so that it looks like
> Name
> Bert
> Address
> A House
> PC
> CL4
> Usually I am doing this the other way around and moving things up onto one
> like, it's been a long morning not much fun being in work at 6.40am so any
> help would be brilliant.
> Thanks Phil, zzzzzzzzzzzzzzzzzz
break up list during bulk load?
Howdy,
I'm doing a bulk load with SQLXML 3.0 and MS SQL 2k. One of the
elements is a (long) comma-space delimited list of quoted URIs. In my
working schema the list is loaded into a child table as a single long
varchar with a key to the parent table.
I'd like to have each URI in its own record in the child table. I've
messed around a bit with xsd:list and simpleType, but BulkLoad is
hearing none of that.
Has anyone any experience manipulating lists with the schema in
BulkLoad? (I can break the list up into individual elements with a
regex on the XML or transform the records with a sproc after loading,
but I'd like to handle this in the bulk load without modifying the xml
data if possible.)
TIA,
Sean G.
Sorry, but that is not possible in the SQLXML Bulkload object...
Best regards
Michael
<SeanGerman@.gmail.com> wrote in message
news:1136915537.981406.87190@.o13g2000cwo.googlegro ups.com...
> Howdy,
> I'm doing a bulk load with SQLXML 3.0 and MS SQL 2k. One of the
> elements is a (long) comma-space delimited list of quoted URIs. In my
> working schema the list is loaded into a child table as a single long
> varchar with a key to the parent table.
> I'd like to have each URI in its own record in the child table. I've
> messed around a bit with xsd:list and simpleType, but BulkLoad is
> hearing none of that.
> Has anyone any experience manipulating lists with the schema in
> BulkLoad? (I can break the list up into individual elements with a
> regex on the XML or transform the records with a sproc after loading,
> but I'd like to handle this in the bulk load without modifying the xml
> data if possible.)
> TIA,
>
> Sean G.
>
|||Michael,
Yeah, that's what I suspected. Thanks for the reply.
Sean G.
I'm doing a bulk load with SQLXML 3.0 and MS SQL 2k. One of the
elements is a (long) comma-space delimited list of quoted URIs. In my
working schema the list is loaded into a child table as a single long
varchar with a key to the parent table.
I'd like to have each URI in its own record in the child table. I've
messed around a bit with xsd:list and simpleType, but BulkLoad is
hearing none of that.
Has anyone any experience manipulating lists with the schema in
BulkLoad? (I can break the list up into individual elements with a
regex on the XML or transform the records with a sproc after loading,
but I'd like to handle this in the bulk load without modifying the xml
data if possible.)
TIA,
Sean G.
Sorry, but that is not possible in the SQLXML Bulkload object...
Best regards
Michael
<SeanGerman@.gmail.com> wrote in message
news:1136915537.981406.87190@.o13g2000cwo.googlegro ups.com...
> Howdy,
> I'm doing a bulk load with SQLXML 3.0 and MS SQL 2k. One of the
> elements is a (long) comma-space delimited list of quoted URIs. In my
> working schema the list is loaded into a child table as a single long
> varchar with a key to the parent table.
> I'd like to have each URI in its own record in the child table. I've
> messed around a bit with xsd:list and simpleType, but BulkLoad is
> hearing none of that.
> Has anyone any experience manipulating lists with the schema in
> BulkLoad? (I can break the list up into individual elements with a
> regex on the XML or transform the records with a sproc after loading,
> but I'd like to handle this in the bulk load without modifying the xml
> data if possible.)
> TIA,
>
> Sean G.
>
|||Michael,
Yeah, that's what I suspected. Thanks for the reply.
Sean G.
break up list during bulk load?
Howdy,
I'm doing a bulk load with SQLXML 3.0 and MS SQL 2k. One of the
elements is a (long) comma-space delimited list of quoted URIs. In my
working schema the list is loaded into a child table as a single long
varchar with a key to the parent table.
I'd like to have each URI in its own record in the child table. I've
messed around a bit with xsd:list and simpleType, but BulkLoad is
hearing none of that.
Has anyone any experience manipulating lists with the schema in
BulkLoad? (I can break the list up into individual elements with a
regex on the XML or transform the records with a sproc after loading,
but I'd like to handle this in the bulk load without modifying the xml
data if possible.)
TIA,
Sean G.Sorry, but that is not possible in the SQLXML Bulkload object...
Best regards
Michael
<SeanGerman@.gmail.com> wrote in message
news:1136915537.981406.87190@.o13g2000cwo.googlegroups.com...
> Howdy,
> I'm doing a bulk load with SQLXML 3.0 and MS SQL 2k. One of the
> elements is a (long) comma-space delimited list of quoted URIs. In my
> working schema the list is loaded into a child table as a single long
> varchar with a key to the parent table.
> I'd like to have each URI in its own record in the child table. I've
> messed around a bit with xsd:list and simpleType, but BulkLoad is
> hearing none of that.
> Has anyone any experience manipulating lists with the schema in
> BulkLoad? (I can break the list up into individual elements with a
> regex on the XML or transform the records with a sproc after loading,
> but I'd like to handle this in the bulk load without modifying the xml
> data if possible.)
> TIA,
>
> Sean G.
>|||Michael,
Yeah, that's what I suspected. Thanks for the reply.
Sean G.
I'm doing a bulk load with SQLXML 3.0 and MS SQL 2k. One of the
elements is a (long) comma-space delimited list of quoted URIs. In my
working schema the list is loaded into a child table as a single long
varchar with a key to the parent table.
I'd like to have each URI in its own record in the child table. I've
messed around a bit with xsd:list and simpleType, but BulkLoad is
hearing none of that.
Has anyone any experience manipulating lists with the schema in
BulkLoad? (I can break the list up into individual elements with a
regex on the XML or transform the records with a sproc after loading,
but I'd like to handle this in the bulk load without modifying the xml
data if possible.)
TIA,
Sean G.Sorry, but that is not possible in the SQLXML Bulkload object...
Best regards
Michael
<SeanGerman@.gmail.com> wrote in message
news:1136915537.981406.87190@.o13g2000cwo.googlegroups.com...
> Howdy,
> I'm doing a bulk load with SQLXML 3.0 and MS SQL 2k. One of the
> elements is a (long) comma-space delimited list of quoted URIs. In my
> working schema the list is loaded into a child table as a single long
> varchar with a key to the parent table.
> I'd like to have each URI in its own record in the child table. I've
> messed around a bit with xsd:list and simpleType, but BulkLoad is
> hearing none of that.
> Has anyone any experience manipulating lists with the schema in
> BulkLoad? (I can break the list up into individual elements with a
> regex on the XML or transform the records with a sproc after loading,
> but I'd like to handle this in the bulk load without modifying the xml
> data if possible.)
> TIA,
>
> Sean G.
>|||Michael,
Yeah, that's what I suspected. Thanks for the reply.
Sean G.
break table across pages but keep the control under it on first page
I am trying to create appointment reminder letters, and at the bottom
of the first page of each letter I need the clinic contact information
(phone number and name). Immediately prior to this I must list various
instructions regarding what to bring, what to eat/not eat beforehand,
etc.
Regardless of the length of the list of instructions I still need the
clinic contact information to render at the bottom of the 1st page of
every letter, even if the list of instructions has to continue on to
page 2.
When I create a table footer it only appears on the 1st page of the 1st
child's letter, while the other several hundred letters get no footer.
(surely this can be done in one .PDF instead of a subscription to
create several hundred separate .PDFs?)
Here's what I need for each child:
|Page 1|
<Patient address, etc.>
<Schedule of appointments for the day>
<List of instructions>
<Clinic CONTACT INFO> <--bottom of 1st page of EVERY letter
|Page 1|
|Page 2|
<any remaining items from the list of instructions above>
<rest of my report requirements here...>
Any suggestions on how to do this are greatly appreciated.
Thanks,
Stewart Whaley
Arkansas Children's HospitalPut the information in the Page footer and set the page footer properties to
only print on the first page.
If you have multiple items in the footer--some for page 1 and some for page
2, you will have to set the visibility in a formula.
"Stewart" <whaleysa@.archildrens.org> wrote in message
news:1121353944.165681.77900@.o13g2000cwo.googlegroups.com...
>I am trying to create appointment reminder letters, and at the bottom
> of the first page of each letter I need the clinic contact information
> (phone number and name). Immediately prior to this I must list various
> instructions regarding what to bring, what to eat/not eat beforehand,
> etc.
> Regardless of the length of the list of instructions I still need the
> clinic contact information to render at the bottom of the 1st page of
> every letter, even if the list of instructions has to continue on to
> page 2.
> When I create a table footer it only appears on the 1st page of the 1st
> child's letter, while the other several hundred letters get no footer.
> (surely this can be done in one .PDF instead of a subscription to
> create several hundred separate .PDFs?)
> Here's what I need for each child:
> |Page 1|
> <Patient address, etc.>
> <Schedule of appointments for the day>
> <List of instructions>
> <Clinic CONTACT INFO> <--bottom of 1st page of EVERY letter
> |Page 1|
> |Page 2|
> <any remaining items from the list of instructions above>
> <rest of my report requirements here...>
> Any suggestions on how to do this are greatly appreciated.
> Thanks,
> Stewart Whaley
> Arkansas Children's Hospital
>|||My problem now is that I have the footer on every page but the last of
every letter except for the very last page of the last kid's letter.
For example, I have:
<PAGE 1>
Bob Jones
1233 Jones Street
Allergy Clinic 9:00
Dental Clinic 10:30
Don't forget:
* Please don't eat Oreos before you dental appointment
* Don't pet tigers
Clinic contact info (this is in a the footer because it MUST be at the
bottom of the 1st page and only the 1st page of each kid's letter).
<PAGE 2>
Continuation of the "Don't Forget" list from page 2
Image of map for the particular clinic(s)
Clinic contact info footer is repeating here and I DON'T want this
repeated within the SAME letter.
PAGE 3 (Start of the next letter)
Jon Smith
111 1st Street
Allergy Clinic 9:30
Orthopedic Clinic 11:00
Don't forget:
* Please don't jump off of buildings
* Still don't pet tigers
Clinic contact information (as expected)
<PAGE 2>
Continuation of "Don't Forget" stuff here if it is long enough to
stretch to page 2
Clinic contact information footer repeated again but I don't want it
repeated.
..........
I don't know what to do but I just want the clinic contact info at the
bottom of only the 1st page of each letter.
Unfortunately, a single letter's isn't guaranteed to be 2 pages,
either, so I'm not sure I can come up with a formula for hiding the
footer on every page but the 1st for each kid...
Thanks,
Stewart
of the first page of each letter I need the clinic contact information
(phone number and name). Immediately prior to this I must list various
instructions regarding what to bring, what to eat/not eat beforehand,
etc.
Regardless of the length of the list of instructions I still need the
clinic contact information to render at the bottom of the 1st page of
every letter, even if the list of instructions has to continue on to
page 2.
When I create a table footer it only appears on the 1st page of the 1st
child's letter, while the other several hundred letters get no footer.
(surely this can be done in one .PDF instead of a subscription to
create several hundred separate .PDFs?)
Here's what I need for each child:
|Page 1|
<Patient address, etc.>
<Schedule of appointments for the day>
<List of instructions>
<Clinic CONTACT INFO> <--bottom of 1st page of EVERY letter
|Page 1|
|Page 2|
<any remaining items from the list of instructions above>
<rest of my report requirements here...>
Any suggestions on how to do this are greatly appreciated.
Thanks,
Stewart Whaley
Arkansas Children's HospitalPut the information in the Page footer and set the page footer properties to
only print on the first page.
If you have multiple items in the footer--some for page 1 and some for page
2, you will have to set the visibility in a formula.
"Stewart" <whaleysa@.archildrens.org> wrote in message
news:1121353944.165681.77900@.o13g2000cwo.googlegroups.com...
>I am trying to create appointment reminder letters, and at the bottom
> of the first page of each letter I need the clinic contact information
> (phone number and name). Immediately prior to this I must list various
> instructions regarding what to bring, what to eat/not eat beforehand,
> etc.
> Regardless of the length of the list of instructions I still need the
> clinic contact information to render at the bottom of the 1st page of
> every letter, even if the list of instructions has to continue on to
> page 2.
> When I create a table footer it only appears on the 1st page of the 1st
> child's letter, while the other several hundred letters get no footer.
> (surely this can be done in one .PDF instead of a subscription to
> create several hundred separate .PDFs?)
> Here's what I need for each child:
> |Page 1|
> <Patient address, etc.>
> <Schedule of appointments for the day>
> <List of instructions>
> <Clinic CONTACT INFO> <--bottom of 1st page of EVERY letter
> |Page 1|
> |Page 2|
> <any remaining items from the list of instructions above>
> <rest of my report requirements here...>
> Any suggestions on how to do this are greatly appreciated.
> Thanks,
> Stewart Whaley
> Arkansas Children's Hospital
>|||My problem now is that I have the footer on every page but the last of
every letter except for the very last page of the last kid's letter.
For example, I have:
<PAGE 1>
Bob Jones
1233 Jones Street
Allergy Clinic 9:00
Dental Clinic 10:30
Don't forget:
* Please don't eat Oreos before you dental appointment
* Don't pet tigers
Clinic contact info (this is in a the footer because it MUST be at the
bottom of the 1st page and only the 1st page of each kid's letter).
<PAGE 2>
Continuation of the "Don't Forget" list from page 2
Image of map for the particular clinic(s)
Clinic contact info footer is repeating here and I DON'T want this
repeated within the SAME letter.
PAGE 3 (Start of the next letter)
Jon Smith
111 1st Street
Allergy Clinic 9:30
Orthopedic Clinic 11:00
Don't forget:
* Please don't jump off of buildings
* Still don't pet tigers
Clinic contact information (as expected)
<PAGE 2>
Continuation of "Don't Forget" stuff here if it is long enough to
stretch to page 2
Clinic contact information footer repeated again but I don't want it
repeated.
..........
I don't know what to do but I just want the clinic contact info at the
bottom of only the 1st page of each letter.
Unfortunately, a single letter's isn't guaranteed to be 2 pages,
either, so I'm not sure I can come up with a formula for hiding the
footer on every page but the 1st for each kid...
Thanks,
Stewart
Break statement does not work
I am trying to delete 500000 rows at a time from a big table. I am trying to
loop through it. I am attaching my code, what am I doing wrong. It either
will delete only 500000 rows and exit ot it will keep on looping. I am askin
g
it to delete data older than a certain date. If there are 3000000 rows , I
want it to loop till it deletes 3000000 rows and then exit. Instead if i set
the loop counter to 10, it will loop ten times and then exit. The break
statement does not work
here is the code
declare @.i int
select @.i=10
set rowcount 1000000
while @.i>0
begin
delete from flat_reporttbl
where logdate<'4/1/2004'
select @.i=@.i-1
break
end
thankscheck it.
delete top(10) from flat_reporttbl
where logdate<'4/1/2004'
the problem in ur code is ,
delete statement deletes all rows
which satisfies the condition in
where clause and
then check the while condition.
"batgirl" <batgirl@.discussions.microsoft.com> wrote in message
news:49793BEE-8D40-480E-8D5A-03E77A45A541@.microsoft.com...
>I am trying to delete 500000 rows at a time from a big table. I am trying
>to
> loop through it. I am attaching my code, what am I doing wrong. It either
> will delete only 500000 rows and exit ot it will keep on looping. I am
> asking
> it to delete data older than a certain date. If there are 3000000 rows , I
> want it to loop till it deletes 3000000 rows and then exit. Instead if i
> set
> the loop counter to 10, it will loop ten times and then exit. The break
> statement does not work
> here is the code
> declare @.i int
> select @.i=10
> set rowcount 1000000
> while @.i>0
> begin
> delete from flat_reporttbl
> where logdate<'4/1/2004'
> select @.i=@.i-1
> break
> end
> thanks|||I don't know if we can use the TOP keyword with delete. It gives me an error
message when I try to use it. Also I have already set the rowcount, so does
using TOP help?
batgirl
"batgirl" wrote:
> I am trying to delete 500000 rows at a time from a big table. I am trying
to
> loop through it. I am attaching my code, what am I doing wrong. It either
> will delete only 500000 rows and exit ot it will keep on looping. I am ask
ing
> it to delete data older than a certain date. If there are 3000000 rows , I
> want it to loop till it deletes 3000000 rows and then exit. Instead if i s
et
> the loop counter to 10, it will loop ten times and then exit. The break
> statement does not work
> here is the code
> declare @.i int
> select @.i=10
> set rowcount 1000000
> while @.i>0
> begin
> delete from flat_reporttbl
> where logdate<'4/1/2004'
> select @.i=@.i-1
> break
> end
> thanks
loop through it. I am attaching my code, what am I doing wrong. It either
will delete only 500000 rows and exit ot it will keep on looping. I am askin
g
it to delete data older than a certain date. If there are 3000000 rows , I
want it to loop till it deletes 3000000 rows and then exit. Instead if i set
the loop counter to 10, it will loop ten times and then exit. The break
statement does not work
here is the code
declare @.i int
select @.i=10
set rowcount 1000000
while @.i>0
begin
delete from flat_reporttbl
where logdate<'4/1/2004'
select @.i=@.i-1
break
end
thankscheck it.
delete top(10) from flat_reporttbl
where logdate<'4/1/2004'
the problem in ur code is ,
delete statement deletes all rows
which satisfies the condition in
where clause and
then check the while condition.
"batgirl" <batgirl@.discussions.microsoft.com> wrote in message
news:49793BEE-8D40-480E-8D5A-03E77A45A541@.microsoft.com...
>I am trying to delete 500000 rows at a time from a big table. I am trying
>to
> loop through it. I am attaching my code, what am I doing wrong. It either
> will delete only 500000 rows and exit ot it will keep on looping. I am
> asking
> it to delete data older than a certain date. If there are 3000000 rows , I
> want it to loop till it deletes 3000000 rows and then exit. Instead if i
> set
> the loop counter to 10, it will loop ten times and then exit. The break
> statement does not work
> here is the code
> declare @.i int
> select @.i=10
> set rowcount 1000000
> while @.i>0
> begin
> delete from flat_reporttbl
> where logdate<'4/1/2004'
> select @.i=@.i-1
> break
> end
> thanks|||I don't know if we can use the TOP keyword with delete. It gives me an error
message when I try to use it. Also I have already set the rowcount, so does
using TOP help?
batgirl
"batgirl" wrote:
> I am trying to delete 500000 rows at a time from a big table. I am trying
to
> loop through it. I am attaching my code, what am I doing wrong. It either
> will delete only 500000 rows and exit ot it will keep on looping. I am ask
ing
> it to delete data older than a certain date. If there are 3000000 rows , I
> want it to loop till it deletes 3000000 rows and then exit. Instead if i s
et
> the loop counter to 10, it will loop ten times and then exit. The break
> statement does not work
> here is the code
> declare @.i int
> select @.i=10
> set rowcount 1000000
> while @.i>0
> begin
> delete from flat_reporttbl
> where logdate<'4/1/2004'
> select @.i=@.i-1
> break
> end
> thanks
Break on Running Total
I am trying to create Packing List(s) based on an individual sales order.
Each Packing List page must be based upon the number of square feet.
I have set up a detail line with
Pieces, Width, Length and a running total of square feet (Pieces*width*length)
When the Running Total Square Feet > 400 then I need to go to a new page and reset the Running Total Square Feet and continue to print the remaining detail, until it again reaches 400 square feet.
I'm close, but I am having trouble. Can anyone help?
Note: Its MAS 90, if that makes a difference.What did you try and what problem are you facing?
Each Packing List page must be based upon the number of square feet.
I have set up a detail line with
Pieces, Width, Length and a running total of square feet (Pieces*width*length)
When the Running Total Square Feet > 400 then I need to go to a new page and reset the Running Total Square Feet and continue to print the remaining detail, until it again reaches 400 square feet.
I'm close, but I am having trouble. Can anyone help?
Note: Its MAS 90, if that makes a difference.What did you try and what problem are you facing?
Subscribe to:
Posts (Atom)