Sql ANSI Padding: rows

显示标签为“rows”的博文。显示所有博文

2012年3月22日星期四

Anybody have any luck using the Filters tab on a table

I can't get a table to return top n rows. The sorting tab works fine. Can
anyone provide a syntax example?
Thanks,
Dave=Fields!MyField.Value TopN =10
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Microsoft PrivateNews" <Dave.Troyer@.cliftoncpa.com> wrote in message
news:O4CS54noEHA.3464@.TK2MSFTNGP14.phx.gbl...
>I can't get a table to return top n rows. The sorting tab works fine. Can
> anyone provide a syntax example?
> Thanks,
> Dave
>|||That's what I have but it doesn't return any rows. There is data in the
dataset.
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:u7DeYfsoEHA.536@.TK2MSFTNGP11.phx.gbl...
> =Fields!MyField.Value TopN =10
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Microsoft PrivateNews" <Dave.Troyer@.cliftoncpa.com> wrote in message
> news:O4CS54noEHA.3464@.TK2MSFTNGP14.phx.gbl...
> >I can't get a table to return top n rows. The sorting tab works fine.
Can
> > anyone provide a syntax example?
> >
> > Thanks,
> > Dave
> >
> >
>|||I was able to get this to work. Something went flaky at one point. Thanks
again.
"Microsoft PrivateNews" <Dave.Troyer@.cliftoncpa.com> wrote in message
news:uNfExIMpEHA.644@.tk2msftngp13.phx.gbl...
> That's what I have but it doesn't return any rows. There is data in the
> dataset.
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:u7DeYfsoEHA.536@.TK2MSFTNGP11.phx.gbl...
> > =Fields!MyField.Value TopN =10
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Microsoft PrivateNews" <Dave.Troyer@.cliftoncpa.com> wrote in message
> > news:O4CS54noEHA.3464@.TK2MSFTNGP14.phx.gbl...
> > >I can't get a table to return top n rows. The sorting tab works fine.
> Can
> > > anyone provide a syntax example?
> > >
> > > Thanks,
> > > Dave
> > >
> > >
> >
> >
>

2012年3月20日星期二

Any way to suppress ADO error from stored proc?

