Books Online Updates: are they cumulative?

Does anyone know, please, if BOL 2005 updates are cumulative (like
service packs) or incremental? On a fresh re-install do I need to
install the April update and then the July update, or will the July
update include what's in the April update?They are full nlown installations, you don=B4t need to install them all
incrementally.
HTH, jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Thanks, Jens

Books Online Updates: are they cumulative?

Does anyone know, please, if BOL 2005 updates are cumulative (like
service packs) or incremental? On a fresh re-install do I need to
install the April update and then the July update, or will the July
update include what's in the April update?They are full nlown installations, you don=B4t need to install them all
incrementally.
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks, Jens

BOL update

Hi!

When may we expect SP1 BOL update? It's hard to update job scripts which is used tokens since the sintax is changed and SP1 CTP BOL updates is not reflecting that!

? Alexey, The BOL update is expected to be online "very soon" but I can't give a specific date and time. I am looking forward to having access to a copy too. Andrew Watt [MVP] <Alexey Shirshov@.discussions.microsoft.com> wrote in message news:e6649386-99b2-43e1-adc8-bc54caef9801@.discussions.microsoft.com... Hi! When may we expect SP1 BOL update? It's hard to update job scripts which is used tokens since the sintax is changed and SP1 CTP BOL updates is not reflecting that!|||? On http://msdn.microsoft.com/sql/ there is a link to what is supposed to be April BOL. Unfortunately it currently takes you to the December BOL. Hopefully it will be fixed soon. Andrew Watt [MVP] "Andrew Watt [MVP]" <SVGDeveloper@.aol.com> wrote in message news:uHJi62RZGHA.1472@.TK2MSFTNGSA01.privatenews.microsoft.com... Alexey, The BOL update is expected to be online "very soon" but I can't give a specific date and time. I am looking forward to having access to a copy too. Andrew Watt [MVP] <Alexey Shirshov@.discussions.microsoft.com> wrote in message news:e6649386-99b2-43e1-adc8-bc54caef9801@.discussions.microsoft.com... Hi! When may we expect SP1 BOL update? It's hard to update job scripts which is used tokens since the sintax is changed and SP1 CTP BOL updates is not reflecting that!

Blocking with RECEIVE and SqlDataReader for real-time updates

I have this scenario which is working fine, but would like to know if others have tried it or can recommend a better approach. Below is a brief description, but code should fully explain:

A process updates a SQL Server table via a stored proc, which in turn writes information to a service broker target queue. In my client/server architecture, I would like clients to see this new information as soon as it gets written to the target queue. To do this, I have a WCF service that calls a stored procedure; the stored proc contains an infinite loop which has a RECEIVE statement with an infinite timeout. Once RECEIVE reads data from the SB queue, data is retrieved via a SqlDataReader and results are sent to clients via a pub/sub. I then wait for more rows until RECEIVE unblocks and so on.

Stroed Proc

Code Snippet

-- ...
WHILE 1 = 1
BEGIN
-- Param declarations

...

BEGIN TRANSACTION;
WAITFOR
(
-- Blocks while TargetQueue is empty
RECEIVE
TOP(1)
@.conversation_handle = conversation_handle,
@.message_type_name = message_type_name,
@.conversation_group_id = conversation_group_id,
@.message_body =
CASE
WHEN validation = 'X' THEN CAST(message_body AS XML)
ELSE CAST(N'' AS XML)
END
FROM [dbo].[TargetQueue] -- No time out!
)

-- Handle errors
...

-- Return received information. After this statement is executed,
-- reader.NextResult unblocks and reader.Read() can read these
-- new values
SELECT 'Conversation Group Id' = @.conversation_group_id,
'Conversation Handle' = @.conversation_handle,
'Message Type Name' = @.message_type_name,
'Message Body' = @.message_body ;

COMMIT TRANSACTION

END -- WHILE

C# Code

Code Snippet

// Create a SqlCommand, and initialize to execute above stored proc
// (set command time to zero!)
...

