Problems in SQL, Updating many rows at once

Howdy all.
I have a person table. The relavent columns are:
PersonID INT
LastName VARCHAR(30)
LastNameSndx CHAR(4)
I'm almost embarressed to ask considering my SQL expertise, but...
I need to take the SOUNDEX of the LastName and put that value in the LastNameSndx column. So I fire off this SQL:
Update Person Set LastNameSndx = SOUNDEX(LastName);
It takes forever. I start tweeking the SQL to commit every 500 records or so. Still takes a long time. I then notice what is happening. The SQL is taking the SOUNDEX of the LastName of the first record, apply it to ALL the records, then taking the SOUNDEX of the LastName of the second records, then updating it to ALL the records, etc.
This is not SQL as I understand it.
What am I doing wrong here?Hi!
> It takes forever. I start tweeking the SQL to commit every 500 records or
so. Still takes a long time. I then notice what is happening. The SQL is
taking the SOUNDEX of the LastName of the first record, apply it to ALL the
records, then taking the SOUNDEX of the LastName of the second records, then
updating it to ALL the records, etc.
>
How did you notice this? This is really strange, I've never heard of
something like this.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||I attempted the same thing and I am not seeing the same behavior. Not sure
what is happening in your case.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Problems in SQL, Updating many rows at once

Howdy all.
I have a person table. The relavent columns are:
PersonID INT
LastName VARCHAR(30)
LastNameSndx CHAR(4)
I'm almost embarressed to ask considering my SQL expertise, but...
I need to take the SOUNDEX of the LastName and put that value in the LastNam
eSndx column. So I fire off this SQL:
Update Person Set LastNameSndx = SOUNDEX(LastName);
It takes forever. I start tweeking the SQL to commit every 500 records or so
. Still takes a long time. I then notice what is happening. The SQL is takin
g the SOUNDEX of the LastName of the first record, apply it to ALL the recor
ds, then taking the SOUNDEX
of the LastName of the second records, then updating it to ALL the records,
etc.
This is not SQL as I understand it.
What am I doing wrong here?Hi!

quote:

---

> It takes forever. I start tweeking the SQL to commit every 500 records or

---

so. Still takes a long time. I then notice what is happening. The SQL is
taking the SOUNDEX of the LastName of the first record, apply it to ALL the
records, then taking the SOUNDEX of the LastName of the second records, then
updating it to ALL the records, etc.

quote:

---

>

---

How did you notice this? This is really strange, I've never heard of
something like this.
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||I attempted the same thing and I am not seeing the same behavior. Not sure
what is happening in your case.
Rand
This posting is provided "as is" with no warranties and confers no rights.

problem with varchar and nvarchar datatype in linked server

Hi,

I am updating a remote table using linked server in sql server 2005.

but in case of varchar and nvarchar i am getting an error :

"OLE DB provider "SQLNCLI" for linked server "LinkedServer1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 16955, Level 16, State 2, Line 1

Could not create an acceptable cursor."

thanks in advance.

Thanks & Regards

Pintu

Pintu, I had a similar problem with deleting rows through a linked sever from SQL Server 2005. I tried from SQL Server 2000 and got a slightly different error :-

"The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements."

which led me to this article:-

http://support.microsoft.com/kb/814581

which solved my problem. Hope it helps you too.

problem with varchar and nvarchar datatype in linked server

Hi,

I am updating a remote table using linked server in sql server 2005.

but in case of varchar and nvarchar i am getting an error :

"OLE DB provider "SQLNCLI" for linked server "LinkedServer1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 16955, Level 16, State 2, Line 1

Could not create an acceptable cursor."

thanks in advance.

Thanks & Regards

Pintu

Pintu, I had a similar problem with deleting rows through a linked sever from SQL Server 2005. I tried from SQL Server 2000 and got a slightly different error :-

"The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements."

which led me to this article:-

http://support.microsoft.com/kb/814581

which solved my problem. Hope it helps you too.

problem with updating record using xml

