alternate colors

Hello all,

I am using sql reproting 2000. I have areport which spans a number of pages...say for every radio station, the report starts with a new page. On every page, I must have alternate row colors(white and gainsboro). It was easy to do that. The issue is, for every new page the report renders for every new radio station, the first row must be white. Say...Radio Station 1, the alternate row colors will continue...i.e white and ganisboro. But for radio Staion 2, the first row should be white again. I hope I have explained it clearly enough.

For alternate row colors, I use either an expression -

IIF(RowNumber("ds_LineUpByFeedDetail") Mod 2, "White", "#dddddd")

OR a VB Function in CODE window.

Thanks......

You can try specifying the name of the radio station group as the scope argument for the RowNumber function, instead of the dataset name, i.e. =IIF(RowNumber(<RadioStationGroup>) Mod 2, "White", "#dddddd").

Alter Table

Warning: The table 'top32_kan_g2_nb' has been created but its maximum row size (14199) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

This is the message I am getting, when i have altered the table definition(i.e. copy the table and pasted in Query Analyzer) using alter.My requirement is to delete some fields from the table at EOD daily. The query is executed but the above warning message appears and the fields I required has been deleted.
Is the above message important or can I use the same method again.As you are removing some of the fields using alter it can work for you provided that it follows the mentioned condition in the warning.

Contact your DBA regarding the warning message .|||

Quote:

Originally Posted by balasach82

Warning: The table 'top32_kan_g2_nb' has been created but its maximum row size (14199) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

This is the message I am getting, when i have altered the table definition(i.e. copy the table and pasted in Query Analyzer) using alter.My requirement is to delete some fields from the table at EOD daily. The query is executed but the above warning message appears and the fields I required has been deleted.
Is the above message important or can I use the same method again.

try doing a

SELECT only, selected, fields, here INTO NEWTABLE from DAILYTABLE..

All tables under partitioned union view get locked - how to reduce?

