Help! A Simple 'update Records' Question
Nov 19, 1999
Hi there,
I was trying to update records of a recordset(ADODB.Recordset) returned from a stored procedure(SQL server 7.0) in ASP file, this stored procedure select records into a temporary table, so the records returned by the SP actually physically are in tempdb database rather than in the user database.
When executing Update statement , I got the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Database name 'Mydatabase' ignored, referencing object in tempdb.
Any ideas/comments would be highly appreciated!
Dana Jian
dan_jian@hotmail.com
View 1 Replies
ADVERTISEMENT
Feb 9, 2007
Hi,
My scenario:
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
Thanks in advance for your help.
Regards,
$wapnil
View 4 Replies
View Related
Jul 23, 2005
I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.
View 2 Replies
View Related
May 21, 2007
greets all, ive got a table with batches of records. each group of records has a batch id as part of the PK in the form BTCXXXX where XXXX is an auto-incremented number. so lets say i have 100 batches of 20k records per batch in the table. so the distinct batch ids are BTC0200 (oldest batch) through BTC0300 (newest batch).
i only want to keep the 90 most recent batches in the table at any given time.
is it ok to just subtract 90 from the last batch id and do something like:
DECLARE @batch_id char(10)
SET @batch_id = 'BTC' + batch_num-90
DELETE FROM ITEM_BATCH
WHERE BATCH_ID < @batch_id
i want to cover if the table has more than 90 batches and if the table has less then 90 batches. is this a feasible approach?
View 4 Replies
View Related
Sep 28, 2007
I need to return one record with concatenated string fields from a table that may contain several records. I think a cursor will be able to do what I want, but I'm not very experienced at writing them.
My data
HDR DMCD
107 TEX
107 AIR
107 LG
108 TEX
108 CAR
109 SM
I want the result of my query to find adn return each header and return the 1 or more DMCD field values concatenated. i.e.
107 TEX AIR LG
108 TEX CAR
109 SM
This is my attempt at the cursor so far
SET NOCOUNT ON
DECLARE @AACODE varchar(50),@hdr varchar(20),@dmcd varchar(20)
DECLARE AAROW_cursor CURSOR FOR
SELECT aaglhdrid,aatrxdimid
FROM aag30003
OPEN AAROW_cursor
FETCH NEXT FROM AAROW_cursor
INTo @hdr, @dmcd
WHILE @@FETCH_STATUS = 0
BEGIN
set @aacode=@aacode+@dmcd
select @hdr,@dim,@aacode
FETCH NEXT FROM aarow_cursor
INTO @hdr, @dmcd
END
CLOSE AAROW_cursor
DEALLOCATE aarow_cursor
View 5 Replies
View Related
Oct 11, 2007
I am using a simple stored proc shown below which inserts a record and updates the same record field called SortID with the Primary Key Column data of the same record.Everything was working fine until few days back. The site has been deployed 2 years back and was LIVE ever since.
We have got thousands of records in this Credits table. The table field called SortID is used for moving the credits up and down.
My problem is now after 2 years of deploying the table has got duplicate records, suprisingly a same credit is appearing under different MemberID and with the same SortID.
How are these duplicate records inserted with a simple insert statement & update ??
Is this a Locking problem or Transaction problem I have no idea.
Any ideas will be of great help
Thanks in Advance
Stored Procedure Used
CREATE PROC SP_SC_INSERT (@memberID int,@title varchar(30),@dir varchar(30),@desc varchar(20))ASINSERT INTO [dbo].[Credits]([MemberID],[Title],[Director], [Description], )VALUES(@memberID,@title,@dir, @desc, )UPDATE Credits Set SortID = @@IDENTITY WHERE CreditID = @@IDENTITYGO
View 4 Replies
View Related
Jul 9, 2013
I want my query to list all SSNS that have more than one record in the table. I have this query:
Code:
SELECT SSN, name4, count(*) from [1099_PER]
group by SSN, name4
having count(SSN) > 1
It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.
For example
SSN NAME4 COUNT
123445555 WALTER - 4
I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.
View 6 Replies
View Related
Nov 23, 1999
Hi..This may be silly but i am stuck at this for some time.
I have 10000 rows in a table and i want to copy the value of column 1 in all the rows to column 2 ( which contains NULL presently in all the rows ). The two columns are of the same datatype.
Thanx.
View 1 Replies
View Related
Mar 16, 2004
Hello all and I look forward to reading your replys. Currently, I'm trying to update a table in my SQL database by writing a script that will go get the field name and correct info, verify it has the correct field by using two other columns and then update the information.
I do not know how to use tell SQL what to update when the field name can change. I've used the following in the past.
UPDATE [Import Table]
SET [Import Table].[???] = [Correction_Table].[Correct_Info]
WHERE [Import Table].[UNIQUE ID] in (select [USO Unique ID] from [Correction_Table])
IN this new instance, I'm reading the Field_Name from the Correction_Table so I don't know what to put in my SET statement.
Please any help or pointers in the right direction would be greatly appreciated.
Battle_n
View 4 Replies
View Related
Dec 11, 2006
I have a table Products with their prices, and I have another table Sales, that have the prices of the products but they are null, I need to make an update that update the tables Sales, inserting the prices from the table Products
View 3 Replies
View Related
Feb 26, 2008
I need to do an update. Can you tell me if it's possible to update multiple tables in a single statement when joined, as with a select, or this has to be taken care of in seperate statements with the same join? I'm sure this is a simple YES / NO answer.
Here is what I'm using, with the same structure as somewhere else... why do I get 'incorrect syntax near q' on that very first line?
-- Do the update
update ccs_quotes q
set ccs_quo_user_date#1 = j.completed
from "ken-sql-002".hettonhosttestdatabase.dbo.job j
inner join ccs_quotes q
on left(j.cid,10) = q.ccs_quo_ref_number#2
where completed is not null
and ccs_quo_ref_number#2 = 'SHGD40021'
Thanks a lot
Mike
View 5 Replies
View Related
Jul 23, 2005
I need to copy data from one column that has a max size of 45 characters toa column that has a max size of 20 characters.What is the syntax to say fill the first 20 characters out of 45 fromcolumn 1 into column 2?jeff--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Jul 20, 2005
Today I need to copy 8 records in a table. I have to use Access 200 becauseof the limitation of Enterprise Manager's inability to cope with field withmore than 900 characters. Selected records, cut, paste. I got an erroormessage about not being able to have a null Key_ID (I copied the reords andtried to paste the Key_ID as part of the records - normally I hide theKey_ID).Now I can't access either the new records or the originals that I was tryingto copy (because, it would seem, they have identical primary keys). I alsocannot export the table via DTS 'unspecified error' and 'integrityviolation'.Or delete the offending records with a Query Anaylyser delete query.Basically the entire SQL Server database has been destroyed with a couple ofkeystrokes.Now, I've being developing database applications for over 20years and theone thing, maybe the only thing I expect from a database server is toprotect the integrity of my data. SQL Server does not, it would seem. Theserecords aren't just any random unimportant records either. They contain the'create views' that my entire application require to function and each oneapproaches the 8000 record limit and have take years to perfect and justchecking that the table is valid could take me days.
View 5 Replies
View Related
Aug 21, 2015
Can the deleted records be recovered in Simple Recovery model database? Are the delete logs present there?
View 3 Replies
View Related
Oct 4, 2007
Hi folks. I'm trying to update a single field in a database table with the input from a single text box, but I'm getting "Insert Error: Column name or number of supplied values does not match table definition". I know at least part of the problem is that I haven't specified the name of the table field, but I'm not sure where to do this. The table in question has many fields, but I only want to update one. The submit button is "sendMail", which calls UpdateDB.I'm learning and trying to limit myself to 1 idiotic question per week. Please have mercy. Thanks. Chrisprivate void UpdateDB(string cmd)
{
// point to connection string in web.config
string connectionString = "Data Source=localhost;Initial Catalog=databaseName;Integrated Security=True";
// create connection object; initialize; open
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);
try
{
connection.Open();
// create sqlcommand object and assign connection
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.Connection = connection;
command.CommandText = cmd;
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
protected void sendMail(object sender, EventArgs e)
{
string cmd = @"INSERT INTO tableName VALUES ('" + this.TextBox2.Text + "')";
UpdateDB(cmd);
// send smtp mail ... }
View 3 Replies
View Related
Oct 9, 2007
I have been trying to create a simple page to update my SQL Express database with some text boxes that are auto filled.
If here is the code if you have any help for me it would be great. Partial Class Secured_Default2
Inherits System.Web.UI.PageProtected Sub Accept_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Accept.Click
SqlDataSource3.Update()
End SubProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
AcceptDate.Text = System.DateTime.Now.ToStringAcceptedBy.Text = Request.ServerVariables.Get("Logon_User")
StatusBox.Text = "2"End Sub
End Class<%@ Page Language="VB" MasterPageFile="~/Secured/MasterPage.master" AutoEventWireup="false" CodeFile="TEST.aspx.vb" Inherits="Secured_TEST" title="Untitled Page" %>
<%@ Import Namespace="System.Data.SqlClient" %><%@ Import Namespace="System.Data" %>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<p>
<br />
Date:
<asp:TextBox ID="AcceptDate" runat="server" ></asp:TextBox> Accepted By:
<asp:TextBox ID="AcceptedByBox" runat="server" ></asp:TextBox><br />
Status:
<asp:TextBox ID="StatusBox" runat="server" Width="18px"></asp:TextBox>
<asp:Button ID="Accept" runat="server" CommandName="Update" Text="Accept Ticket" /><br />
<br />
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:HelpDeskConnectionString2 %>"
SelectCommand="SELECT TicketNUM, Accepted, AcceptedBy, Status FROM HelpDesk WHERE (TicketNUM = @TicketNUM)"
UpdateCommand="UPDATE HelpDesk SET Accepted = @UAccepted, AcceptedBy = @UAcceptedBy, Status = @UStatus WHERE (TicketNUM = @TicketNUM)">
<UpdateParameters>
<asp:FormParameter Name="UAccepted" FormField="AcceptedDate"/>
<asp:FormParameter Name="UAcceptedBy" FormField="AcceptedByBox" />
<asp:FormParameter Name="UStatus" FormField="StatusBox" />
<asp:QueryStringParameter Name="TicketNUM" QueryStringField="TicketNUM" />
</UpdateParameters>
<SelectParameters>
<asp:QueryStringParameter Name="TicketNUM" QueryStringField="TicketNUM" />
</SelectParameters>
</asp:SqlDataSource>
</p>
</asp:Content>
View 4 Replies
View Related
Jun 18, 2008
I am attempting to update contact data that is displayed in a form. Unfortunately the followiing code that runs off the button click event does not seem to work or I am misssing something to make it fire. I get no errors but the data does not get updated. What am I missing? Thanks.Private Sub btn_Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Submit.Click
Dim strConnection As StringDim cn As SqlConnection
Dim sql As String
strConnection = ConfigurationSettings.AppSettings("ConnectionString")cn = New SqlConnection(strConnection)
sql = "UPDATE vw_BS_IssuerContacts SET ContactLastName = @ContactLastName, ContactFirstName = @ContactFirstName, ContactTitle = @ContactTitle, ContactEmail = @ContactEmail, ContactPhone = @ContactPhone, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, Country = @Country, ZipCode = @zipCode, AccessLevel = @AccessLevel WHERE IssuerCode = @IssuerCode"Dim UpdateCommand As New SqlCommand(sql, cn)
UpdateCommand.Connection = cn
UpdateCommand.CommandText = sqlUpdateCommand.Parameters.Add(New SqlParameter("@IssuerCode", Lbl_IssuerCode.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@ContactLastName", txt_Lname.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ContactFirstName", txt_Fname.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@ContactTitle", txt_title.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ContactEmail", txt_Email.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@ContactPhone", txt_Phone.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Address1", txt_Address.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@Address2", txt_Address2.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Address3", txt_City.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@Country", txt_Country.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ZipCode", txt_zipCode.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@AccessLevel", Lbl_currAccessLevel.Text))
'UpdateCommand.Parameters.Add(New SqlParameter("@AccessLevel", SqlDbType.Char, 2).Value = txt_CurrAccessLevelSelectedValue))UpdateCommand.Parameters.Add(New SqlParameter("@UserName", txt_Username.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@infopw", txt_pw.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Company", txt_IssName.Text))
'do the update
cn.Open()
UpdateCommand.ExecuteNonQuery()
cn.Close()
End Sub
View 1 Replies
View Related
Feb 5, 2007
hi, i've just started using sql server 2005 and visual studio, i have made 3 stored procedures, an update, insert and delete. The update one doesn't work but the others do so i'm on the right track with them..
is there anything obvious i am missing in the following update stored procedure? All of the datatypes and names line up with the datatypes/sizes/names in the table..
Code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spUpdateAddresses]
-- Add the parameters for the stored procedure here
(@addressID int, @fname varchar(50), @lname varchar(50), @address01 varchar(50), @address02 varchar(50), @suburb varchar(50), @state varchar(50), @postcode char(10), @comments varchar(1250), @phone char(10), @fax char(10), @mobile char(10))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Update addresses
set @fname=fname, @lname=lname, @address01=address01, @address02=address02, @suburb=suburb, @state=state, @postcode=postcode, @comments=comments, @phone=phone, @fax=fax, @mobile=mobile
where (@addressID = addresses.addressID)
END
View 2 Replies
View Related
Feb 27, 2007
Hi, wondered if anyone here can help out with an SQL query thats been driving me mad. Currently I do this programmatically in a C# program with a hard coded mapping class but want to put the new codes into a table and run an update query instead.
2 tables like this;
Table: CarModels
SORT
GROUP_CODE
MODEL_NAME
PK: SORT
Table: NewGroupCodes
OLD_CODE
NEW_CODE
PK: OLD_CODE, NEW_CODE
Basically I want to update all GROUP_CODE entries in the CarModels table with the NEW_CODE values from NewGroupCodes where there is a match between CarModels.GROUP_CODE and NewGroupCodes.OLD_CODE.
thanks in advance,
afro
View 7 Replies
View Related
Jul 20, 2005
Hey all,i have a students_table which i want to do a multiple update on.Update student_tableset grant = 35000where course_id = 2There are lots of students in the student table which have course_id of2.I get this error:Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used as anexpression.The statement has been terminated.Does anyone know how to do a multiple update like this.It seems such asimple thing but im confused*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Mar 20, 2008
I've got two tables:
CallLog (one)
Repository (many)
They both have the shared id of CallID. I'm trying to write a trigger that when one adds a record to the repository table, I want the CallLog table flagged with 'T'. My existing code is flagging everything thing in the CallLog table that has CallID match from the Repository table... How do I update the single row in the CallLog table? Driving me nuts!
USE [HEATPROD]
GO
/****** Object: Trigger [dbo].[WriteAttachmentFlagNew] Script Date: 03/19/2008 12:29:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[WriteAttachmentFlagNew]
ON [dbo].[Repository]
AFTER Insert
AS
BEGIN
UPDATE CallLog
SET AttachmentFlagNew = 'T'
FROM repository
WHERE calllog.CallID= repository.CallID
END
View 6 Replies
View Related
Jul 10, 2006
hi, everyone,
When I update a row that does not exist in a table using VBscript and SQL 2003 server, the row is automatically added to the table. Why does this happen?
Can somebody help me? Thanks in advance!
View 2 Replies
View Related
May 3, 2000
I have a simple update/initialization query (set integer column = 0 on all rows) that's been running for over 28 hours. There are just over 27 million rows in the table. In current activity it shows that the transaction is open but it's sleeping, and in locks it shows 1 DB S mode lock, 766 page X mode locks, 1 page U mode lock, and one table X mode lock. Server is 7.0 with 1.7 gig ram. Anyone have any ideas as to why it's taking so long? Table is about 7 gig in size; can't get to it in Enterprise Manager without locking it up...
View 3 Replies
View Related
Feb 9, 2006
Hi i have three tables
T1 is a table of data imported by DTS, it has staff numbers and location names
T2 is a table that contains system data about staff and has a column for the location id
T3 is a table for the location names and their ID's
i am trying to write the sql to update T2 with the new location IDs for staff that have moved location but am not getting what i need.
so far i have :
update
T2
set T2.LocationId =T3.LocationID
from T3
inner join T1
on T3.departmentname = T1.contact_location
the update runs but sets all rows to the same value. am now burried deep in the [I've stared at this too long and cant see the wood for the trees] stage :mad:
Thanks
FatherJack
View 2 Replies
View Related
Oct 5, 2006
Can we add if statements in Update query?
I have to do following task in one stored proc:
I am passing 2 param to the stored proc @spkid, @Repspk
Update myTable
set spk1=@Repspk
where spk1=@spkid
Update myTable
set spk2=@Repspk
where spk2=@spkid
Update myTable
set spk3=@Repspk
where spk3=@spkid
Instead of writing 3 update statements what is the other option that will make the query run faster? Thanks alot for transfering knowledge...
View 1 Replies
View Related
Aug 31, 2005
Kairn writes "I have created a table and imported data from another table to it. I need to update the existing data with values from another import but only need specific columns and am assuming a stored procedure is the way to go...IF I create a source table as well as destination(?). I have to compare the value in 6 columns to find the matching record in the destination, then update 5 of the other columns where source=dest. Finally, I need to sum, by row, the value in 3 of the columns and update another column to reflect it. All rows are unique in the destination. The source is a duplicate of them. Basically, the rows are identical aside from the values in the columns I want transferred. The rows must remain unique. I am a newbie and have no idea how to do this. Would you please submit a basic outline and I can fill in the rest?
Thank-you very much for your time and expertise,
Kairn"
View 1 Replies
View Related
Mar 4, 2008
When I execute following SQL Query Analyzer (SQL 2005) i am getting message 3 times "95 Records effected" in query analyzer message window.
UPDATE dbo.Load_AC_Install
SET Load_AC_Install.Rejected_Reason = Load_AC_Install_Rejected.Validation_Reason
FROM (Load_AC_Install JOIN Load_AC_Install_Rejected
ON Load_AC_Install.Hardware_Portfolio_Barcode = Load_AC_Install_Rejected.Hardware_Portfolio_Barcode
AND Load_AC_Install.Install_Model_Barcode = Load_AC_Install_Rejected.Model_BarCode )
After I load "Load_AC_Install" table into Temporary table #Load_Install and When I execute following SQL Query from SQL Query Analyzer using temporary table I am getting message Only 1 time "95 Records effected"
Update #Load_Install
SET #Load_Install.Rejected_Reason = Load_AC_Install_Rejected.Validation_Reason
FROM (#Load_Install JOIN Load_AC_Install_Rejected
ON #Load_Install.Hardware_Portfolio_Barcode = Load_AC_Install_Rejected.Hardware_Portfolio_Barcode
AND #Load_Install.Install_Model_Barcode = Load_AC_Install_Rejected.Model_BarCode )
So My Question is if i use phicical table (dbo.Load_AC_Install) then i get message 3 times, but If i use same data with tempoary table(#load_Install) I get message only one time "95 Records Effected" in query analyzer message window!
Anyone know why?
Thanks for response and please ask me if you still have any questions!
View 4 Replies
View Related
Jul 20, 2005
I am extremely new at SQL Server2000 and t-sql and I'm looking tocreate a simple trigger. For explanation sake, let's say I have 3columns in one table ... Col_1, Col_2 and Col_3. The data type forCol_1 and Col_2 are bit and Col_3 is char. I want to set a trigger onCol_2 to compare Col_1 to Col_2 when Col_2 is updated and if they'rethe same, set the value on Col_3 to "Completed". Can someone pleasehelp me?Thanks,Justin
View 7 Replies
View Related
Nov 20, 2006
Hi,
I have a table called "Cust_Vehicle_Specialisation" consisting columns named "Vehicle_Specialisation_Id", "Motortrade_Id" and Effectdate(datetime).
My data in the table consists like this..
Vehicle_Specialisation_Id Motortrade_Id Effectdate
A94ACDEE 007094C1 2006-05-16 13:01:00.000
2EC8B2BF 007094C1 2006-06-05 12:19:00.000
A1C617D2 007094C1 2006-06-12 17:37:00.000
458C5F70 007094C1 2006-07-25 09:44:00.000
139954DE 007094C1 2006-08-01 12:05:00.000
1F3D7BDB E1A50F16 2006-05-22 12:00:00.000
F85EA44A E1A50F16 2006-07-14 15:05:00.000
489700E8 E1A50F16 2006-08-21 12:59:00.000
I want to update the Vehicle_Specialisation_Id records for the same Motortrade_Id and the minimum effectdate record. In other words i want my outout like this..
Vehicle_Specialisation_Id Motortrade_Id Effectdate
A94ACDEE 007094C1 2006-05-16 13:01:00.000
A94ACDEE 007094C1 2006-06-05 12:19:00.000
A94ACDEE 007094C1 2006-06-12 17:37:00.000
A94ACDEE 007094C1 2006-07-25 09:44:00.000
A94ACDEE 007094C1 2006-08-01 12:05:00.000
1F3D7BDB E1A50F16 2006-05-22 12:00:00.000
1F3D7BDB E1A50F16 2006-07-14 15:05:00.000
1F3D7BDB E1A50F16 2006-08-21 12:59:00.000
I have wriiten a SP to do this one but it is a bit slow and i have quarter million records in my table. So i want a simple and single update query to do this one.
Cheers
Praveen
View 3 Replies
View Related
Jan 2, 2008
I am tring to execute the simple update query in query analyiser but after some seconds process got block by the same process(DEADLOCK).
query:
update tablename
set col1 = 0
Other info may help you:
Database had 3 different primary data files. In these files, first two files restricted file growth option is selected and there size is full. Only third file has space and option to grow.
Table containts total 50 columns and 15 milion rows.
Please help me to solve this ASAP.
Thanks in advance.
View 12 Replies
View Related
Apr 29, 2008
hello,
I recently transfered date into my sql server source table from Access data base but at that time one column was blank. ID and mainID so mainID was missing
Now i received and excell sheet. in which again i have ID and mainID both provided .
please let me knwo how do i update those mainID into the sqlserver cource table column using available ID.
thank youi
View 3 Replies
View Related
Aug 29, 2007
I searched a bit but didn€™t get too far in actually solving a case of deadlock in a simple query I have running here. The queries in question are executed under 2 separate transactions (Serializable IsolationLevel) and are shown below. I guess I don€™t understand how those 2 can deadlock because they are operating on different rows of the table and Serializable should keep them isolated pretty well too. Is it because I€™m using the column value inside an update stmt? How should this query be split if that€™s the case?
This is what the SQL Profiler has to say:
Lock: Deadlock Chain Deadlock Chain SPID = 59
Lock: Deadlock Chain Deadlock Chain SPID = 57
Lock: Deadlock my_user_name
57: UPDATE CreditCard SET Balance = Balance - 200 WHERE (Account = 0 AND CardHolder = 'Foo' AND Balance - 200 >= 0)
59: UPDATE CreditCard SET Balance = Balance - 250 WHERE (Account = 3 AND CardHolder = 'Bar' AND Balance - 250 >= 0)
I also used DBCC TRACEON(1204, 3605, -1) but I don€™t understand what the SQL log is telling me. Can anyone shed some light on why the above 2 statements sometimes cause the following:
System.Data.SqlClient.SqlException: Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I really don't want to retry the update if I don't have to. Table looks like:
Column DataType Length
Account int 4
CardHolder char 64
Balance float 8
View 9 Replies
View Related
Mar 21, 2005
I'm converting an ASP system from using msaccess to SQL Server as the db engine, and I'm stumped on the following query
update
timecard
set
TcdDate = #3/18/05#
TcdDate is defined as a date/time type
It will not run with the date bracketed by # signs, and when I take them out, 1/1/1900 is stored in the dbs. Is there a different symbol to bracket the date with or should I be using a function to convert the date?
View 4 Replies
View Related
