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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
Finding Last Updated/modified Date
Hi, I am using SQL Server 2000 and have the following questions: 1. How do I know the last updated (data) date using system objects or any other method? 2. How do I know the last modified date of a table using system objects or any other method? 3. How do I know when a table is last accessed Any help is appreciated Thanks for your time and help in advance Kumar
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 !
How To Keep Tables Updated That Are Imported From Other Database
Dear All, I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2000 as a back End. Actually I am fetching data from other database(named as retailexcel but in same server). I have wriiten a view (in retailexcel database) to get required data from four table of other database. My problem is when I fetch those data(about 40000 to 50000 record) It taking to much time. I tried catch feature of asp.net2.0. Time reduced but not significantly. So I am trying to import those tables to my database (named Inventory) from that retailexcel. Now problem is every day or two, record of those tables changes. How can I keep update my tables (which are importing from retailexcel) Please suggest me some idea. Thanks
View Replies !
Keep Remote SQL Data Tables Updated
Hello, I need some guidance in the best method to accomplish this task. I have a network with a SBS 2003 server and a SQL 2000 member server in the SBS domain. I have a remotely hosted website at discountasp.net with a SQL 2000 database. The website will host a modified e-commerce kit where corporate clients can order parts. I need to keep the products table on the remotely hosted site as updated as possible. The website orders are placed via email from the commerce kit and the fulfillment department proccesses the order against the SQL server in the SBS domain. Any ideas on how to keep the data updated on the website?
View Replies !
Insert/Updated SP From Multiple Tables
How do I insert unrelated statistical data from three tables into another table that already exist with data using an insert or update stored procedure? OR... How do I write an insert/Update stored procedure that has multiple select and a where something = something statements? This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this.... CREATE PROCEDURE AddDrawStats AS INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers) SELECT WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing), TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1), Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ), RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID ) FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID WHERE D.DrawingID = DS.CurrentDrawing GO
View Replies !
How To Find The Last Updated Tables In A Database
Hi, I am trying to create a data dictionary for a huge application which has aroung 300 tables in the database....when i perform any operation in the application some tables are updated.... can you help me to find out how can we find out the last updated tables in the database ??
View Replies !
Propagation Of Event Notification When Tables Are Updated.
I would like to propagate an event signal to an external applicationwhen a table in my MSSql2000 server is updated.Prog A; I have an external application adding records to a table.Prog B; I have another external application using this table as well.When Prog A creates a new record in the Table, how can I have Prog B benotified of the event without polling the table or creating a linkbetween Prog A and Prog B.Thanks.
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 !
Replacing Tables In SQL Express By An Updated Table Of The Same Name Through VB2008 Code
Hello, I have an application written in Visual Basic 2008, using a SQL Express 2008 database. This application runs in several locations on stand-alone PC's and there is no network connecting them. I have to maintain a SQL database in a central location an would like to be able to distribute updated tables from that central location by using an USB stick and replace the tables of the same name in the different locations with the updated ones on my USB stick. This I would like to do using code within my VB2008 application. Can someone please help me on the way to achieve this. Is it possible to give some concrete code examples, as I am a newbie as a SQL Server user. Many thanks and greetings, Michel
View Replies !
Finding Affected Tables
I am attempting to document a sql server 2000 based accounting system.Is there any way to see what tables a stored procedure affectswithout diving into the code?Regards,Ty
View Replies !
Finding Out All The User Tables Being Used By An Application
Here's the situation, developers inherit a web app from someone,backend SQL db has about 120 user tables and the db is also being usedby other apps. Developers don't have a list of user tables being usedby this app, now, I need to create a new db based on this one, whichwould be used by this app only. So, I intend to find all the usertables being used by this app, then copy its schema and possibly dataas well to a new db. FYI, current ERD is not this app.One option to find out all the user tables being used by this app is todo recursive search for FROM and JOIN against the full spectrum of theuncompiled source code, then, weed through such an extracted list whenin doubt about a particular table, one caveat is a portion of code withsql stmt could have already been commented (no longer being used thoughstill in the code).Better option?TIA.
View Replies !
Help Finding And Updating Dupes In 2 Tables
Being fairly new to SQL and SQL scripting, I am at a loss on how to proceed on my issue. I have a MSDE database with 2 tables that need to modified. I am changing to a standard 12 digit code in my PATIENTS table for the field sChartCode nvarchar). That code will be in the form of 110012345678. 1100 will preceed the actual 8 digit chartcode In the PATIENTS table, the same person may be duplicated many times using vaiations such as 123456, 12345678, 012345678, 12345678 SMITH, 012345678 SMITH. For each of these records, they are linked to the RECORDS db using the field lPatientId (int). I have already manually updated about 20K records in the RECORDS db which takes way to many hours of time. New records will be imported at about 10K a week or so and will be over 100K soon. By the way, the SQL server is on the way. What I am looking for is an easier way to find the records that have not been converted in the PATIENTS db and see if they match one that has already been converted. If it has, it would need to update all records in the RECORDS db with the correct updated lPatientId and then delete the duplicate record(s) from the PATENTS db. If not, it would only need to add '1100...' to the lPatientId field. Any help or guidance that anybody can give will be most appreciated. Dale
View Replies !
Finding If A Record Exists In Either Of Two Tables
Hello, I have two tables with the same field layout, and they both have the same field as the Primary Key. They just contain different data. I would like to know if a record exists in one, or both, tables. The tables are InvTemp1 and SalesTemp1. The key for both is stock_number. Here is the command so far: SELECT COUNT(*) FROM InvTemp1 INNER JOIN SalesTemp1 ON InvTemp1.Stock_number = SalesTemp1.Stock_number WHERE (InvTemp1.Stock_number = '101053') Thank you for any ideas, Tom
View Replies !
SQL Server - Finding The Different Records In Two Identical Tables
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..
View Replies !
Correct Way Of Finding A Tables Primary Keys??
Hope this is in the right thread, sorry if not! I have run into a problem, i need to find out that column(s) in a table that makes the primary key. I thought that this code did the trick. *** DECLARE @c varchar(4000), @t varchar(128) SET @c = '' SET @t='contact_pmc_contact_relations' Select @c = @c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @t and k.colid = c.colid ORDER BY c.colid SELECT Substring(@c, 1, Datalength(@c) - 1) *** This works in most of my cases. But i have encounterd tabels where this code doesn't work. Here is a dump from one of the tabels where it doesn't work. SELECT * FROM sysindexkeys WHERE (id = 933578364) <--id of the table *** id indid colid keyno 933578364 1 1 1 933578364 1 2 2 933578364 2 1 1 933578364 3 2 1 933578364 4 3 1 933578364 5 4 1 933578364 6 5 1 933578364 7 6 1 933578364 8 7 1 Not sure if that dump made any sense, but i hope it did. If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2). So, anyone know how i could solve this problem? Regards /Anders
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 !
Finding Mismatched Rows Between Identical Tables Based On 2 Or More Cols
CREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)
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 !
Problem When Applying A Snapshot When Tables Have Been Updated During Snapshot Generation
Hi I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated. Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name. The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors. I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name. All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
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 !
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 !
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 !
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 !
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 !
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 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 !
|
TRACKER
