Big Records Not Always Updated
In our database we have a memo field that sometimes coud be over 9 MBytes.
We are using the following code (VB) to update it
lReg = Len(fielText)
lOffset = 0
Do While lOffset < lReg
sChunk = Left(Right(fieldText, lReg - lOffset), 32768)
rs("document").AppendChunk sChunk
lOffset = lOffset + 32768
DoEvents
Loop
rs.Update
The problem is that sometimes the record is NOT updated and there is NO errors returned by SQLServer.
Do you have any suggestion? Is there another way to update records with big fields?
View Complete Forum Thread with Replies
Related Forum Messages:
Get Last Updated Records?
If I update a recordset a group of records using dynamic SQL where I update the TOP n records, is it possible to get the set of records that was updated? CREATE PROCEDURE usp_Structural_ScheduleComponent @cProject char(7), @cComponentID char(10), @iPour int, @iQuantity int, @iAvailable int OUTPUT, @dtCast datetime OUTPUT AS SET @dtCast = convert(char(10), getdate(), 120) DECLARE @cSql varchar(500) SET @cSql = 'UPDATE tbStructuralComponentSchedule SET PourNumber = ' + CAST (@iPour AS VARCHAR) + ', ScheduledDate = ' + '''' + CAST(@dtCast AS VARCHAR) + '''' + ' WHERE EntryID IN ( SELECT TOP ' + CAST(@iQuantity AS VARCHAR) + ' FROM tbStructuralComponentSchedule ' + ' WHERE fkProjectNumber = ' + '''' + @cProject + '''' + ' AND fkComponentID = ' + '''' + @cComponentID + '''' + ' AND IssueDate IS NOT NULL' + ' AND ScheduledDate IS NULL' + ' ORDER BY EntryID DESC)' EXEC(@cSql) IF(@@ERROR <> 0 OR @@ROWCOUNT < = 0) RAISERROR('Failed to add components to pour!',16,1) SELECT @iAvailable = SUM(CASE WHEN IssueDate IS NOT NULL AND ScheduledDate IS NULL THEN 1 ELSE 0 END) FROM tbStructuralComponentSchedule WHERE fkProjectNumber = @cProject AND fkComponentID = @cComponentID GO -- Is there a way to return the recordset that were modified in the update? Mike B
View Replies !
Need To Track Updated Records
Hi all, I have a data of applicants. Everyday we dump this data into SQL server. Now I need to generate reports everyday so that we can track how many records get updated everyday. Now the thing is that the applicants are in various stages. So my reports need to track the how many applicants changed from stage "abc" to "pqr" and how many changed from "pqr" to "xyz". Now it is not necessary that all the records change stages everyday. thanks in advance, Rohit
View Replies !
How Can I Report On Entered And Updated Records
My database has many table, each table has a DateEntered (datetime), EnteredBy (nvarchar(50), LastUpdate (datetime), and LastUpdateBy (nvarachar(50). Is there an easy (ha) way to pull a list of the records that were entered and/or updated for a date range. Hopefully without a select for each table. Maybe a tool someone knows of?
View Replies !
Protecting Records From Being Updated And Deleted
Hi I am using sql server 2005 express and would like to keep all my fields from being both updated and deleted. In other words, once I create a new record, I would like to have it protected from being deleted and I dont want the field values to be updated/changed from the values initially entered. Is there a way to this without running triggers or changing database permissions and user roles? I tried making the database read-only, but then of course i cant add new records. Thanks
View Replies !
Updated Records From A Table Insert Into Another
Hi: I need to create a trigger to save every record which has been updated in a table (e.g. "Contacts") into another (e.g. "Shadow_contacts). So basically something like a versioning on database level. I have no experience with it and any precise hints/solutions would be very appreciated selmahan
View Replies !
UPDATE Stored Proc -- Updated Over All Of My Records
I wrote a stored proc to be implemented in a datagrid. I went and used it on a record to update some information and it updated THE WHOLE DATABASE WITH THAT ONE RECORD.. IF anyone could shead some light on what I'm doing wrong that would be great. Here is the syntax for my stored proc. CREATE PROC updateResults2 @id int, @address1 nvarchar (50), @address2 nvarchar (50), @CITY nvarchar (33), @ST nvarchar (10), @ZIP_OUT nvarchar (5), @ZIP4_OUT nvarchar (4), @home_phone nvarchar (22), @NEWPhone nvarchar (20) AS UPDATE Results SET address1 = @address1, address2 = @address2, CITY = @CITY, ST = @ST, ZIP_OUT = @ZIP_OUT, ZIP4_OUT = @ZIP4_OUT, home_phone = @home_phone, NEWPhone = @NEWPhone GO As said previously it ran but it updated the WHOLE DATABASE with the same change (WHICH I DIDNT WANT IT TO DO)!! Thanks in advance. RB
View Replies !
Linked Tables - After Path Updated Can't Add Records
Hello-I created a MS Access 2002 database with linked tables on a SQL Serverdatabase by way of a File DSN, A. I have created a form which points toone of the linked tables. After I finished testing it in thedevelopment environment I updated the path for all the linked tables toa new File DSN, B. After this action, I opened the form to test addinga new record and the "add new record" navigation button has beendisabled. Can anyone help? I need to be able to add new recordsthrough the form and can't figure out why I am now NOT able to add arecord. PLEASE HELP!!Thank you,nosenia--noseniaPosted via http://dbforums.com
View Replies !
Modifying Inserted Or Updated Records Without Recursion?
I am looking for a way to update the information in the "inserted" logical record without having to call something like this: UPDATE tblX SET ValueY = ValueA/100 FROM tblX INNER JOIN inserted ON tblX.ID = inserted.ID because this may result in the update trigger firing (again). I'd like to avoid this. As a better example, if I have a table of transactions with cost and price info, plus a flag indicating whether the transaction has been merged to AR or GL, I want to be able to update cost or price directly, which will clear the flag and indicate that I have a batch out of balance. This I can do easily with Update(Cost) Or Update(Price). But, I also need to be able to change the supplier in the transaction record. If I do this, I want the trigger to fetch the new cost and price for me. If the cost or price change, I still want to update the flag. To get there I can call UPDATE tbl SET Cost = @EffectiveCost, Price = @EffectivePrice FROM tbl INNER JOIN inserted ON tbl.ID = inserted.ID which is recursive. I can't say "UPDATE inserted" because it's a logical table. Is there any way to avoid this and to set values in the middle of an insert/update?
View Replies !
Linked Reports Not Being Updated When Master Report Is Updated
Since updating to SQL Server 2005 SP2 I've noticed two things about Linked Reports. 1. I do a lot of 'Snapshot' reports. With SP-1 if I updated a master report and made any changes to the Parameter List - it undid all my custom parameter changes on linked versions (restored to the Master Reports Defaults). While this is no longer happening with SP2 - it is still 'unhiding' the parameters. 2. With SP-1 if I added/deleted columns or made other changes to the report structure - the linked reports would pick up on the changes with their next refresh. With SP-2 I'm finding that I have to 'Re-link' the linked report back to the master report before the changes are refreshed. This is very time consuming especially with each report having 8 or more Snapshot reports pre-set up. Am I missing something - or is this a 'bug'... Any help would be appreciated...
View Replies !
Save Updated Date When Row Is Updated
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
View Replies !
How To Save &&"New&&" / &&"Updated&&" Records Only In To Destination Tables
Hi I have a requirement like, i need to save all the records from my Flat File on Monthly basis to my database table. In the next month, the flat file may be added with 10-20 records and also some updates happened to my old records. Though the faltfile is same for each month, the changes are occured in some records and also added with few records. So when i am loading this data in to Database table, i need to just update the changed records and also needs to add the new records. I don't need to touch the remaining records. How can i do that in SSIS 2005 using different data flow tasks ? Thanks Kumaran
View Replies !
How To Tell If A Row Is Updated
Hi Im doing a simple update in my SP: update users set Name = @Name where id=@userID and password=@password i want to know if a row gets updated. for example if the userID and password dont match then the row will not get updated.so i want some way to tell if a row has been updated. how do i do this?thanks
View Replies !
Updated
Thanks i just re tested again i think i had the database selected on the wrong one and was getting incorrect results. Seems to be working just as i thought. Snapshot got record 1 here then if udpate to 2 then this does not update the 1 to 2 Cheers
View Replies !
Data Flow Task To Delete Records And Then Insert Records In Transaction
HI, I have been trying to solve the locking problem from past couple of days. Please help mee!! Scenario: -------------- I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation. The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED. I dont know how to come out of this locking. Please help. Thanks , Sunil
View Replies !
SQL Help: Get The Record I Just Updated
Hi - apologies if this is not the right forum for this - I've searched a bit and this seems to be the best fit. I have the following problem: I want to update records in a table that fit certain criteria. The way the insert logic works make sure that there will always be only one record that fits the criteria and I'd like to get the ID value of that record once the update went through. So here is what I tried: 1 UPDATE Timeslot 2 SET StartTime = @StartTime, EndTime = @EndTime 3 WHERE (ProfessionalID = @ProfessionalID) AND (ProviderLocationID = @ProviderLocationID) AND (RequestID IS NULL) AND (StartTime > @StartTime) AND 4 (EndTime < @EndTime); 5 SELECT SCOPE_IDENTITY() My hope was that the select scope_identity would return the Timeslot ID of the row that was affected, but it doesn't. How do I get that row? Thanks!!! Oliver
View Replies !
Query Cannot Be Updated Because The FROM
Hi everybody, I am a total noob conserning ASP, but I am willing to learn We have a sql2005 SRV(hosted by our ISP, so limited access) and a ASP based forum (WEB WIZ) When I try to login I get this error: Support Error Code:- err_SQLServer_loginUser()_update_USR_CodeFile Name:- functions_login.aspError details:-Microsoft OLE DB Provider for ODBC DriversQuery cannot be updated because the FROM clause is not a single simple table name.Can somebody tell me whats wrong? Thanx in advance. Gerry de Bruijn!
View Replies !
Index Not Updated.
Hi, We run SQL SERVER 2000 on win2000 I've a question regarding Index on a table. I've a table (1 milj rows) with a extra index ZINDEX1. Now lets say I insert 1000 rows in that table. Now what will happend if I search on these NEW rows in my table. The Index is not updated. //Martin
View Replies !
Log All Updated Tables
We have a third party process that runs and updated several SQL tables. Is there any way to find out what tables are being updated and store it in another table?
View Replies !
When Was Db Object Last Updated?
Guys, Is there a way to find out when a certain DB object (e.g. Stored procedure) was last modified? SYSOBJECTS table contains crdate and refdate fields. None of these, however, appear to tell me when when the object was last updated. Any suggestions? Thanks a lot
View Replies !
#of Rows Updated
Is there a command that will tell me the number of rows that are updated in a statement. I would like to put this in an Stored Procedure and pass the #rows updated back out.
View Replies !
Getting The Name Of The Updated Table
I am writing a generic trigger in VS 2005 that selects records from the inserted table, and updates an audit table. I am, however, unable to retrieve the name of the table that the insert occurred on. I am using the following code to select the records, and obtain the name.. Can anyone offer any alternatives to accomplishing this task? Thanks in advnace for any help you can provide. Craig SqlDataAdapter tableLoader = new SqlDataAdapter("SELECT * FROM inserted", connection); DataTable insertedTable = new DataTable(); tableLoader.Fill(insertedTable); string insertedTableName = insertedTable.TableName;
View Replies !
Database Is Not Updated
I am trying to insert data to a simple table using SQL express However , I can see the data saved in the DataSet but not in the actual database. If I try to insert the same user again , I got primary key violation ??? Here is the insert function : string SqlConnection = @"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|QJ.mdf;Integrated Security=True;User Instance=True"; SqlConnection Conn = new SqlConnection(SqlConnection); SqlDataAdapter DataAdapter = new SqlDataAdapter("SELECT * FROM USERS", Conn); SqlCommandBuilder ComandBuilder = new SqlCommandBuilder(DataAdapter); DataAdapter.InsertCommand = ComandBuilder.GetInsertCommand(); DataAdapter.UpdateCommand = ComandBuilder.GetUpdateCommand(); DataSet DS = new DataSet(); DataAdapter.Fill(DS, "USERS"); DataTable DT = DS.Tables["USERS"]; DataRow DR = DT.NewRow(); DR["NAME"] = txtUserName.Text; DR["Password"] = txtPass.Text; DR["Type"] = 1; DT.Rows.Add(DR); DataAdapter.Update(DS,"USERS"); DS.AcceptChanges(); Conn.Close(); I dont know what I have done wrong Please help Thanks very much
View Replies !
Error{no Row Is Was Updated}
hey expert out ther.. i have encounter this error ..and i dont know what is the wrong with it.. the error goes: No row was updated Error source: .net SQLClient Data provider Error Message:string or binary data would be trancated. plz help me to slove this prob. thanks in advance!!!!!!!
View Replies !
I Want To Transfer ONLY New Records AND Update Any Modified Records From Oracle Into SQL Server Using DTS
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 Replies !
Need Efficient Query To Partition Records By Type And Pull Top N Records From DB
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each. 1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for? 2) CTE doesn't work on SQL 2000. I need a query that does. 3) I cannot modify the database itself in the process. ;WITH cte AS ( SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number() OVER(PARTITION BY [LogProcessName] ORDER BY [LogDate] DESC) AS RN FROM [LOGTABLE] WHERE [LogTypeGUID] IN ( SELECT LogTypeGUID FROM LOGTYPE WHERE LogTypeName = 'Process Status Start' OR LogTypeName = 'Process Status End' ) ) SELECT * FROM cte WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6 ORDER BY [LogProcessName] DESC, [LogDate] DESC Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above? Thanks in advance.
View Replies !
Table Is Not Getting Updated Fully
Hi all, i have a table1 with 3,25,000 records in US database.i want to upadate this table information in to table2 in SG database based on some condition (if prodid and skuno match in both tables, then update ordertransferind column in table2 from table1) what is happening is after 10,000 records query is not executing. in status bar it is displaying 'done'. but only 10,000 records are updating not the remaining ones. for this 10,000 records also it is taking more than 1 hour time. i tried by incresing the commandTimeout to max value(99999)...but still it is same...any advise...please suggest... i am using sql2005,visualstudio 2005.... thanks for the help.. Anne
View Replies !
Current Updated Value In Table
hello all.. i want to update my table information with the value which is currently inserted/updated in another table dynamically..how to get the value of a currently inserted single cell in that table..? table1 contains Refno,.....Refno is primarykey,identity table 2 contains uploadID,....uploadID is primary key,identity table3 contains RefNo,UploadID(both r foriegn keys corresponding to table1 & table2) how to fill table3 with values(not manually) when i am inserting records into table1 n table2 ..( refno and uploadId are identity columns )..Any Idea..? thanks in Advance Anne
View Replies !
An Email Is Sent When The Database Is Updated..any Help?
in the database, there is some information about the applicant: his name, his email, his status (accepted or rejected)....etc. the page that is shown to the administrator displays the applicant name and his status, when he changes any of the applicants status, an email is sent to that applicant. is there any way to do that? thanks in advance
View Replies !
How To Retrieve Last Updated Record
I have some set of records in my table. The same set of records will be updated often. Now I have a column as "lastupdated" While i am displaying the records in a datagrid, The LAST UPDATED record should only be displayed. Means, the recently updated records should be displayed in datagrid. Pls give me the sql code / i am also in need of a Stored procedure for this. I am working in SQL 2005
View Replies !
Finding Updated Tables
I have a front end inteface that adds data to tables in a SQL database.My question is:Is there an easy way of finding out what tables are affected by the update? Is there a sp_ or DBCC command that would give me the update or altered tables?
View Replies !
Help With Trigger: Updated Oct 11, 2005
Nevermind, I'll just use a stored procedure. Update: I've figured out what is going wrong now. I'm trying to find the subaccounts from the old invoice using the new invoicedate. This is doomed to fail! I've worked out a way to save the old invoice date in the C# programming, but is there anyway to make that value accessible to the trigger? Note that there will be nothing useful in the deleted table--this trigger is on inserts only. Any help is appreciated!
View Replies !
How Can I Be Notified When Record Is Updated
I want to build an windows application by using a visual C# to Notify the user that his data in the database had been changed ..such like "New Message In Your Mail Box Alert"..So I need to know if there is way that to let the SQL Server send a notify (just like Trigger) ..
View Replies !
Register Hasn't Been Updated
hi,i have a programmer problem. When i exec a update sql with odbcdrivers, in database i dont update register, but if i exec the executesql in manager, then i update the register. Someone knwos what ishappening?thanks for answer.
View Replies !
Prevent One Single Row From Being Updated
I am doing customization for microsoft POS. I manually added a recordto a table. The manage and maintenance of this table are done by POS,and user can update the contents of this table. Is there any way I canlock this single row at database level to prevent it from being deletedor changed by user? I am using SQL 2000 and vb.net.Thanks.Leanne
View Replies !
Finding Last Updated With A Timestamp
Hi AllI know an SQL Server timestamp seems to be as useful as rocking horse forshow jumping, but I'm hoping you know a 'fudge' to get me round a problem orat least confirm that it isn't possible.I have 2 tables, one called ACCOUNTS and one called STOCK.These tables have the usual ints, varchars, etc and have a timestamp fieldas well.My end user wants to see a simple list of the details in these tables(individually - no joins present here), but sorted from most recentlyupdated to never touched.As the timestamp seems to update each time a transaction hits it I thoughthis would be perfect, but I've had the following mixed results:1) I did a simple ORDER BY TIMESTAMP DESC, but because the order isalphanumeric I don't get a true 'recent to old' list.2) So I did ORDER BY CAST(TIMESTAMP AS INT) DESC. There is a numeric sortnow, but the timestamp values seem to be hit and miss so that an accountthat I know should be near the top is around about the middle.Do you know how I can achieve the results I want?Is the timestamp a waste of time?ThanksRobbie
View Replies !
Triggers - Run For Multiple Updated
I am new to trigger and I have following question. I have two tables: "Customer" & "AccountDetails". "Customer" stored customer's personal information, so one customer will be at one row. "AccountDetails" stored all the accounts information which tie to each customer, so there will be multiple rows for a customer since one customer can have mulitple accounts. Right now I have a updated trigger on "AccountDetails" table. When there is an updated to this table, it will insert some data to another "CustLog" table for logging when customer does the updates. Let's say Customer "A" has 5 accounts. When "A" updates his accounts' information, all 5 rows will be updated, triggers will be called 5 times, and 5 insert rows will be added to "CustLog". Is that any way to keep track those 5 updated on "AccountDetails" are referred to the same customer (by customer ID or so) so that it will only run the trigger once instead of 5 times? I hope I make it clear and please help me on this! :o
View Replies !
Log_shipping_secondaries Table Not Updated
Hi, I have configured the logshipping like this... 1. Primary server and monitoring server are same.. 2. Service (SQL & Agent) startup account (Domainuser) on primary and secondary server are same and added to local Administrator group and builtinadministrators group in SQLServer.These accounts have access to msdb database.. 3.Primary Server is is in a different timezone and 8 hours ahead of secondary. 4.Synchronisation is perfect on secondary server with primary server... But the log_shipping_secondaries table in the monitoring/primary server was not being updated by the secondary server..And as a result log_shipping restore alert job is failing with a message that secondary server is outof sync by 2111 minutes... Can anybody tell me the cause... Regards, NSR
View Replies !
|
TRACKER