// ExecuteReader blocks until RECEIVE reads data from the target queue
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Begin an infinite loop
while (true)
{
// Process data retrieved by RECEIVE
while (reader.Read())
{
Trace.WriteLine("Conversation Group Id :" + reader.GetGuid(0) +
"Conversation Handle: " + reader.GetGuid(1) +
"Message Type Name : " + reader.GetString(2) +
"Message Body : " + reader.GetString(3));
// Send data via pub/sub
...
}

// Blocks until stored procedure returns another select statement
// i.e., blobks until RECEIVE unblocks and retrieves more data from queue
reader.NextResult();
} // while
}

There isn't anything wrong with your approach from what you show, but I do have a few questions:

Who ends the dialogs and how? The RECEIVE loop has to be prepared to process the with EndDialog and Error messages, otherwise you will leak conversations and your database will grow indefinetly. Also the stored procedure you mention, how does it find the dialog to send on and how does it end it?

Are the notifications important? Can you afford to loose one? You will lose notifications because WCF is cannot guarantee reliable delivery.

What scale are you talking about? One single RECEIVE loop with TOP(1) will have problems if you expect thousands of notifications per second.

|||

Remus,

Thanks for your thoughtful questions. My comments are below:

>What scale are you talking about? One single RECEIVE loop with TOP(1) will have problems if you expect thousands of notifications per second

Currently there will be less than 100 (One hundred) notifications per day. It is likely however, that these notifications will come in bursts as the process that ultimately populates the target queue (via uspSendMessage below) kicks in multiple times during the day. I can remove TOP(1) if necessary.

>Are the notifications important? Can you afford to loose one? You will lose notifications because WCF is cannot guarantee reliable delivery

Yes notifications to clients are very important as they will be used for trade position management. With WCF I am planning on using MSMQ to gurarantee delivery to clients. Is this what you meant?

> Who ends the dialogs and how? how does the stored proc find the dialog to send on and how does it end it?

I have two stored proc. uspSendMessage which updates a database table with new information, also does BEGIN DIALOG, then SEND ON CONVERSATION, then END CONVERSATION and COMMIT TRAN. uspReceiveMessage on the other hand does a RECEIVE (does not specify a conversion group id order to retreive all data), SELECT to select and send data to the SqlDataReader, END CONVERSATION, and then COMMIT TRAN.

Both stored proc from my test harness are shown below:

Code Snippet

CREATE PROCEDURE uspSendMessage

-- Add the parameters for the stored procedure here

@.CompanyName nvarchar(40),

@.PhoneName nvarchar(24)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON

begin try

-- Begin a transaction.

BEGIN TRANSACTION;

-- Insert data

insert into Shippers (CompanyName, Phone) values(@.CompanyName, @.PhoneName)

-- Create the message.

DECLARE @.message XML

SET @.message = N'<Shipper><CompanyName>' + @.CompanyName + '</CompanyName><Phone>' + @.PhoneName + '</Phone></Shipper>'

-- Declare a variable to hold the conversation handle, and then start the conversation

DECLARE @.conversationHandle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE InitiatorService

TO SERVICE 'TargetService'

ON CONTRACT NewShipperContract

WITH ENCRYPTION = OFF;

-- Send the message on the dialog.

SEND ON CONVERSATION @.conversationHandle MESSAGE TYPE NewShipperMessage (@.message);

-- End the conversation.

END CONVERSATION @.conversationHandle

COMMIT TRANSACTION

end try

begin catch

select error_number() as ErrorNum, error_message() as ErrorMSg

-- Test XACT_STATE

IF (XACT_STATE()) = -1 -- Uncmmitable transaction state

BEGIN

ROLLBACK TRANSACTION;

END;

IF (XACT_STATE()) = 1 -- Tranaction active

BEGIN

COMMIT TRANSACTION;

END;

end catch

END

Code Snippet

CREATE PROCEDURE uspReceiveMessage

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

WHILE 1 = 1

BEGIN

DECLARE @.conversation_handle UNIQUEIDENTIFIER,

@.conversation_group_id UNIQUEIDENTIFIER,

@.message_body XML,

@.message_type_name NVARCHAR(128);

BEGIN TRANSACTION;

WAITFOR

(

RECEIVE TOP(1)

@.conversation_handle = conversation_handle,

@.message_type_name = message_type_name,

@.conversation_group_id = conversation_group_id,

@.message_body =

CASE

WHEN validation = 'X' THEN CAST(message_body AS XML)

ELSE CAST(N'<none/>' AS XML)

END

FROM [dbo].[TargetQueue]

)