Hi,
I have a stored procedure that can insert multiple rows but it does them in
a loop so the inserts happen one at a time. There is a unique constraint on
the table and sometimes the insert violates this and the constraint
violation gets returned as an error. What I want to do is to trap the error
report it in another table and continue processing all of the inserts and
not have the unique constraint error returned to the calling application.
The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
procedure processing all the inserts and then I just do a RETURN(0) at the
end. Unfortunately this still returns the unique constraint violation even
though I am doing the RETURN(0). There doesn't seem to be any way to keep
the unique constraint from going through to ADO and reporting back an error.
The procedure has now been changed to check for the unique problem before
performing the insert but that seems ineffecient so I would rather just
suppress the error if possible. Thanks in advance for any ideas.
Wayne AntinoreHi Wayne.
Unfortunately, T-SQL offers no way to either suppress errors or provide
run-time inspection of full error messages.
You will have to implement exception management in the client to ignore
error message passed back up to ADO if you really want to take the approach
you've described.
Many people take the approach you've described & perform the distinct query
before performing the insert, but even this is not fool-proof in a high
concurrency environment.
fwiw - this has been bitched about for years & we've got some better error
handling tools coming in the next version of SQL Server. For now though,
you're stuck with this problem as you've described.
Regards,
Greg Linwood
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>|||Wayne
Look at SET XACT_ABORT on BOL
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>|||Hi Uri.
I think this won't help Wayne.
SET XACT_ABORT ON will attempt to rollback an entire transaction on any
error. The way I read Wayne's post, he wants his transaction to continue
processing other rows, even after a constraint violation, so it would have
the opposite effect from what he's after.
Regards,
Greg Linwood
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> Wayne
> Look at SET XACT_ABORT on BOL
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > I have a stored procedure that can insert multiple rows but it does them
> in
> > a loop so the inserts happen one at a time. There is a unique
constraint
> on
> > the table and sometimes the insert violates this and the constraint
> > violation gets returned as an error. What I want to do is to trap the
> error
> > report it in another table and continue processing all of the inserts
and
> > not have the unique constraint error returned to the calling
application.
> > The procedure was changed to check @.@.ERROR and use a CONTINUE to keep
the
> > procedure processing all the inserts and then I just do a RETURN(0) at
the
> > end. Unfortunately this still returns the unique constraint violation
> even
> > though I am doing the RETURN(0). There doesn't seem to be any way to
keep
> > the unique constraint from going through to ADO and reporting back an
> error.
> > The procedure has now been changed to check for the unique problem
before
> > performing the insert but that seems ineffecient so I would rather just
> > suppress the error if possible. Thanks in advance for any ideas.
> >
> > Wayne Antinore
> >
> >
>|||Hi,Greg
>The way I read Wayne's post, he wants his transaction to continue
>processing other rows, even after a constraint violation, so it would have
>the opposite effect from what he's after.
So ,he can use
This is an example from BOL
CREATE TABLE t1 (a int PRIMARY KEY)
CREATE TABLE t2 (a int REFERENCES t1(a))
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (4)
INSERT INTO t1 VALUES (6)
GO
SET XACT_ABORT OFF
GO
BEGIN TRAN
INSERT INTO t2 VALUES (1)
INSERT INTO t2 VALUES (2) /* Foreign key error */
INSERT INTO t2 VALUES (3)
COMMIT TRAN
GO
/* Select shows only keys 1 and 3 added.
Key 2 insert failed and was rolled back, but
XACT_ABORT was OFF and rest of transaction
succeeded.
*/
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> Hi Uri.
> I think this won't help Wayne.
> SET XACT_ABORT ON will attempt to rollback an entire transaction on any
> error. The way I read Wayne's post, he wants his transaction to continue
> processing other rows, even after a constraint violation, so it would have
> the opposite effect from what he's after.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > Wayne
> > Look at SET XACT_ABORT on BOL
> >
> > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > > I have a stored procedure that can insert multiple rows but it does
them
> > in
> > > a loop so the inserts happen one at a time. There is a unique
> constraint
> > on
> > > the table and sometimes the insert violates this and the constraint
> > > violation gets returned as an error. What I want to do is to trap the
> > error
> > > report it in another table and continue processing all of the inserts
> and
> > > not have the unique constraint error returned to the calling
> application.
> > > The procedure was changed to check @.@.ERROR and use a CONTINUE to keep
> the
> > > procedure processing all the inserts and then I just do a RETURN(0) at
> the
> > > end. Unfortunately this still returns the unique constraint violation
> > even
> > > though I am doing the RETURN(0). There doesn't seem to be any way to
> keep
> > > the unique constraint from going through to ADO and reporting back an
> > error.
> > > The procedure has now been changed to check for the unique problem
> before
> > > performing the insert but that seems ineffecient so I would rather
just
> > > suppress the error if possible. Thanks in advance for any ideas.
> > >
> > > Wayne Antinore
> > >
> > >
> >
> >
>|||Hi Uri.
I now see you intended to set it OFF rather than ON.
However - Wayne's problem still remains that errors will be thrown through
ADO to his client, regardless of how XACT_ABORT is set.
Whether you set XACT_ABORT either ON / OFF won't solve his problem..
Regards,
Greg Linwood
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OtZIISNuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> Hi,Greg
> >The way I read Wayne's post, he wants his transaction to continue
> >processing other rows, even after a constraint violation, so it would
have
> >the opposite effect from what he's after.
>
> So ,he can use
> This is an example from BOL
> CREATE TABLE t1 (a int PRIMARY KEY)
> CREATE TABLE t2 (a int REFERENCES t1(a))
> GO
> INSERT INTO t1 VALUES (1)
> INSERT INTO t1 VALUES (3)
> INSERT INTO t1 VALUES (4)
> INSERT INTO t1 VALUES (6)
> GO
> SET XACT_ABORT OFF
> GO
> BEGIN TRAN
> INSERT INTO t2 VALUES (1)
> INSERT INTO t2 VALUES (2) /* Foreign key error */
> INSERT INTO t2 VALUES (3)
> COMMIT TRAN
> GO
> /* Select shows only keys 1 and 3 added.
> Key 2 insert failed and was rolled back, but
> XACT_ABORT was OFF and rest of transaction
> succeeded.
> */
> "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
> message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > Hi Uri.
> >
> > I think this won't help Wayne.
> >
> > SET XACT_ABORT ON will attempt to rollback an entire transaction on any
> > error. The way I read Wayne's post, he wants his transaction to continue
> > processing other rows, even after a constraint violation, so it would
have
> > the opposite effect from what he's after.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > > Wayne
> > > Look at SET XACT_ABORT on BOL
> > >
> > > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > Hi,
> > > > I have a stored procedure that can insert multiple rows but it does
> them
> > > in
> > > > a loop so the inserts happen one at a time. There is a unique
> > constraint
> > > on
> > > > the table and sometimes the insert violates this and the constraint
> > > > violation gets returned as an error. What I want to do is to trap
the
> > > error
> > > > report it in another table and continue processing all of the
inserts
> > and
> > > > not have the unique constraint error returned to the calling
> > application.
> > > > The procedure was changed to check @.@.ERROR and use a CONTINUE to
keep
> > the
> > > > procedure processing all the inserts and then I just do a RETURN(0)
at
> > the
> > > > end. Unfortunately this still returns the unique constraint
violation
> > > even
> > > > though I am doing the RETURN(0). There doesn't seem to be any way
to
> > keep
> > > > the unique constraint from going through to ADO and reporting back
an
> > > error.
> > > > The procedure has now been changed to check for the unique problem
> > before
> > > > performing the insert but that seems ineffecient so I would rather
> just
> > > > suppress the error if possible. Thanks in advance for any ideas.
> > > >
> > > > Wayne Antinore
> > > >
> > > >
> > >
> > >
> >
> >
>|||THere is no way to suppress the Constraint violation error from the stored
procedure ( on the back end.).
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>|||Greg
I hope it will be possible with "Yukon" to use begin try catch ( I don't
remember exactly what is a syntax)
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:#2qpMoNuDHA.2508@.TK2MSFTNGP12.phx.gbl...
> Hi Uri.
> I now see you intended to set it OFF rather than ON.
> However - Wayne's problem still remains that errors will be thrown through
> ADO to his client, regardless of how XACT_ABORT is set.
> Whether you set XACT_ABORT either ON / OFF won't solve his problem..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OtZIISNuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > Hi,Greg
> > >The way I read Wayne's post, he wants his transaction to continue
> > >processing other rows, even after a constraint violation, so it would
> have
> > >the opposite effect from what he's after.
> >
> >
> > So ,he can use
> > This is an example from BOL
> >
> > CREATE TABLE t1 (a int PRIMARY KEY)
> > CREATE TABLE t2 (a int REFERENCES t1(a))
> > GO
> > INSERT INTO t1 VALUES (1)
> > INSERT INTO t1 VALUES (3)
> > INSERT INTO t1 VALUES (4)
> > INSERT INTO t1 VALUES (6)
> > GO
> > SET XACT_ABORT OFF
> > GO
> > BEGIN TRAN
> > INSERT INTO t2 VALUES (1)
> > INSERT INTO t2 VALUES (2) /* Foreign key error */
> > INSERT INTO t2 VALUES (3)
> > COMMIT TRAN
> > GO
> >
> > /* Select shows only keys 1 and 3 added.
> > Key 2 insert failed and was rolled back, but
> > XACT_ABORT was OFF and rest of transaction
> > succeeded.
> > */
> >
> > "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote
in
> > message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > Hi Uri.
> > >
> > > I think this won't help Wayne.
> > >
> > > SET XACT_ABORT ON will attempt to rollback an entire transaction on
any
> > > error. The way I read Wayne's post, he wants his transaction to
continue
> > > processing other rows, even after a constraint violation, so it would
> have
> > > the opposite effect from what he's after.
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > > > Wayne
> > > > Look at SET XACT_ABORT on BOL
> > > >
> > > > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > > > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > Hi,
> > > > > I have a stored procedure that can insert multiple rows but it
does
> > them
> > > > in
> > > > > a loop so the inserts happen one at a time. There is a unique
> > > constraint
> > > > on
> > > > > the table and sometimes the insert violates this and the
constraint
> > > > > violation gets returned as an error. What I want to do is to trap
> the
> > > > error
> > > > > report it in another table and continue processing all of the
> inserts
> > > and
> > > > > not have the unique constraint error returned to the calling
> > > application.
> > > > > The procedure was changed to check @.@.ERROR and use a CONTINUE to
> keep
> > > the
> > > > > procedure processing all the inserts and then I just do a
RETURN(0)
> at
> > > the
> > > > > end. Unfortunately this still returns the unique constraint
> violation
> > > > even
> > > > > though I am doing the RETURN(0). There doesn't seem to be any way
> to
> > > keep
> > > > > the unique constraint from going through to ADO and reporting back
> an
> > > > error.
> > > > > The procedure has now been changed to check for the unique problem
> > > before
> > > > > performing the insert but that seems ineffecient so I would rather
> > just
> > > > > suppress the error if possible. Thanks in advance for any ideas.
> > > > >
> > > > > Wayne Antinore
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yep - Try / Catch will be good in Yukon.
I'm not sure if it allows error suppression, but I sure hope so!
Regards,
Greg Linwood
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#yR3yTOuDHA.1596@.TK2MSFTNGP10.phx.gbl...
> Greg
> I hope it will be possible with "Yukon" to use begin try catch ( I don't
> remember exactly what is a syntax)
>
>
> "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
> message news:#2qpMoNuDHA.2508@.TK2MSFTNGP12.phx.gbl...
> > Hi Uri.
> >
> > I now see you intended to set it OFF rather than ON.
> >
> > However - Wayne's problem still remains that errors will be thrown
through
> > ADO to his client, regardless of how XACT_ABORT is set.
> >
> > Whether you set XACT_ABORT either ON / OFF won't solve his problem..
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:OtZIISNuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > Hi,Greg
> > > >The way I read Wayne's post, he wants his transaction to continue
> > > >processing other rows, even after a constraint violation, so it would
> > have
> > > >the opposite effect from what he's after.
> > >
> > >
> > > So ,he can use
> > > This is an example from BOL
> > >
> > > CREATE TABLE t1 (a int PRIMARY KEY)
> > > CREATE TABLE t2 (a int REFERENCES t1(a))
> > > GO
> > > INSERT INTO t1 VALUES (1)
> > > INSERT INTO t1 VALUES (3)
> > > INSERT INTO t1 VALUES (4)
> > > INSERT INTO t1 VALUES (6)
> > > GO
> > > SET XACT_ABORT OFF
> > > GO
> > > BEGIN TRAN
> > > INSERT INTO t2 VALUES (1)
> > > INSERT INTO t2 VALUES (2) /* Foreign key error */
> > > INSERT INTO t2 VALUES (3)
> > > COMMIT TRAN
> > > GO
> > >
> > > /* Select shows only keys 1 and 3 added.
> > > Key 2 insert failed and was rolled back, but
> > > XACT_ABORT was OFF and rest of transaction
> > > succeeded.
> > > */
> > >
> > > "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote
> in
> > > message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > Hi Uri.
> > > >
> > > > I think this won't help Wayne.
> > > >
> > > > SET XACT_ABORT ON will attempt to rollback an entire transaction on
> any
> > > > error. The way I read Wayne's post, he wants his transaction to
> continue
> > > > processing other rows, even after a constraint violation, so it
would
> > have
> > > > the opposite effect from what he's after.
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > > > > Wayne
> > > > > Look at SET XACT_ABORT on BOL
> > > > >
> > > > > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > > > > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > > Hi,
> > > > > > I have a stored procedure that can insert multiple rows but it
> does
> > > them
> > > > > in
> > > > > > a loop so the inserts happen one at a time. There is a unique
> > > > constraint
> > > > > on
> > > > > > the table and sometimes the insert violates this and the
> constraint
> > > > > > violation gets returned as an error. What I want to do is to
trap
> > the
> > > > > error
> > > > > > report it in another table and continue processing all of the
> > inserts
> > > > and
> > > > > > not have the unique constraint error returned to the calling
> > > > application.
> > > > > > The procedure was changed to check @.@.ERROR and use a CONTINUE to
> > keep
> > > > the
> > > > > > procedure processing all the inserts and then I just do a
> RETURN(0)
> > at
> > > > the
> > > > > > end. Unfortunately this still returns the unique constraint
> > violation
> > > > > even
> > > > > > though I am doing the RETURN(0). There doesn't seem to be any
way
> > to
> > > > keep
> > > > > > the unique constraint from going through to ADO and reporting
back
> > an
> > > > > error.
> > > > > > The procedure has now been changed to check for the unique
problem
> > > > before
> > > > > > performing the insert but that seems ineffecient so I would
rather
> > > just
> > > > > > suppress the error if possible. Thanks in advance for any
ideas.
> > > > > >
> > > > > > Wayne Antinore
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks Uri, Greg, and Wayne for your replies. I didn't think there was any
way to do this currently but I'm glad you confirmed it for me. This will be
a very useful feature in Yukon
Thanks again,
Wayne
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>

