QN Best Practices

Hi everyone,

I wonder if somebody here could recommend a good article about MS Service Broker. I'm looking for some advice and tips in designing applications using SQL Service Broker, mainly QN. For instance, maintenance routines and common faulty scenarios I might find later when my solution is implemented. I have googled for a while but all I can find are recopied examples of QN.

Thanks

Are you interested in SSB best practices in general or specifically QN practices? The (typical) QN use of SSB is very limited (local only, a very limited message exchange pattern, conversations are created by the system for you etc etc). What are your main concerns?

Q186726 Error 80004005 Occurs When Retrieving Data from SQL Server

Does anyone know if this article is valid for connection between servers.
I'm getting an intermittent
|308|80004005|[Microsoft][ODBC_SQL_Server_Driver]Timeout_expired when a
website running on a windows 2000sp3 tries to write data to sql 2000 server
running on windows 2003 server. Could this be the cause? Is there anything
else that could be causing this problem?
Hi
Run sp_who2 and look for blocking processes when this occurs. Intermittent
issues like this point to row blocking issues in the database
One process might update a row, hold the locks, whilst a second process
tries to do the same. The 2nd process waits until process 1 is finished and
usually after 90 seconds, the MDAC stack gives up and returns the error.
Regards
Mike
"shalafi" wrote:

> Does anyone know if this article is valid for connection between servers.
> I'm getting an intermittent
> |308|80004005|[Microsoft][ODBC_SQL_Server_Driver]Timeout_expired when a
> website running on a windows 2000sp3 tries to write data to sql 2000 server
> running on windows 2003 server. Could this be the cause? Is there anything
> else that could be causing this problem?

Q186726 Error 80004005 Occurs When Retrieving Data from SQL Server

Does anyone know if this article is valid for connection between servers.
I'm getting an intermittent
|308|80004005|[Microsoft]& #91;ODBC_SQL_Server_Driver]Timeout_expir
ed whe
n a
website running on a windows 2000sp3 tries to write data to sql 2000 server
running on windows 2003 server. Could this be the cause? Is there anything
else that could be causing this problem?Hi
Run sp_who2 and look for blocking processes when this occurs. Intermittent
issues like this point to row blocking issues in the database
One process might update a row, hold the locks, whilst a second process
tries to do the same. The 2nd process waits until process 1 is finished and
usually after 90 seconds, the MDAC stack gives up and returns the error.
Regards
Mike
"shalafi" wrote:

> Does anyone know if this article is valid for connection between servers.
> I'm getting an intermittent
> |308|80004005|[Microsoft]& #91;ODBC_SQL_Server_Driver]Timeout_expir
ed w
hen a
> website running on a windows 2000sp3 tries to write data to sql 2000 serve
r
> running on windows 2003 server. Could this be the cause? Is there anythin
g
> else that could be causing this problem?

q; using cursors

Hello,
Can anyone direct me a good article that is about why we should try avoiding
using cursors and what are the alternatives?You can Google "cursor" and get many hits in this newsgroup. The short
answer is that in the majority of cases, cursors run slower than
(properly-written) set-level SQL. What you should be searching for are good
SQL texts. I like Joe Celko's SQL Puzzles and Answers. A lot of the
examples in there look like you'd need to use a cursor - but you don't. In
"advanced Transact-SQL for SQL Server 2000", we have chapter on cursors in
the latter part of the book. We show examples of set-based code that
obviated the need for cursors.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:F6EF4FBC-39D3-4D91-BCDD-E5B5F7B93EF7@.microsoft.com...
Hello,
Can anyone direct me a good article that is about why we should try avoiding
using cursors and what are the alternatives?|||JIM.H. wrote:
> Hello,
> Can anyone direct me a good article that is about why we should try avoiding
> using cursors and what are the alternatives?
There are plenty of good reasons to use declarative, set-based SQL code
instead of cursors. The reason most usually given is performance. SQL
Server, like other SQL databases, is designed primarily for
set-at-a-time rather than row-at-a-time operations. Cursors are
typically very slow compared to the alternatives, although performance
obviously varies considerably depending on what you are doing.
Paradoxically, it is also true to say that there is a small class of
problems for which cursors are faster than any set-based solution. In a
well-designed database those situations are uncommon in my experience
and you would be well-advised to get a second opinion if you think you
have come across such a case. Performance and locking issues with
cursors tend to mean they are far less scalable than the set-based
alternatives so even if they work for you today they may not be a
viable solution in future.
Besides performance there are other good reasons to use set-based code
rather than cursors: The declarative code is usually much more concise
and therefore easier to develop, inspect, test and maintain; It's more
likely to be portable to other database platforms; SQL professionals
(good ones using T-SQL anyway) tend to write cursors seldom and so are
likely to be more comfortable and more productive writing set-based
code; Set-based code avoids or tends to show-up some of the logical
anomalies and design problems that can lie hidden and unnoticed in
procedural cursor code.
A legitimate place to use a cursor is for something inherently
procedural (typically admin and control tasks such as managing backups,
working with metadata or invoking other external processes) but in
general you should assume the solution to any data-manipulation problem
will be set-based unless expert analysis proves otherwise. That's why I
would class cursors as a feature for advanced users only. If you find
yourself writing cursors regularly then it's time to re-think what you
are doing or maybe go on a course to learn grown-up SQL. Too many
cursors are written by inexperienced programmers who don't know better
techniques.
As for giving alternatives. There isn't a generic answer to that
question. Loops and cursors are just programming constructs not actual
problems to be solved, so there is no general solution or example to
show how to avoid them. The alternative is simply to use standard
set-based SQL DML: SELECT, UPDATE, DELETE and INSERT. In other words,
statements that operate on SETS of rows rather than one row at a time.
The actual details will depend on exactly what you want to achieve.
If you require hlep with a specific problem then please post more
details as described in:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