hi,
i have the following tables
client
clientID IDENTITY
FirstName
LastName
AddressId
Contact
AddressID
address1
address2
city
state
country
i have two system S1 and S2 , both will be in a different places.
both s1 and s2 can add client data. remember clientId is identity field.
once in a interval S2 will generate a xml file and upload into a server
(third system
S3) now S1 will get the XML file from S3 i.e. server.
now S1 has to update all those records from XML which is sent by S2.
the problem is both S1 and S2 would have clientID with same id but with
different
informations.
if i update records in S1 using ClientID from S2, then records which have same
ID will get affected.
in this scanario how can i update records in S1...
after some days again S2 will upload xml file , and that file should also
be updated again without affecting records entered in S1.
can anyone help in this regard
Hi Thiru,
It really depends on how you've implemented the process. Please provide more
details of what technology you are using to achieve this.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Thiru.S" <Thiru.S@.discussions.microsoft.com> wrote in message
news:F5AE4559-8377-4E0D-8C1A-CD7B5820ED67@.microsoft.com...
> hi,
> i have the following tables
> client
> --
> clientID IDENTITY
> FirstName
> LastName
> AddressId
> Contact
> --
> AddressID
> address1
> address2
> city
> state
> country
>
> i have two system S1 and S2 , both will be in a different places.
> both s1 and s2 can add client data. remember clientId is identity field.
> once in a interval S2 will generate a xml file and upload into a server
> (third system
> S3) now S1 will get the XML file from S3 i.e. server.
> now S1 has to update all those records from XML which is sent by S2.
> the problem is both S1 and S2 would have clientID with same id but with
> different
> informations.
> if i update records in S1 using ClientID from S2, then records which have
> same
> ID will get affected.
> in this scanario how can i update records in S1...
> after some days again S2 will upload xml file , and that file should also
> be updated again without affecting records entered in S1.
> can anyone help in this regard
>
>
>

problem with updating record using xml

hi,
i have the following tables
client
--
clientID IDENTITY
FirstName
LastName
AddressId
Contact
--
AddressID
address1
address2
city
state
country
i have two system S1 and S2 , both will be in a different places.
both s1 and s2 can add client data. remember clientId is identity field.
once in a interval S2 will generate a xml file and upload into a server
(third system
S3) now S1 will get the XML file from S3 i.e. server.
now S1 has to update all those records from XML which is sent by S2.
the problem is both S1 and S2 would have clientID with same id but with
different
informations.
if i update records in S1 using ClientID from S2, then records which have sa
me
ID will get affected.
in this scanario how can i update records in S1...
after some days again S2 will upload xml file , and that file should also
be updated again without affecting records entered in S1.
can anyone help in this regardHi Thiru,
It really depends on how you've implemented the process. Please provide more
details of what technology you are using to achieve this.
thanks
Chandra
---
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Thiru.S" <Thiru.S@.discussions.microsoft.com> wrote in message
news:F5AE4559-8377-4E0D-8C1A-CD7B5820ED67@.microsoft.com...
> hi,
> i have the following tables
> client
> --
> clientID IDENTITY
> FirstName
> LastName
> AddressId
> Contact
> --
> AddressID
> address1
> address2
> city
> state
> country
>
> i have two system S1 and S2 , both will be in a different places.
> both s1 and s2 can add client data. remember clientId is identity field.
> once in a interval S2 will generate a xml file and upload into a server
> (third system
> S3) now S1 will get the XML file from S3 i.e. server.
> now S1 has to update all those records from XML which is sent by S2.
> the problem is both S1 and S2 would have clientID with same id but with
> different
> informations.
> if i update records in S1 using ClientID from S2, then records which have
> same
> ID will get affected.
> in this scanario how can i update records in S1...
> after some days again S2 will upload xml file , and that file should also
> be updated again without affecting records entered in S1.
> can anyone help in this regard
>
>
>

Problem with updating only one row in a SQL 2005 database

hi,

I have a problem with updating a row in my database. I don't know how to update and i need help.

I have a form where the users select a category and a sub category to enter a message.

I have two dropdown lists, one is for selecting the category and the other is for the sub-category.

In my database for the categories and the sub-categories, i have a variable (Quantity) where i keep the quantity for each table.

I just want to be able to increment this variable by 1 (for the selected category and sub-category) when a user enter a new message...

The insert procedure is working well and have no problem with it.

