Can we do a double Cursor where we select the data form one table using a
cursor and then use the key data such as ORDER number to select data from
another table using a cursor?
regards,
RonYou can. You can also try to take your bike on the freeway at rush hour and
challenge a street racer...
Any reason you can't do this with a simple join? What exactly are you
trying to accomplish?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
<Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
> Can we do a double Cursor where we select the data form one table using a
> cursor and then use the key data such as ORDER number to select data from
> another table using a cursor?
> --
> regards,
> Ron|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:
> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:
> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I have absolutely no idea what an "SQR program" is. Do you mean square
root?
Anyway, a double cursor would look like this:
CREATE TABLE #pk
(
id INT PRIMARY KEY
)
CREATE TABLE #fk
(
id int FOREIGN KEY REFERENCES #pk(id),
foo VARCHAR(2)
)
SET NOCOUNT ON
INSERT #pk
SELECT 1
UNION SELECT 2
INSERT #fk
SELECT 1, 'a'
UNION SELECT 1, 'b'
UNION SELECT 2, 'c'
DECLARE @.pkID INT, @.foo VARCHAR(2)
DECLARE c1 CURSOR FOR
SELECT id FROM #pk
OPEN c1
FETCH NEXT FROM c1 INTO @.pkID
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE c2 CURSOR FOR
SELECT foo FROM #fk WHERE id = @.pkID
OPEN c2
FETCH NEXT FROM c2 INTO @.foo
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Outer loop: '+RTRIM(@.pkID)
PRINT 'Inner loop: '+@.foo
FETCH NEXT FROM c2 INTO @.foo
END
CLOSE c2
DEALLOCATE c2
FETCH NEXT FROM c1 INTO @.pkID
END
CLOSE c1
DEALLOCATE c1
DROP TABLE #fk, #pk
Please don't do this on a production system. If you do, don't tell them I
told you how to do it. I will deny it and tell them it was Celko, spoofing
my IP and all.
On 3/22/05 8:03 PM, in article
9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
<Ronhayim@.discussions.microsoft.com> wrote:
> Im trying to simulate SQR program. Do you have an example where you use
> double cursor.|||On Tue, 22 Mar 2005 15:41:02 -0800, Ron wrote:
>Can we do a double Cursor where we select the data form one table using a
>cursor and then use the key data such as ORDER number to select data from
>another table using a cursor?
Hi Ron,
Even if you really do need a cursor (which I doubt - and the statistics
are on my side), there is really no need to use two of the beasts.
Why not create one query that joins the two tables the way you want them
to be joined, filters rows you don't need and returns only the columns
you need? Then, if you really must, you can always use that query for
your cursor...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The script was a lot of help. SQR is a program that used by Peoplesoft.
This was just a special need. All of you right, just joining the tables
should do the it.
"Aaron [SQL Server MVP]" wrote:
> I have absolutely no idea what an "SQR program" is. Do you mean square
> root?
> Anyway, a double cursor would look like this:
>
> CREATE TABLE #pk
> (
> id INT PRIMARY KEY
> )
> CREATE TABLE #fk
> (
> id int FOREIGN KEY REFERENCES #pk(id),
> foo VARCHAR(2)
> )
> SET NOCOUNT ON
> INSERT #pk
> SELECT 1
> UNION SELECT 2
> INSERT #fk
> SELECT 1, 'a'
> UNION SELECT 1, 'b'
> UNION SELECT 2, 'c'
> DECLARE @.pkID INT, @.foo VARCHAR(2)
> DECLARE c1 CURSOR FOR
> SELECT id FROM #pk
> OPEN c1
> FETCH NEXT FROM c1 INTO @.pkID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DECLARE c2 CURSOR FOR
> SELECT foo FROM #fk WHERE id = @.pkID
> OPEN c2
> FETCH NEXT FROM c2 INTO @.foo
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Outer loop: '+RTRIM(@.pkID)
> PRINT 'Inner loop: '+@.foo
> FETCH NEXT FROM c2 INTO @.foo
> END
> CLOSE c2
> DEALLOCATE c2
> FETCH NEXT FROM c1 INTO @.pkID
> END
> CLOSE c1
> DEALLOCATE c1
> DROP TABLE #fk, #pk
>
> Please don't do this on a production system. If you do, don't tell them I
> told you how to do it. I will deny it and tell them it was Celko, spoofin
g
> my IP and all.
>
> On 3/22/05 8:03 PM, in article
> 9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
> <Ronhayim@.discussions.microsoft.com> wrote:
>
>
Showing posts with label number. Show all posts
Showing posts with label number. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Can we break a chart line
I have a line chart which describe the number of absent students every week.
There is summer vocation during a year (week 27th to week 39th)
I want to the line break up when summer vocation.
Can we break line where summer vocation?As I understand your problem you have dates plotted along the X-axis and your
chart shows the dates during the summer vactaion with data points of zero
being charted during that period (assuming that the minum value for the
Y-axis is set at zero). One sulution that you can use is to force the reurned
the data to have minus values during the summer vacation. That way the data
points are invisible because they are off the chart. You can force the minus
values in a number of ways.
There may be other solutions but hopefully this will help.
"ad" wrote:
> I have a line chart which describe the number of absent students every week.
> There is summer vocation during a year (week 27th to week 39th)
> I want to the line break up when summer vocation.
> Can we break line where summer vocation?
>
>|||Thank,
I have filtered out the 27th week to 39th weeks,So there are no value
between 26th week and 40 weeks.
But there is still a line between 26th week and 40 week.
I want that there are not any line between 26th week and 4o weeks.
Can I do that?
"B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:A4567ECA-D3C0-4C5B-A870-A252D35338A2@.microsoft.com...
> As I understand your problem you have dates plotted along the X-axis and
your
> chart shows the dates during the summer vactaion with data points of zero
> being charted during that period (assuming that the minum value for the
> Y-axis is set at zero). One sulution that you can use is to force the
reurned
> the data to have minus values during the summer vacation. That way the
data
> points are invisible because they are off the chart. You can force the
minus
> values in a number of ways.
> There may be other solutions but hopefully this will help.
> "ad" wrote:
> > I have a line chart which describe the number of absent students every
week.
> >
> > There is summer vocation during a year (week 27th to week 39th)
> >
> > I want to the line break up when summer vocation.
> >
> > Can we break line where summer vocation?
> >
> >
> >|||I don't think that you can simply not chart the summer vacation period (break
the line) as long as this period is displayed. Thus my suggestion to force
the data points to a negative value show that they do not show up. Note that
a side result of this is that the charted line will appear to drop off the
chart at the beginning of the summer and then rise back up after the summer
break is over. Similar to charting values that are higher than a maximum
value that you set for the Y-axis.
"ad" wrote:
> Thank,
> I have filtered out the 27th week to 39th weeks,So there are no value
> between 26th week and 40 weeks.
> But there is still a line between 26th week and 40 week.
> I want that there are not any line between 26th week and 4o weeks.
> Can I do that?
>
>
> "B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :A4567ECA-D3C0-4C5B-A870-A252D35338A2@.microsoft.com...
> > As I understand your problem you have dates plotted along the X-axis and
> your
> > chart shows the dates during the summer vactaion with data points of zero
> > being charted during that period (assuming that the minum value for the
> > Y-axis is set at zero). One sulution that you can use is to force the
> reurned
> > the data to have minus values during the summer vacation. That way the
> data
> > points are invisible because they are off the chart. You can force the
> minus
> > values in a number of ways.
> >
> > There may be other solutions but hopefully this will help.
> >
> > "ad" wrote:
> >
> > > I have a line chart which describe the number of absent students every
> week.
> > >
> > > There is summer vocation during a year (week 27th to week 39th)
> > >
> > > I want to the line break up when summer vocation.
> > >
> > > Can we break line where summer vocation?
> > >
> > >
> > >
>
>
There is summer vocation during a year (week 27th to week 39th)
I want to the line break up when summer vocation.
Can we break line where summer vocation?As I understand your problem you have dates plotted along the X-axis and your
chart shows the dates during the summer vactaion with data points of zero
being charted during that period (assuming that the minum value for the
Y-axis is set at zero). One sulution that you can use is to force the reurned
the data to have minus values during the summer vacation. That way the data
points are invisible because they are off the chart. You can force the minus
values in a number of ways.
There may be other solutions but hopefully this will help.
"ad" wrote:
> I have a line chart which describe the number of absent students every week.
> There is summer vocation during a year (week 27th to week 39th)
> I want to the line break up when summer vocation.
> Can we break line where summer vocation?
>
>|||Thank,
I have filtered out the 27th week to 39th weeks,So there are no value
between 26th week and 40 weeks.
But there is still a line between 26th week and 40 week.
I want that there are not any line between 26th week and 4o weeks.
Can I do that?
"B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:A4567ECA-D3C0-4C5B-A870-A252D35338A2@.microsoft.com...
> As I understand your problem you have dates plotted along the X-axis and
your
> chart shows the dates during the summer vactaion with data points of zero
> being charted during that period (assuming that the minum value for the
> Y-axis is set at zero). One sulution that you can use is to force the
reurned
> the data to have minus values during the summer vacation. That way the
data
> points are invisible because they are off the chart. You can force the
minus
> values in a number of ways.
> There may be other solutions but hopefully this will help.
> "ad" wrote:
> > I have a line chart which describe the number of absent students every
week.
> >
> > There is summer vocation during a year (week 27th to week 39th)
> >
> > I want to the line break up when summer vocation.
> >
> > Can we break line where summer vocation?
> >
> >
> >|||I don't think that you can simply not chart the summer vacation period (break
the line) as long as this period is displayed. Thus my suggestion to force
the data points to a negative value show that they do not show up. Note that
a side result of this is that the charted line will appear to drop off the
chart at the beginning of the summer and then rise back up after the summer
break is over. Similar to charting values that are higher than a maximum
value that you set for the Y-axis.
"ad" wrote:
> Thank,
> I have filtered out the 27th week to 39th weeks,So there are no value
> between 26th week and 40 weeks.
> But there is still a line between 26th week and 40 week.
> I want that there are not any line between 26th week and 4o weeks.
> Can I do that?
>
>
> "B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :A4567ECA-D3C0-4C5B-A870-A252D35338A2@.microsoft.com...
> > As I understand your problem you have dates plotted along the X-axis and
> your
> > chart shows the dates during the summer vactaion with data points of zero
> > being charted during that period (assuming that the minum value for the
> > Y-axis is set at zero). One sulution that you can use is to force the
> reurned
> > the data to have minus values during the summer vacation. That way the
> data
> > points are invisible because they are off the chart. You can force the
> minus
> > values in a number of ways.
> >
> > There may be other solutions but hopefully this will help.
> >
> > "ad" wrote:
> >
> > > I have a line chart which describe the number of absent students every
> week.
> > >
> > > There is summer vocation during a year (week 27th to week 39th)
> > >
> > > I want to the line break up when summer vocation.
> > >
> > > Can we break line where summer vocation?
> > >
> > >
> > >
>
>
Thursday, March 22, 2012
CAN USER EDIT DATA in the report?
Hi,
I have a report which shows all the orders in the systam . I want to be able
to view the order details for an order when an order number is clicked and be
able to edit some information so thagt it goes back to the databse. Is it
possible to edit data with REpotrting svcs?
If not, then is there a way, i can show all the orders in the databse, and
have a link in the report which takes the user to a web page where the user
can edit the order and then after editing the order, he again can view the
orginal (all orders ) in the system from the report and then click a link
which takes the user to the order details of that order so that the order can
be edited?
--
pmudNo, it is not possible to edit data in report in place.
Yes, it is possible to make a URL link to the edit page for every order.
You just need to setup an action for the textbox with order id that will be
an http link with order id parameter. Action is visible in properties for
the textbox.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:7CC6E584-B705-45A9-B83A-9821109B592A@.microsoft.com...
> Hi,
> I have a report which shows all the orders in the systam . I want to be
able
> to view the order details for an order when an order number is clicked and
be
> able to edit some information so thagt it goes back to the databse. Is it
> possible to edit data with REpotrting svcs?
> If not, then is there a way, i can show all the orders in the databse,
and
> have a link in the report which takes the user to a web page where the
user
> can edit the order and then after editing the order, he again can view the
> orginal (all orders ) in the system from the report and then click a link
> which takes the user to the order details of that order so that the order
can
> be edited?
> --
> pmudsql
I have a report which shows all the orders in the systam . I want to be able
to view the order details for an order when an order number is clicked and be
able to edit some information so thagt it goes back to the databse. Is it
possible to edit data with REpotrting svcs?
If not, then is there a way, i can show all the orders in the databse, and
have a link in the report which takes the user to a web page where the user
can edit the order and then after editing the order, he again can view the
orginal (all orders ) in the system from the report and then click a link
which takes the user to the order details of that order so that the order can
be edited?
--
pmudNo, it is not possible to edit data in report in place.
Yes, it is possible to make a URL link to the edit page for every order.
You just need to setup an action for the textbox with order id that will be
an http link with order id parameter. Action is visible in properties for
the textbox.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:7CC6E584-B705-45A9-B83A-9821109B592A@.microsoft.com...
> Hi,
> I have a report which shows all the orders in the systam . I want to be
able
> to view the order details for an order when an order number is clicked and
be
> able to edit some information so thagt it goes back to the databse. Is it
> possible to edit data with REpotrting svcs?
> If not, then is there a way, i can show all the orders in the databse,
and
> have a link in the report which takes the user to a web page where the
user
> can edit the order and then after editing the order, he again can view the
> orginal (all orders ) in the system from the report and then click a link
> which takes the user to the order details of that order so that the order
can
> be edited?
> --
> pmudsql
Tuesday, March 20, 2012
Can t-log size handle large number of deletions?
Hi All,
Im just wondering if there is a way i could find out if the size of my
transaction log will be able to record the number of deletions i will be
performing? Basically i have to delete 325 million rows from a table and my
t-log is, say 20GB. is there a way i can calculate if the t-log is big
enough or will it need to expand?
thx in advance.Well I certainly would not advocate deleting them all in one batch. If you
delete them in smaller batches (say 10K or 100K at a time) it will not only
be faster but you would have the option to backup or truncate the log as you
go along. How many rows in the table do you want to keep. It may be
easier to BCP out the ones to keep, truncate the table and bcp them back in.
--
Andrew J. Kelly
SQL Server MVP
"M Sandico" <msandico@.muchomail.com> wrote in message
news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Im just wondering if there is a way i could find out if the size of my
> transaction log will be able to record the number of deletions i will be
> performing? Basically i have to delete 325 million rows from a table and
my
> t-log is, say 20GB. is there a way i can calculate if the t-log is big
> enough or will it need to expand?
> thx in advance.
>|||You are correct in that I dont delete them all in one batch. I delete in
batches of 1000 with a BEGIN TRAN and COMMIT TRAN as well. What I am
planning to do is just set the model to SIMPLE during the deletion and have
the COMMIT TRAN force the log to be flushed during the automatic checkpoint
(when log is 70% full).
Just thought there would be a way to guesstimate how big a t-log youd need
for certain operations..
thx.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e9rVaHJpDHA.360@.TK2MSFTNGP12.phx.gbl...
> Well I certainly would not advocate deleting them all in one batch. If
you
> delete them in smaller batches (say 10K or 100K at a time) it will not
only
> be faster but you would have the option to backup or truncate the log as
you
> go along. How many rows in the table do you want to keep. It may be
> easier to BCP out the ones to keep, truncate the table and bcp them back
in.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "M Sandico" <msandico@.muchomail.com> wrote in message
> news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > Im just wondering if there is a way i could find out if the size of my
> > transaction log will be able to record the number of deletions i will be
> > performing? Basically i have to delete 325 million rows from a table and
> my
> > t-log is, say 20GB. is there a way i can calculate if the t-log is big
> > enough or will it need to expand?
> >
> > thx in advance.
> >
> >
>|||I don't know of a formula off hand. You would have to account for at least
the amount of data that you are deleting and then some percentage for
overhead etc. What that percentage is I don't really know.
--
Andrew J. Kelly
SQL Server MVP
"M Sandico" <msandico@.muchomail.com> wrote in message
news:ugg192JpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> You are correct in that I dont delete them all in one batch. I delete in
> batches of 1000 with a BEGIN TRAN and COMMIT TRAN as well. What I am
> planning to do is just set the model to SIMPLE during the deletion and
have
> the COMMIT TRAN force the log to be flushed during the automatic
checkpoint
> (when log is 70% full).
> Just thought there would be a way to guesstimate how big a t-log youd need
> for certain operations..
> thx.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e9rVaHJpDHA.360@.TK2MSFTNGP12.phx.gbl...
> > Well I certainly would not advocate deleting them all in one batch. If
> you
> > delete them in smaller batches (say 10K or 100K at a time) it will not
> only
> > be faster but you would have the option to backup or truncate the log as
> you
> > go along. How many rows in the table do you want to keep. It may be
> > easier to BCP out the ones to keep, truncate the table and bcp them back
> in.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "M Sandico" <msandico@.muchomail.com> wrote in message
> > news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > >
> > > Im just wondering if there is a way i could find out if the size of my
> > > transaction log will be able to record the number of deletions i will
be
> > > performing? Basically i have to delete 325 million rows from a table
and
> > my
> > > t-log is, say 20GB. is there a way i can calculate if the t-log is big
> > > enough or will it need to expand?
> > >
> > > thx in advance.
> > >
> > >
> >
> >
>
Im just wondering if there is a way i could find out if the size of my
transaction log will be able to record the number of deletions i will be
performing? Basically i have to delete 325 million rows from a table and my
t-log is, say 20GB. is there a way i can calculate if the t-log is big
enough or will it need to expand?
thx in advance.Well I certainly would not advocate deleting them all in one batch. If you
delete them in smaller batches (say 10K or 100K at a time) it will not only
be faster but you would have the option to backup or truncate the log as you
go along. How many rows in the table do you want to keep. It may be
easier to BCP out the ones to keep, truncate the table and bcp them back in.
--
Andrew J. Kelly
SQL Server MVP
"M Sandico" <msandico@.muchomail.com> wrote in message
news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Im just wondering if there is a way i could find out if the size of my
> transaction log will be able to record the number of deletions i will be
> performing? Basically i have to delete 325 million rows from a table and
my
> t-log is, say 20GB. is there a way i can calculate if the t-log is big
> enough or will it need to expand?
> thx in advance.
>|||You are correct in that I dont delete them all in one batch. I delete in
batches of 1000 with a BEGIN TRAN and COMMIT TRAN as well. What I am
planning to do is just set the model to SIMPLE during the deletion and have
the COMMIT TRAN force the log to be flushed during the automatic checkpoint
(when log is 70% full).
Just thought there would be a way to guesstimate how big a t-log youd need
for certain operations..
thx.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e9rVaHJpDHA.360@.TK2MSFTNGP12.phx.gbl...
> Well I certainly would not advocate deleting them all in one batch. If
you
> delete them in smaller batches (say 10K or 100K at a time) it will not
only
> be faster but you would have the option to backup or truncate the log as
you
> go along. How many rows in the table do you want to keep. It may be
> easier to BCP out the ones to keep, truncate the table and bcp them back
in.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "M Sandico" <msandico@.muchomail.com> wrote in message
> news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > Im just wondering if there is a way i could find out if the size of my
> > transaction log will be able to record the number of deletions i will be
> > performing? Basically i have to delete 325 million rows from a table and
> my
> > t-log is, say 20GB. is there a way i can calculate if the t-log is big
> > enough or will it need to expand?
> >
> > thx in advance.
> >
> >
>|||I don't know of a formula off hand. You would have to account for at least
the amount of data that you are deleting and then some percentage for
overhead etc. What that percentage is I don't really know.
--
Andrew J. Kelly
SQL Server MVP
"M Sandico" <msandico@.muchomail.com> wrote in message
news:ugg192JpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> You are correct in that I dont delete them all in one batch. I delete in
> batches of 1000 with a BEGIN TRAN and COMMIT TRAN as well. What I am
> planning to do is just set the model to SIMPLE during the deletion and
have
> the COMMIT TRAN force the log to be flushed during the automatic
checkpoint
> (when log is 70% full).
> Just thought there would be a way to guesstimate how big a t-log youd need
> for certain operations..
> thx.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e9rVaHJpDHA.360@.TK2MSFTNGP12.phx.gbl...
> > Well I certainly would not advocate deleting them all in one batch. If
> you
> > delete them in smaller batches (say 10K or 100K at a time) it will not
> only
> > be faster but you would have the option to backup or truncate the log as
> you
> > go along. How many rows in the table do you want to keep. It may be
> > easier to BCP out the ones to keep, truncate the table and bcp them back
> in.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "M Sandico" <msandico@.muchomail.com> wrote in message
> > news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > >
> > > Im just wondering if there is a way i could find out if the size of my
> > > transaction log will be able to record the number of deletions i will
be
> > > performing? Basically i have to delete 325 million rows from a table
and
> > my
> > > t-log is, say 20GB. is there a way i can calculate if the t-log is big
> > > enough or will it need to expand?
> > >
> > > thx in advance.
> > >
> > >
> >
> >
>
Can this stored procedure be optimised?
Hi there,
I hoping someone can help me reduce the number of line of code I'm
using, as these IF's are nested inside a bigger one.
The main problem I have is I need to add another variable to the IF and
don't want to copy and paste and make this statement even larger.
I have tried playing about with EXEC but with no joy. As far as I'm
aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
CSPerson > 0
I'm using SQL Server 2000 SP4.
Hopefully I'm missing the obvious, although any help or suggestions are
welcome.
-- snippet --
IF @.CSOrder > 0 AND @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE IF @.CSOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
ORDER BY DateToArrive, Status
ELSE IF @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
ORDER BY DateToArrive, StatusWhat you can do is something like this.
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = CSAE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
END
AND SalesPerson = CASE @.SalesOrder WHEN 0 THEN SalesPerson ELSE
@.SalesOrder END
AND ... -- add more
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status|||Mia,
Try something like this
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder AND SalesPerson = @.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
@.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
EXEC (@.sSQL)
Hope this assists,
Tony
"mia_cid@.hotmail.com" wrote:
> Hi there,
> I hoping someone can help me reduce the number of line of code I'm
> using, as these IF's are nested inside a bigger one.
> The main problem I have is I need to add another variable to the IF and
> don't want to copy and paste and make this statement even larger.
> I have tried playing about with EXEC but with no joy. As far as I'm
> aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
> CSPerson > 0
> I'm using SQL Server 2000 SP4.
> Hopefully I'm missing the obvious, although any help or suggestions are
> welcome.
> -- snippet --
> IF @.CSOrder > 0 AND @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.CSOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> ORDER BY DateToArrive, Status
>|||Ooops,
take the following line out of the code:::
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
it was an oversight on my part, sorry,
Tony
"Tony Scott" wrote:
> Mia,
> Try something like this
>
> DECLARE @.strWHERE varchar(200)
> SET @.strWHERE = ''
> IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder AND SalesPerson = @.SalesOrder'
> IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder'
> IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
> @.SalesOrder'
> DECLARE @.sSQL varchar(2000)
> SET @.sSQL = ''
> SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
> SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
> SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
> SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
> SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
> SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
> SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
> SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
> SET @.sSQL = @.sSQL + @.strWHERE
> SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
> SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
> EXEC (@.sSQL)
>
> Hope this assists,
> Tony
> "mia_cid@.hotmail.com" wrote:
>|||Thanks for the help guys,
Patrik I liked your idea, only I need:
AND CSPerson = CASE @.CSOrder WHEN > 0 THEN CSPerson ELSE @.CSOrder
Not
AND CSPerson = CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
Or
AND CSPerson >= CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
As if @.CSOrder = 0, then I want to select everything, so CSPerson needs
to be greater than 0.
Tony,
I had a couple of errors with your original code, so now have:
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder AND SalesPerson =
@.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND SalesPerson = @.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] =
AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] =
MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' ORDER BY DateToArrive, Status'
EXEC (@.sSQL)
Only I've getting a syntax error near the keyword 'ELSE'. If I comment
this code out and put a standard SELECT in it works okay.
Any ideas?|||You can do that.
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN CSPerson ELSE @.CSOrder END
Although, it would probably be more efficient with dynamic sql in this
case.
I suggest that you use sp_executesql instaead of exec.
Read sommarskogs really good article about using dynamic sql.
http://www.sommarskog.se/dynamic_sql.html|||Thanks for that Partrik,
I just needed it the other way round:
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN @.CSOrder ELSE CSPerson END
It works a treat, for the time being I'm going to stick with it as
it'll be running over a LAN, have bookmarked the site you recommended
and will have a read when I get the chance.
Thanks again to Tony for his help as well.
I hoping someone can help me reduce the number of line of code I'm
using, as these IF's are nested inside a bigger one.
The main problem I have is I need to add another variable to the IF and
don't want to copy and paste and make this statement even larger.
I have tried playing about with EXEC but with no joy. As far as I'm
aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
CSPerson > 0
I'm using SQL Server 2000 SP4.
Hopefully I'm missing the obvious, although any help or suggestions are
welcome.
-- snippet --
IF @.CSOrder > 0 AND @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE IF @.CSOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
ORDER BY DateToArrive, Status
ELSE IF @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
ORDER BY DateToArrive, StatusWhat you can do is something like this.
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = CSAE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
END
AND SalesPerson = CASE @.SalesOrder WHEN 0 THEN SalesPerson ELSE
@.SalesOrder END
AND ... -- add more
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status|||Mia,
Try something like this
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder AND SalesPerson = @.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
@.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
EXEC (@.sSQL)
Hope this assists,
Tony
"mia_cid@.hotmail.com" wrote:
> Hi there,
> I hoping someone can help me reduce the number of line of code I'm
> using, as these IF's are nested inside a bigger one.
> The main problem I have is I need to add another variable to the IF and
> don't want to copy and paste and make this statement even larger.
> I have tried playing about with EXEC but with no joy. As far as I'm
> aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
> CSPerson > 0
> I'm using SQL Server 2000 SP4.
> Hopefully I'm missing the obvious, although any help or suggestions are
> welcome.
> -- snippet --
> IF @.CSOrder > 0 AND @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.CSOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> ORDER BY DateToArrive, Status
>|||Ooops,
take the following line out of the code:::
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
it was an oversight on my part, sorry,
Tony
"Tony Scott" wrote:
> Mia,
> Try something like this
>
> DECLARE @.strWHERE varchar(200)
> SET @.strWHERE = ''
> IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder AND SalesPerson = @.SalesOrder'
> IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder'
> IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
> @.SalesOrder'
> DECLARE @.sSQL varchar(2000)
> SET @.sSQL = ''
> SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
> SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
> SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
> SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
> SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
> SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
> SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
> SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
> SET @.sSQL = @.sSQL + @.strWHERE
> SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
> SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
> EXEC (@.sSQL)
>
> Hope this assists,
> Tony
> "mia_cid@.hotmail.com" wrote:
>|||Thanks for the help guys,
Patrik I liked your idea, only I need:
AND CSPerson = CASE @.CSOrder WHEN > 0 THEN CSPerson ELSE @.CSOrder
Not
AND CSPerson = CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
Or
AND CSPerson >= CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
As if @.CSOrder = 0, then I want to select everything, so CSPerson needs
to be greater than 0.
Tony,
I had a couple of errors with your original code, so now have:
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder AND SalesPerson =
@.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND SalesPerson = @.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] =
AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] =
MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' ORDER BY DateToArrive, Status'
EXEC (@.sSQL)
Only I've getting a syntax error near the keyword 'ELSE'. If I comment
this code out and put a standard SELECT in it works okay.
Any ideas?|||You can do that.
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN CSPerson ELSE @.CSOrder END
Although, it would probably be more efficient with dynamic sql in this
case.
I suggest that you use sp_executesql instaead of exec.
Read sommarskogs really good article about using dynamic sql.
http://www.sommarskog.se/dynamic_sql.html|||Thanks for that Partrik,
I just needed it the other way round:
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN @.CSOrder ELSE CSPerson END
It works a treat, for the time being I'm going to stick with it as
it'll be running over a LAN, have bookmarked the site you recommended
and will have a read when I get the chance.
Thanks again to Tony for his help as well.
Monday, March 19, 2012
Can the SQL Server 2005 be installed on Windows 2003 SP1?
I recently attempted to install Windows 2003 SP1 on a machine on which someone had previously installed SQL Server 2005. The build number for the SQL Server 2005 installation is 9.0.1116. (BTW, which CTP is this build number?)
The installation of the service pack seemed to go okay, but I soon got calls from users reporting that they could not log in to the instance. I investigated, and discovered that the SQL Server service had not started. We were unable to start the service, and had to remove SP1.
This was the message I found in Event Viewer:
Event Type: Error
Event Source: SQLDUMPER
Event Category: None
Event ID: 5000
Date: 8/15/2005
Time: 10:30:30
User: N/A
Computer: YKOADS3
Description:
EventType sql90exception, P1 sqlservr.exe, P2 2000.90.1116.0, P3 4258cf76, P4 msvcrt.dll, P5 7.0.3790.1830, P6 4243785e, P7 0, P8 00038944, P9 a3a034ae, P10 NIL.
My questions are:
1) What happened?
2) Can I install the current CTP on Windows 2003 SP1?
That version corresponds to the April CTP of SQL Server.
You could log that as a bug in Product Feedback:
http://lab.msdn.microsoft.com/productfeedback/
Regards,
Vikram|||Well, if my issue occurred with the April CTP I see no need to log the issue now. I'm currently installing the June CTP, whose documentation says it works with 2003 SP1. I'll post the build number when I'm finished, as well as let everyone know the results.
Thanks for the reply,
Ken|||The Build number for June CTP needs is 9.0.1187. I have it running successfully on machine with Win2003 server with SP1. I installed SQL Server 2005 after installing SP1.
Regards,
Vikram|||
I installed the June CTP on Windows 2003 SP1 and I am up and running. Thanks.
Ken
Can the report table have varying number of columns ??
The requierment is tyo have a dynamic report, that has a table with number of
columns decided on the input from recordset.
The SQL procedure takes in parameters and returns recordset, that can have
varying number of columns. All we want to do is to link this recordset to a
table on the report.
The report table should show the columns as they are in the Recordset. ( and
the column name in the recordset shall appear as the column header in the
Reporting table.)
-- Alok Kumar GuptaThere's really no good way to do this in the current version, no.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Alok" <Alok@.discussions.microsoft.com> wrote in message
news:47BD7BFE-DCAF-4DC1-A360-98BF8F9FB586@.microsoft.com...
> The requierment is tyo have a dynamic report, that has a table with number
of
> columns decided on the input from recordset.
> The SQL procedure takes in parameters and returns recordset, that can have
> varying number of columns. All we want to do is to link this recordset to
a
> table on the report.
> The report table should show the columns as they are in the Recordset. (
and
> the column name in the recordset shall appear as the column header in the
> Reporting table.)
> -- Alok Kumar Gupta
>
columns decided on the input from recordset.
The SQL procedure takes in parameters and returns recordset, that can have
varying number of columns. All we want to do is to link this recordset to a
table on the report.
The report table should show the columns as they are in the Recordset. ( and
the column name in the recordset shall appear as the column header in the
Reporting table.)
-- Alok Kumar GuptaThere's really no good way to do this in the current version, no.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Alok" <Alok@.discussions.microsoft.com> wrote in message
news:47BD7BFE-DCAF-4DC1-A360-98BF8F9FB586@.microsoft.com...
> The requierment is tyo have a dynamic report, that has a table with number
of
> columns decided on the input from recordset.
> The SQL procedure takes in parameters and returns recordset, that can have
> varying number of columns. All we want to do is to link this recordset to
a
> table on the report.
> The report table should show the columns as they are in the Recordset. (
and
> the column name in the recordset shall appear as the column header in the
> Reporting table.)
> -- Alok Kumar Gupta
>
Sunday, March 11, 2012
Can stored procedure be called recursively?
Is stored procedure in mssql 2000 support recursion? Is there any limitation on the number of recursion?
what happens when you run this:
|||? Yes, it can. 32 levels. You can also use @.@.NESTLEVEL to determine the current level: CREATE PROCEDURE recurseASBEGIN PRINT @.@.NESTLEVEL EXEC recurseENDGO EXEC recurseGO DROP PROC recurseGO -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <nonno@.discussions.microsoft.com> wrote in message news:452f0e66-9594-4818-9d92-6de799ad0dd5@.discussions.microsoft.com...Is stored procedure in mssql 2000 support recursion? Is there any limitation on the number of recursion?|||
Does @.@.NESTLEVEL has a transactional scope?
What happens with the value of @.@.NESTLEVEL when two users (from my application) will call the same procedure, but both users have a different transaction?
Like:
UserA calls the procudure for the first time: NestLevel = 1
UserA calls the procedure again: NestLevel = 2
What will be the value of NestLevel for user B? 1 or 3?
TIA,
Isabel
|||? 1. It's the nesting level for the current scope; it has nothing to do with some other connection's scope. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Isabel_ve@.discussions..microsoft.com> wrote in message news:dd99b941-0788-4916-a1f8-19fe6117f330@.discussions.microsoft.com... Does @.@.NESTLEVEL has a transactional scope? What happens with the value of @.@.NESTLEVEL when two users (from my application) will call the same procedure, but both users have a different transaction? Like:UserA calls the procudure for the first time: NestLevel = 1UserA calls the procedure again: NestLevel = 2 What will be the value of NestLevel for user B? 1 or 3? TIA, IsabelThursday, February 16, 2012
Can Report (rdlc) Table or Matrix Column Width Be Set at Runtime?
Using an rdlc report in ReportViewer, I need to create a table or matrix where the number of columns and the kinds of data displayed in the columns changes with each report. For example, in one report, the second column may hold price information. In another report, the second column may hold a product description.
Obviously, a column with descriptive text needs to be wider than a column with price info. So, the column widths can't be firmly set in the designer. But, I can't figure out how to change the column width during runtime, in either the Table control or Matrix control. (I prefer the matrix, but can use either.)
Setting the header or details textbox CanGrow property to true doesn't help for two reasons. One is that (in a Table control) the textbox grows in width only to the first linebreak opportunity (a space between words). The other is that we need to export the report to PDF and even if the CanGrow widens the column in the viewer, when exported to PDF the column returns to the design width.
Tried setting the Width property of the column to an expression, but it wouldn't let me.
Spent a week on this. Is it possible? If not, if I switched to a Crystal Report (don't want to, I'm Crystal-Report challenged), is there a way to make it work?
There is one very ugly workaround: the Hidden property of a table column can be set to an expression. I could set up the table with two or three columns of different widths for each column in the final report. Depending on the column header ('Price', 'Description', etc.), certain columns would be hidden based on how wide a column was needed. For example, there could be two design columns (one narrow, one wide) for column two of the final report. If the heading on column 2 is Description, make the narrow column hidden so only the wide column displays with the Description text.
Seems it would be better to set the column widths, if possible.
Thanks.
I never found a pretty solution, so went with an ugly one.
The original ugly workaround I proposed worked great for viewing the report on-screen, but did not work when exporting to PDF. By having three columns for every visible column in the final report, the table was 22 inches wide in design view, forcing the report to be 22 inches wide. The export to PDF didn't like that, creating lots of extra blank pages.
The ugly solution that worked was creating multiple tables to acccount for all varieties of column widths and number of columns The tables are stacked on top of one another. The "Hidden" property in each table is set to an expression that checks a report parameter to determine if that table is hidden or visible [=IIF(Parameters!paramTableId.Value = 33, False, True)]. The parameter value is passed in from the codebehind on the aspx page that holds the ReportViewer.
Currently there at 23 tables in the stack. All of the tables are centered horizontally. When I had just 5 or 6 tables, the non-hidden table would appear on the screen centered in the report. With 23 tables, the non-hidden table is left justified. But, in both scenarios, the table is centered in the PDF version, which is all I care about.
|||I also had this issue - you're dirty hack solution worked well for me - cheers for sharing
rich
can Profiler display port number?
Where - in SQL Server Profiler - can I see the port number that someone has used to connect to a database?
e.g. given the connect string "tcp:MACHINE1\INSTANCE7,3045" - where in Profiler does it tell me that the connection is using port 3045? I looked at "audit login" and "existing connection" but I don't see a port number...
Any other SQL/Windows tools that I can use to monitor connections to databases on specific ports?
thanks
Hi,
open ports can be monitored using the commandline tool netstat -a.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)