I would like to alternate the background color for rows in a table. Is there
a simple way to do this?
Basically I got it working by returning a rank column that identified the
row number. I then used the following code in the TableRow BackgroundColor
property:
IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
Seems like there must be an easier way then having to return a rank column
in the dataset associated with the table.Greg,
That's pretty much the only way I know to do it. Although, I used a
different formula then you did. Here's what I stuck in my Background Color
property box:
=iif(RowNumber(Nothing) mod 2=1, "OldLace", Nothing)
OldLace was the color I chose because I didn't want something hard on the
eyes. The Nothing leaves it as Transparent, which isn't really a color, but
doesn't force it to be another color. You could just as easily enter another
one of the available colors in the False part.
Does this help any?
Catadmin
"Greg Larsen" wrote:
> I would like to alternate the background color for rows in a table. Is there
> a simple way to do this?
> Basically I got it working by returning a rank column that identified the
> row number. I then used the following code in the TableRow BackgroundColor
> property:
> IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
> Seems like there must be an easier way then having to return a rank column
> in the dataset associated with the table.|||Yes this does help. By using RowNumber function you don't need to generate a
row number(or rank as I called it) as part of the dataset being returned.
Thank you that is exactly want I needed.
"Catadmin" wrote:
> Greg,
> That's pretty much the only way I know to do it. Although, I used a
> different formula then you did. Here's what I stuck in my Background Color
> property box:
> =iif(RowNumber(Nothing) mod 2=1, "OldLace", Nothing)
> OldLace was the color I chose because I didn't want something hard on the
> eyes. The Nothing leaves it as Transparent, which isn't really a color, but
> doesn't force it to be another color. You could just as easily enter another
> one of the available colors in the False part.
> Does this help any?
> Catadmin
> "Greg Larsen" wrote:
> > I would like to alternate the background color for rows in a table. Is there
> > a simple way to do this?
> >
> > Basically I got it working by returning a rank column that identified the
> > row number. I then used the following code in the TableRow BackgroundColor
> > property:
> >
> > IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
> >
> > Seems like there must be an easier way then having to return a rank column
> > in the dataset associated with the table.
Showing posts with label alternate. Show all posts
Showing posts with label alternate. Show all posts
Thursday, March 29, 2012
Alternating background color
Got a dataset that is used to populate a table. Want to alternate the
background color on every other row in the displayed detail group. Easy
enough right? Here's the catch: the output is grouped at display time. A
query output might be:
KEY Value1 Value2
A 0 1
A 1 0
B 5 0
C 3 0
C 0 7
etc...
The DISPLAY output is grouped on the KEY, and the two values are summed to
give me a display such as:
KEY Value1 Value2
A 1 1
B 5 0
C 3 7
Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
"Grey")", it counts EVERY row returned from the original query, not the
grouped output, so I don't get a uniform white-grey-white pattern. Anyone
know a workaround for this?
TIA,
BrianOk, I found my workaround. Someone is bound to have this issue sometime in
the future, so I'll put the workaround here.
I created a little routine in the custom Code area of the report that simply
toggles and returns an integer value:
Dim Public bgColor As Integer = 0
Public Function alternateColor As Integer
If bgColor = 0
bgColor = 1
return bgColor
else
bgColor = 0
return bgColor
end if
End Function
When i put my method call in the background color on the entire table ROW,
the result was alternating COLUMN colors. This is because the method was
called for every cell (column) in the row. In order to get alternating ROW
color, I only called the alternateColor routine in the FIRST column in the
table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
column in the row would simply check the "Code.bgColor" value for its
current value, and base its color on that (iif(Code.bgColor = 0, "white",
"grey")).
Maybe this will come in handy for someone else someday....
Brian
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> Got a dataset that is used to populate a table. Want to alternate the
> background color on every other row in the displayed detail group. Easy
> enough right? Here's the catch: the output is grouped at display time. A
> query output might be:
> KEY Value1 Value2
> A 0 1
> A 1 0
> B 5 0
> C 3 0
> C 0 7
> etc...
> The DISPLAY output is grouped on the KEY, and the two values are summed to
> give me a display such as:
> KEY Value1 Value2
> A 1 1
> B 5 0
> C 3 7
> Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> "Grey")", it counts EVERY row returned from the original query, not the
> grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> know a workaround for this?
> TIA,
> Brian
>|||Great solution, I've been playing around with RowNumber for ages - this is
much better!!!
Thanks Brian!!!
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>|||This was just what i was looking for. My returned dataset sometimes Groups
so that rownumbers aren't in a consecutive order, giving some strange
alternate highlighting results using the conventional method. This should
work nicely, cheers
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>|||This didn't work for me since I am setting a row to hidden based on a value
in that row. SQL RS thinks that row is still there and displays two back to
back colors instead of alternating the colors.
Any ideas?
Thanks,
Don
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>
background color on every other row in the displayed detail group. Easy
enough right? Here's the catch: the output is grouped at display time. A
query output might be:
KEY Value1 Value2
A 0 1
A 1 0
B 5 0
C 3 0
C 0 7
etc...
The DISPLAY output is grouped on the KEY, and the two values are summed to
give me a display such as:
KEY Value1 Value2
A 1 1
B 5 0
C 3 7
Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
"Grey")", it counts EVERY row returned from the original query, not the
grouped output, so I don't get a uniform white-grey-white pattern. Anyone
know a workaround for this?
TIA,
BrianOk, I found my workaround. Someone is bound to have this issue sometime in
the future, so I'll put the workaround here.
I created a little routine in the custom Code area of the report that simply
toggles and returns an integer value:
Dim Public bgColor As Integer = 0
Public Function alternateColor As Integer
If bgColor = 0
bgColor = 1
return bgColor
else
bgColor = 0
return bgColor
end if
End Function
When i put my method call in the background color on the entire table ROW,
the result was alternating COLUMN colors. This is because the method was
called for every cell (column) in the row. In order to get alternating ROW
color, I only called the alternateColor routine in the FIRST column in the
table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
column in the row would simply check the "Code.bgColor" value for its
current value, and base its color on that (iif(Code.bgColor = 0, "white",
"grey")).
Maybe this will come in handy for someone else someday....
Brian
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> Got a dataset that is used to populate a table. Want to alternate the
> background color on every other row in the displayed detail group. Easy
> enough right? Here's the catch: the output is grouped at display time. A
> query output might be:
> KEY Value1 Value2
> A 0 1
> A 1 0
> B 5 0
> C 3 0
> C 0 7
> etc...
> The DISPLAY output is grouped on the KEY, and the two values are summed to
> give me a display such as:
> KEY Value1 Value2
> A 1 1
> B 5 0
> C 3 7
> Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> "Grey")", it counts EVERY row returned from the original query, not the
> grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> know a workaround for this?
> TIA,
> Brian
>|||Great solution, I've been playing around with RowNumber for ages - this is
much better!!!
Thanks Brian!!!
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>|||This was just what i was looking for. My returned dataset sometimes Groups
so that rownumbers aren't in a consecutive order, giving some strange
alternate highlighting results using the conventional method. This should
work nicely, cheers
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>|||This didn't work for me since I am setting a row to hidden based on a value
in that row. SQL RS thinks that row is still there and displays two back to
back colors instead of alternating the colors.
Any ideas?
Thanks,
Don
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>
Alternate ways of deleting records - without logging
Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.All DELETE statements are logged and there is no way around that. The
"temp" table that you mention is actually a permanent table. Have you
considered going with an actual temp table - one whose name begins with#?
That would allow you to truncate the entire table (unlogged) without
affecting other users.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.|||Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||That's a toughie. The only other suggestion is to delete in chunks, say
10,000 rows at a time.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||I'm sure this is not a good solution, but you could change your database
recovery to simple.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Andrei" wrote:
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary tabl
e.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write inf
o
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>
>|||Alternatively you could put the table in a database by itself and then set
that database to simple recover mode.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Greg Larsen" wrote:
> I'm sure this is not a good solution, but you could change your database
> recovery to simple.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Andrei" wrote:
>|||Deletes are still logged in simple mode.
This solution depends on what the OP wants. If he wants the operation to be
as fast as a truncate, simple mode won't help. If he wants to make sure the
log doesn't fill up during the delete, simple won't help.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
> Alternatively you could put the table in a database by itself and then set
> that database to simple recover mode.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Greg Larsen" wrote:
>|||Thank you, Tom, I'm going to try that.
Still leaves me with a big log file :(
I guess I'll have to clear it more often...
Andrei.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YLmjBXmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> That's a toughie. The only other suggestion is to delete in chunks, say
> 10,000 rows at a time.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only
> temporary from the point of view of the reporting action... we're so used
> to
> call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Ouch! That's a very unwieldly design issue.
Based upon your reply, it may be that your only viable option is DELETE
(which is logged).
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only temporary from the point of view of the reporting action... we're so
> used to call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Thank you, Greg and Kalen.
I need to get both of them - don't we all ? :) - less delete time and
smaller log size but in this specific order...
Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORvK%23LXmGHA.464@.TK2MSFTNGP05.phx.gbl...
> Deletes are still logged in simple mode.
> This solution depends on what the OP wants. If he wants the operation to
> be as fast as a truncate, simple mode won't help. If he wants to make sure
> the log doesn't fill up during the delete, simple won't help.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
> news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
>
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.All DELETE statements are logged and there is no way around that. The
"temp" table that you mention is actually a permanent table. Have you
considered going with an actual temp table - one whose name begins with#?
That would allow you to truncate the entire table (unlogged) without
affecting other users.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.|||Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||That's a toughie. The only other suggestion is to delete in chunks, say
10,000 rows at a time.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||I'm sure this is not a good solution, but you could change your database
recovery to simple.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Andrei" wrote:
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary tabl
e.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write inf
o
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>
>|||Alternatively you could put the table in a database by itself and then set
that database to simple recover mode.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Greg Larsen" wrote:
> I'm sure this is not a good solution, but you could change your database
> recovery to simple.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Andrei" wrote:
>|||Deletes are still logged in simple mode.
This solution depends on what the OP wants. If he wants the operation to be
as fast as a truncate, simple mode won't help. If he wants to make sure the
log doesn't fill up during the delete, simple won't help.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
> Alternatively you could put the table in a database by itself and then set
> that database to simple recover mode.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Greg Larsen" wrote:
>|||Thank you, Tom, I'm going to try that.
Still leaves me with a big log file :(
I guess I'll have to clear it more often...
Andrei.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YLmjBXmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> That's a toughie. The only other suggestion is to delete in chunks, say
> 10,000 rows at a time.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only
> temporary from the point of view of the reporting action... we're so used
> to
> call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Ouch! That's a very unwieldly design issue.
Based upon your reply, it may be that your only viable option is DELETE
(which is logged).
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only temporary from the point of view of the reporting action... we're so
> used to call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Thank you, Greg and Kalen.
I need to get both of them - don't we all ? :) - less delete time and
smaller log size but in this specific order...
Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORvK%23LXmGHA.464@.TK2MSFTNGP05.phx.gbl...
> Deletes are still logged in simple mode.
> This solution depends on what the OP wants. If he wants the operation to
> be as fast as a truncate, simple mode won't help. If he wants to make sure
> the log doesn't fill up during the delete, simple won't help.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
> news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
>
Alternate to sql web assistant
I am currently with sql 2k5 and using web assistant to generate some HTML files and email the same automatically to a set of users.
these html files that are generated are processed via a JOB in sql server.
I am in process of replacing of the web assistant procedures.
Is this possible to make this with XQuery of SQL Server 2K5 and convert it into HTML files with Stored Procedure.
Is there any other possible way to do it?
Hi Brinda,
I have similar stored procedures that use web assistnant to generate xls files and then email them. I have heard that web assistant can be replaced with reporting services in SQL2K5, but I am unaware of the details of how to do this-if anyone has details on this I am interested to learn about it.
Thanks,
-Dave
alternate to formula
I have a table where one of the field is having formula, where UDF is used.
Is there any way by which formula can be removed and any other method is
used like trigger,
Because we can not have index on a formula based fieldCOMPUTED column? Can you show us the source?
"Vikram" <aa@.aa> wrote in message
news:eq0vE6aeGHA.3572@.TK2MSFTNGP03.phx.gbl...
>I have a table where one of the field is having formula, where UDF is used.
> Is there any way by which formula can be removed and any other method is
> used like trigger,
> Because we can not have index on a formula based field
>|||I think you cannot have an index on computed field either.
Though in SQL Server 2005 you can set the computed column as persisited and
create an index on it.|||Why not?
create table test (c1 int not null,c2 as c1*10)
create index ind_comp on test(c2)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:45CD1DD1-6AF4-4872-AD5D-28450AA30E23@.microsoft.com...
>I think you cannot have an index on computed field either.
> Though in SQL Server 2005 you can set the computed column as persisited
> and
> create an index on it.|||Oops.. sorry... you are right.. tea time for me :)
I
it with foreign key constraints|||In fact, computed columns may be indexed if certain conditions are met:
http://msdn2.microsoft.com/en-us/library/ms189292.aspx
In SQL 2000 a computed column is not persisted until it's used in an index,
while SQL 2005 has this new option that you've mentioned.
ML
http://milambda.blogspot.com/|||> I it with foreign key constraints
You can create an index on the column that has FK
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BB66B25E-F4A8-4163-B5C5-8C20A8734C48@.microsoft.com...
> Oops.. sorry... you are right.. tea time for me :)
> I it with foreign key constraints|||No.. i meant foreign key on a computed column.|||Yes ,it is true
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:0A422A1B-2100-4D50-A610-AAD305AFDD3B@.microsoft.com...
> No.. i meant foreign key on a computed column.
>sql
Is there any way by which formula can be removed and any other method is
used like trigger,
Because we can not have index on a formula based fieldCOMPUTED column? Can you show us the source?
"Vikram" <aa@.aa> wrote in message
news:eq0vE6aeGHA.3572@.TK2MSFTNGP03.phx.gbl...
>I have a table where one of the field is having formula, where UDF is used.
> Is there any way by which formula can be removed and any other method is
> used like trigger,
> Because we can not have index on a formula based field
>|||I think you cannot have an index on computed field either.
Though in SQL Server 2005 you can set the computed column as persisited and
create an index on it.|||Why not?
create table test (c1 int not null,c2 as c1*10)
create index ind_comp on test(c2)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:45CD1DD1-6AF4-4872-AD5D-28450AA30E23@.microsoft.com...
>I think you cannot have an index on computed field either.
> Though in SQL Server 2005 you can set the computed column as persisited
> and
> create an index on it.|||Oops.. sorry... you are right.. tea time for me :)
I
it with foreign key constraints|||In fact, computed columns may be indexed if certain conditions are met:http://msdn2.microsoft.com/en-us/library/ms189292.aspx
In SQL 2000 a computed column is not persisted until it's used in an index,
while SQL 2005 has this new option that you've mentioned.
ML
http://milambda.blogspot.com/|||> I
it with foreign key constraintsYou can create an index on the column that has FK
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BB66B25E-F4A8-4163-B5C5-8C20A8734C48@.microsoft.com...
> Oops.. sorry... you are right.. tea time for me :)
> I
it with foreign key constraints|||No.. i meant foreign key on a computed column.|||Yes ,it is true"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:0A422A1B-2100-4D50-A610-AAD305AFDD3B@.microsoft.com...
> No.. i meant foreign key on a computed column.
>sql
alternate to DBCC command
Hi ,
Is Yukon not supporting all dbcc commands or if they are used they are not
reliable .
If its not supporting where do I find the alternate DMVs for the same .
Thanks
ARR
All documented DBCC commands in Yukon are backwards-compatible and are
supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats). As
always, undocumented DBCC commands are not supported and we reserve the
right to alter or completely remove them at any time. Can you tell us what
piece of information you're looking for specifically?
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> Is Yukon not supporting all dbcc commands or if they are used they are not
> reliable .
> If its not supporting where do I find the alternate DMVs for the same .
>
> Thanks
> ARR
>
|||Thanks for the reply .
am looking for all documented DBCC command alternative .
Also looking for some difference between sql2000 sysobjects & Yukon . Do you
have any whitepaper for the same . Currently yukon has 46 DMVs but am not
getting info about all those . Please help for the same .
Thanks
ARR
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> All documented DBCC commands in Yukon are backwards-compatible and are
> supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
> and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
As
> always, undocumented DBCC commands are not supported and we reserve the
> right to alter or completely remove them at any time. Can you tell us
what
> piece of information you're looking for specifically?
> Thanks,
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
not
>
|||All the documented DBCC commands are reliable and none have alternatives in
Yukon except for the two that Ryan mentions below. What do you mean when you
say they're 'not reliable' and why do you want alternatives for the
operations they perform?
The DMVs will be fully documented in the BOL for Beta 3.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:OhBU2gqAFHA.608@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply .
> am looking for all documented DBCC command alternative .
> Also looking for some difference between sql2000 sysobjects & Yukon . Do
you[vbcol=seagreen]
> have any whitepaper for the same . Currently yukon has 46 DMVs but am not
> getting info about all those . Please help for the same .
> Thanks
> ARR
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
deprecated[vbcol=seagreen]
> As
> what
> rights.
> not
..
>
Is Yukon not supporting all dbcc commands or if they are used they are not
reliable .
If its not supporting where do I find the alternate DMVs for the same .
Thanks
ARR
All documented DBCC commands in Yukon are backwards-compatible and are
supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats). As
always, undocumented DBCC commands are not supported and we reserve the
right to alter or completely remove them at any time. Can you tell us what
piece of information you're looking for specifically?
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> Is Yukon not supporting all dbcc commands or if they are used they are not
> reliable .
> If its not supporting where do I find the alternate DMVs for the same .
>
> Thanks
> ARR
>
|||Thanks for the reply .
am looking for all documented DBCC command alternative .
Also looking for some difference between sql2000 sysobjects & Yukon . Do you
have any whitepaper for the same . Currently yukon has 46 DMVs but am not
getting info about all those . Please help for the same .
Thanks
ARR
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> All documented DBCC commands in Yukon are backwards-compatible and are
> supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
> and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
As
> always, undocumented DBCC commands are not supported and we reserve the
> right to alter or completely remove them at any time. Can you tell us
what
> piece of information you're looking for specifically?
> Thanks,
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
not
>
|||All the documented DBCC commands are reliable and none have alternatives in
Yukon except for the two that Ryan mentions below. What do you mean when you
say they're 'not reliable' and why do you want alternatives for the
operations they perform?
The DMVs will be fully documented in the BOL for Beta 3.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:OhBU2gqAFHA.608@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply .
> am looking for all documented DBCC command alternative .
> Also looking for some difference between sql2000 sysobjects & Yukon . Do
you[vbcol=seagreen]
> have any whitepaper for the same . Currently yukon has 46 DMVs but am not
> getting info about all those . Please help for the same .
> Thanks
> ARR
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
deprecated[vbcol=seagreen]
> As
> what
> rights.
> not
..
>
alternate to DBCC command
Hi ,
Is Yukon not supporting all dbcc commands or if they are used they are not
reliable .
If its not supporting where do I find the alternate DMVs for the same .
Thanks
ARRAll documented DBCC commands in Yukon are backwards-compatible and are
supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats). As
always, undocumented DBCC commands are not supported and we reserve the
right to alter or completely remove them at any time. Can you tell us what
piece of information you're looking for specifically?
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> Is Yukon not supporting all dbcc commands or if they are used they are not
> reliable .
> If its not supporting where do I find the alternate DMVs for the same .
>
> Thanks
> ARR
>|||Thanks for the reply .
am looking for all documented DBCC command alternative .
Also looking for some difference between sql2000 sysobjects & Yukon . Do you
have any whitepaper for the same . Currently yukon has 46 DMVs but am not
getting info about all those . Please help for the same .
Thanks
ARR
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> All documented DBCC commands in Yukon are backwards-compatible and are
> supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
> and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
As
> always, undocumented DBCC commands are not supported and we reserve the
> right to alter or completely remove them at any time. Can you tell us
what
> piece of information you're looking for specifically?
> Thanks,
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> > Hi ,
> > Is Yukon not supporting all dbcc commands or if they are used they are
not
> > reliable .
> > If its not supporting where do I find the alternate DMVs for the same .
> >
> >
> > Thanks
> > ARR
> >
> >
>|||All the documented DBCC commands are reliable and none have alternatives in
Yukon except for the two that Ryan mentions below. What do you mean when you
say they're 'not reliable' and why do you want alternatives for the
operations they perform?
The DMVs will be fully documented in the BOL for Beta 3.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:OhBU2gqAFHA.608@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply .
> am looking for all documented DBCC command alternative .
> Also looking for some difference between sql2000 sysobjects & Yukon . Do
you
> have any whitepaper for the same . Currently yukon has 46 DMVs but am not
> getting info about all those . Please help for the same .
> Thanks
> ARR
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> > All documented DBCC commands in Yukon are backwards-compatible and are
> > supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been
deprecated
> > and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> > INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
> As
> > always, undocumented DBCC commands are not supported and we reserve the
> > right to alter or completely remove them at any time. Can you tell us
> what
> > piece of information you're looking for specifically?
> >
> > Thanks,
> > Ryan Stonecipher
> > Microsoft SQL Server Storage Engine, DBCC
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Aju" <ajuonline@.yahoo.com> wrote in message
> > news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> > > Hi ,
> > > Is Yukon not supporting all dbcc commands or if they are used they are
> not
> > > reliable .
> > > If its not supporting where do I find the alternate DMVs for the same
.
> > >
> > >
> > > Thanks
> > > ARR
> > >
> > >
> >
> >
>
Is Yukon not supporting all dbcc commands or if they are used they are not
reliable .
If its not supporting where do I find the alternate DMVs for the same .
Thanks
ARRAll documented DBCC commands in Yukon are backwards-compatible and are
supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats). As
always, undocumented DBCC commands are not supported and we reserve the
right to alter or completely remove them at any time. Can you tell us what
piece of information you're looking for specifically?
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> Is Yukon not supporting all dbcc commands or if they are used they are not
> reliable .
> If its not supporting where do I find the alternate DMVs for the same .
>
> Thanks
> ARR
>|||Thanks for the reply .
am looking for all documented DBCC command alternative .
Also looking for some difference between sql2000 sysobjects & Yukon . Do you
have any whitepaper for the same . Currently yukon has 46 DMVs but am not
getting info about all those . Please help for the same .
Thanks
ARR
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> All documented DBCC commands in Yukon are backwards-compatible and are
> supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
> and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
As
> always, undocumented DBCC commands are not supported and we reserve the
> right to alter or completely remove them at any time. Can you tell us
what
> piece of information you're looking for specifically?
> Thanks,
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> > Hi ,
> > Is Yukon not supporting all dbcc commands or if they are used they are
not
> > reliable .
> > If its not supporting where do I find the alternate DMVs for the same .
> >
> >
> > Thanks
> > ARR
> >
> >
>|||All the documented DBCC commands are reliable and none have alternatives in
Yukon except for the two that Ryan mentions below. What do you mean when you
say they're 'not reliable' and why do you want alternatives for the
operations they perform?
The DMVs will be fully documented in the BOL for Beta 3.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:OhBU2gqAFHA.608@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply .
> am looking for all documented DBCC command alternative .
> Also looking for some difference between sql2000 sysobjects & Yukon . Do
you
> have any whitepaper for the same . Currently yukon has 46 DMVs but am not
> getting info about all those . Please help for the same .
> Thanks
> ARR
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> > All documented DBCC commands in Yukon are backwards-compatible and are
> > supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been
deprecated
> > and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> > INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
> As
> > always, undocumented DBCC commands are not supported and we reserve the
> > right to alter or completely remove them at any time. Can you tell us
> what
> > piece of information you're looking for specifically?
> >
> > Thanks,
> > Ryan Stonecipher
> > Microsoft SQL Server Storage Engine, DBCC
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Aju" <ajuonline@.yahoo.com> wrote in message
> > news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> > > Hi ,
> > > Is Yukon not supporting all dbcc commands or if they are used they are
> not
> > > reliable .
> > > If its not supporting where do I find the alternate DMVs for the same
.
> > >
> > >
> > > Thanks
> > > ARR
> > >
> > >
> >
> >
>
alternate to DBCC command
Hi ,
Is Yukon not supporting all dbcc commands or if they are used they are not
reliable .
If its not supporting where do I find the alternate DMVs for the same .
Thanks
ARRAll documented DBCC commands in Yukon are backwards-compatible and are
supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats). As
always, undocumented DBCC commands are not supported and we reserve the
right to alter or completely remove them at any time. Can you tell us what
piece of information you're looking for specifically?
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> Is Yukon not supporting all dbcc commands or if they are used they are not
> reliable .
> If its not supporting where do I find the alternate DMVs for the same .
>
> Thanks
> ARR
>|||Thanks for the reply .
am looking for all documented DBCC command alternative .
Also looking for some difference between sql2000 sysobjects & Yukon . Do you
have any whitepaper for the same . Currently yukon has 46 DMVs but am not
getting info about all those . Please help for the same .
Thanks
ARR
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> All documented DBCC commands in Yukon are backwards-compatible and are
> supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
> and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
As
> always, undocumented DBCC commands are not supported and we reserve the
> right to alter or completely remove them at any time. Can you tell us
what
> piece of information you're looking for specifically?
> Thanks,
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
not[vbcol=seagreen]
>|||All the documented DBCC commands are reliable and none have alternatives in
Yukon except for the two that Ryan mentions below. What do you mean when you
say they're 'not reliable' and why do you want alternatives for the
operations they perform?
The DMVs will be fully documented in the BOL for Beta 3.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:OhBU2gqAFHA.608@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply .
> am looking for all documented DBCC command alternative .
> Also looking for some difference between sql2000 sysobjects & Yukon . Do
you
> have any whitepaper for the same . Currently yukon has 46 DMVs but am not
> getting info about all those . Please help for the same .
> Thanks
> ARR
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
deprecated[vbcol=seagreen]
> As
> what
> rights.
> not
.[vbcol=seagreen]
>
Is Yukon not supporting all dbcc commands or if they are used they are not
reliable .
If its not supporting where do I find the alternate DMVs for the same .
Thanks
ARRAll documented DBCC commands in Yukon are backwards-compatible and are
supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats). As
always, undocumented DBCC commands are not supported and we reserve the
right to alter or completely remove them at any time. Can you tell us what
piece of information you're looking for specifically?
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> Is Yukon not supporting all dbcc commands or if they are used they are not
> reliable .
> If its not supporting where do I find the alternate DMVs for the same .
>
> Thanks
> ARR
>|||Thanks for the reply .
am looking for all documented DBCC command alternative .
Also looking for some difference between sql2000 sysobjects & Yukon . Do you
have any whitepaper for the same . Currently yukon has 46 DMVs but am not
getting info about all those . Please help for the same .
Thanks
ARR
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> All documented DBCC commands in Yukon are backwards-compatible and are
> supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
> and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
As
> always, undocumented DBCC commands are not supported and we reserve the
> right to alter or completely remove them at any time. Can you tell us
what
> piece of information you're looking for specifically?
> Thanks,
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
not[vbcol=seagreen]
>|||All the documented DBCC commands are reliable and none have alternatives in
Yukon except for the two that Ryan mentions below. What do you mean when you
say they're 'not reliable' and why do you want alternatives for the
operations they perform?
The DMVs will be fully documented in the BOL for Beta 3.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:OhBU2gqAFHA.608@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply .
> am looking for all documented DBCC command alternative .
> Also looking for some difference between sql2000 sysobjects & Yukon . Do
you
> have any whitepaper for the same . Currently yukon has 46 DMVs but am not
> getting info about all those . Please help for the same .
> Thanks
> ARR
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
deprecated[vbcol=seagreen]
> As
> what
> rights.
> not
.[vbcol=seagreen]
>
Alternate to cursors
Hi,
I have a situation where I am loading data into a staging
table for multiple data sources. My next step is to pick up the
records from the staging table and compare with the data in the
database and based on the certain conditions, decide whether to insert
the data into the database or update an existing record in the
database. I have to do this job as an sp and schedule it to run on the
server as per the requirements. I thought that cursors are the only
option in this situation. Can anyone suggest if there is any other way
to achieve this in SQL 2005 please.
I have a situation where I am loading data into a staging
table for multiple data sources. My next step is to pick up the
records from the staging table and compare with the data in the
database and based on the certain conditions, decide whether to insert
the data into the database or update an existing record in the
database. I have to do this job as an sp and schedule it to run on the
server as per the requirements. I thought that cursors are the only
option in this situation. Can anyone suggest if there is any other way
to achieve this in SQL 2005 please.
Thanks
SeshadriIn general this is done with two commands, an UPDATE of existing rows
followed by an INSERT of new rows. Very generally:
UPDATE Target
SET cola = A.cola,
colb - A.colb
FROM Staging as A
WHERE Target.keycol = Staging.keycol
INSERT Target
SELECT keycol, cola, colb
FROM Staging as A
WHERE NOT EXISTS
(SELECT * FROM Target as B
WHERE A.keycol = B.keycol)
Whether this fits your requirements is unknown because you didn't
provide much information. It would require knowing at least the table
definitions and keys, as well as the "certain conditions".
Roy Harvey
Beacon Falls, CT
On Mon, 01 Oct 2007 00:12:22 -0700, srirangam.seshadri@.gmail.com
wrote:
Quote:
Originally Posted by
>Hi,
I have a situation where I am loading data into a staging
>table for multiple data sources. My next step is to pick up the
>records from the staging table and compare with the data in the
>database and based on the certain conditions, decide whether to insert
>the data into the database or update an existing record in the
>database. I have to do this job as an sp and schedule it to run on the
>server as per the requirements. I thought that cursors are the only
>option in this situation. Can anyone suggest if there is any other way
>to achieve this in SQL 2005 please.
>
>Thanks
>
>Seshadri
Alternate to a not in query
-- tested schema below --
-- create tables --
create table tbl_test
(serialnumber char(12))
go
create table tbl_test2
(serialnumber char(12),
exportedflag int)
go
--insert data --
insert into tbl_test2 values ('123456789010',0)
insert into tbl_test2 values ('123456789011',0)
insert into tbl_test2 values ('123456789012',0)
insert into tbl_test2 values ('123456789013',0)
insert into tbl_test2 values ('123456789014',0)
insert into tbl_test2 values ('123456789015',0)
insert into tbl_test2 values ('123456789016',0)
insert into tbl_test2 values ('123456789017',0)
insert into tbl_test2 values ('123456789018',0)
insert into tbl_test2 values ('123456789019',0)
-- create tables --
create table tbl_test
(serialnumber char(12))
go
create table tbl_test2
(serialnumber char(12),
exportedflag int)
go
--insert data --
insert into tbl_test2 values ('123456789010',0)
insert into tbl_test2 values ('123456789011',0)
insert into tbl_test2 values ('123456789012',0)
insert into tbl_test2 values ('123456789013',0)
insert into tbl_test2 values ('123456789014',0)
insert into tbl_test2 values ('123456789015',0)
insert into tbl_test2 values ('123456789016',0)
insert into tbl_test2 values ('123456789017',0)
insert into tbl_test2 values ('123456789018',0)
insert into tbl_test2 values ('123456789019',0)
insert into tbl_test values ('123456789011')
insert into tbl_test values ('123456789012')
insert into tbl_test values ('123456789013')
insert into tbl_test values ('123456789014')
insert into tbl_test values ('123456789015')
-- query --
Select serialnumber from tbl_test2
where serialnumber
not in (select serialnumber from tbl_test) and
exportedflag=0
This query runs quite fast with only the data above but when both
tables get million plus rows, the query simply bogs down. Is there a
better way to write this query?Select serialnumber
from tbl_test2 a
left joint tbl_test b on a. serialnumber = b.serialnumber
where (b.serialnumber IS NULL)
AND (a.exportedflag=0)|||There is another way to write the query, but it's not better (in fact,
I think it's worse):
Select tbl_test2.serialnumber from tbl_test2
left join tbl_test on tbl_test2.serialnumber=tbl_test.serialnumber
where exportedflag=0 and tbl_test.serialnumber is null
To improve the performance of this query, you should create primary
keys on the tables. Besides the conceptual benefits of a proper design,
this would accomplish (at least) the following things:
- create an index on the serialnumber column
- declare that the serialnumber column does not allow duplicates
- declare that the serialnumber column does not allow nulls
These things will help the Query Optimizer very much to create a better
execution plan.
Razvan|||
Razvan Socol wrote:
> There is another way to write the query, but it's not better (in fact,
> I think it's worse):
> Select tbl_test2.serialnumber from tbl_test2
> left join tbl_test on tbl_test2.serialnumber=tbl_test.serialnumber
> where exportedflag=0 and tbl_test.serialnumber is null
Razvan,
Why worse?
The common wisdom seems to be that it is always more efficient
eliminate nested subqueries, if possible.
My understanding is that the optimizer will internally eliminate the
subquery by doing a left join as above if it can.|||Ira Gladnick (IraGladnick@.yahoo.com) writes:
> Why worse?
> The common wisdom seems to be that it is always more efficient
> eliminate nested subqueries, if possible.
It's worse, becase it does not express the intent of the query equally
well, and therefore can contribute to higher maintenance costs.
> My understanding is that the optimizer will internally eliminate the
> subquery by doing a left join as above if it can.
I don't know if this is the case, but in such case there is even less
reason to rewrite the query in an obscure way.
I would write the query as:
Select serialnumber
from tbl_test2 t2
where not exists (select *
from tbl_test t
where t2.serialnuber = t.serialnumber)
and exportedflag=0
In SQL 6.5 this would typically perform better than NOT IN. But I believe
SQL 2000 will rewrite NOT IN to NOT EXISTS internally, so it is not that
much of an issue for performance. But NOT EXISTS is more general to use
than NOT IN, because you can handle multi-column conditions. Furthermore,
if there are NULL values involved, NOT IN can give you surpriese.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(kjaggi@.hotmail.com) writes:
> -- query --
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
> This query runs quite fast with only the data above but when both
> tables get million plus rows, the query simply bogs down. Is there a
> better way to write this query?
Beside the obvious point from Razvan about indexes, if you are on a multi-
CPU box, you can try this at the end of the query:
OPTION (MAXDOP 1)
this turns off parallelism. I've seen SQL Server use massive parallel
plans for this type of query, when a non-parallel plan have been much
faster.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
alternate to "NOT IN " query
any better way of writing this
Select serialnumber from tbl_test2
where serialnumber
not in (select serialnumber from tbl_test) and
exportedflag=0Vikram
Select serialnumber from tbl_test2
where not exists
(select * from tbl_test where tbl_test.serialnumber
=tbl_test2.serialnumber )
and exportedflag=0
"Vikram" <aa@.aa> wrote in message
news:OE88tbrBGHA.3396@.tk2msftngp13.phx.gbl...
> any better way of writing this
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
>|||Also:
Select SerialNumber from tbl_test2
left join tbl_test on tbl_test2.SerialNumber = tbl_test.SerialNumber
where tbl_test.serialNumber is null and exportedFlag=0
"Vikram" <aa@.aa> wrote in message
news:OE88tbrBGHA.3396@.tk2msftngp13.phx.gbl...
> any better way of writing this
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
>
Select serialnumber from tbl_test2
where serialnumber
not in (select serialnumber from tbl_test) and
exportedflag=0Vikram
Select serialnumber from tbl_test2
where not exists
(select * from tbl_test where tbl_test.serialnumber
=tbl_test2.serialnumber )
and exportedflag=0
"Vikram" <aa@.aa> wrote in message
news:OE88tbrBGHA.3396@.tk2msftngp13.phx.gbl...
> any better way of writing this
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
>|||Also:
Select SerialNumber from tbl_test2
left join tbl_test on tbl_test2.SerialNumber = tbl_test.SerialNumber
where tbl_test.serialNumber is null and exportedFlag=0
"Vikram" <aa@.aa> wrote in message
news:OE88tbrBGHA.3396@.tk2msftngp13.phx.gbl...
> any better way of writing this
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
>
Alternate the background color of rows
Hi!
I 'm working whit a matrix.
I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:
Please, if somebody can help me
Thanks
hey there
there is something on this page that may help you
http://blogs.msdn.com/chrishays/
|||Specifically you want to read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
-- Robert
|||Robert, I've used the suggestions in Chris Hay's example. However, it only gets me 90% to where I need.
I cannot get the subtotal background color to alternate. Do you know a way to get this functionality. Kind of pointless and ugly to have everything alternate except the last column or row.
Alternate the background color of rows
Hi!
I 'm working whit a matrix.
I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:
Please, if somebody can help me
Thanks
Please read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
-- Robert
Alternate the background color of rows
Hi!
I 'm working whit a matrix.
I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:
Please, if somebody can help me
Thanks
hey there
there is something on this page that may help you
http://blogs.msdn.com/chrishays/
|||Specifically you want to read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
-- Robert
|||Robert, I've used the suggestions in Chris Hay's example. However, it only gets me 90% to where I need.
I cannot get the subtotal background color to alternate. Do you know a way to get this functionality. Kind of pointless and ugly to have everything alternate except the last column or row.
alternate table value
I need to read through two tables, first stepping thru the first one,
finding a value to a particular field (model no) along with the value of
another (price), and then read thru the second table, looking for the same
field value (model no), and if found, use that (price) field vale verses the
original value for the (price) field.
TABLE 1
Model No. Price
X45 $30
X60 $50
TABLE 2
Model No. Price
X50 $35
X60 $48
RESULT SET
Model No. Price
X45 $30
X50 $35
X60 $48
Thanks,
DonOne method:
SELECT
COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
COALESCE(t2.Price, t1.Price) AS Price
FROM Table1 t1
FULL JOIN Table2 t2 ON
t1.ModelNo = t2.ModelNo
ORDER BY 1
Hope this helps.
Dan Guzman
SQL Server MVP
<dbj> wrote in message news:%23D%23EntISFHA.2132@.TK2MSFTNGP14.phx.gbl...
>I need to read through two tables, first stepping thru the first one,
>finding a value to a particular field (model no) along with the value of
>another (price), and then read thru the second table, looking for the same
>field value (model no), and if found, use that (price) field vale verses
>the original value for the (price) field.
> TABLE 1
> Model No. Price
> X45 $30
> X60 $50
> TABLE 2
> Model No. Price
> X50 $35
> X60 $48
> RESULT SET
> Model No. Price
> X45 $30
> X50 $35
> X60 $48
> Thanks,
> Don
>
>|||Hi Dan,
Thanks for the help. It appears as though this is very close. But, I am
still getting duplicate records for the same model number, when the desire
is to only use the record from table 2 when the same model no is found in
both.
Here is the result set I am getting:
Model No. Price
X45 $30
X50 $35
X60 $50
X60 $48
When I am looking for:
Model No. Price
X45 $30
X50 $35
X60 $48 <-- Table 2 is specifically used when same
model no is in both tables.
Below is the actual code being used:
SELECT TOP 100 PERCENT COALESCE (dbo.hb_view_organization_plan.plan_id,
dbo.hb_view_business_unit_plan.plan_id,
dbo.hb_view_subdivision_plan.plan_id,
dbo.hb_view_lot_plan.plan_id) AS plan_id, COALESCE
(dbo.hb_view_organization_plan.plan_no_name,
dbo.hb_view_business_unit_plan.plan_no_name,
dbo.hb_view_subdivision_plan.plan_no_name,
dbo.hb_view_lot_plan.plan_no_name)
AS plan_no_name, COALESCE
(dbo.hb_view_organization_plan.current_retail_price,
dbo.hb_view_business_unit_plan.current_retail_price,
dbo.hb_view_subdivision_plan.current_retail_price,
dbo.hb_view_lot_plan.current_retail_price) AS current_retail_price,
COALESCE (dbo.hb_view_organization_plan.source,
dbo.hb_view_business_unit_plan.source, dbo.hb_view_subdivision_plan.source,
dbo.hb_view_lot_plan.source) AS source
FROM dbo.hb_view_organization_plan FULL OUTER JOIN
dbo.hb_view_business_unit_plan ON
dbo.hb_view_organization_plan.plan_id =
dbo.hb_view_business_unit_plan.plan_id FULL OUTER JOIN
dbo.hb_view_lot_plan FULL OUTER JOIN
dbo.hb_view_subdivision_plan ON
dbo.hb_view_lot_plan.plan_id = dbo.hb_view_subdivision_plan.plan_id ON
dbo.hb_view_business_unit_plan.plan_id =
dbo.hb_view_subdivision_plan.plan_id
ORDER BY dbo.hb_view_organization_plan.plan_no_name
The logic here is that is to sum all the records and when a duplicate
plan_no_name is found, is to display only one record from the lowest in the
hierarchy, which organization, business_unit, subdivsion, and lot. Always
use the lot record first (lowest), subdivision second, business unit third,
and finally organization last (highest).
Again, thanks for your help.
Regards,
Don
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O1LO80ISFHA.2788@.TK2MSFTNGP09.phx.gbl...
> One method:
> SELECT
> COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
> COALESCE(t2.Price, t1.Price) AS Price
> FROM Table1 t1
> FULL JOIN Table2 t2 ON
> t1.ModelNo = t2.ModelNo
> ORDER BY 1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <dbj> wrote in message news:%23D%23EntISFHA.2132@.TK2MSFTNGP14.phx.gbl...
>|||You design is wrong. Given a fixed hierarchy, each level should be in
the same row since it is an attribute of that entity. This is a basic
principle of data modeling. You might want to read what Chris Date and
Dave McGoveran said about this kind of design error. They called it
orthogonal design, while I prefer attribute splitting.
duplicate plan_no_name is found, is to display only one record [sic]
from the lowest in the hierarchy, which organization, business_unit,
subdivsion, and lot. Always use the lot record [sic] first (lowest),
subdivision second, business unit third, and finally organization last
(highest). <<
Row are not records and until you learn the differences, you will not
be able to write good SQL. You will think in terms of DML for
solutions that shoudl have been done with correct DDL. Since you did
not post DDL, it is impossible to make anything but a guess about the
data.
And using things like "view" or "tbl" in a data element name to tell us
about the storage method is a violationfo ISO-11179 and good data
mdoeling practices, too.|||On Sun, 24 Apr 2005 15:17:14 -0500, <dbj> wrote:
>Hi Dan,
>Thanks for the help. It appears as though this is very close. But, I am
>still getting duplicate records for the same model number, when the desire
>is to only use the record from table 2 when the same model no is found in
>both.
>Here is the result set I am getting:
>Model No. Price
> X45 $30
> X50 $35
> X60 $50
> X60 $48
>When I am looking for:
>Model No. Price
> X45 $30
> X50 $35
> X60 $48 <-- Table 2 is specifically used when same
>model no is in both tables.
>Below is the actual code being used:
(snip)
Hi Don,
Checking the code you posted, I see that you're not attempting to combine
two tables, but a total of four! Now, whereas a full outer join betwee two
tables is not very tough, a full outer join of three or more tables has
some gotchas in the ON clause.
Here's a simplified version of a four-way full outer join to show you how
you should approach this:
SELECT COALESCE (t1.keycol, t2.keycol, t3.keycol, t4.keycol),
COALESCE (t1.datacol, t2.datacol, t3.datacol, t4.datacol)
FROM Table1 AS t1
FULL JOIN Table2 AS t2
ON t2.keycol = t1.keycol
FULL JOIN Table3 AS t3
ON t3.keycol = COALESCE(t1.keycol, t2.keycol) -- Gotcha!
FULL JOIN Table4 AS t4
ON t4.keycol = COALESCE(t1.keycol, t2.keycol, t3.keycol) -- Gotcha!
I'll leave it too you to fill this pattern with your (IMO much too long)
table and column names.
Oh, and by the way - TOP 100 PERCENT is totally meaningless; it clutters
your query, and in the worst case introduces unnecessary overhead at
execution time. Please remove it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||<dbj> wrote in message news:OJ3NcqQSFHA.3144@.tk2msftngp13.phx.gbl...
> Hi Dan,
> Thanks for the help. It appears as though this is very close. But, I am
> still getting duplicate records for the same model number, when the desire
> is to only use the record from table 2 when the same model no is found in
> both.
> Here is the result set I am getting:
> Model No. Price
> X45 $30
> X50 $35
> X60 $50
> X60 $48
> When I am looking for:
> Model No. Price
> X45 $30
> X50 $35
> X60 $48 <-- Table 2 is specifically used when same
> model no is in both tables.
I get the expected results using the sample data provided in your original
post. Below is the complete script:
CREATE TABLE Table1
(
ModelNo char(3) NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
price int NOT NULL
)
CREATE TABLE Table2
(
ModelNo char(3) NOT NULL
CONSTRAINT PK_Table2 PRIMARY KEY,
price int NOT NULL
)
INSERT INTO Table1 VALUES('X45', 30)
INSERT INTO Table1 VALUES('X60', 50)
INSERT INTO Table2 VALUES('X50', 35)
INSERT INTO Table2 VALUES('X60', 48)
SELECT
COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
COALESCE(t2.Price, t1.Price) AS Price
FROM Table1 t1
FULL JOIN Table2 t2 ON
t1.ModelNo = t2.ModelNo
ORDER BY 1
--results
ModelNo Price
-- --
X45 30
X50 35
X60 48
> Below is the actual code being used:
> SELECT TOP 100 PERCENT COALESCE
> (dbo.hb_view_organization_plan.plan_id,
> dbo.hb_view_business_unit_plan.plan_id,
> dbo.hb_view_subdivision_plan.plan_id,
> dbo.hb_view_lot_plan.plan_id) AS plan_id, COALESCE
> (dbo.hb_view_organization_plan.plan_no_name,
> dbo.hb_view_business_unit_plan.plan_no_name,
> dbo.hb_view_subdivision_plan.plan_no_name,
> dbo.hb_view_lot_plan.plan_no_name)
> AS plan_no_name, COALESCE
> (dbo.hb_view_organization_plan.current_retail_price,
> dbo.hb_view_business_unit_plan.current_retail_price,
> dbo.hb_view_subdivision_plan.current_retail_price,
> dbo.hb_view_lot_plan.current_retail_price) AS current_retail_price,
> COALESCE (dbo.hb_view_organization_plan.source,
> dbo.hb_view_business_unit_plan.source,
> dbo.hb_view_subdivision_plan.source,
> dbo.hb_view_lot_plan.source) AS source
> FROM dbo.hb_view_organization_plan FULL OUTER JOIN
> dbo.hb_view_business_unit_plan ON
> dbo.hb_view_organization_plan.plan_id =
> dbo.hb_view_business_unit_plan.plan_id FULL OUTER JOIN
> dbo.hb_view_lot_plan FULL OUTER JOIN
> dbo.hb_view_subdivision_plan ON
> dbo.hb_view_lot_plan.plan_id = dbo.hb_view_subdivision_plan.plan_id ON
> dbo.hb_view_business_unit_plan.plan_id =
> dbo.hb_view_subdivision_plan.plan_id
> ORDER BY dbo.hb_view_organization_plan.plan_no_name
> The logic here is that is to sum all the records and when a duplicate
> plan_no_name is found, is to display only one record from the lowest in
> the hierarchy, which organization, business_unit, subdivsion, and lot.
> Always use the lot record first (lowest), subdivision second, business
> unit third, and finally organization last (highest).
> Again, thanks for your help.
> Regards,
> Don
>
I wouldn't expect duplicate plan_ids as long as that is the is the primary
key of these tables. However, you might very well have duplicate
plan_no_name data. In that case, it is a symptom of a flaw in your data
model.
Hope this helps.
Dan Guzman
SQL Server MVP|||Call me old-fashioned or just plain dumb, but I'd go about it like
this:
SELECT t1.modelno, price = isnull(t2.price,t1.price)
FROM t1
LEFT JOIN t2 ON t1.modelno = t2.modelno
The existence of a row in t2 results in the t2 price; whereas an
absence of a row in t2 results in t1 price.|||> The existence of a row in t2 results in the t2 price; whereas an
> absence of a row in t2 results in t1 price.
>
True, but also the absence of a row in t1 should provide the t2 price based
on Don's requirements, hence the FULL JOIN. With his original data:
SELECT t1.modelno, price = isnull(t2.price,t1.price)
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.modelno = t2.modelno
Results:
modelno price
-- --
X45 30
X60 48
Desired results:
modelno price
-- --
X45 30
X50 35
X60 48
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeme" <jeme.rey@.gmail.com> wrote in message
news:1114383919.282023.11520@.o13g2000cwo.googlegroups.com...
> Call me old-fashioned or just plain dumb, but I'd go about it like
> this:
> SELECT t1.modelno, price = isnull(t2.price,t1.price)
> FROM t1
> LEFT JOIN t2 ON t1.modelno = t2.modelno
> The existence of a row in t2 results in the t2 price; whereas an
> absence of a row in t2 results in t1 price.
>|||Hi Hugo,
Your suggestion worked. Thanks VERY MUCH for your help. And I would also
like to thank everyone else who responded as well. I am sure my
inexperience was apparent but really appreciate your patience helping me
through this. Below is your code that worked!
SELECT TOP 100 PERCENT COALESCE (t1.plan_id, t2.plan_id, t3.plan_id,
t4.plan_id) AS plan_id, COALESCE (t1.plan_availability_pricing_id,
t2.plan_availability_pricing_id,
t3.plan_availability_pricing_id, t4.plan_availability_pricing_id) AS
plan_availability_pricing_id,
COALESCE (t1.plan_number, t2.plan_number,
t3.plan_number, t4.plan_number) AS plan_number, COALESCE (t1.plan_number,
t2.plan_name,
t3.plan_name, t4.plan_name) AS plan_namer, COALESCE
(t1.plan_no_name, t2.plan_no_name, t3.plan_no_name, t4.plan_no_name) AS
plan_no_name,
COALESCE (t1.current_retail_price,
t2.current_retail_price, t3.current_retail_price, t4.current_retail_price)
AS current_retail_price, COALESCE (t1.source,
t2.source, t3.source, t4.source) AS source
FROM dbo.hb_view_lot_plan t1 FULL OUTER JOIN
dbo.hb_view_subdivision_plan t2 ON t2.plan_id =
t1.plan_id FULL OUTER JOIN
dbo.hb_view_business_unit_plan t3 ON t3.plan_id =
COALESCE (t1.plan_id, t2.plan_id) FULL OUTER JOIN
dbo.hb_view_organization_plan t4 ON t4.plan_id =
COALESCE (t1.plan_id, t2.plan_id, t3.plan_id)
ORDER BY t1.plan_no_name
Regards,
Don
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vo3o61t3hm1doo7ou1f1hoimq6nrdq5b07@.
4ax.com...
> On Sun, 24 Apr 2005 15:17:14 -0500, <dbj> wrote:
>
> (snip)
> Hi Don,
> Checking the code you posted, I see that you're not attempting to combine
> two tables, but a total of four! Now, whereas a full outer join betwee two
> tables is not very tough, a full outer join of three or more tables has
> some gotchas in the ON clause.
> Here's a simplified version of a four-way full outer join to show you how
> you should approach this:
> SELECT COALESCE (t1.keycol, t2.keycol, t3.keycol, t4.keycol),
> COALESCE (t1.datacol, t2.datacol, t3.datacol, t4.datacol)
> FROM Table1 AS t1
> FULL JOIN Table2 AS t2
> ON t2.keycol = t1.keycol
> FULL JOIN Table3 AS t3
> ON t3.keycol = COALESCE(t1.keycol, t2.keycol) -- Gotcha!
> FULL JOIN Table4 AS t4
> ON t4.keycol = COALESCE(t1.keycol, t2.keycol, t3.keycol) -- Gotcha!
> I'll leave it too you to fill this pattern with your (IMO much too long)
> table and column names.
> Oh, and by the way - TOP 100 PERCENT is totally meaningless; it clutters
> your query, and in the worst case introduces unnecessary overhead at
> execution time. Please remove it.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 24 Apr 2005 22:48:27 -0500, <dbj> wrote:
>Hi Hugo,
>Your suggestion worked. Thanks VERY MUCH for your help. And I would also
>like to thank everyone else who responded as well. I am sure my
>inexperience was apparent but really appreciate your patience helping me
>through this. Below is your code that worked!
>SELECT TOP 100 PERCENT COALESCE (t1.plan_id, t2.plan_id, t3.plan_id,
(snip)
Hi Don,
Good to hear that it worked. Now all that's left to do is to get rid of
the totally useless TOP 100 PERCENT. :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql
finding a value to a particular field (model no) along with the value of
another (price), and then read thru the second table, looking for the same
field value (model no), and if found, use that (price) field vale verses the
original value for the (price) field.
TABLE 1
Model No. Price
X45 $30
X60 $50
TABLE 2
Model No. Price
X50 $35
X60 $48
RESULT SET
Model No. Price
X45 $30
X50 $35
X60 $48
Thanks,
DonOne method:
SELECT
COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
COALESCE(t2.Price, t1.Price) AS Price
FROM Table1 t1
FULL JOIN Table2 t2 ON
t1.ModelNo = t2.ModelNo
ORDER BY 1
Hope this helps.
Dan Guzman
SQL Server MVP
<dbj> wrote in message news:%23D%23EntISFHA.2132@.TK2MSFTNGP14.phx.gbl...
>I need to read through two tables, first stepping thru the first one,
>finding a value to a particular field (model no) along with the value of
>another (price), and then read thru the second table, looking for the same
>field value (model no), and if found, use that (price) field vale verses
>the original value for the (price) field.
> TABLE 1
> Model No. Price
> X45 $30
> X60 $50
> TABLE 2
> Model No. Price
> X50 $35
> X60 $48
> RESULT SET
> Model No. Price
> X45 $30
> X50 $35
> X60 $48
> Thanks,
> Don
>
>|||Hi Dan,
Thanks for the help. It appears as though this is very close. But, I am
still getting duplicate records for the same model number, when the desire
is to only use the record from table 2 when the same model no is found in
both.
Here is the result set I am getting:
Model No. Price
X45 $30
X50 $35
X60 $50
X60 $48
When I am looking for:
Model No. Price
X45 $30
X50 $35
X60 $48 <-- Table 2 is specifically used when same
model no is in both tables.
Below is the actual code being used:
SELECT TOP 100 PERCENT COALESCE (dbo.hb_view_organization_plan.plan_id,
dbo.hb_view_business_unit_plan.plan_id,
dbo.hb_view_subdivision_plan.plan_id,
dbo.hb_view_lot_plan.plan_id) AS plan_id, COALESCE
(dbo.hb_view_organization_plan.plan_no_name,
dbo.hb_view_business_unit_plan.plan_no_name,
dbo.hb_view_subdivision_plan.plan_no_name,
dbo.hb_view_lot_plan.plan_no_name)
AS plan_no_name, COALESCE
(dbo.hb_view_organization_plan.current_retail_price,
dbo.hb_view_business_unit_plan.current_retail_price,
dbo.hb_view_subdivision_plan.current_retail_price,
dbo.hb_view_lot_plan.current_retail_price) AS current_retail_price,
COALESCE (dbo.hb_view_organization_plan.source,
dbo.hb_view_business_unit_plan.source, dbo.hb_view_subdivision_plan.source,
dbo.hb_view_lot_plan.source) AS source
FROM dbo.hb_view_organization_plan FULL OUTER JOIN
dbo.hb_view_business_unit_plan ON
dbo.hb_view_organization_plan.plan_id =
dbo.hb_view_business_unit_plan.plan_id FULL OUTER JOIN
dbo.hb_view_lot_plan FULL OUTER JOIN
dbo.hb_view_subdivision_plan ON
dbo.hb_view_lot_plan.plan_id = dbo.hb_view_subdivision_plan.plan_id ON
dbo.hb_view_business_unit_plan.plan_id =
dbo.hb_view_subdivision_plan.plan_id
ORDER BY dbo.hb_view_organization_plan.plan_no_name
The logic here is that is to sum all the records and when a duplicate
plan_no_name is found, is to display only one record from the lowest in the
hierarchy, which organization, business_unit, subdivsion, and lot. Always
use the lot record first (lowest), subdivision second, business unit third,
and finally organization last (highest).
Again, thanks for your help.
Regards,
Don
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O1LO80ISFHA.2788@.TK2MSFTNGP09.phx.gbl...
> One method:
> SELECT
> COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
> COALESCE(t2.Price, t1.Price) AS Price
> FROM Table1 t1
> FULL JOIN Table2 t2 ON
> t1.ModelNo = t2.ModelNo
> ORDER BY 1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <dbj> wrote in message news:%23D%23EntISFHA.2132@.TK2MSFTNGP14.phx.gbl...
>|||You design is wrong. Given a fixed hierarchy, each level should be in
the same row since it is an attribute of that entity. This is a basic
principle of data modeling. You might want to read what Chris Date and
Dave McGoveran said about this kind of design error. They called it
orthogonal design, while I prefer attribute splitting.
duplicate plan_no_name is found, is to display only one record [sic]
from the lowest in the hierarchy, which organization, business_unit,
subdivsion, and lot. Always use the lot record [sic] first (lowest),
subdivision second, business unit third, and finally organization last
(highest). <<
Row are not records and until you learn the differences, you will not
be able to write good SQL. You will think in terms of DML for
solutions that shoudl have been done with correct DDL. Since you did
not post DDL, it is impossible to make anything but a guess about the
data.
And using things like "view" or "tbl" in a data element name to tell us
about the storage method is a violationfo ISO-11179 and good data
mdoeling practices, too.|||On Sun, 24 Apr 2005 15:17:14 -0500, <dbj> wrote:
>Hi Dan,
>Thanks for the help. It appears as though this is very close. But, I am
>still getting duplicate records for the same model number, when the desire
>is to only use the record from table 2 when the same model no is found in
>both.
>Here is the result set I am getting:
>Model No. Price
> X45 $30
> X50 $35
> X60 $50
> X60 $48
>When I am looking for:
>Model No. Price
> X45 $30
> X50 $35
> X60 $48 <-- Table 2 is specifically used when same
>model no is in both tables.
>Below is the actual code being used:
(snip)
Hi Don,
Checking the code you posted, I see that you're not attempting to combine
two tables, but a total of four! Now, whereas a full outer join betwee two
tables is not very tough, a full outer join of three or more tables has
some gotchas in the ON clause.
Here's a simplified version of a four-way full outer join to show you how
you should approach this:
SELECT COALESCE (t1.keycol, t2.keycol, t3.keycol, t4.keycol),
COALESCE (t1.datacol, t2.datacol, t3.datacol, t4.datacol)
FROM Table1 AS t1
FULL JOIN Table2 AS t2
ON t2.keycol = t1.keycol
FULL JOIN Table3 AS t3
ON t3.keycol = COALESCE(t1.keycol, t2.keycol) -- Gotcha!
FULL JOIN Table4 AS t4
ON t4.keycol = COALESCE(t1.keycol, t2.keycol, t3.keycol) -- Gotcha!
I'll leave it too you to fill this pattern with your (IMO much too long)
table and column names.
Oh, and by the way - TOP 100 PERCENT is totally meaningless; it clutters
your query, and in the worst case introduces unnecessary overhead at
execution time. Please remove it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||<dbj> wrote in message news:OJ3NcqQSFHA.3144@.tk2msftngp13.phx.gbl...
> Hi Dan,
> Thanks for the help. It appears as though this is very close. But, I am
> still getting duplicate records for the same model number, when the desire
> is to only use the record from table 2 when the same model no is found in
> both.
> Here is the result set I am getting:
> Model No. Price
> X45 $30
> X50 $35
> X60 $50
> X60 $48
> When I am looking for:
> Model No. Price
> X45 $30
> X50 $35
> X60 $48 <-- Table 2 is specifically used when same
> model no is in both tables.
I get the expected results using the sample data provided in your original
post. Below is the complete script:
CREATE TABLE Table1
(
ModelNo char(3) NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
price int NOT NULL
)
CREATE TABLE Table2
(
ModelNo char(3) NOT NULL
CONSTRAINT PK_Table2 PRIMARY KEY,
price int NOT NULL
)
INSERT INTO Table1 VALUES('X45', 30)
INSERT INTO Table1 VALUES('X60', 50)
INSERT INTO Table2 VALUES('X50', 35)
INSERT INTO Table2 VALUES('X60', 48)
SELECT
COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
COALESCE(t2.Price, t1.Price) AS Price
FROM Table1 t1
FULL JOIN Table2 t2 ON
t1.ModelNo = t2.ModelNo
ORDER BY 1
--results
ModelNo Price
-- --
X45 30
X50 35
X60 48
> Below is the actual code being used:
> SELECT TOP 100 PERCENT COALESCE
> (dbo.hb_view_organization_plan.plan_id,
> dbo.hb_view_business_unit_plan.plan_id,
> dbo.hb_view_subdivision_plan.plan_id,
> dbo.hb_view_lot_plan.plan_id) AS plan_id, COALESCE
> (dbo.hb_view_organization_plan.plan_no_name,
> dbo.hb_view_business_unit_plan.plan_no_name,
> dbo.hb_view_subdivision_plan.plan_no_name,
> dbo.hb_view_lot_plan.plan_no_name)
> AS plan_no_name, COALESCE
> (dbo.hb_view_organization_plan.current_retail_price,
> dbo.hb_view_business_unit_plan.current_retail_price,
> dbo.hb_view_subdivision_plan.current_retail_price,
> dbo.hb_view_lot_plan.current_retail_price) AS current_retail_price,
> COALESCE (dbo.hb_view_organization_plan.source,
> dbo.hb_view_business_unit_plan.source,
> dbo.hb_view_subdivision_plan.source,
> dbo.hb_view_lot_plan.source) AS source
> FROM dbo.hb_view_organization_plan FULL OUTER JOIN
> dbo.hb_view_business_unit_plan ON
> dbo.hb_view_organization_plan.plan_id =
> dbo.hb_view_business_unit_plan.plan_id FULL OUTER JOIN
> dbo.hb_view_lot_plan FULL OUTER JOIN
> dbo.hb_view_subdivision_plan ON
> dbo.hb_view_lot_plan.plan_id = dbo.hb_view_subdivision_plan.plan_id ON
> dbo.hb_view_business_unit_plan.plan_id =
> dbo.hb_view_subdivision_plan.plan_id
> ORDER BY dbo.hb_view_organization_plan.plan_no_name
> The logic here is that is to sum all the records and when a duplicate
> plan_no_name is found, is to display only one record from the lowest in
> the hierarchy, which organization, business_unit, subdivsion, and lot.
> Always use the lot record first (lowest), subdivision second, business
> unit third, and finally organization last (highest).
> Again, thanks for your help.
> Regards,
> Don
>
I wouldn't expect duplicate plan_ids as long as that is the is the primary
key of these tables. However, you might very well have duplicate
plan_no_name data. In that case, it is a symptom of a flaw in your data
model.
Hope this helps.
Dan Guzman
SQL Server MVP|||Call me old-fashioned or just plain dumb, but I'd go about it like
this:
SELECT t1.modelno, price = isnull(t2.price,t1.price)
FROM t1
LEFT JOIN t2 ON t1.modelno = t2.modelno
The existence of a row in t2 results in the t2 price; whereas an
absence of a row in t2 results in t1 price.|||> The existence of a row in t2 results in the t2 price; whereas an
> absence of a row in t2 results in t1 price.
>
True, but also the absence of a row in t1 should provide the t2 price based
on Don's requirements, hence the FULL JOIN. With his original data:
SELECT t1.modelno, price = isnull(t2.price,t1.price)
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.modelno = t2.modelno
Results:
modelno price
-- --
X45 30
X60 48
Desired results:
modelno price
-- --
X45 30
X50 35
X60 48
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeme" <jeme.rey@.gmail.com> wrote in message
news:1114383919.282023.11520@.o13g2000cwo.googlegroups.com...
> Call me old-fashioned or just plain dumb, but I'd go about it like
> this:
> SELECT t1.modelno, price = isnull(t2.price,t1.price)
> FROM t1
> LEFT JOIN t2 ON t1.modelno = t2.modelno
> The existence of a row in t2 results in the t2 price; whereas an
> absence of a row in t2 results in t1 price.
>|||Hi Hugo,
Your suggestion worked. Thanks VERY MUCH for your help. And I would also
like to thank everyone else who responded as well. I am sure my
inexperience was apparent but really appreciate your patience helping me
through this. Below is your code that worked!
SELECT TOP 100 PERCENT COALESCE (t1.plan_id, t2.plan_id, t3.plan_id,
t4.plan_id) AS plan_id, COALESCE (t1.plan_availability_pricing_id,
t2.plan_availability_pricing_id,
t3.plan_availability_pricing_id, t4.plan_availability_pricing_id) AS
plan_availability_pricing_id,
COALESCE (t1.plan_number, t2.plan_number,
t3.plan_number, t4.plan_number) AS plan_number, COALESCE (t1.plan_number,
t2.plan_name,
t3.plan_name, t4.plan_name) AS plan_namer, COALESCE
(t1.plan_no_name, t2.plan_no_name, t3.plan_no_name, t4.plan_no_name) AS
plan_no_name,
COALESCE (t1.current_retail_price,
t2.current_retail_price, t3.current_retail_price, t4.current_retail_price)
AS current_retail_price, COALESCE (t1.source,
t2.source, t3.source, t4.source) AS source
FROM dbo.hb_view_lot_plan t1 FULL OUTER JOIN
dbo.hb_view_subdivision_plan t2 ON t2.plan_id =
t1.plan_id FULL OUTER JOIN
dbo.hb_view_business_unit_plan t3 ON t3.plan_id =
COALESCE (t1.plan_id, t2.plan_id) FULL OUTER JOIN
dbo.hb_view_organization_plan t4 ON t4.plan_id =
COALESCE (t1.plan_id, t2.plan_id, t3.plan_id)
ORDER BY t1.plan_no_name
Regards,
Don
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vo3o61t3hm1doo7ou1f1hoimq6nrdq5b07@.
4ax.com...
> On Sun, 24 Apr 2005 15:17:14 -0500, <dbj> wrote:
>
> (snip)
> Hi Don,
> Checking the code you posted, I see that you're not attempting to combine
> two tables, but a total of four! Now, whereas a full outer join betwee two
> tables is not very tough, a full outer join of three or more tables has
> some gotchas in the ON clause.
> Here's a simplified version of a four-way full outer join to show you how
> you should approach this:
> SELECT COALESCE (t1.keycol, t2.keycol, t3.keycol, t4.keycol),
> COALESCE (t1.datacol, t2.datacol, t3.datacol, t4.datacol)
> FROM Table1 AS t1
> FULL JOIN Table2 AS t2
> ON t2.keycol = t1.keycol
> FULL JOIN Table3 AS t3
> ON t3.keycol = COALESCE(t1.keycol, t2.keycol) -- Gotcha!
> FULL JOIN Table4 AS t4
> ON t4.keycol = COALESCE(t1.keycol, t2.keycol, t3.keycol) -- Gotcha!
> I'll leave it too you to fill this pattern with your (IMO much too long)
> table and column names.
> Oh, and by the way - TOP 100 PERCENT is totally meaningless; it clutters
> your query, and in the worst case introduces unnecessary overhead at
> execution time. Please remove it.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 24 Apr 2005 22:48:27 -0500, <dbj> wrote:
>Hi Hugo,
>Your suggestion worked. Thanks VERY MUCH for your help. And I would also
>like to thank everyone else who responded as well. I am sure my
>inexperience was apparent but really appreciate your patience helping me
>through this. Below is your code that worked!
>SELECT TOP 100 PERCENT COALESCE (t1.plan_id, t2.plan_id, t3.plan_id,
(snip)
Hi Don,
Good to hear that it worked. Now all that's left to do is to get rid of
the totally useless TOP 100 PERCENT. :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql
Alternate Synchronization Server Schema Change Problem
Greetings:
We have set up a test scenario in Sql Server 2000 using merge replication
with:
A primary synchronization server (Publisher A)
An alternate synchronization partner (Publisher B)
Two subscribers to both publishers (Subscriber Y and Subscriber Z)
After some initial difficulties, we were able to get the alternate synch
partner running where both subscribers will successfully synch to either
machine without difficulty. (See thread “Alternate Synchronization Partner
-- Error”, originally posted on March 10.)
The next part of testing phase before going live is to test how schema
changes occur with alternate synch partners. We find NO documentation on
this subject anywhere, so if anyone has resources on how to do schema changes
where an alternate synchronization partner is involved, we would be very,
very grateful.
Our basic question: How are schema changes done where an alternate
synchronization partner is involved? We want to know exact procedures to do
the following:
-- Add a column
-- Drop a column
-- Add a table
We have tried to add a column as follows:
1.Used “sp_repladdcolumn” and added a column to one table in Publisher A,
the primary publisher. (See next paragraph on warning received.)
2.Synchronized the Publisher B, the alternate synch partner and it brought
in the column.
3.Synchronized Subscriber Y to Publisher A, and it brought in the column to
the subscriber.
At #1, when “sp_repladdcolumn” ran, I received the following warning:
“Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_D536588A75244907040F5997233E41C8_pal'.
The stored procedure will still be created.”
The Microsoft knowledge base article 811483 says this warning can be
ignored, but I wonder about being able to ignore this warning because of the
results we received described below.
Results from above process:
-- NO DATA UPDATES FROM PUBLISHER A WILL PROPAGATE TO EITHER PUBLISHER B,
THE ALTERNATE, OR TO SUBSCRIBER Y WHEN SYNCRHONIZING. New records added at
Publisher A do propagate, but no updates of existing data will. Inserts
propagate – updates do not propagate.
-- In addition, all data changes, inserts, and deletes made at either
Publisher B, the alternate synch partner, or at Subscriber Y, DO propagate
back to the primary Publisher A.
When I found that no data updates would propagate to the alternate synch
partner or the subscribers, I then
1.Used “sp_repldropcolumn” to drop the column I had added on Publisher A,
the primary.
2.Synchronized Publisher B, the alternate synch partner and it dropped the
column.
3.Synchronized Subscriber Y and it dropped the column.
I then tested data updates from Publisher A, the primary, and the updates
all began to propagate to Publisher B, the alternate and Subscriber Y once
again.
Summary:
-- A column was added to the Publisher A and the schema change propagated
to Publisher B and Subscriber Y.
-- After the schema change was propagated, any data UPDATES on Publisher A
would not propagate to the Publisher B or Subscriber Y. INSERTS however,
would propagate.
-- The column was dropped at Publisher A and that schema change propagated
to Publisher B and Subscriber Y.
-- Data updates from Publish A began to propagate again go Publisher B and
Subscriber Y.
What is wrong with the procedures I am using to make schema changes that are
causing the primary synchronization server to stop propagating data updates
after the column is added, but when the column is dropped, updates begin to
propagate again?
Any procedures for schema changes when an alternate synchronization partner
is involved would be very much appreciated.
Thank you.
Bill
Hi Bill,
From your descriptions, I understood that you would like to why
sp_repladdcolumn does not take effect. If I have misunderstood your
concern, please feel free to point it out.
First of all, please understand that replication issues tend to be very
complex and hard to troubleshoot in newsgroups. If you need detail and
prompt assistance, I recommend that you open a Support incident with
Microsoft Customer Service and Support (CSS) so that a dedicated Support
Professional can work with you in a more timely and efficient manner. If
you need any help in this regard, please let me know.
For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Secondly, based on my knowledge, once the column is added at the original
publisher, the merge agent between the original publisher and the alternate
publisher has to be run so that the new schema is sent to the alternate
publisher. Also, the merge agent between the original publisher and the
subscriber needs to be run so that the schema definition is sent here. As
long as the schema of the replicating pairs match, there are no issues.
It seems your process is OK and it's weird that you are not able to
synchronize the data. Do you find any other error messages in Event Logs or
SQL Logs?
You may generate the file as following Knowledge Base article asked and
then send to me direcly v-mingqc@.online.microsoft.com (plase ensure to
remove 'online' in the email address as it's only for SPAM)
HOW TO: Enable Replication Agents for Logging to Output Files in SQL Server
http://support.microsoft.com/?id=312292
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Michael:
Thanks for the information. I was preparing to send you the logs when I
suddenly wondered if I needed to reinitialize the pull subscriptions. So I
decided to try that first, and it worked. After reinitializing all
subscriptions, all data changes began to synchronize with no errors or
problems.
I then ran several additional tests with adding and dropping new columns.
When I reinitialized the subscriptions, everything synchronized. When I did
not reinitialize, the schema would propagate, but data changes would not.
This is clearly different from when there is no alternate synch server. We
have added and deleted columns many times before with sp_repladdcolumn or
sp_repldropcolumn when no alternate synch server was involved. Data changes
always continued to synchronize properly in that circumstance. Perhaps some
note needs to be put in BOL about this difference when an alternate
synchronization server is involved? Just a suggestion that might save others
time.
The next test I am going to try is to add a new table (article) to our test
database and see if I can get it to propagate to the alternate synch server
and the two subscribers we have.
You have been a great help in pointing me the right direction. I really
appreciate it.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> From your descriptions, I understood that you would like to why
> sp_repladdcolumn does not take effect. If I have misunderstood your
> concern, please feel free to point it out.
> First of all, please understand that replication issues tend to be very
> complex and hard to troubleshoot in newsgroups. If you need detail and
> prompt assistance, I recommend that you open a Support incident with
> Microsoft Customer Service and Support (CSS) so that a dedicated Support
> Professional can work with you in a more timely and efficient manner. If
> you need any help in this regard, please let me know.
> For a complete list of Microsoft Customer Service and Support phone
> numbers, please go to the following address on the World Wide Web:
> <http://support.microsoft.com/directory/overview.asp>
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Secondly, based on my knowledge, once the column is added at the original
> publisher, the merge agent between the original publisher and the alternate
> publisher has to be run so that the new schema is sent to the alternate
> publisher. Also, the merge agent between the original publisher and the
> subscriber needs to be run so that the schema definition is sent here. As
> long as the schema of the replicating pairs match, there are no issues.
> It seems your process is OK and it's weird that you are not able to
> synchronize the data. Do you find any other error messages in Event Logs or
> SQL Logs?
> You may generate the file as following Knowledge Base article asked and
> then send to me direcly v-mingqc@.online.microsoft.com (plase ensure to
> remove 'online' in the email address as it's only for SPAM)
> HOW TO: Enable Replication Agents for Logging to Output Files in SQL Server
> http://support.microsoft.com/?id=312292
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
I searched internal but limited resource on this topic about why
reinitializing make all things go smoothly as I cannot reproduce it on my
side as you described. If you want to fingure out the root cause of this
issue, you'd better send the logs to our PSS guys as Replication issues
might turn out to be very complicated.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael;
With the reinitialization of the subscriptions, the schema changes and data
changes synchronized perfectly on numerous tests. I put together a process
for us to use in these circumstances because it is a bit different between
add/drop a column and adding a new table. In any case, I did get them work
fine with the altternate synch server.
If I did not reinitialize the subscriptions, the schema would propagate, but
the data changes would not from the primary server. I tried this in several
tests and could not get it go, but since I have a procedure that works now,
my business partners and I are happy with it and will go live in the next
month or two with our alternate synchronization partner.
I appreciate all your help and suggestions.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> I searched internal but limited resource on this topic about why
> reinitializing make all things go smoothly as I cannot reproduce it on my
> side as you described. If you want to fingure out the root cause of this
> issue, you'd better send the logs to our PSS guys as Replication issues
> might turn out to be very complicated.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
Thanks for your perfect summary and I believe others will also benefit from
your great work
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
We have set up a test scenario in Sql Server 2000 using merge replication
with:
A primary synchronization server (Publisher A)
An alternate synchronization partner (Publisher B)
Two subscribers to both publishers (Subscriber Y and Subscriber Z)
After some initial difficulties, we were able to get the alternate synch
partner running where both subscribers will successfully synch to either
machine without difficulty. (See thread “Alternate Synchronization Partner
-- Error”, originally posted on March 10.)
The next part of testing phase before going live is to test how schema
changes occur with alternate synch partners. We find NO documentation on
this subject anywhere, so if anyone has resources on how to do schema changes
where an alternate synchronization partner is involved, we would be very,
very grateful.
Our basic question: How are schema changes done where an alternate
synchronization partner is involved? We want to know exact procedures to do
the following:
-- Add a column
-- Drop a column
-- Add a table
We have tried to add a column as follows:
1.Used “sp_repladdcolumn” and added a column to one table in Publisher A,
the primary publisher. (See next paragraph on warning received.)
2.Synchronized the Publisher B, the alternate synch partner and it brought
in the column.
3.Synchronized Subscriber Y to Publisher A, and it brought in the column to
the subscriber.
At #1, when “sp_repladdcolumn” ran, I received the following warning:
“Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_D536588A75244907040F5997233E41C8_pal'.
The stored procedure will still be created.”
The Microsoft knowledge base article 811483 says this warning can be
ignored, but I wonder about being able to ignore this warning because of the
results we received described below.
Results from above process:
-- NO DATA UPDATES FROM PUBLISHER A WILL PROPAGATE TO EITHER PUBLISHER B,
THE ALTERNATE, OR TO SUBSCRIBER Y WHEN SYNCRHONIZING. New records added at
Publisher A do propagate, but no updates of existing data will. Inserts
propagate – updates do not propagate.
-- In addition, all data changes, inserts, and deletes made at either
Publisher B, the alternate synch partner, or at Subscriber Y, DO propagate
back to the primary Publisher A.
When I found that no data updates would propagate to the alternate synch
partner or the subscribers, I then
1.Used “sp_repldropcolumn” to drop the column I had added on Publisher A,
the primary.
2.Synchronized Publisher B, the alternate synch partner and it dropped the
column.
3.Synchronized Subscriber Y and it dropped the column.
I then tested data updates from Publisher A, the primary, and the updates
all began to propagate to Publisher B, the alternate and Subscriber Y once
again.
Summary:
-- A column was added to the Publisher A and the schema change propagated
to Publisher B and Subscriber Y.
-- After the schema change was propagated, any data UPDATES on Publisher A
would not propagate to the Publisher B or Subscriber Y. INSERTS however,
would propagate.
-- The column was dropped at Publisher A and that schema change propagated
to Publisher B and Subscriber Y.
-- Data updates from Publish A began to propagate again go Publisher B and
Subscriber Y.
What is wrong with the procedures I am using to make schema changes that are
causing the primary synchronization server to stop propagating data updates
after the column is added, but when the column is dropped, updates begin to
propagate again?
Any procedures for schema changes when an alternate synchronization partner
is involved would be very much appreciated.
Thank you.
Bill
Hi Bill,
From your descriptions, I understood that you would like to why
sp_repladdcolumn does not take effect. If I have misunderstood your
concern, please feel free to point it out.
First of all, please understand that replication issues tend to be very
complex and hard to troubleshoot in newsgroups. If you need detail and
prompt assistance, I recommend that you open a Support incident with
Microsoft Customer Service and Support (CSS) so that a dedicated Support
Professional can work with you in a more timely and efficient manner. If
you need any help in this regard, please let me know.
For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Secondly, based on my knowledge, once the column is added at the original
publisher, the merge agent between the original publisher and the alternate
publisher has to be run so that the new schema is sent to the alternate
publisher. Also, the merge agent between the original publisher and the
subscriber needs to be run so that the schema definition is sent here. As
long as the schema of the replicating pairs match, there are no issues.
It seems your process is OK and it's weird that you are not able to
synchronize the data. Do you find any other error messages in Event Logs or
SQL Logs?
You may generate the file as following Knowledge Base article asked and
then send to me direcly v-mingqc@.online.microsoft.com (plase ensure to
remove 'online' in the email address as it's only for SPAM)
HOW TO: Enable Replication Agents for Logging to Output Files in SQL Server
http://support.microsoft.com/?id=312292
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Michael:
Thanks for the information. I was preparing to send you the logs when I
suddenly wondered if I needed to reinitialize the pull subscriptions. So I
decided to try that first, and it worked. After reinitializing all
subscriptions, all data changes began to synchronize with no errors or
problems.
I then ran several additional tests with adding and dropping new columns.
When I reinitialized the subscriptions, everything synchronized. When I did
not reinitialize, the schema would propagate, but data changes would not.
This is clearly different from when there is no alternate synch server. We
have added and deleted columns many times before with sp_repladdcolumn or
sp_repldropcolumn when no alternate synch server was involved. Data changes
always continued to synchronize properly in that circumstance. Perhaps some
note needs to be put in BOL about this difference when an alternate
synchronization server is involved? Just a suggestion that might save others
time.
The next test I am going to try is to add a new table (article) to our test
database and see if I can get it to propagate to the alternate synch server
and the two subscribers we have.
You have been a great help in pointing me the right direction. I really
appreciate it.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> From your descriptions, I understood that you would like to why
> sp_repladdcolumn does not take effect. If I have misunderstood your
> concern, please feel free to point it out.
> First of all, please understand that replication issues tend to be very
> complex and hard to troubleshoot in newsgroups. If you need detail and
> prompt assistance, I recommend that you open a Support incident with
> Microsoft Customer Service and Support (CSS) so that a dedicated Support
> Professional can work with you in a more timely and efficient manner. If
> you need any help in this regard, please let me know.
> For a complete list of Microsoft Customer Service and Support phone
> numbers, please go to the following address on the World Wide Web:
> <http://support.microsoft.com/directory/overview.asp>
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Secondly, based on my knowledge, once the column is added at the original
> publisher, the merge agent between the original publisher and the alternate
> publisher has to be run so that the new schema is sent to the alternate
> publisher. Also, the merge agent between the original publisher and the
> subscriber needs to be run so that the schema definition is sent here. As
> long as the schema of the replicating pairs match, there are no issues.
> It seems your process is OK and it's weird that you are not able to
> synchronize the data. Do you find any other error messages in Event Logs or
> SQL Logs?
> You may generate the file as following Knowledge Base article asked and
> then send to me direcly v-mingqc@.online.microsoft.com (plase ensure to
> remove 'online' in the email address as it's only for SPAM)
> HOW TO: Enable Replication Agents for Logging to Output Files in SQL Server
> http://support.microsoft.com/?id=312292
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
I searched internal but limited resource on this topic about why
reinitializing make all things go smoothly as I cannot reproduce it on my
side as you described. If you want to fingure out the root cause of this
issue, you'd better send the logs to our PSS guys as Replication issues
might turn out to be very complicated.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael;
With the reinitialization of the subscriptions, the schema changes and data
changes synchronized perfectly on numerous tests. I put together a process
for us to use in these circumstances because it is a bit different between
add/drop a column and adding a new table. In any case, I did get them work
fine with the altternate synch server.
If I did not reinitialize the subscriptions, the schema would propagate, but
the data changes would not from the primary server. I tried this in several
tests and could not get it go, but since I have a procedure that works now,
my business partners and I are happy with it and will go live in the next
month or two with our alternate synchronization partner.
I appreciate all your help and suggestions.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> I searched internal but limited resource on this topic about why
> reinitializing make all things go smoothly as I cannot reproduce it on my
> side as you described. If you want to fingure out the root cause of this
> issue, you'd better send the logs to our PSS guys as Replication issues
> might turn out to be very complicated.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
Thanks for your perfect summary and I believe others will also benefit from
your great work
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Alternate Synchronization Partner Error
Greetings:
I am working on a setting up an alternate synchronization partner, and
getting an error. Bear with me as the explanation of what I have done so far
is long.
We have about 75 customers with about 100 pull subscriptions using about 20
publications in merge replication with Sql Server 2000. Our customers are
located where many have dial up connections and they need to set own
synchronization schedules. All are synchronizing with programs we wrote in
VB 6.0 using SQLDMO and the ActiveX merge control. All our customers have
been able to successfully synchronize now for many months, so we know our
programs are working as they should. They all synchronize over the internet
to a single publisher/distributor.
We would like to build some redundancy into our topology so that we have an
alternate synchronization server, or partner, as the BOL calls it, in case
the primary publisher fails. However, we want to locate the alternate synch
partner at a site other than where the primary publisher is located.
I have printed the MS KB article # 321176 on how to set up an alternate
synch partner. I also listened to the web cast from MS on this subject.
Following the KB #321176, I have set up two test databases, and a
subscriber. There is publisher A, and publisher B, with subscriber A.
Publisher A is the primary publisher and publisher B is to become our
alternate synch server. As the KB article and web cast said to do, I have
completed the following:
1.Created a global pull subscription on publisher B to publisher A.
2.Created a publication on publisher B which is identical to the database
on publisher A. The database names on both publisher A and publisher B are
the same name as are the publication names.
3.Generated a snapshot of the database on publisher B.
4.Enabled subscriber A on publisher B.
5.Set the alternate synch partners on both publisher A and publisher B so
each can use the other as a synch partner.
6.Created a pull subscription to publisher A from subscriber A which
synchronizes fine.
I then have tried to synchronize to the alternate publisher B from
subscriber A by changing the job commands as described in the KB article, but
get an error. In fact, the merge agent is able to connect to publisher B,
and initializes the process. In other words, I believe I have everything
set up correctly since the subscriber A connects and initializes to publisher
B. When it starts the actual synchronization, I get the following message:
“The process could not drop one or more tables because the tables are being
used by other publications.“ This is shown as error # -2147200976.
Apparently, subscriber A is trying to use the snapshot from publisher B,
which has scripts that tell it to drop tables. However, the tables trying to
be dropped are the subscription database as it was originally set up from the
pull subscription to publisher A. So, while the error make sense in a way
because the tables are replicated, subscriber A cannot synchronize to
publisher B because it is stopped by the error.
What am I doing wrong?
Do the database names and/or the publication names on the two publishers
need to be different? I had read one reference in BOL that said they should
be the same, but the KB article seems to imply otherwise.
I have found very little on any internet web site about Sql Server that
describes setting up and running alternate synch partners. Microsoft has the
most I have found on the subject, and I think I am following the MS set-up
correctly.
Any help would be gratefully appreciated.
Thanks!
Bill
Hi Bill,
From your descriptions, I understood that when applying alternate
synchronization partner, you encouter the error message "The process could
not drop one or more tables because the tables are being used by other
publications." Have I understood you? Correct me if I was wrong.
Based on my knowledge, this is because some entries does not match between
table sysmergepublications and sysmergesubscriptions on the publisher and
subscriber. A quick resolution is drop the publication completely and then
create it again.
How to manually remove a replication in SQL Server 2000
http://support.microsoft.com/kb/324401
I understood drop the publication may have huge business impact to your
business, as an option, please send the the result of following T-SQL
statement. I would like to check to see whether I could help further
select * from sysmerge_subscriptions
go
sp_helpserver
However, please understand that this replication issues tend to be very
complex and hard to troubleshoot in newsgroups. If you need further
assistance, I recommend that you open a Support incident with Microsoft
Customer Service and Support (CSS) so that a dedicated Support Professional
can work with you in a more timely and efficient manner. If you need any
help in this regard, please let me know.
For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael:
On a hunch before I sent you the requested the information, I changed the
database name on the alternate synch server (Publisher B) to a name that was
different from the original on Publisher A. Then the subscriber A was able
to sychronize with Publisher B, so I made it over that hurdle.
However, now, Subscriber A will not syncrhonize with Publisher A, but only
with Publsiher B. I get the following error:
"The Publisher has been restored from a backup whose schema change version
is different from the Subscriber. Rerun the Snapshot Agent and reinitialize"
The Publisher has NOT been restored from a backup. However, I went ahead as
the message said and reran the snapshot and reinitialized the subscription
from Subscriber A. Now the subscriber will not synch with Publisher B, and I
get the same error as above. So, on Publisher B, I do the snapshap again,
and reinitialize the subscription and then synch and it works. Then I try to
synch to Publisher A again, and get the error.
In other words, after I get a successful synch to either of the publishers,
I get the error on the other one the next time I try to synch to the other.
It is like an infinite loop. One works, the other doesn't. I run the
snapshot on the other and reinitialize, and it synchs, and then first one
doesn't, etc, etc. etc.
What is wrong now?
Thanks in advance for any help you can give!
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> From your descriptions, I understood that when applying alternate
> synchronization partner, you encouter the error message "The process could
> not drop one or more tables because the tables are being used by other
> publications." Have I understood you? Correct me if I was wrong.
> Based on my knowledge, this is because some entries does not match between
> table sysmergepublications and sysmergesubscriptions on the publisher and
> subscriber. A quick resolution is drop the publication completely and then
> create it again.
> How to manually remove a replication in SQL Server 2000
> http://support.microsoft.com/kb/324401
> I understood drop the publication may have huge business impact to your
> business, as an option, please send the the result of following T-SQL
> statement. I would like to check to see whether I could help further
> --
> select * from sysmerge_subscriptions
> go
> sp_helpserver
> --
> However, please understand that this replication issues tend to be very
> complex and hard to troubleshoot in newsgroups. If you need further
> assistance, I recommend that you open a Support incident with Microsoft
> Customer Service and Support (CSS) so that a dedicated Support Professional
> can work with you in a more timely and efficient manner. If you need any
> help in this regard, please let me know.
> For a complete list of Microsoft Customer Service and Support phone
> numbers, please go to the following address on the World Wide Web:
> <http://support.microsoft.com/directory/overview.asp>
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi,
I found you may encounter the problem described by KB article 814460
FIX: Merge Replication with Alternate Synchronization Partners May Not
Succeed After You Change the Retention Period
http://support.microsoft.com/kb/814460
Please feel free to ask this hotfix by contacting CSS. If you are simply
requesting a hotfix be sent to you and no other support then charges are
usually refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
NOTE that the hotfix is only intended to correct the problem that is
described in this article. Only apply it to systems that are experiencing
this specific problem. This hotfix may receive additional testing.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thank you, Michael. This sounds like the issue I am having.
I looked at the "http://support.microsoft.com/kb/814460" as you suggested.
At the bottom of the page is says that the fix is also included in "MS03-031:
Security patch for SQL Server 2000 Service Pack 3" and gives a link to
download it, so I would prefer to do that rather than place a phone call as I
think it will save time.
However, in the middle of the KB # 814460, it says:
"The problem exists in SQL Server 2000 Service Pack 3, version 8.00.0760. To
fix this version, you must apply SQL Server 2000 Service Pack 3 rollup,
version 8.00.0765."
The versions we are running are 8.00.0760 on both of the affected servers,
so I tried to find the "SQL Server 2000 Service Pack 3 rollup, version
8.00.0765" on the Microsoft web site and cannot. When I search both
downloads and the entire site, I get no hits except back to the KB # 814460.
Can you tell me where I can get "SQL Server 2000 Service Pack 3 rollup,
version 8.00.0765"?
Thank you.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi,
> I found you may encounter the problem described by KB article 814460
> FIX: Merge Replication with Alternate Synchronization Partners May Not
> Succeed After You Change the Retention Period
> http://support.microsoft.com/kb/814460
> Please feel free to ask this hotfix by contacting CSS. If you are simply
> requesting a hotfix be sent to you and no other support then charges are
> usually refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default...S;PHONENUMBERS
> NOTE that the hotfix is only intended to correct the problem that is
> described in this article. Only apply it to systems that are experiencing
> this specific problem. This hotfix may receive additional testing.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
Sorry that I should have clarified it more clearly.
Yes, You could download the patch available in the KB article 821277
MS03-031: Security patch for SQL Server 2000 Service Pack 3
http://support.microsoft.com/kb/821277
Let me know whether this security patch resolves your issue.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael:
Thanks for this reference. I have installed the hot fix on the alternate
synchronization server, but have not yet installed it on the primary server.
That will have to wait a couple of days as that server is very busy right now
with our customer's activity, so I won't be able to check the results on the
error I was getting for at least a couple of days. The hot fix requires Sql
Server to be stopped to install it, and we need to wait until a more
opportune time that has minimal impact on our customers.
I will be sure, however, to let you know the results when I have a chance to
test it. I just wanted you to know I won't be able to do my next testing on
this issue for a little while yet.
Thanks for your continuing help and I'll be sure to let you know what happens.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> Sorry that I should have clarified it more clearly.
> Yes, You could download the patch available in the KB article 821277
> MS03-031: Security patch for SQL Server 2000 Service Pack 3
> http://support.microsoft.com/kb/821277
> Let me know whether this security patch resolves your issue.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
I haven't heard back from you yet and I'm just writing in to see if you
have had an opportunity to perform the hotfix. If you could get back to me
at your earliest convenience, we will be able to go ahead
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael:
Sorry it took me so long to respond back to you. I have been extremely busy
with customer issues and a family crisis of sorts, so I didn't get time unitl
yesterday to do some more testing.
I applied the hotfix to the three servers involved and it eliminated the
error! I was able to synchronize successfully numerous times between all the
machines. I was very pleased with the result.
However, I had my business partner add a another subscriber late yesterday
afternoon. He was able to create a suscription fine to the primary synch
server, but when trying the alternate, he started getting some errors. But
we both had other matters to attend to at that time and did not have a chance
yet to analyze the errors and if it was the way Sql Server is setup on his
machine or some other issue. We will work on that issue today, and may have
some additional questions for you. We did apply the hotfix to the computer
he is using for this test.
Thanks for you continuing help.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> I haven't heard back from you yet and I'm just writing in to see if you
> have had an opportunity to perform the hotfix. If you could get back to me
> at your earliest convenience, we will be able to go ahead
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Michael:
We resolved all the initial errors and have the test alternate synch partner
running well with two subscribers. We have been able to run many, many
successful test syncrhonizations to both servers now without difficulty.
Part of testing plan, however, is to make some schema changes, and I have
run into another problem there. I will start a new thread for this issue.
Thanks for your help.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> I haven't heard back from you yet and I'm just writing in to see if you
> have had an opportunity to perform the hotfix. If you could get back to me
> at your earliest convenience, we will be able to go ahead
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
I am working on a setting up an alternate synchronization partner, and
getting an error. Bear with me as the explanation of what I have done so far
is long.
We have about 75 customers with about 100 pull subscriptions using about 20
publications in merge replication with Sql Server 2000. Our customers are
located where many have dial up connections and they need to set own
synchronization schedules. All are synchronizing with programs we wrote in
VB 6.0 using SQLDMO and the ActiveX merge control. All our customers have
been able to successfully synchronize now for many months, so we know our
programs are working as they should. They all synchronize over the internet
to a single publisher/distributor.
We would like to build some redundancy into our topology so that we have an
alternate synchronization server, or partner, as the BOL calls it, in case
the primary publisher fails. However, we want to locate the alternate synch
partner at a site other than where the primary publisher is located.
I have printed the MS KB article # 321176 on how to set up an alternate
synch partner. I also listened to the web cast from MS on this subject.
Following the KB #321176, I have set up two test databases, and a
subscriber. There is publisher A, and publisher B, with subscriber A.
Publisher A is the primary publisher and publisher B is to become our
alternate synch server. As the KB article and web cast said to do, I have
completed the following:
1.Created a global pull subscription on publisher B to publisher A.
2.Created a publication on publisher B which is identical to the database
on publisher A. The database names on both publisher A and publisher B are
the same name as are the publication names.
3.Generated a snapshot of the database on publisher B.
4.Enabled subscriber A on publisher B.
5.Set the alternate synch partners on both publisher A and publisher B so
each can use the other as a synch partner.
6.Created a pull subscription to publisher A from subscriber A which
synchronizes fine.
I then have tried to synchronize to the alternate publisher B from
subscriber A by changing the job commands as described in the KB article, but
get an error. In fact, the merge agent is able to connect to publisher B,
and initializes the process. In other words, I believe I have everything
set up correctly since the subscriber A connects and initializes to publisher
B. When it starts the actual synchronization, I get the following message:
“The process could not drop one or more tables because the tables are being
used by other publications.“ This is shown as error # -2147200976.
Apparently, subscriber A is trying to use the snapshot from publisher B,
which has scripts that tell it to drop tables. However, the tables trying to
be dropped are the subscription database as it was originally set up from the
pull subscription to publisher A. So, while the error make sense in a way
because the tables are replicated, subscriber A cannot synchronize to
publisher B because it is stopped by the error.
What am I doing wrong?
Do the database names and/or the publication names on the two publishers
need to be different? I had read one reference in BOL that said they should
be the same, but the KB article seems to imply otherwise.
I have found very little on any internet web site about Sql Server that
describes setting up and running alternate synch partners. Microsoft has the
most I have found on the subject, and I think I am following the MS set-up
correctly.
Any help would be gratefully appreciated.
Thanks!
Bill
Hi Bill,
From your descriptions, I understood that when applying alternate
synchronization partner, you encouter the error message "The process could
not drop one or more tables because the tables are being used by other
publications." Have I understood you? Correct me if I was wrong.
Based on my knowledge, this is because some entries does not match between
table sysmergepublications and sysmergesubscriptions on the publisher and
subscriber. A quick resolution is drop the publication completely and then
create it again.
How to manually remove a replication in SQL Server 2000
http://support.microsoft.com/kb/324401
I understood drop the publication may have huge business impact to your
business, as an option, please send the the result of following T-SQL
statement. I would like to check to see whether I could help further
select * from sysmerge_subscriptions
go
sp_helpserver
However, please understand that this replication issues tend to be very
complex and hard to troubleshoot in newsgroups. If you need further
assistance, I recommend that you open a Support incident with Microsoft
Customer Service and Support (CSS) so that a dedicated Support Professional
can work with you in a more timely and efficient manner. If you need any
help in this regard, please let me know.
For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael:
On a hunch before I sent you the requested the information, I changed the
database name on the alternate synch server (Publisher B) to a name that was
different from the original on Publisher A. Then the subscriber A was able
to sychronize with Publisher B, so I made it over that hurdle.
However, now, Subscriber A will not syncrhonize with Publisher A, but only
with Publsiher B. I get the following error:
"The Publisher has been restored from a backup whose schema change version
is different from the Subscriber. Rerun the Snapshot Agent and reinitialize"
The Publisher has NOT been restored from a backup. However, I went ahead as
the message said and reran the snapshot and reinitialized the subscription
from Subscriber A. Now the subscriber will not synch with Publisher B, and I
get the same error as above. So, on Publisher B, I do the snapshap again,
and reinitialize the subscription and then synch and it works. Then I try to
synch to Publisher A again, and get the error.
In other words, after I get a successful synch to either of the publishers,
I get the error on the other one the next time I try to synch to the other.
It is like an infinite loop. One works, the other doesn't. I run the
snapshot on the other and reinitialize, and it synchs, and then first one
doesn't, etc, etc. etc.
What is wrong now?
Thanks in advance for any help you can give!
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> From your descriptions, I understood that when applying alternate
> synchronization partner, you encouter the error message "The process could
> not drop one or more tables because the tables are being used by other
> publications." Have I understood you? Correct me if I was wrong.
> Based on my knowledge, this is because some entries does not match between
> table sysmergepublications and sysmergesubscriptions on the publisher and
> subscriber. A quick resolution is drop the publication completely and then
> create it again.
> How to manually remove a replication in SQL Server 2000
> http://support.microsoft.com/kb/324401
> I understood drop the publication may have huge business impact to your
> business, as an option, please send the the result of following T-SQL
> statement. I would like to check to see whether I could help further
> --
> select * from sysmerge_subscriptions
> go
> sp_helpserver
> --
> However, please understand that this replication issues tend to be very
> complex and hard to troubleshoot in newsgroups. If you need further
> assistance, I recommend that you open a Support incident with Microsoft
> Customer Service and Support (CSS) so that a dedicated Support Professional
> can work with you in a more timely and efficient manner. If you need any
> help in this regard, please let me know.
> For a complete list of Microsoft Customer Service and Support phone
> numbers, please go to the following address on the World Wide Web:
> <http://support.microsoft.com/directory/overview.asp>
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi,
I found you may encounter the problem described by KB article 814460
FIX: Merge Replication with Alternate Synchronization Partners May Not
Succeed After You Change the Retention Period
http://support.microsoft.com/kb/814460
Please feel free to ask this hotfix by contacting CSS. If you are simply
requesting a hotfix be sent to you and no other support then charges are
usually refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
NOTE that the hotfix is only intended to correct the problem that is
described in this article. Only apply it to systems that are experiencing
this specific problem. This hotfix may receive additional testing.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thank you, Michael. This sounds like the issue I am having.
I looked at the "http://support.microsoft.com/kb/814460" as you suggested.
At the bottom of the page is says that the fix is also included in "MS03-031:
Security patch for SQL Server 2000 Service Pack 3" and gives a link to
download it, so I would prefer to do that rather than place a phone call as I
think it will save time.
However, in the middle of the KB # 814460, it says:
"The problem exists in SQL Server 2000 Service Pack 3, version 8.00.0760. To
fix this version, you must apply SQL Server 2000 Service Pack 3 rollup,
version 8.00.0765."
The versions we are running are 8.00.0760 on both of the affected servers,
so I tried to find the "SQL Server 2000 Service Pack 3 rollup, version
8.00.0765" on the Microsoft web site and cannot. When I search both
downloads and the entire site, I get no hits except back to the KB # 814460.
Can you tell me where I can get "SQL Server 2000 Service Pack 3 rollup,
version 8.00.0765"?
Thank you.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi,
> I found you may encounter the problem described by KB article 814460
> FIX: Merge Replication with Alternate Synchronization Partners May Not
> Succeed After You Change the Retention Period
> http://support.microsoft.com/kb/814460
> Please feel free to ask this hotfix by contacting CSS. If you are simply
> requesting a hotfix be sent to you and no other support then charges are
> usually refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default...S;PHONENUMBERS
> NOTE that the hotfix is only intended to correct the problem that is
> described in this article. Only apply it to systems that are experiencing
> this specific problem. This hotfix may receive additional testing.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
Sorry that I should have clarified it more clearly.
Yes, You could download the patch available in the KB article 821277
MS03-031: Security patch for SQL Server 2000 Service Pack 3
http://support.microsoft.com/kb/821277
Let me know whether this security patch resolves your issue.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael:
Thanks for this reference. I have installed the hot fix on the alternate
synchronization server, but have not yet installed it on the primary server.
That will have to wait a couple of days as that server is very busy right now
with our customer's activity, so I won't be able to check the results on the
error I was getting for at least a couple of days. The hot fix requires Sql
Server to be stopped to install it, and we need to wait until a more
opportune time that has minimal impact on our customers.
I will be sure, however, to let you know the results when I have a chance to
test it. I just wanted you to know I won't be able to do my next testing on
this issue for a little while yet.
Thanks for your continuing help and I'll be sure to let you know what happens.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> Sorry that I should have clarified it more clearly.
> Yes, You could download the patch available in the KB article 821277
> MS03-031: Security patch for SQL Server 2000 Service Pack 3
> http://support.microsoft.com/kb/821277
> Let me know whether this security patch resolves your issue.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
I haven't heard back from you yet and I'm just writing in to see if you
have had an opportunity to perform the hotfix. If you could get back to me
at your earliest convenience, we will be able to go ahead
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael:
Sorry it took me so long to respond back to you. I have been extremely busy
with customer issues and a family crisis of sorts, so I didn't get time unitl
yesterday to do some more testing.
I applied the hotfix to the three servers involved and it eliminated the
error! I was able to synchronize successfully numerous times between all the
machines. I was very pleased with the result.
However, I had my business partner add a another subscriber late yesterday
afternoon. He was able to create a suscription fine to the primary synch
server, but when trying the alternate, he started getting some errors. But
we both had other matters to attend to at that time and did not have a chance
yet to analyze the errors and if it was the way Sql Server is setup on his
machine or some other issue. We will work on that issue today, and may have
some additional questions for you. We did apply the hotfix to the computer
he is using for this test.
Thanks for you continuing help.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> I haven't heard back from you yet and I'm just writing in to see if you
> have had an opportunity to perform the hotfix. If you could get back to me
> at your earliest convenience, we will be able to go ahead
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Michael:
We resolved all the initial errors and have the test alternate synch partner
running well with two subscribers. We have been able to run many, many
successful test syncrhonizations to both servers now without difficulty.
Part of testing plan, however, is to make some schema changes, and I have
run into another problem there. I will start a new thread for this issue.
Thanks for your help.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> I haven't heard back from you yet and I'm just writing in to see if you
> have had an opportunity to perform the hotfix. If you could get back to me
> at your earliest convenience, we will be able to go ahead
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
Subscribe to:
Posts (Atom)