-- Handle error

IF @.@.ERROR <> 0

BEGIN

COMMIT TRANSACTION

BREAK

END

-- Show received information

SELECT 'Conversation Group Id' = @.conversation_group_id,

'Conversation Handle' = @.conversation_handle,

'Message Type Name' = @.message_type_name,

'Message Body' = @.message_body ;

-- If the message_type_name indicates that the message is an error

-- or an end dialog message, end the conversation.

IF @.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

OR @.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'

BEGIN

END CONVERSATION @.conversation_handle ;

END ;

COMMIT TRANSACTION

END -- WHILE

END

|||

Yazan Diranieh wrote:

>What scale are you talking about? One single RECEIVE loop with TOP(1) will have problems if you expect thousands of notifications per second

Currently there will be less than 100 (One hundred) notifications per day. It is likely however, that these notifications will come in bursts as the process that ultimately populates the target queue (via uspSendMessage below) kicks in multiple times during the day. I can remove TOP(1) if necessary.

If you will hit scalabilty problems removing TOP(1) will not help, as you are sending only one message per dialog anyway, so RECEIVE can only return one message at a time. See http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx and http://blogs.msdn.com/remusrusanu/archive/2007/05/02/recycling-conversations.aspx on how to send more than one message per dialog. I wouldn't focus on this at the moment though.

Yazan Diranieh wrote:

>Are the notifications important? Can you afford to loose one? You will lose notifications because WCF is cannot guarantee reliable delivery

Yes notifications to clients are very important as they will be used for trade position management. With WCF I am planning on using MSMQ to gurarantee delivery to clients. Is this what you meant?

As it is now, if the process crashes after the RECEIVE was commited but before the WCF send occured (or reached the outgoing MSMQ queue), your notification is lost. You must use a distributed transaction and enroll both the SqlCommand.ExecuteReader and the WCF send in the same transaction context. This also means you cannot use one infintely executing batch, but instead loop in CLR code and call one RECEIVE at a time.

Yazan Diranieh wrote:

> Who ends the dialogs and how? how does the stored proc find the dialog to send on and how does it end it?

I have two stored proc. uspSendMessage which updates a database table with new information, also does BEGIN DIALOG, then SEND ON CONVERSATION, then END CONVERSATION and COMMIT TRAN. uspReceiveMessage on the other hand does a RECEIVE (does not specify a conversion group id order to retreive all data), SELECT to select and send data to the SqlDataReader, END CONVERSATION, and then COMMIT TRAN.

You are doing Fire-and-Forget, this will get you into trouble, see http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx. Try to end the conversation from the target side first and have the sender end its side as a response to the target's EndDialog message.

|||Very informative. Thanks very much.

Blocking updates of a table

Hi,
I've been trying to design a way for me to issue a transaction that:

Block all inserts on a table when row X has a certain value (call it A)
Add a row to the table with row X containing A Add rows to another table Unblock inserts Commit transaction

Is this possible? Can anyone give me some pointers as to what to do?
Thanks in advance!

For (1) and (2)
While inserting the row that has a certain value (A), you can request a TABLOCKX. This will acquire X lock on the table and there by block other inserts by other concurrent transactions.

For (3): Is it like any other insert to another table? In that case, nothing special needs to be done

Thanks
Sunil Agarwal

|||For 3 that is what I meant.
For 1 and 2, it sounds like TABLOCKX locks the whole table exclusively (from what I can find in Books Online) - have I read it correctly? Is there any way to block other transactions from inserting rows (while the first transaction is not yet commited) when a table key column (A in my original post) is a certain value (X in my original post) - rather than just locking the whole table?
Thanks for your help!
|||For 1 and 2, it sounds like TABLOCKX locks the whole table exclusively (from what I can find in Books Online) - have I read it correctly

sunil> yes

Is there any way to block other transactions from inserting rows (while the first transaction is not yet commited) when a table key column (A in my original post) is a certain value (X in my original post) - rather than just locking the whole table?

sunila> if you only want to block inserts by other transactions under the condition you have mentioned but allow updates/selects, then there is no way.
thanks,|||I assume you only want to block Inserts but not Updates, Deletes and SELECTS.