q; using cursors

Hello,
Can anyone direct me a good article that is about why we should try avoiding
using cursors and what are the alternatives?You can Google "cursor" and get many hits in this newsgroup. The short
answer is that in the majority of cases, cursors run slower than
(properly-written) set-level SQL. What you should be searching for are good
SQL texts. I like Joe Celko's SQL Puzzles and Answers. A lot of the
examples in there look like you'd need to use a cursor - but you don't. In
"advanced Transact-SQL for SQL Server 2000", we have chapter on cursors in
the latter part of the book. We show examples of set-based code that
obviated the need for cursors.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:F6EF4FBC-39D3-4D91-BCDD-E5B5F7B93EF7@.microsoft.com...
Hello,
Can anyone direct me a good article that is about why we should try avoiding
using cursors and what are the alternatives?|||JIM.H. wrote:
> Hello,
> Can anyone direct me a good article that is about why we should try avoidi
ng
> using cursors and what are the alternatives?
There are plenty of good reasons to use declarative, set-based SQL code
instead of cursors. The reason most usually given is performance. SQL
Server, like other SQL databases, is designed primarily for
set-at-a-time rather than row-at-a-time operations. Cursors are
typically very slow compared to the alternatives, although performance
obviously varies considerably depending on what you are doing.
Paradoxically, it is also true to say that there is a small class of
problems for which cursors are faster than any set-based solution. In a
well-designed database those situations are uncommon in my experience
and you would be well-advised to get a second opinion if you think you
have come across such a case. Performance and locking issues with
cursors tend to mean they are far less scalable than the set-based
alternatives so even if they work for you today they may not be a
viable solution in future.
Besides performance there are other good reasons to use set-based code
rather than cursors: The declarative code is usually much more concise
and therefore easier to develop, inspect, test and maintain; It's more
likely to be portable to other database platforms; SQL professionals
(good ones using T-SQL anyway) tend to write cursors seldom and so are
likely to be more comfortable and more productive writing set-based
code; Set-based code avoids or tends to show-up some of the logical
anomalies and design problems that can lie hidden and unnoticed in
procedural cursor code.
A legitimate place to use a cursor is for something inherently
procedural (typically admin and control tasks such as managing backups,
working with metadata or invoking other external processes) but in
general you should assume the solution to any data-manipulation problem
will be set-based unless expert analysis proves otherwise. That's why I
would class cursors as a feature for advanced users only. If you find
yourself writing cursors regularly then it's time to re-think what you
are doing or maybe go on a course to learn grown-up SQL. Too many
cursors are written by inexperienced programmers who don't know better
techniques.
As for giving alternatives. There isn't a generic answer to that
question. Loops and cursors are just programming constructs not actual
problems to be solved, so there is no general solution or example to
show how to avoid them. The alternative is simply to use standard
set-based SQL DML: SELECT, UPDATE, DELETE and INSERT. In other words,
statements that operate on SETS of rows rather than one row at a time.
The actual details will depend on exactly what you want to achieve.
If you require hlep with a specific problem then please post more
details as described in:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

q; Tracing the deadlock

Hi,
I need to trace deadlock, one of article was mentioning â'QL Server
Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server
7.0, is there any way I can do this in Sql Server 2000?INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
How to identify SQL Server performance issues, by analyzing Profiler output?
http://vyaskn.tripod.com/analyzing_profiler_output.htm
AMB
"JIM.H." wrote:
> Hi,
> I need to trace deadlock, one of article was mentioning â'QL Server
> Profiler's Create
> Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server
> 7.0, is there any way I can do this in Sql Server 2000?
>

q; Tracing the deadlock

Hi,
I need to trace deadlock, one of article was mentioning “QL Server
Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server
7.0, is there any way I can do this in Sql Server 2000?INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/defaul...kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
How to identify SQL Server performance issues, by analyzing Profiler output?
http://vyaskn.tripod.com/analyzing_profiler_output.htm
AMB
"JIM.H." wrote:

> Hi,
> I need to trace deadlock, one of article was mentioning “QL Server
> Profiler's Create
> Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server
> 7.0, is there any way I can do this in Sql Server 2000?
>