Any way to optimise this query?

Hi guys

Is there any way I can run this query faster? Should I take out the ORDER BY
clause? This is supposed to return 17,000 rows and takes around 30 minutes or
so. Is there no way at all to get this result faster?

select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.
clientinitials, r.clientidno, r.grantedamount, r.bankname,
r.accountnumber, r.status, r.grantdate, r.consultantname, r.propertyaddress,
r.erfdescription, r.commenthistory, br.expectedregdate
from bondtrak..rptdetail r
join ebondprd..bankresponse br
on br.applicationid = r.applicationid
where
r.rundate = '20051010'
and r.primarybankind = 'Y'
and r.status = 'granted'
and r.statusdate between '20020101' and '20050930'
and r.businessunit in ('bond choice', 'ppl')
order by r.sitename, r.consultantname, r.statusdate

Thanks for any help.
Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1If you don't need the result ordered then definitely drop the ORDER BY.

Why do you want to return 17,000 rows in one hit anyway? Can't you
process the data server side?

--
David Portas
SQL Server MVP
--|||Do you have any usefull indexes on these tables?

"I sense many useless updates in you... Useless updates lead to
defragmentation... Defragmentation leads to downtime...Downtime leads
to suffering..Defragmentation is the path to the darkside.. DBCC
INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with
you" -- http://sqlservercode.blogspot.com/|||Driesen via SQLMonster.com (u11907@.uwe) writes:
> Is there any way I can run this query faster? Should I take out the
> ORDER BY clause? This is supposed to return 17,000 rows and takes around
> 30 minutes or so. Is there no way at all to get this result faster?
> select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.
> clientinitials, r.clientidno, r.grantedamount, r.bankname,
> r.accountnumber, r.status, r.grantdate, r.consultantname,
> r.propertyaddress,
> r.erfdescription, r.commenthistory, br.expectedregdate
> from bondtrak..rptdetail r
> join ebondprd..bankresponse br
> on br.applicationid = r.applicationid
> where
> r.rundate = '20051010'
> and r.primarybankind = 'Y'
> and r.status = 'granted'
> and r.statusdate between '20020101' and '20050930'
> and r.businessunit in ('bond choice', 'ppl')
> order by r.sitename, r.consultantname, r.statusdate