You could try using an Insert trigger that would rollback the other transactions until a condition has been achieved.

Blocking SPID ... no apparent reason

Hi,
I hava a JAVA application that updates a SQL2000 (SP3a)database.
The application handles different types of "jobs" which effectively update the DB.

One job in particular appears to block all subsequent jobs. It comprises of a large amount of inserts/updates in a single transaction. This is necessary as it is an "all or nothing" scenario - so we cannot break the transaction into smaller ones. The transaction appears to succeed as it reaches the COMMIT TRAN statement without error.
However the records do not get written to the database.
EM indicates a large number of locks held on the tables accessed by the transaction and these do not get released.

Using the SP sp_blocker_pss80, the blocking SPID has a waittime of 0 and a waittype of 0x0000 - the lastwaittype is WRITELOG and its status is AWAITING COMMAND

I am using MS SQLSERVER JDBC Driver SP2 (considering using jTDS)

I have tried
- increasing Transaction Log size
- Moving Transaction Log to a separate Disk
- Reducing Isolation Mode to Read Uncommitted
- Set AutoCOMMIT to true
- set Close Cursor on COMMIT
- set SelectMethod to Direct - (we use Cursor by default)

None of these have succeeded in fixing the issue.

The job will succeed if it is the first/only job to access the database.
But if another job precedes it - then the blocking occurs.
I have verified that the preceding job only holds shared dataabase locks
before the blocking job is run.

Each job will use its own JDBC connections to access the database for reading
purposes, but all of the writing goes through the blocking SPID.

Any ideas?
Thanks, LiamIf you close the JDBC connection (when the transaction is complete), does that fix the problem?

-PatP|||I wonder if it is not a deadlock situation. By default deadlocks are not logged. Try running the following:

dbcc traceon (-1, 1205)

then run the big update process. This should start logging deadlock information to the SQL Errorlog.

Except for the data not being written at the end, you have described exactly what locking is designed to do. While someone is writing data to the database, no one else can read that data until they are done. You can potentially try to reduce table locks by checking that the update process is using an appropriate index. Try running the Index Tuning Wizard, and see if it makes any suggestions. This should, of course, be done on a test box first.|||Hi,
Thanks for replies.
1) Connections cannot be terminated after batch jobs complete due to nature of the application

2) DBCC TRACEON resulted in a number of the following entries in the SQL Log

Starting deadlock search 5306
Target Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:57 ECID:0 Ec:(0x484D9510) Value:0x4b0eb320
Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:57 ECID:0 Ec:(0x484D9510) Value:0x4b0eb320
Node:2 ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x42ECD510) Value:0x4b103b00
End deadlock search 5306 ... a deadlock was not found.

3) Index tuner - havent tackled yet|||I thought 1205 gave you information about deadlocks in progress. I was apparently wrong. Try this:

dbcc traceoff (-1, 1205)
go
dbcc traceon (-1, 1204)

Blocking problem

SQL2K, sp3
Active Server Pages front-end

I am having a blocking problem with a spid executing a stored
procedure that does no updates. When I look at the locks the blocking
spid has, some of them have lock mode "IS". Looking in BOL, I see "S"
is shared, but I don't see "IS". Can anyone tell me what lock mode
"IS" is?

And how could a stored procedure that does no updates be guilty of
blocking?
There are no cursors in it.

Thanks![posted and mailed, please reply in news]

Hal (hforman1@.cfl.rr.com) writes:
> I am having a blocking problem with a spid executing a stored
> procedure that does no updates. When I look at the locks the blocking
> spid has, some of them have lock mode "IS". Looking in BOL, I see "S"
> is shared, but I don't see "IS". Can anyone tell me what lock mode
> "IS" is?

Intent Shared. If memory serves, if a process has a lock on row level,
it will also take out an intent lock on table level, at least under
some circumstances.

> And how could a stored procedure that does no updates be guilty of
> blocking?

It could block a writer that wishes to update one of the rows that the
reader locks.

To get a better hold of the locking situation, it's probably better to
lock at the blocking processes, and see what lock it is blocked on. I
have a routine on my web site that is conventient for this kind of thing.
You get all active processes (and if a process holds a lock it's active
by definition), their locks and their current statements. Spot the blocked
processes and lock for locks with status WAIY. You find it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

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

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