I use VWD 2005 Express with SQL 2005 express and i code in VB.

HELP!!!!!

Please post your table structure, some sample data and the update you are expecting to happen. Also the query you currently have would help.

|||

ok

Table Categories
------
CategoryID / int / Primary key
CategoryName / varchar(50)
Description / varchar (50)
Quantity / int

Table SubCategories
------
SubCategoryID / int / Primary key
CategoryID / int / Foreign key, linked to Table Categories-CategoryID
SubCategoryName / varchar(50)
Description / varchar (50)
Quantity / int

So when a user post a message, i want that the Quantity for table Categories and SubCategories increment by 1.

Portion of my code :
-------

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="CategoryName" DataValueField="CategoryID">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories] ORDER BY [CategoryName]">
</asp:SqlDataSource>

<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
DataTextField="SousCategoryName" DataValueField="SubCategoryID" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [SubCategoryID], [SubCategoryName] FROM [SousCategories] WHERE ([CategoryID] = @.CategoryID) ORDER BY [SubCategoryName]">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="CategoryID" PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

|||

With corrections!

----------

ok

Table Categories
------
CategoryID / int / Primary key
CategoryName / varchar(50)
Description / varchar (50)
Quantity / int

Table SubCategories
------
SubCategoryID / int / Primary key
CategoryID / int / Foreign key, linked to Table Categories-CategoryID
SubCategoryName / varchar(50)
Description / varchar (50)
Quantity / int

So when a user post a message, i want that the Quantity for table Categories and SubCategories increment by 1.

Portion of my code :
-------

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="CategoryName" DataValueField="CategoryID">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories] ORDER BY [CategoryName]">
</asp:SqlDataSource>

<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
DataTextField="SubCategoryName" DataValueField="SubCategoryID" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [SubCategoryID], [SubCategoryName] FROM [SubCategories] WHERE ([CategoryID] = @.CategoryID) ORDER BY [SubCategoryName]">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="CategoryID" PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

|||So any ideas?|||

Hi,