There might be. But without knowledge of the tables, indexes and how
big they are, all you can get is guesses. If you don't need data to be
sorted, you can remove ORDER BY, but it doesn't take 29 minutes to sort
17000 rows, so the effect is moderate of that operation.

A clustered index on rptdetail(rundste, statusdate) or only (rundate)
should be a good start. An index on bankreponse(applicationid) is also
necessary.

You may also have problems with statistics that are out of date.
UPDATE STATISTICS WITH FULLSCAN on both table can address this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi David

I dropped the ORDER BY and copied it Excel. This is only a once off query for
the big wigs.

Thanks for the help
Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1|||Hi Erland

Thanks for the help. I did drop the ORDER BY. The only reason this takes so
long to run is becasue our DataBase sits over 500 miles away. We to get
through a whole lot of banking environment security to access our DB.

Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1|||> We to get
> through a whole lot of banking environment security to access our DB.

So you thought you'd undermine all that security by copying the data to
an insecure Excel spreadsheet... :-)

Maybe you can use DTS to export the data to Excel. Or use BCP to create
a delimited file that can be opened in Excel.

--
David Portas
SQL Server MVP
--

2012年3月19日星期一

Any way to delete rows from table and ignore Foreign Keys

I am trying to delete data from a table which is being referenced by a lot
of tables by Foreign Keys. There are 2 tables in particular that do not
have an index on the Foreign Key field referencing the table that I am
deleting from.
I am certain that there are no rows in this dependant table referencing the
rows that I want to delete.
Is it possible to somehow perform the delete without checking the Foreign
Key? Since this is a production database - I don't want to drop the FK and
then redefine it later because I will experience blocking while the create
of the Foreign Key is running.
Any help would be appreciated.
ThanksYou can disable the FK, delete and then enable it. But that leave the FK non-trusted, unless you
enable it with the CHECK option, but that leaves you with SQL Server checking the data when enabling
it (basically same as dropping and creating). Same old story, can't eat the cake and have it. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:O9VnXOyiGHA.1552@.TK2MSFTNGP03.phx.gbl...
>I am trying to delete data from a table which is being referenced by a lot
> of tables by Foreign Keys. There are 2 tables in particular that do not
> have an index on the Foreign Key field referencing the table that I am
> deleting from.
> I am certain that there are no rows in this dependant table referencing the
> rows that I want to delete.
> Is it possible to somehow perform the delete without checking the Foreign
> Key? Since this is a production database - I don't want to drop the FK and
> then redefine it later because I will experience blocking while the create
> of the Foreign Key is running.
> Any help would be appreciated.
> Thanks
>

Any way to delete rows from table and ignore Foreign Keys

