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 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)

Bitwise NOT Operation

I was looking into some of the System stored procedures like sp_denylogin, sp_grantLogin etc. I found bit wise operations like

update master.dbo.sysxlogins set xstatus = (xstatus & ~1) | 2, xdate2 = getdate()
where name = @.loginame and srvid IS NULL

How does the bitwise NOT(~) works with different datatypes like INT, SMALLINT and TINYINT?

Look at the following sample code and result. I just wanted to understand how it works with int and smallint

declare @.a int
declare @.b smallint
declare @.c tinyint

select @.a = 1, @.b = 1, @.c = 1

select @.a=~@.a, @.b= ~@.b, @.c= ~@.c

select @.a, @.b, @.c

Result
~~~~~
-2 -2 254

Thanks in advance
GnanaInteresting question! In a former life, I worked on addressable converter interfaces, and much was done with bitmaps...so I played a bit also...

Note the results if you execute the following select in your example:
select @.a, @.b, @.c, cast(@.a AS varbinary), cast(@.b as varbinary), cast (@.c as varbinary)

Result
~~~~~
-2 -2 254 0xFFFFFFFE 0xFFFE 0xFE

Now you get to see the binary results...which still does not answer your question, but I betcha (as I head back to BOL) that the answer lies in there as the way the sign bit is interpreted in the larger two data types.

Betcha that the tinyint data type does not have a sign bit.

yep...that's it...from BOL:tinyint

Integer data from 0 through 255. Storage size is 1 byte.