Hi. We are using a partitioned view across a number of tables. They are
partitioned on a single column. We use the view to insert and update rows in
the underlying tables.
When loading all the rows for table XYZ through the view we find that SQL
Server is creating IX locks against all of the tables even though all the
rows are for one table only as identified by the check constraint on the
partitioned column.
Is there any way to get SQL Server to lock only the table that will be
affected?
McGy
[url]http://mcgy.blogspot.com[/url]"McGy" <anon@.anon.com> wrote in message
news:eIsUGsjhGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Hi. We are using a partitioned view across a number of tables. They are
> partitioned on a single column. We use the view to insert and update rows
> in the underlying tables.
> When loading all the rows for table XYZ through the view we find that SQL
> Server is creating IX locks against all of the tables even though all the
> rows are for one table only as identified by the check constraint on the
> partitioned column.
> Is there any way to get SQL Server to lock only the table that will be
> affected?
>
Partitioned views have serious limitations. You should expect to have to
directly address the underlying tables for many operations. Qu|||Cheers David. I am considering duplicating the load stored procedures, 1 per
table, so as to remove the dependency on the partitioned view. It will make
maintenance a bit more complex but ultimately performance should improve.
Does that sound sensible?
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O4TVf3khGHA.3860@.TK2MSFTNGP02.phx.gbl...
> "McGy" <anon@.anon.com> wrote in message
> news:eIsUGsjhGHA.4368@.TK2MSFTNGP03.phx.gbl...
rows
SQL
the
> Partitioned views have serious limitations. You should expect to have to
> directly address the underlying tables for many operations. Qu
>|||"McGy" <anon@.anon.com> wrote in message
news:%23AUMJNvhGHA.4252@.TK2MSFTNGP04.phx.gbl...
> Cheers David. I am considering duplicating the load stored procedures, 1
> per
> table, so as to remove the dependency on the partitioned view. It will
> make
> maintenance a bit more complex but ultimately performance should improve.
> Does that sound sensible?
>
Yes. And you can always use dynamic SQL to load the table.
David|||The only problem with dynamic SQL is that the stored procedure would be a
nightmare to maintain in that form as it uses lots of variables. Also,
because of the stored procedure's size it takes several seconds to compile.
Presumably we would take that compilation hit each time the dynamic SQL is
constructed and prepared for execution?
McGy
[url]http://mcgy.blogspot.com[/url]
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:#cVWYyzhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> "McGy" <anon@.anon.com> wrote in message
> news:%23AUMJNvhGHA.4252@.TK2MSFTNGP04.phx.gbl...
improve.
> Yes. And you can always use dynamic SQL to load the table.
> David
>|||Not necessarily; if you use sp_executeSQL with variables you are
essentially creating a parameterized SQL statement which increases the
likelihood that the execution plan will be reused.
This issue is interesting to me; we use partioned views as well.
However all of our inserts are done with bulk load methods, so locking
hasn't been a problem (yet). I understand the SQL 2005's partitoned
tables are much better than the the views; yet another reason to
consider upgrading.
Stu
McGy wrote:
> The only problem with dynamic SQL is that the stored procedure would be a
> nightmare to maintain in that form as it uses lots of variables. Also,
> because of the stored procedure's size it takes several seconds to compile
.
> Presumably we would take that compilation hit each time the dynamic SQL is
> constructed and prepared for execution?
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:#cVWYyzhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> improve.|||Hmmm. Interesting point on the sp_executesql. I'll take a look.
We use bulk insert too; but we also need to apply a bunch of rules to the
records that get loaded. They get loaded twice: once in to a transaction
table which are all inserts - the other in to an aggregation table that
applies certain rules depending upon what type the transaction is.
Just thought of a problem on the dynamic SQL front. The stored procedure
would be well in excess of the 8000 char limit on variable sizes. Is there a
way to work around that?
McGy
[url]http://mcgy.blogspot.com[/url]
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1149427023.769413.5440@.i39g2000cwa.googlegroups.com...
> Not necessarily; if you use sp_executeSQL with variables you are
> essentially creating a parameterized SQL statement which increases the
> likelihood that the execution plan will be reused.
> This issue is interesting to me; we use partioned views as well.
> However all of our inserts are done with bulk load methods, so locking
> hasn't been a problem (yet). I understand the SQL 2005's partitoned
> tables are much better than the the views; yet another reason to
> consider upgrading.
> Stu
> McGy wrote:
a
compile.
is
procedures, 1
will
>|||McGy (anon@.anon.com) writes:
> Hi. We are using a partitioned view across a number of tables. They are
> partitioned on a single column. We use the view to insert and update
> rows in the underlying tables.
> When loading all the rows for table XYZ through the view we find that SQL
> Server is creating IX locks against all of the tables even though all the
> rows are for one table only as identified by the check constraint on the
> partitioned column.
> Is there any way to get SQL Server to lock only the table that will be
> affected?
Are the intent locks causing any real problems? I ran a quick test, and
I was not able detect any locking problems, but I might have missed
something. (I was only testing concurrent SELECT statements.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland. I am not actually sure if those IX locks are causing a problem.
What is the impact of an IX lock? When we were loading multiple files in
production we noticed that some were being blocked waiting for others to
complete. We assumed it was because of the view creating locks against all
the tables - but perhaps not?
McGy
[url]http://mcgy.blogspot.com[/url]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97D9732464F2Yazorman@.127.0.0.1...
> McGy (anon@.anon.com) writes:
> Are the intent locks causing any real problems? I ran a quick test, and
> I was not able detect any locking problems, but I might have missed
> something. (I was only testing concurrent SELECT statements.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||McGy (anon@.anon.com) writes:
> Hi Erland. I am not actually sure if those IX locks are causing a problem.
> What is the impact of an IX lock? When we were loading multiple files in
> production we noticed that some were being blocked waiting for others to
> complete. We assumed it was because of the view creating locks against all
> the tables - but perhaps not?
The purpose of an intent lock is to tell "I am working here, so don't
try to take all this place for your own".
If a transaction updates a few rows in a table it acquires an X lock
on these rows, and also an IX lock on the table. This prevents other
processes from getting an X-lock on the table. Or put into other words,
the process that wants an exclusive lock on the table, does not need to
check if any rows are currently locked.
In case of the partitioned view the IX locks are there to prevent other
processes from acquire exclusive locks on the other tables, as there
could suddenly appear a row that should be inserted the other tables.
SQL Server cannot conclude that all data that is being inserted goes
only into one table in the view.
So, yes, if you have different insert processes in parallel, they will
block each other. And a process what would try "SELECT COUNT(*) FROM
pview" or anything else that requires a scan would also probably be
blocked, even with a condition that filtered out the table being
loaded.
If you want to run parallel loads at maximum speeds, you will probably
have to load into the underlying tables directly.
I will have to admit that I had read up in Books Online on what an
intent lock really is.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

All schedulers on Node 0 appear deadlocked due to a large number o

Hi,
I've noticed the following error message in the event viewer being reported
by SQL Server for a site that we've had online for several months without
incident. However, every so often now, SQL server is locking up and it seems
to be occurring after this error message:
All schedulers on Node 0 appear deadlocked due to a large number of worker
threads waiting on MSSEARCH
I can't seem to find any information on the specifics of what this error
means (or even the error number) or any recommendations for resolving it.
I'm presuming that this could be caused by a code issue, however, since there
Full Text Search is being called by itself (in the application) when it is
used, I don't see how this could be causing a deadlock in the traditional
sense. It also is weird that this seems to happen all at once, go away when
the server is restarted and then come back after a period of time (usually
during a period of low usage, like at night or over a weekend) ...
Thanks,
JeremyHello Jeremy,
Based on my experience, this problem might be a known issue fixed in SP1.
some of xp(name started with sql_OA) in SQL has CoInitialize leak that
causes FTS related issue. Full Text threads all waiting to be signaled,
with no apparent thread to signal them, causing a SQL Server Scheduler to
become hung.
Do you have SQL 2005 sp2 installed? If not, please apply SP2 to see if the
issue is resolved. If you have any update, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Peter,
We do have SP2 installed, already ...
Thanks!
""Peter Yang[MSFT]"" wrote:
> Hello Jeremy,
> Based on my experience, this problem might be a known issue fixed in SP1.
> some of xp(name started with sql_OA) in SQL has CoInitialize leak that
> causes FTS related issue. Full Text threads all waiting to be signaled,
> with no apparent thread to signal them, causing a SQL Server Scheduler to
> become hung.
> Do you have SQL 2005 sp2 installed? If not, please apply SP2 to see if the
> issue is resolved. If you have any update, please feel free to let's know.
> Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Jeremy,
Since you have SP2 installed, I suspect the problem might be caused by your
code related to FTS. Since FTS is deadlock due to this error, SQL Server
sessions waiting on MSSEARCH waittype, and some unresponsiveness in SQL
Server itself.
You need to restart FTS and/or SQL service to work around the problem. If
the issue persists, a system reboot is necessary. If you'd like to know the
root cause of the problem, you may need to analyze memory dumps, this work
has to be done by contacting Microsoft Product Support Services. Therefore,
we probably will not be able to resolve the issue through the newsgroups.
I recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Why wouldn't the deadlock manager be resolving the issue, if it is just a
deadlock issue?
Thanks,
Jeremy
""Peter Yang[MSFT]"" wrote:
> Hello Jeremy,
> Since you have SP2 installed, I suspect the problem might be caused by your
> code related to FTS. Since FTS is deadlock due to this error, SQL Server
> sessions waiting on MSSEARCH waittype, and some unresponsiveness in SQL
> Server itself.
> You need to restart FTS and/or SQL service to work around the problem. If
> the issue persists, a system reboot is necessary. If you'd like to know the
> root cause of the problem, you may need to analyze memory dumps, this work
> has to be done by contacting Microsoft Product Support Services. Therefore,
> we probably will not be able to resolve the issue through the newsgroups.
> I recommend that you open a Support incident with Microsoft Product Support
> Services so that a dedicated Support Professional can assist with this
> case. If you need any help in this regard, please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> When responding to posts, please "Reply to Group" via your
> newsreader so that others may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Jeremy,
This is not a simple deadlock situation that could be resolved by SQL
engine itself.
When the lock monitor initiates deadlock search for a particular thread, it
identifies the resource on which the thread is waiting. The lock monitor
then finds the owner(s) for that particular resource and recursively
continues the deadlock search for those threads until it finds a cycle. A
cycle identified in this manner forms a deadlock.
However, the deadlock is caused by FTS which is not a component inside SQL
engine. Therefore, it's more like a distributed deadlock that could not be
resolved by SQL engine itself.
Please understand above is just a suspect and you need to contact MS PSS to
analyze memory dump so taht you may get more clues on this issue. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Peter,
Thanks for that clarification. Out of curiosity, since this appears to be
able to hang FTE for all databases (and the SQL server itself, in entirely),
do you know how hosting providers (with multiple clients each writing
uncontrollable queries) handle this issue?
Is there a particularly good way to detect this problem automatically and
restart FTE (since the process still looks to be in a normal state, even
while it is actually hung ...)
Thanks,
Jeremy
""Peter Yang[MSFT]"" wrote:
> Hello Jeremy,
> This is not a simple deadlock situation that could be resolved by SQL
> engine itself.
> When the lock monitor initiates deadlock search for a particular thread, it
> identifies the resource on which the thread is waiting. The lock monitor
> then finds the owner(s) for that particular resource and recursively
> continues the deadlock search for those threads until it finds a cycle. A
> cycle identified in this manner forms a deadlock.
> However, the deadlock is caused by FTS which is not a component inside SQL
> engine. Therefore, it's more like a distributed deadlock that could not be
> resolved by SQL engine itself.
> Please understand above is just a suspect and you need to contact MS PSS to
> analyze memory dump so taht you may get more clues on this issue. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> When responding to posts, please "Reply to Group" via your
> newsreader so that others may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Jeremy,
Thank you for your reply.
When the issue occurs, you should be able to find Sysprocesses shows lots
of queries waiting on MSSEARCH. To temporarily work around the issue, you
may want to develope some monitor tools to check the status of
Sysprocesses. If you find too many Sysprocesses are waiting for MSSEARCH,
you may try to restart FTS service to see if the issue is not solved. If
not, it should notify admin to manually do some operations.
As I mention, to find the root cause of the issue, it's suggested that you
contact MS PSS for dump analysis. If you have any further qusetions or
concerns on this, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.
If this response time does not meet your needs, please contact CSS for more
immediate assistance:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
<http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone>.
Feedback on your service and satisfaction can be posted to:
from the web interface: Partner Feedback
from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Jeremy and Peter,
Any progress on this issue? One of my colleagues just stumbled upon
the same bug/feature. Should we open contact PSS?
Thanks!
On Mar 26, 5:54 am, pet...@.online.microsoft.com ("Peter Yang[MSFT]")
wrote:
> Hello Jeremy,
> Thank you for your reply.
> When the issue occurs, you should be able to find Sysprocesses shows lots
> of querieswaitingonMSSEARCH. To temporarily work around the issue, you
> may want to develope some monitor tools to check the status of
> Sysprocesses. If you find too many Sysprocesses arewaitingforMSSEARCH,
> you may try to restart FTS service to see if the issue is not solved. If
> not, it should notify admin to manually do some operations.
> As I mention, to find the root cause of the issue, it's suggested that you
> contact MS PSS for dump analysis. If you have any further qusetions or
> concerns on this, please feel free to let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> =====================================================> Please note that the newsgroups are staffed weekdays with a goal to provide
> ONE BUSINESS DAY RESPONSE toallposts.
> If this response time does not meet your needs, please contact CSS for more
> immediate assistance:http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone...
> <http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone>.
> Feedback on your service and satisfaction can be posted to:
> from the web interface: Partner Feedback
> from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your
> newsreader so that others may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.