I am trying to delete data from a table which is being referenced by a lot
of tables by Foreign Keys. There are 2 tables in particular that do not
have an index on the Foreign Key field referencing the table that I am
deleting from.
I am certain that there are no rows in this dependant table referencing the
rows that I want to delete.
Is it possible to somehow perform the delete without checking the Foreign
Key? Since this is a production database - I don't want to drop the FK and
then redefine it later because I will experience blocking while the create
of the Foreign Key is running.
Any help would be appreciated.
ThanksYou can disable the FK, delete and then enable it. But that leave the FK non
-trusted, unless you
enable it with the CHECK option, but that leaves you with SQL Server checkin
g the data when enabling
it (basically same as dropping and creating). Same old story, can't eat the
cake and have it. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:O9VnXOyiGHA.1552@.TK2MSFTNGP03.phx.gbl...seagreen">
>I am trying to delete data from a table which is being referenced by a lot
> of tables by Foreign Keys. There are 2 tables in particular that do not
> have an index on the Foreign Key field referencing the table that I am
> deleting from.
> I am certain that there are no rows in this dependant table referencing th
e
> rows that I want to delete.
> Is it possible to somehow perform the delete without checking the Foreign
> Key? Since this is a production database - I don't want to drop the FK an
d
> then redefine it later because I will experience blocking while the create
> of the Foreign Key is running.
> Any help would be appreciated.
> Thanks
>

any way to check the duplicated rows in destination before loading data?

Hi. As the title, I am try to figure out how to write script to prevent duplicated rows before loading data from couple csv files to the OLE database table.
Another quick question, when I use Data Conversion to convert data from string to datetime or decimal type, it always return error like potential data loss.

For your first question, probably an easier approach is to use a SORT transform to remove duplicate records before loading data into your destination.

For the other question, I think it's a matter of which format you used in your source strings. Firstly pls be aware we use locale information when doing converting strings to date types or decimals. Secondly, when converting string to date types, you have two options: normal conversion and fast-parse conversion. Normal conversion supports standard oledb formats while fastparse supports ISO 8601. (fastparse option is on the DataConversion output columns)

You'll need to get more detailed helps on this from SQLServer Books On Line. e.g. For fastparse, pls refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/bed6e2c1-791a-4fa1-b29f-cbfdd1fa8d39.htm

thanks

wenyang

|||Thanks for your fast response. My first question is to load date from CSV files to the table, but don't insert the duplicated rows which are already existed in the table.|||

I see. you want to avoid inserting rows which'll duplicate rows in your existing destination table. In that case, you can do a lookup first, then leading only those "failing" rows to destination. Remember to set Lookup's error flow handling to Redirect.

thanks

wenyang

2012年3月11日星期日

Any suggestions pls....

Hi,
I will explain the scenario and the way i am doing it. Pls suggest me on how
to continue.
I have a ReportA with a table with 100 rows. I have a textbox that says
Filter which has a navigation to another Report, ReportB. Now this Report is
like a filter report - Where all the field names in ReportA are loaded as
parameters. Once the user selects the reports - clicks on the view button - I
want this to be show in the table on ReportB and filtered on ReportA.
How can i do that ?
Any help is appreciated.
Thanks
RPI'm not sure I entirely understand what you want to acheive but if you
need to generate data from one report and pass it to another, you may
want to use some VB.Net code using ADO.Net to generate the data which
can then be read in the other report.
If all you're trying to do is mirror parameters selected from ReportA
through to ReportB then set up appropriate parameters in ReportB and
you can pass the selections through dynamically in the navigation
properties.
Chris|||why don't you use a report parameter to filter?
see:
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=990da582-3282-45e9-9469-fb6647bd8ee6
for some other possibilities
"RP" wrote:
> Hi,
> I will explain the scenario and the way i am doing it. Pls suggest me on how
> to continue.
> I have a ReportA with a table with 100 rows. I have a textbox that says
> Filter which has a navigation to another Report, ReportB. Now this Report is
> like a filter report - Where all the field names in ReportA are loaded as
> parameters. Once the user selects the reports - clicks on the view button - I
> want this to be show in the table on ReportB and filtered on ReportA.
> How can i do that ?
> Any help is appreciated.
> Thanks
> RP

Any suggestions on how to optimize a query written in Dynamic SQL?

I added the subquery and now this thing consistently takes more than five minutes to return 7100+ rows. Any suggestions? Thanks again, you guys are the best.

ddave
---------
SET @.StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
FROM dbo.ViewEligibilityPHC VEL
LEFT OUTER JOIN (SELECT *
FROM dbo.MEMB_LISHISTS l
WHERE l.LISThruDate is null
AND l.Deleted = ''0'') AS Q1 ON VEL.MEMBID = Q1.MemberID
WHERE VEL.OPTHRUDT is null
AND VEL.OPT LIKE ''' + @.HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @.Prvdr + ''')
AND VEL.HCC LIKE ''' + @.HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @.CaseMngr + ''')
AND VEL.OPFROMDT <= CAST(''' + @.SDate + ''' AS datetime)
AND ISNULL(VEL.OPTHRUDT, CAST(''' + @.TDate + ''' AS datetime)) >= CAST(''' + @.EDate + ''' AS datetime)) 'Be nice and post the results of:

PRINT @.StrQry1

...so we can focus on the SQL and not the dynamic concatenation...|||Sorry it took so long. I had to modify the procedure to print that string and run it again:-). These are the fields. It is confidential medical data so I can't put the actual data here. The procedure is below. Also if there are any obvious errors any tips would be greatly appreciated.

Counter
SubsidyLevel
MEMBID
PATID
SUBSSN
AIDSDATE
AIDCODE
OPFROMDT
OPTHRUDT
OPT
CURRENTCOUNTYID
LASTNM
FIRSTNM
BIRTH
HCP1NUM
SEX
HCC
HCCName
CaseMgrID
CaseMgrName
PCPFROMDT
PCP
PCPName
STREET
STREET2
CITY
STATE
ZIP
PHONE
CURRHIST
INTLZIP
TransferOut
CoPay
CoPayEffDate
PartsABD