You can create a button_click event in your code-behind page. The following code (c#) is just a sample which can achieve your purpose.

protected void Button1_Click(object sender, EventArgs e) {/// Get the CategoryName value from DropDownList1string CategoryName =this.DropDownList1.SelectedItem.Value;/// Get the CategoryName value from DropDownList2string SubCategoryName =this.DropDownList2.SelectedItem.Value;/// in this sample,we get the message and the username value from textboxesstring Message =this.TextBox1.Text;string User=this.TextBox2.Text;/// connstr is the name of connection string you set in Web.configstring conn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString; SqlConnection myconn =new SqlConnection(conn);/// write the message which user submit into YourTablestring insert_command ="Insert into YourTable (CategoryName,SubCategoryName,Message,Users) values('"+CategoryName+"','"+SubCategoryName+"','"+Message+"','"+User+"')"; myconn.Open(); SqlCommand mycomm =new SqlCommand(insert_command, myconn); mycomm.ExecuteNonQuery(); mycomm.Dispose();/// sql for updating the Quantity number in the Categories tablestring up_cate ="update Categories set Quantity = (Quantity+1) where CategoryName = '" + CategoryName +"'";/// sql for updating the Quantity number in the SubCategories tablestring up_subcate ="update SubCategories set Quantity = (Quantity+1) where SubCategoryName ='" + SubCategoryName +"'"; SqlCommand mycomm_1 =new SqlCommand(up_cate, myconn); SqlCommand mycomm_2 =new SqlCommand(up_subcate, myconn); mycomm_1.ExecuteNonQuery(); mycomm_2.ExecuteNonQuery(); mycomm_1.Dispose(); mycomm_2.Dispose(); myconn.Close(); }

Hope it helps. Thanks.|||

Sorry i code in VB...

I tried that and it's not working (on a click event) :

Dim myConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("FouinerDB").ConnectionString
Dim myConnectionDB As New Data.SqlClient.SqlConnection(myConnection)
Dim myDA As New Data.SqlClient.SqlDataAdapter()

Dim CatID As Integer = ddlCategories.SelectedValue
Dim SubCatID As Integer = ddlSousCategories.SelectedValue

myDA.UpdateCommand = New Data.SqlClient.SqlCommand("UPDATE Categories SET Quantity=10 WHERE CategoryID = " & CatID.ToString(), myConnectionDB)

|||

(1) Please use parameterized queries to prevent SQL injeection attacks.

I would recommend you do it via stored procedure as follows:

Update CategoriesSET Quantity = Quantity + 1WHERE Categoryid = @.CategoryidUpdate SubCategoriesSET Quantity = Quantity + 1WHERE SubCategories = @.SubCategoriesand Categoryid = @.Categoryid

If you had to do it via adhoc T-SQL, you'd be making 2 trips once for each Update. Through a stored proc, you can do it all in one trip.

|||Ok, but how do you call the stored procedure in the vb code.

I dont know nothing about it... ;-(|||I tired that for stored procedure and when i try it (execute), it doesn't change anything in my database (i also tried to change @.Categoryid and @.SousCategoriesID by values that i know are valid ) :

ALTER PROCEDURE

dbo.UpdateQuantity/*(@.parameter1 int = 5,@.parameter2 datatype OUTPUT)*/

AS

/* SET NOCOUNT ON */RETURN

BEGIN

DECLARE@.CategoryidintDECLARE@.SousCategoriesIDint

UPDATE

CategoriesSETQuantity = Quantity + 1WHERECategoryID = @.Categoryid

UPDATE

SousCategoriesSETQuantity = Quantity + 1WHERESousCategoriesID = @.SousCategoriesID

END

|||I don't understand why aSelectCommand works in my page and not anUpdateCommand...

Works!!!
myDA.SelectCommand =

New Data.SqlClient.SqlCommand("SELECT * FROM Annonces WHERE SousCategoryID = " & SubCatID.ToString(), myConnectionDB)

Dont works!!!
myDA.UpdateCommand =New Data.SqlClient.SqlCommand("UPDATE Categories SET Quantity = 10 WHERE CategoryID = " & CatID.ToString(), myConnectionDB)
|||

Ok i finally got it!!!

It's theUpdateCommand.ExecuteNonQuery() that i missed. ;-)

I will also try with a Stored Procedure... See you soon!
-----------

Dim myConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("DatabaseDB").ConnectionString
Dim myConnectionDB As New Data.SqlClient.SqlConnection(myConnection)
myConnectionDB.Open()
Dim myDA1 As New Data.SqlClient.SqlDataAdapter()
Dim myDA2 As New Data.SqlClient.SqlDataAdapter()

Dim CatID As Integer = ddlCategories.SelectedValue
Dim SubCatID As Integer = ddlSousCategories.SelectedValue

myDA1.UpdateCommand = New Data.SqlClient.SqlCommand("UPDATE Categories SET Quantity = Quantity + 1 WHERE CategoryID = " & CatID.ToString(), myConnectionDB)
myDA2.UpdateCommand = New Data.SqlClient.SqlCommand("UPDATE SousCategories SET Quantity = Quantity + 1 WHERE SousCategoriesID = " & SubCatID.ToString(), myConnectionDB)
myDA1.UpdateCommand.ExecuteNonQuery()
myDA2.UpdateCommand.ExecuteNonQuery()
myConnectionDB.Close()

|||

It would be more like this: The parameters values would come from your VB application. For your update on SubCategories I also added the CategoryId= @.CategoryID condition because I assumed that the same subcategory could be present for multiple categories. So its important to also specify the Category level.

ALTER PROCEDURE dbo.UpdateQuantity ( @.Categoryidint , @.SousCategoriesIDinT)ASBeginSET NOCOUNT ON UPDATE CategoriesSET Quantity = Quantity + 1WHERE CategoryID = @.CategoryidUPDATE SousCategoriesSET Quantity = Quantity + 1WHERE SousCategoriesID = @.SousCategoriesIDAnd CategoryID = @.CategoryidSET NOCOUNT OFFEND

Your VB code could look like this:

Dim myCommandAs SqlCommandDim myParamAs SqlParametermyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="updateQuantity"myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.Add(New SqlParameter("@.Categoryid",SqlDbType.int))myCommand.Parameters("@.Categoryid").Value = 10myCommand.Parameters.Add(New SqlParameter("@.SousCategoriesID",SqlDbType.int))myCommand.Parameters("@.SousCategoriesID").Value = 1TryIf objCon.State = 0Then objCon.Open()mycommand.ExecuteNonQuery()Catch excAs ExceptionResponse.Write(exc)FinallyIf objCon.State = ConnectionState.OpenThen objCon.Close()End IfEnd Try

|||Hi,

In terms of process speed, wich one is the fastest?

Hard coding or Stored Procedure?|||

Whether you use T-SQL/stored proc the actual statements are executed at the DB. IF you had multiple T-SQL statements to be executed you are better off using stored procs as you can do it all in one trip. Also, if you have T-SQL all over your app it becomes a maintenance nightmare if your underlying schema changes in future. Stored procs provide a channelized way of accesssing the data. Also, for security reasons procs are a better choice. You'd have to give SELECT/UPDATE/DELETE rights to the userid you are using on the actual table if you use adhoc queries. If you do the same thing via procs, you just give the user permission to execute the proc. There are tons of other pros vs cons. You can also google (adhoc query vs stored proc) and see some good debate about this.

problem with updating identity in transactional replication

Hey. I've a few transactional publications. It fails with this error. When I
looked at the command, it's trying to insert a null in identity column. Why
does it do that? As of now, I've commented the update for the identity column
in the sp_msupd_logdevicebeacon. But now, if somebody tries to update the
identity column, what would happen? The reason I need the identity property
on subscriber side is because these replicated tables will be horizontally
partitioned and replicated to another server. And I'm hoping to use
Transactional Replication for that. Please let me know what should be done
and what's the ideal. thank you.
Cannot update identity column 'DeviceBeaconID'.
(Source: XIAN\XIAN2 (Data source); Error number: 8102)
{CALL sp_MSupd_logDeviceBeacon
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,2005-11-29 15:43:34.000,2005-11-29
15:44:00.663,NULL,NULL,2005-11-29 15:43:00,4004089,0x8009)}
Transaction sequence number and command ID of last execution batch are
0x002BA62A00000E7E000100000000 and 1.
This looks like you update proc as opposed to your insert proc.
Take the proc and open it up in a text editor. In the bottom half of the
proc comment out the part where it updates the identity column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:728B16DD-98F6-41F1-925F-53C6684FD831@.microsoft.com...
> Hey. I've a few transactional publications. It fails with this error. When
> I
> looked at the command, it's trying to insert a null in identity column.
> Why
> does it do that? As of now, I've commented the update for the identity
> column
> in the sp_msupd_logdevicebeacon. But now, if somebody tries to update the
> identity column, what would happen? The reason I need the identity
> property
> on subscriber side is because these replicated tables will be horizontally
> partitioned and replicated to another server. And I'm hoping to use
> Transactional Replication for that. Please let me know what should be done
> and what's the ideal. thank you.
>
> Cannot update identity column 'DeviceBeaconID'.
> (Source: XIAN\XIAN2 (Data source); Error number: 8102)
>
> {CALL sp_MSupd_logDeviceBeacon
> (NULL,NULL,NULL,NULL,NULL,NULL,NULL,2005-11-29 15:43:34.000,2005-11-29
> 15:44:00.663,NULL,NULL,2005-11-29 15:43:00,4004089,0x8009)}
> Transaction sequence number and command ID of last execution batch are
> 0x002BA62A00000E7E000100000000 and 1.
|||Yes, I'm sorry. I used the wrong word. It's trying to update the Ident column
with a 'NULL' value. Can you explain what this statement does?
update "datObjects" set
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
I think this is the line you have asked me to comment out. In my case, I
checked the @.bitmap variable and I found it to be '0x8009' when it does a
substring, it'll be case 0, right? so, it'll go to the else statement,
right?
Now, the question is why does the sp try to insert a null? Is it because no
modifications were made to it?
Thank you for your help.
Tejas
|||It should not be trying to do a null update. I am really confused here
however, you persist in talking about inserts, for the life of me it should
be an update. Or perhaps you are that pesky Paul Ibison in disguise trying
to push me over the edge?
The @.bitmap dictates which columns are to be updated. It looks like for your
bitmask the else will be used which means that the identity value will be
updated to the same value. A Null is passed due to the call type you are
using MCALL IIRC. As the identity value is not updated on the publisher no
value is passed - i.e. a NULL is passed. If the identity column was updated
(if this is possible) a value would be passed here instead of the null.
I think your update proc portion should look like this
update "datObjects" set
-- "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
-- "ObjectTypeID" end
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:2D828E4F-AC1A-4875-83CB-85B58FC80A73@.microsoft.com...
> Yes, I'm sorry. I used the wrong word. It's trying to update the Ident
> column
> with a 'NULL' value. Can you explain what this statement does?
> update "datObjects" set
> "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end
> I think this is the line you have asked me to comment out. In my case, I
> checked the @.bitmap variable and I found it to be '0x8009' when it does a
> substring, it'll be case 0, right? so, it'll go to the else statement,
> right?
> Now, the question is why does the sp try to insert a null? Is it because
> no
> modifications were made to it?
> Thank you for your help.
> Tejas
|||lol, no it's not Paul. And I rectified it to update in my 2nd post. I'm sorry
again for using the word insert in my first post.Thank you very much for your
help. This was what I was actually looking for. Thank you.
|||Ok, thanks Paul.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:50263252-D63C-448A-8A89-470CABA983EA@.microsoft.com...
> lol, no it's not Paul. And I rectified it to update in my 2nd post. I'm
> sorry
> again for using the word insert in my first post.Thank you very much for
> your
> help. This was what I was actually looking for. Thank you.
|||Hey Hilary. I could not do what you had asked. I'm pasting the whole
sp_msupd_datobjects sp here. Then I'll tell you what the problem is.
Below, objectId is the identity column. But it doesn't exist in the else part.
I've pasted the sp as is. Made no modifications to it. So, if i comment the
objectid in the if part it works fine... Is it normal to be not there in the
else part? because that's where u asked me to comment it out.
-----
CREATE procedure "sp_MSupd_datObjects"
@.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
,@.bitmap binary(3)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "datObjects" set
"ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else "ObjectID"
end
,"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID" end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "datObjects" set
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end,
"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID" end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
|||Ok Paul, I think this is it
CREATE procedure "sp_MSupd_datObjects"
@.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
,@.bitmap binary(3)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "datObjects" set
--on the safe side I will do this too
--"ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
"ObjectID"
--end
--,
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "datObjects" set
--"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
--"ObjectTypeID" end,
"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
>
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:5FA239EC-5A40-4D2F-AB2F-570BF4B6FAF9@.microsoft.com...
> Hey Hilary. I could not do what you had asked. I'm pasting the whole
> sp_msupd_datobjects sp here. Then I'll tell you what the problem is.
> Below, objectId is the identity column. But it doesn't exist in the else
> part.
> I've pasted the sp as is. Made no modifications to it. So, if i comment
> the
> objectid in the if part it works fine... Is it normal to be not there in
> the
> else part? because that's where u asked me to comment it out.
> -----
> CREATE procedure "sp_MSupd_datObjects"
> @.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
> bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
> bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
> ,@.bitmap binary(3)
> as
> if substring(@.bitmap,1,1) & 1 = 1
> begin
> update "datObjects" set
> "ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
> "ObjectID"
> end
> ,"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end
> ,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
> "ObjectSubTypeID" end
> ,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "ObjectName" end
> ,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "ObjectNameStringID" end
> ,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "ParentObjectID" end
> ,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "OwnerObjectID" end
> ,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
> "IsDeleted" end
> ,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
> "IsEnabled" end
> ,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "DateCreated" end
> ,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
> "DateModified" end
> ,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
> "DateDeleted" end
> ,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
> else "ModifiersObjectID" end
> ,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
> "PathDepth" end
> ,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
> end
> ,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
> "Rowguid" end
> where "ObjectID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> else
> begin
> update "datObjects" set
> "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end,
> "ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
> "ObjectSubTypeID" end
> ,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "ObjectName" end
> ,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "ObjectNameStringID" end
> ,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "ParentObjectID" end
> ,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "OwnerObjectID" end
> ,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
> "IsDeleted" end
> ,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
> "IsEnabled" end
> ,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "DateCreated" end
> ,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
> "DateModified" end
> ,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
> "DateDeleted" end
> ,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
> else "ModifiersObjectID" end
> ,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
> "PathDepth" end
> ,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
> end
> ,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
> "Rowguid" end
> where "ObjectID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> GO
>
|||Just wanted to know if you noticed that the commented lines in the sp are two
different fields...
In the if, it's objectid
in the else, it's objectTypeID.
These are two different columns with no correlation...
Is the commenting still correct?
Just want to be sure.
Thank you,
TEJAS
(not Paul)
|||Oops, yes you are correct. I should have commented out the identity column -
its ObjectTypeID right?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:561E41D4-279E-4FF3-B976-8F75A2AA7689@.microsoft.com...
> Just wanted to know if you noticed that the commented lines in the sp are
> two
> different fields...
> In the if, it's objectid
> in the else, it's objectTypeID.
> These are two different columns with no correlation...
> Is the commenting still correct?
> Just want to be sure.
> Thank you,
> TEJAS
> (not Paul)

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
Ragnar
On Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.
|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the si
ze
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you g
enerate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log
automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insigh
t into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_kara...>
rinking.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but
> I have been digging through the SQL Server Books Online without finding an
ything about this.
> I have seen a few cases in our company where someone has set up an SQL Ser
ver DB without setting
> up regular backups, where the log just kept growing. In one case the log u
sed up all the available
> space on the disk and effectively shut down the computer, not enough room
for the swap file.
> I think there is a way to control the growth of the log file, but I have n
ot found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you generate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insight into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log will grow if needed, but
> I have been digging through the SQL Server Books Online without finding anything about this.
> I have seen a few cases in our company where someone has set up an SQL Server DB without setting
> up regular backups, where the log just kept growing. In one case the log used up all the available
> space on the disk and effectively shut down the computer, not enough room for the swap file.
> I think there is a way to control the growth of the log file, but I have not found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>

Problem with updating a mobile sql database

This is strange. Cause earlier I could update the mobile sql server with no problem, but now I am getting this parsing error

it says Token line number = 1, ....

The windows mobile emulator works, but not in the actual pda.

The sql query is following
sql2 = "update flexi_pump set difference = " + divergence + " where tag = '" + tag + "'";

selecting data from the database works fine, no problem with it. only writing to it is the problem I would recommend using command parameters to avoid occasional string concatenation mishaps and possible SQL injection attacks on your code. If the tag variable is a string, it may contain a quote and it will break the syntax.

Problem with update when updating all rows of a table through dataset and saving back to d

Hi,

I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions?

SqlCommand snUpdate = conn.CreateCommand();

snUpdate.CommandType =CommandType.Text;

snUpdate.CommandText ="Update TestTable set shipdate = @.shipdate";

snUpdate.Parameters.Add("@.shipdate",SqlDbType.Char, 10,"shipdate");

string jdate ="";

for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)

{

jdate = ds.Tables[0].Rows[i]["shipdate"].ToString();

ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate);

}

da.Update(ds,"Table1");

conn.Close();

-Thanks

a quick once over and i have a few questions--what all in your TestTable? Also in all lines of code you refer to your table as Tables[0] you should always stick to one way. Your right about your Update, so when we see what's in your TestTable we'll be able to help you out a little bit more.

|||

Thanks for your response. My table contains 2 fields SKU numbers and Ship Dates. I changed my update statement as follows and it worked.

snUpdate.CommandText ="Update TestTable set shipdate = @.shipdate where skunum=@.skunum";

snUpdate.Parameters.Add("@.shipdate",SqlDbType.Char, 10,"shipdate");

snUpdate.Parameters.Add("@.skunum",SqlDbType.Int, 4,"skunum");

snUpdate.Parameters["@.skunum"].SourceVersion =DataRowVersion.Original;

-Thanks

|||So is skunum a unique value? If not, then it'll update all rows with that skunum. If it is, then why isn't that your primary key?|||The skunum is not unique. The table does have some duplicated data which I don't have to worry about if they get updated with the same value. This is just an old table whose data will be transformed into a different table and used in an app after my updates are done. Thanks.