So the issue is not so much that the bitwise operation actually WORKS any differently...what you are seeing is just the PRESENTATION differences between data types (the final select assumes you want the data interpreted in it's numeric value, not the bit-representation of the data in the variable...so you get a negative number because you have set the sign bit on with the bitwise operation you used on the larger data types (that have sign bits).

In other words, the bitwise operator works exactly the same on all three data types, it just LOOKS like it works differently when you select it back to see the results (without casting, that is).|||Nice explanation, Paul.|||Yeah, thanks! I learned something too (something I usually try to avoid at all costs)...though we don't use much bit manipulation here (at least not out where everyone can see ;) ).

Bit type parameter for stored procedure

I am trying to supply a bit type parameter to a stored procedure. This is used to update a Bit type field in a table. The field is called PDI

The syntax I am trying to use is:

MyStoredProcedure.Parameters.Add(New SqlParameter("@.Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@.pdi").value = -1

When I do my ExecuteNonQuery I get error 8114

What am I doing wrong?The bit data type can hold one of the following values: 1, 0, or NULL. You cannot store a -1 in a bit column.

Terri|||Ok thanks for that. It will indeed accept 1 or 0

I now need to use the value from a CheckBox type control on my Form. So now
My Code reads:

MyStoredProcedure.Parameters.Add(New SqlParameter("@.Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@.pdi").value = pdi.value

This generates the Error: "String was not recognised as a valid boolean."

What is the syntax for casting to a bit type value?|||You will likely need to convert your string value to a boolean value, using the ToBoolean method, something like this:

MyStoredProcedure.Parameters("@.pdi").value = System.Convert.ToBoolean(pdi.value)

See this article with handy chart for reference:Accessing SQL Server Data in C# with ADO.NET.

Terri|||No that didn't work either.

Just for the hell of it I decided to put the value of the PDI control into a span control like this:

message.innerhtml=pdi.value

I expected to see True or False or 1 or 0 or something. What I got was S00817

What is that!! I'm obviosly going the wrong way about extracting the value from a CheckBox control|||We might need to see more of your code.

Are you using a CheckBox or a CheckBoxList?

If a CheckBox, then it would probably be pdi.Checked, which already returns a boolean value.

I am sorry to be giving you the answer in pieces.

Terri|||Hi,

You need to use:

MyStoredProcedure.Parameters.Add(New SqlParameter("@.Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@.pdi").value = pdi.checked

or unchecked.

HTH

JB|||Yes that fixed it.

MyStoredProcedure.Parameters("@.pdi").value = pdi.checked

Thanks for your help|||

my check box is in the datagrid. I used this line. it have error.

comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.p_PDA", System.Data.SqlDbType.Bit, "PDA")).Value = CType(e.Item.FindControl("ckPDA"), CheckBox).Checked

Here is my html code. I tried to assign the check box value to byte. It did not work, too. Thanks.

<asp:datagrid id="dtgPhysician" style="Z-INDEX: 102; LEFT: 8px; POSITION: absolute; TOP: 88px" runat="server" Font-Names="Arial" OnCancelCommand="dtgPhysician_Cancel" OnEditCommand="dtgPhysician_Edit" Width="985px" AutoGenerateColumns="False" PagerStyle-Mode="NumericPages" DataKeyField="License" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" ShowHeader="true" EditItemStyle-BackColor="#eeeeee" BackColor="White" OnUpdateCommand="dtgPhysician_Update">
<SelectedItemStyle Font-Bold="True" ForeColor="#CCFF99" BackColor="#009999"></SelectedItemStyle>
<EditItemStyle BackColor="#EEEEEE"></EditItemStyle>
<AlternatingItemStyle BorderColor="#C0C0FF"></AlternatingItemStyle>
<ItemStyle ForeColor="#003399" BackColor="White"></ItemStyle>
<HeaderStyle Font-Size="Medium" Font-Names="Arial Black" Font-Bold="True" HorizontalAlign="Center" ForeColor="#CCCCFF" VerticalAlign="Middle" BackColor="#003399"></HeaderStyle>
<FooterStyle ForeColor="#003399" BackColor="#99CCCC"></FooterStyle>
<Columns>
<asp:EditCommandColumn ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" EditText="Edit Info"></asp:EditCommandColumn>
<asp:BoundColumn DataField="PName" SortExpression="PNAME asc" ReadOnly="True" HeaderText="Name" HeaderStyle-Font-Name="arial">
<HeaderStyle Font-Names="arial"></HeaderStyle>
</asp:BoundColumn>
<asp:TemplateColumn HeaderText="Status">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "Status") %>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList runat="server" id="lstStatus" DataTextField="Status" SelectedIndex ='<%# GetSelIndex(Container.DataItem("Status")) %>'>
<asp:ListItem Value="0">DOC</asp:ListItem>
<asp:ListItem Value="1">POE</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Group">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "PGroup") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtGroup" Width="100%" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PGroup") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Email">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "Email") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtEmail" Width="100%" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "Email") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Logon">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "LogonName") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtLogon" Width="100%" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "LogonName") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="PDA">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "PDA") %>
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox id="ckPDA" runat="server" Checked ='<%# DataBinder.Eval(Container.DataItem, "PDA") %>'>
</asp:CheckBox>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Training">
<EditItemTemplate>
<asp:DropDownList runat="server" id="dpTraining">
<asp:ListItem Value="0">Init Training</asp:ListItem>
<asp:ListItem Value="1">Retraining</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Date">
<EditItemTemplate>
<asp:textbox runat="server" id="txtDate"></asp:textbox>
<asp:CompareValidator id="vrDate" runat="server" ErrorMessage="*date*" ControlToValidate="txtDate" Type="Date" Operator="DataTypeCheck"></asp:CompareValidator>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField="PID" SortExpression="PID" ReadOnly="True" HeaderText="Physician ID">
<HeaderStyle Font-Names="arial"></HeaderStyle>
</asp:BoundColumn>
</Columns>
<PagerStyle HorizontalAlign="Left" ForeColor="#003399" Position="Top" BackColor="#99CCCC" Mode="NumericPages"></PagerStyle>
</asp:datagrid>

|||

yan19454:

I used this line. it have error.

comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.p_PDA", System.Data.SqlDbType.Bit, "PDA")).Value = CType(e.Item.FindControl("ckPDA"), CheckBox).Checked

Please please please always post the error encountered, not just the fact that you have an error.