-- The entire code is:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

alter PROCEDURE DBO.STANDARD_MEMBERSHIP_QUERY_test_20071015

@.StartDate DateTime, @.EndDate DateTime,
@.HPlan varchar(20), @.HCC varchar(3),
@.Prvdr varchar(20), @.CaseMngr varchar(5),
@.Report smallint

AS

SET NOCOUNT ON

BEGIN

DECLARE @.SDate varchar(10), @.EDate varchar(10),
@.TDate varchar(10)

SET @.SDate = Convert(varchar(10),@.StartDate, 101)
SET @.EDate = Convert(varchar(10), @.EndDate, 101)
SET @.TDate = '06/06/2079'

DECLARE @.SDateP varchar(10), @.EDateP varchar(10)
DECLARE @.MySQL varchar(1600), @.StrQry1 varchar(800), @.StrQry2 varchar(800)

SET @.SDateP = Convert(varchar(10), DateAdd(Month, -1, @.StartDate), 101)
SET @.EDateP = Convert(varchar(10), DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, Cast(@.SDateP AS datetime))+1, 0)), 101)

SET @.StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
FROM dbo.ViewEligibilityPHC VEL
LEFT OUTER JOIN (SELECT *
FROM dbo.MEMB_LISHISTS l
WHERE l.LISThruDate is null
AND l.Deleted = ''0'') AS Q1 ON VEL.MEMBID = Q1.MemberID
WHERE VEL.OPTHRUDT is null
AND VEL.OPT LIKE ''' + @.HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @.Prvdr + ''')
AND VEL.HCC LIKE ''' + @.HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @.CaseMngr + ''')
AND VEL.OPFROMDT <= CAST(''' + @.SDate + ''' AS datetime)
AND ISNULL(VEL.OPTHRUDT, CAST(''' + @.TDate + ''' AS datetime)) >= CAST(''' + @.EDate + ''' AS datetime)) '

SET @.MySQL = @.StrQry1

END

EXEC (@.MySQL)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||also and in the meantime I am studying how to index the view in question. I have rarely worked with views and I have never created an index before.

ddave|||One more time...
Be nice and post the results of:

PRINT @.StrQry1

...so we can focus on the SQL and not the dynamic concatenation...|||to do what blindman is asking, change your EXEC(@.MySQL) to PRINT @.MySQL and post the result.|||You should try to avoid sub queries.

SET @.StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
FROM dbo.ViewEligibilityPHC VEL
LEFT JOIN dbo.MEMB_LISHISTS Q1
on l.LISThruDate is null
AND l.Deleted = ''0'' and VEL.MEMBID = Q1.MemberID
WHERE VEL.OPTHRUDT is null
AND VEL.OPT LIKE ''' + @.HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @.Prvdr + ''')
AND VEL.HCC LIKE ''' + @.HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @.CaseMngr + ''')
AND VEL.OPFROMDT <= CAST(''' + @.SDate + ''' AS datetime)
AND ISNULL(VEL.OPTHRUDT, CAST(''' + @.TDate + ''' AS datetime)) >= CAST(''' + @.EDate + ''' AS datetime)) '

I also suspect that massive index/table scans causes your problem.

Please post the execution plan for the query.
How to:
print @.StrQry1
copy the SQL to a new query windows.
Execute
set showplan_text on
go
your query here...

Copy and paste the text from the result window to this thread.|||Actually we resolved it by altering the view. We needed to add one field and we just added it to the view instead of going through all this. Thanks anyway.

ddave|||the word dynamic in the term dynamic sql almost makes it sound like a good thing. it deserves a name more akin to it's nature. maybe kludged sql? maybe poor perfroming insecure code?|||Perhaps "Injectable SQL"? Or maybe "Objectional SQL"?

It has its uses, but buy can it be abused.|||Why are you using dynamic sql ?, I usually only use dynamic sql if table names need to be assigned dynamically based on certain conditions. Where do you append the "%" for the likes, as part of the input variable ?|||That's when I use it also to dynamically name tables for example. I got it that way from my supervisor. Sigh, I guess I will just have to blame him. :)

ddave|||And when you have to use dynamic SQL (or injection-sql) do NOT use do it by concatenating the parameters into the sql string. Use sp_executesql with parameters.

Any store dprocedure to know this in merge replictaion

Is there a way i can know the rows that where synchronised in the
publisher after some updates are done on subcriber and synchronised
to publishers?
Example say i update a table1 in subcriber and click synchronise and
the data is updated in publisher.
But on publisher side i need to know what rows were added either
through trigger,jobs or columns in system merge tables
Not sure if this helps...but with Merge Replication, there are triggers
(Insert, Update and Delete) set up for tables that particpate in the
replication. Perhaps you could hook into these existing triggers some way?
"vk" wrote:

> Is there a way i can know the rows that where synchronised in the
> publisher after some updates are done on subcriber and synchronised
> to publishers?
> Example say i update a table1 in subcriber and click synchronise and
> the data is updated in publisher.
> But on publisher side i need to know what rows were added either
> through trigger,jobs or columns in system merge tables
>

2012年3月6日星期二

Any other way but Cursors or While loop

Hi all,

I have a bit of a challenging on my hands with this,

i have a result set in a table which i am trying to iterate through the rows one by one i.e

Values

Values Dates

1.10

1.20

1.30

1.40

2006-03-31 00:00:00.000

2006-06-30 00:00:00.000

2006-09-30 00:00:00.000

2006-12-31 00:00:00.000

go to each row get the value and the date perform some calculation with the value and the date and insert it into another table(which sounds easy enough) my problem is how do i iterate through the result set one by one without using a cursor or a while loop?

do i have any other options at all?

Shadow:

You can take advantage of the explicit values you have in your "Values" column and use that as an in situ iterator table. For example:

set nocount on
declare @.quarter table
( quarter# numeric (9,2) not null,
qtrDate datetime not null
)
insert into @.quarter values (1.10, '2006-03-31 00:00:00.000')
insert into @.quarter values (1.20, '2006-06-30 00:00:00.000')
insert into @.quarter values (1.30, '2006-09-30 00:00:00.000')
insert into @.quarter values (1.40, '2006-12-31 00:00:00.000')
declare @.mockData table
( mock_id integer not null,
mock_dt datetime not null,
mock_value float not null
)
insert into @.mockData values (1, '1/14/6 1:00', 25.4)
insert into @.mockData values (2, '2/15/6 2:00', 17.3)
insert into @.mockData values (3, '5/16/6 5:00', 4.3)
insert into @.mockData values (4, '7/5/6 6:00', 7.7)
insert into @.mockData values (5, '9/30/6 23:00', 96.6)
insert into @.mockData values (6, '10/7/6', 14.5)
select iter,
qtrDate,
sum (mock_value)
from ( select convert (int, 10*(quarter# - convert (integer, quarter#))) as iter,
qtrDate
from @.quarter
) qtr
inner join @.mockData d
on mock_dt < dateadd (day, 1, qtrDate)
group by iter, qtrDate

An alternative is to establish an explicit iterator table in your database. Such as:

create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)

I populate my iterator tables with values 1-32767.

Dave

|||It seems what you need is a straight set operation in SQL. Post the caculation you want to operate on the result set.
I think you can do this in a SELECT statement , then INTO another new table with the caculated results.|||

Hi Dave thanks for the solution but iam not quite sure how the above works I can see you have declared another table 'mock table ' and given it like an identity field, what is the select statement:

select iter,
qtrDate,
sum (mock_value)
from ( select convert (int, 10*(quarter# - convert (integer, quarter#))) as iter,
qtrDate
from @.quarter
) qtr
inner join @.mockData d
on mock_dt < dateadd (day, 1, qtrDate)
group by iter, qtrDate

supposed to be doing?

|||

Hi thanks for your reply, but i am not sure how you mean could you give me an example with my table above on how to do this ? imagin that the values in each row some how breaks up into 3 (calc) and the valuedate for that row has been calculated into three seperate months....how do you then do it in a straight set as you have suggested?

|||

Sorry that I rushed this, Shadow. I needed to get to a meeting so I rushed.

Dave
-- --
-- First, let's load your quarterly values into a table.
-- --
set nocount on
declare @.quarter table
( quarter# numeric (9,2) not null,
qtrDate datetime not null
)
insert into @.quarter values (1.10, '2006-03-31 00:00:00.000')
insert into @.quarter values (1.20, '2006-06-30 00:00:00.000')
insert into @.quarter values (1.30, '2006-09-30 00:00:00.000')
insert into @.quarter values (1.40, '2006-12-31 00:00:00.000')
-- --
-- Now lets show it; notice that we can leverage the fractional part of the
-- first data element and use that as an iterator from 1-4.
-- --
select convert (int, 10*(quarter# - convert (integer, quarter#))) as iter,
quarter#,
qtrDate
from @.quarter
-- --
-- Some arbitrary data that can be partitioned by date
-- --
declare @.mockData table
( mock_id integer not null,
mock_dt datetime not null,
mock_value float not null
)
insert into @.mockData values (1, '1/14/6 1:00', 25.4)
insert into @.mockData values (2, '2/15/6 2:00', 17.3)
insert into @.mockData values (3, '5/16/6 5:00', 4.3)
insert into @.mockData values (4, '7/5/6 6:00', 7.7)
insert into @.mockData values (5, '9/30/6 23:00', 96.6)
insert into @.mockData values (6, '10/7/6', 14.5)
-- --
-- Now, we can use the defactor iterator that came from the data -- in this
-- case the "inner select." The scan will now make 4 passes through the
-- mock data and generate a sum for each pass.
--
-- What I am showing here is YTD sum of the "mock_value" field. Note that if
-- we would want to show this value for only a quarter that it would be best
-- to modify our @.quarter table to include a data range instead of a simple
-- date. Also notice the "DATEADD". This is because the "time" that is
-- part of the 9/30 record puts this record technically past the 9/30 EOQ date
-- --
select iter,
qtrDate,
sum (mock_value)
from ( select convert (int, 10*(quarter# - convert (integer, quarter#))) as iter,
qtrDate
from @.quarter
) qtr
inner join @.mockData d
on mock_dt < dateadd (day, 1, qtrDate)
group by iter, qtrDate
-- --
-- OK, the derived table is not necessary; a straight-up join in this case
-- is better. I hurried because I had a meeting to go to -- you know, a work
-- break. The "convert" feature is not necessary unless you need the
-- enumerations.
-- --
select convert (int, 10*(quarter# - convert (integer, quarter#))) as iter,
qtrDate,
sum (mock_value)
from @.quarter
inner join @.mockData d
on mock_dt < dateadd (day, 1, qtrDate)
group by convert (int, 10*(quarter# - convert (integer, quarter#))), qtrDate

|||Hello:
You can post a few records for your first table, then describe how you want your calculation done, then post the result you want in another table. In this way, it will hep to work out a SQL solution which may use a set operation, instead of the loop technique you are looking for.
Thanks.|||

Hi Thanks,

For your reply below is basically what i am trying to do the process is almost the same for my monthly and quaterly table except for the calculations in my quarterly table this is a detailed process below...

Basically i want to iterate through the top table getting each row and then expanding it into another table that represents a month and inserting it into a final table that may or may not have those dates essentially doing and insert( if the consecutive dates do not exist, hence create them in the final and then add in the data ) or an update (if the consecutive dates exists) in the final..

..for simplicity i have created all tables involved, but in reality the final table is not a temp table and exists already

--This is the Sample Monthly Data Table that needs to be iterated through..That i dont want to use a cursor for or a while loop
if Object_id('tempdb..#MonthlyTable') IS NOT NULL
Drop Table #MonthlyTable

Create table #MonthlyTable(MonthlyIndexCode varchar(10), MonthlyIndexLevel float, MonthlyValueDate datetime)

Insert Into #MonthlyTable
(MonthlyIndexCode, MonthlyIndexLevel, MonthlyValueDate)

Select 'Test1' , 1.50 , '20060131' --Jan
Union
Select 'Test2' , 2.50 , '20060228' --Feb
Union
Select 'Test3' , 8.50 , '20060331' --Mar
Union
Select 'Test4' , 4.50 , '20060430' --Apr
Union
Select 'Test5' , 5.50 , '20060531' --May
Union
Select 'Test6' , 6.50 , '20060630' --Jun
Union
Select 'Test7' , 5.50 , '20060731' --Jul
Union
Select 'Test8' , 8.50 , '20060831' --Aug
Union
Select 'Test9' , 9.50 , '20060930' --Sep
Union
Select 'Test10' , 10.50 , '20061031' --Oct
Union
Select 'Test11' , 11.50 , '20061130' --Nov
Union
Select 'Test12' , 12.50 , '20061231' --Dec

--Check table
--Select * from #MonthlyTable Order by MonthlyValueDate

--START PROCESS: FOR EACH ROW IN THE ABOVE #MONTHLY TABLE
--For each record in the above Table decompound each record as it represents data for a whole month (for that records ValueDate)

Declare @.StartDate datetime,@.TempStartDate Datetime, @.EndDate datetime
set @.StartDate = '20060131'

--derive the MonthEndDate from the StartDate(make sure the start date is corrected to its end of the month)
set @.EndDate = DateAdd(Month, 1, dbo.GetMonthendDate(@.StartDate) )

if Object_id('tempdb..#FullMonthlyTable') IS NOT NULL
Drop Table #FullMonthlyTable

Create table #FullMonthlyTable(IndexCode varchar(10), IndexLevel float, ValueDate datetime)

--Insert the start and endDate
Insert Into #FullMonthlyTable
(IndexCode, IndexLevel, ValueDate)
Select 'Test1' , 1.50 , @.StartDate
Union
Select 'Test1' , 1.50 ,@.EndDate
--use a tempstart date for the while loop population
Set @.TempStartDate = @.StartDate

--now populate the rows between StartDate and EndDate
-- i.e Create all the consecutive ValueDates so as to have a complete month(from the 31 of the previous month to the end of next month -1day)
While(@.TempStartDate < @.EndDate -1 )
Begin
Insert Into #FullMonthlyTable( ValueDate ) Values (@.TempStartDate + 1)
Set @.TempStartDate = @.TempStartDate + 1 --Increment dates consecutively..can do monthly as well as quarterly
End

--now populate the Values for the valuedates between start and end date
Update #FullMonthlyTable
Set IndexCode = 'Test1',
IndexLevel = 1.50
--Where #FullMonthlyTable -- for some reason this bloody where clause ain't working!! no matter i can just update since i have the start and end date
--Between @.StartDate And @.EndDate

--Create final Table:
Create table #MyFinalTable(IndexCode varchar(10), IndexLevel float, ValueDate datetime)

--1,DO UPDATE IF THE DATES ALREADY EXIST IN THE FINAL Table
--update final table:
Update #MyFinalTable
Set IndexLevel = #FullMonthlyTable.IndexLevel
ValueDate = #FullMonthlyTable.ValueDate
Where #FullMonthlyTable
Between @.StartDate And @.EndDate

--2,DO INSERT IF THE DATES DONT ALREADY EXIST IN THE FINAL Table
--Insert Into MyFinal Table:

--Create Date Range in the final table first...and insert the value in the start and end date
--(that will be used to populate all the dates for that month)as above using the file loop
Insert Into #MyFinalTable
(IndexCode, IndexLevel , ValueDate)
Select 'Test1' , #FullMonthlyTable.IndexLevel , @.StartDate
Union
Select 'Test1' , #FullMonthlyTable.IndexLevel , @.EndDate

--...then do the first update to create the consecutive dates between start and end dates
--use a tempstart date for the while loop population
Set @.TempStartDate = @.StartDate

--now populate the rows between StartDate and EndDate
-- i.e Create all the consecutive ValueDates so as to have a complete month(from the 31 of the previous month to the end of next month -1day)
While(@.TempStartDate < @.EndDate -1 )
Begin
Insert Into #MyFinalTable( ValueDate ) Values (@.TempStartDate + 1)
Set @.TempStartDate = @.TempStartDate + 1 --Increment dates consecutively..can do monthly as well as quarterly
End

--Now do this for every row in

--END PROCESS:

Drop Table #MonthlyTable
Drop Table #FullMonthlyTable
Drop Table #MyFinalTable

--As you can see i have to use a while loop each tme i need to create my consecutive dates between the start and end dates in both the #FullMonthlyTable and the #MyFinalTable...and i really dont want to do it this way or with a cursor for lots of reasons as well as perfomance!

if there is a way with set operations please help.