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 Complete Forum Thread with Replies
Related Forum Messages:
Keep Track Of When And By Who A Record Was Created And Also When And By Who The Record Was Last Updated
I have not yet succeeded in getting an aswer to this in a previous post, so I'll try again and rephrase the question I have 2 fields, 1 called 'Created' the other 'Updated' I would like to create a function, user procedure ,whatever in SQL something along the lines of... FUNCTION myUID() RETURN Date(Today)+Time(Now)+USERNAME END FUNCTION so that the value returned by the above pseudo code is something like '20080526T21:01:05.620SamL' where the date part is in yyyy0m0d format I have got this working fine for the default 'Created' field but the calculated (and persisted) field comes up with something about being non-deterministic and refuses to play Clearly something along these lines is a very straightforward requirement (I have it working fine in my 20+ year old database) , but have spent a week trying to get an answer (even from this forum) without success, so would be grateful for any help that you can provide me with to get round this barrier
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 !
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 !
Need Help Checking To See If More Than 1 Record Updated.
I am trying to write a trigger that, on an update, will check to see if more than one record is being updated. If more than one record is being updated, rollback the transaction and print a message. If not, then commit the transaction and print an OK message. My problem is I am having troubles writing this trigger. I will post my SQL trigger code so far. I am new with Triggers, so any help or advice is appreciated. Code: CREATE TRIGGER trgPaperCheck ON BOOK AFTER UPDATE AS IF UPDATE(Paperback) BEGIN DECLARE @count AS INT SELECT @count = COUNT(Paperback) FROM Book IF @count > 1 BEGIN PRINT('Only one Book record can be updated at a time!') ROLLBACK TRANSACTION END ELSE IF @count =1 BEGIN PRINT('Paperback Updated') COMMIT TRANSACTION END END
View Replies !
How To Get The Record I Just Updated By Mistake
Hi, I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record, is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB. Also, is there a way to know the value that was just there before the changes? Thanks a lot.
View Replies !
Retrieve Last Inserted Or Updated Record
Hi I have an application which get any change from database using sql dependency. When a record is inserted or updated it will fire an event and my application get that event and perform required operation. On the event handler I am usin select ID,Name from my [table]; this will return all record from database. I just want to get the record which is inserted or updated. Can u help me in that. Take care Bye
View Replies !
Trigger To Update An Updated Record?
I have a table called invoice. The invoice table has two audit columns: last_update_by and last_updated_date. I am trying to create an ON UPDATE trigger that will use the getdate() and user_name() functions and udpate the updated record with those two values. In other words, I want to create an ON UPDATE trigger on the invoice table that udpates the invoice table. Would this create an infinite loop? Can you update an updated record through the use of a trigger in this manner? Thanks for your help.
View Replies !
(sql 2005) How To Get The Record I Just Updated By Mistake
Hi, I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record, is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB. Also, is there a way to know the value that was just there before the changes? Thanks a lot.
View Replies !
Auto Update Value When Record Gets Updated
I have a table that contains a field LastUpdated. This field should contains the date of the last update of the record. Is there a way to auto-update this field value to the getdate() value each time the record is "touched" by an UPDATE sql command? Thanks in advance, Joannes
View Replies !
Display Last Updated Record By A User
I would like to show when leads updated last their records in database. An automated report that tells me when the last date was that the leads updated an entry, only 1 entry per lead. select Lead,LastUpdated from dbo.KPITbl I have a table with data that looks like this: Lead LastUpdated ----------- ----------------------- JOHN SMITH 2008-03-26 08:45:00 JOHN SMITH 2008-03-20 09:33:00 MEG RYAN 2008-02-21 16:16:00 JOHN SMITH 2008-02-21 16:19:00 MEG RYAN 2008-02-21 16:22:00 JOHN SMITH 2008-03-28 16:10:00 JOHN SMITH 2008-03-28 08:49:00 JOHN SMITH 2008-03-23 19:23:00 MARK MCRAE 2008-03-27 03:12:00 MARK MCRAE 2008-03-26 08:48:00 MARK MCRAE 2008-03-26 08:46:00 JOHN SMITH 2008-03-26 08:47:00 JOHN SMITH 2008-03-26 08:48:00 ALLAN WHITE 2008-03-26 08:43:00 ALLAN WHITE 2008-03-26 08:40:00 JOHN SMITH 2008-03-26 08:48:00 Thank you appreciate it.
View Replies !
How To Determine Which Record Was Updated Using A Update Trigger?
Im using a trigger to check updates on particular table and execute a email. it works but it doesnt show the right record im looking into one table called SiteInfo. here is my code Im using sql 2005, can someone look at my code or the select statement. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE TRIGGER TTSUpdate ON SiteInfo FOR UPDATE AS declare @SiteID varchar(10) declare @Body2 varchar(2000) declare @Sitename varchar(50) declare @TTSCreate varchar(30) declare @TTSCreator varchar(50) declare @Subject2 varchar (100) SELECT @SiteID = SiteID,@Sitename = AccountName,@TTSCreator = TTSOwner,@TTSCreate = TTSCreatedDate from SiteInfo SET @Body2 = 'New TTS site created: ' + @Sitename + ' With TTS Site ID:' + @SiteID + ' TTS was created on: ' + @TTSCreate + ' By:' + @TTSCreator SET @subject2 = 'New TTS site created: ' + @Sitename EXEC msdb.dbo.sp_send_dbmail @profile_name = 'TTSAdmin', @recipients = 'email address here', @subject = @subject2, @body = @body2 GO
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 !
Ways To Make This Work: Several Selectable Related Record For One Main Record.
Hey all! Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen. To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues. On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form. I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it. Thanks in advance, Johan
View Replies !
Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem
Hi, We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker. If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below. Thanks, Sarah The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on. This is running on a Websphere Application Server v6.1. Code snippet - getting the record thru JDBC: DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer"); Connection wsCon = wsDataSource.getConnection(); // wsCon.setAutoCommit(false); //have tried with and without this flag - same results Statements stmt = wsCon.createStatement(); String sql = "SELECT * FROM Person where personID = 12345"; ResultSet rs = stmt.executeQuery(sql); if(rs.next()){ System.out.println(rs.getString("lastName")); } if (rs != null){ rs.close(); } if (stmt != null) { stmt.close(); } if (wsCon != null) { wsCon.close(); }
View Replies !
SSIS: Multi-Record File Extract With 9 Record Types
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful: http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx I have been able to create a sample file with two of my record types. I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file. does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this? Thanks in advance for any help you might be able to provide.
View Replies !
Delete Record Based On Existence Of Another Record In Same Table?
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View Replies !
Record Locking: Multiple Users Accessing The Same Record
I have read several discussions about SQL 7 having built-in record locking. I am assuming that this is only during the transaction process. I have a problem with multiple users access the same record on a SQL table. We have these users accessing the SQL data with an Access 2000 DB Project form. When one person accesses the form to pull up a record, someone doing this at the same time will get an error window that asks the user to Save/Drop changes. Is there any way to LOCK DOWN a record until a user has finished making changes to it?
View Replies !
Switch Record Background Color With Each Record In Report
Hi Everyone- i have a matrix report and i want to switch the record background color with each record in the value column in that matrix report e.g 1st record background color is gray and next record background color is white and then the next record background color is gray ... and so on can anyone help? thanx Maylo
View Replies !
SQL Challenge - How To Return A Record Set Starting At A Particular Record?
I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.
View Replies !
Joining Record With The Most Recent Record On Second Table
Could anybody help me with the following scenario: Table 1 Table2 ID,Date1 ID, Date2 I would like to link the two tables and receive all records from table2 joined on ID and the record from table1 that has the most recent date. Example: Table1 data Table2 Data ID Date1 ID Date2 31 1/1/2008 31 1/5/2008 34 1/4/3008 31 4/1/2008 31 3/2/2008 The first record in table2 would only link to the first record in table1 The second record in table2 would only link to the third record in table1 Any help would be greatly appreciated. Thanks
View Replies !
Lookup && Update Record && Insert Record
Hi All, I am trying to create package something like that.. 1- New Customer table as OleDB source component 2- Lookup component - checks customer id with Dimension_Customer table 3- And if same customer exist : I have to update couple fields on Dimension_Customer table 4- if it does not exist then I have insert those records to Dimension_Customer table I am able to move error output from lookup to Dimension_Customer table using oledb destination but How can I update the existing ones? I have tried to use oledb command but somehow it didnt work my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup) I will be appreciated if you can help me...
View Replies !
Multiple Record Insertion For Each Record From Source
Hi, How can we insert multiple records in a OLEDB destination table for each entry from the source table. To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record. how is this achieved. thanks.
View Replies !
Update A Record Based Of A Record In The Same Table
I am trying to update a record in a table based off of criteria of another record in the table. So suppose I have 2 records ID owner type 1 5678 past due 2 5678 late So, I want to update the type field to "collections" only if the previous record for the same record is "past due". Any ideas?
View Replies !
Column Locked In Record Even Though No One Accessing Record
Hello. I have a database with a record that has two columns locked. descrip1 and descrip2. they are both nvarchar(max) columns. These are the only two columns of the record that remain locked. I am certain no user is accessing the record. I have even moved a backup of the database to my testing computer and the lock still exists. How do I remove the lock from these two columns on that particular record. I can edit these two columns on other records. I have researched "Unlock" on MSDN but it doesn't seem to apply to t-sql. Any help would be greatly appreciated. Thanks. Gary.
View Replies !
How To Return First Record Child Record And Count
I've been looking for examples online to write a SPROC to get some data. Here are the tables. Album_Category AlbumCategoryID (PK, int, not null) Caption (nvarchar(max), not null) IsPublic (bit, not null) Albums AlbumID (PK, int, not null) AlbumCategoryID (int, null) Caption (nvarchar(max), not null) IsPublic (bit, not null) I need to return: -[Album_Category].[AlbumCategoryID] -[Album_Category].[Caption] -[Albums].[Single AlubmID for each AlbumCategoryID] -[Count of Albums in each AlbumCategory] I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.
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 !
TOUGH INSERT: Copy Sale Record/Line Items For &"Duplicate&" Record
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View Replies !
Check Values Record By Record
Consider this scenario. I have two database in the sql server and consider that i have a query which has 4 tables inner joined. When i execute the query in the database1 , the query is returning rows, But when i execute the same query in the database2, the query is not retuning rows . I know that the no rows are returned because of missing data in the database2. But have no idea how to trace what values are missing in the database2. Please note the tables is having a huge list of records by which manually comparison is painfull. Please consider i dont have any background idea of the values in the tables but just using it. Any help would be appericated.
View Replies !
Grabbing First Record Rather Than The Record I Am Trying To Find.
I tried checking to see if the point at which the reader was, that if it was the record I am looking for to go ahead and add the table data to a label. But for some reason it's only taking the first record in the database and not the one I thought I was at.[CODE] public void UpdateMaleHistLbl() { SqlConnection conn = new SqlConnection("Server=localhost\SqlExpress;Database=MyFamTree;" + "Integrated Security=True"); SqlCommand comm = new SqlCommand("SELECT * FROM FatherHistTable, MotherHistTable, UsersTable WHERE UsersTable.UserName = @usrnmeLbl ", conn); comm.Parameters.AddWithValue("@usrnmeLbl", usrnmeLbl.Text); conn.Open(); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { string usr = reader["username"].ToString(); usr = usr.TrimEnd(); string pss = reader["password"].ToString(); pss = pss.TrimEnd(); if (usrnmeLbl.Text == usr) { if (hiddenpassLbl.Text == pss) { maleHistLbl.Text = reader["GG_Grandfather"] + " > "; maleHistLbl.Text += reader["G_Grandfather"] + " > "; maleHistLbl.Text += reader["Grandfather"] + " > "; maleHistLbl.Text += reader["Father"] + " > "; maleHistLbl.Text += reader["Son"] + " > "; maleHistLbl.Text += reader["Grandson"] + " > "; maleHistLbl.Text += reader["G_Grandson"] + " > "; maleHistLbl.Text += reader["GG_Grandson"] + "<br /><br />"; } } break; //exit out of the loop since user found } reader.Close(); conn.Close(); }}[/CODE]Thanks in advance
View Replies !
Value Of A Record Based On A Previous Record
I hope you can help me. I posted this in the microsoft sql server newsgroupa few days ago and got no response so I thought I'd try here. If I canprovide any clarification I'll be glad to do so.I'm trying to calculate a column based on the value of the previous record.I'm not very experienced with SQL-Server.I'm using the following table:CREATE TABLE tblPayment([PaymentID] [int] IDENTITY (1, 1) NOT NULL ,[LoanID] [int] NULL ,[PaymentPeriod] [int] NULL ,[PaymentRecDate] [datetime] NULL ,[PaymentAMT] [money] NULL)I have a view based on this table. That view has the following calculatedcolumnsBeginningBalance: For the first record, this is equal to the loan amountfrom the loan table. For each additional record this is equal to the endingbalance from the previous payment record.Interest: BeginningBalance * the monthly interest rate from the loantablePrincipal: PaymentAMT - InterestEndingBalance: BeginningBalance - PrincipalIt might seem I could use a subquery to calculate the Beginning Balance asin:SELECT LoanID, PaymentPeriod, PaymentAMT,(SELECT SUM(PaymentAMT) FROM tblPayment AS tbl1WHERE tbl1.LoanID = tblPayment.LoanID AND tbl1.PaymentPeriod <tblPayment.PaymentPeriod) AS BeginBalanceFROM tblPaymentWHERE (LoanID = @LoanID)But this will not work, because the interest is calculated on the previousmonth's balance. I need to find a way to loop through the recordset. Isthis possible?Thank you,--Derek CooperDatabase9www.database9.com
View Replies !
Missing Record - Phantom Record
Hi All,Have come across something weird and am after some help.Say i run this query where rec_id is a column of table arlhrl,select * from arlhrl where rec_id >= 14260This returns to me 2 records with rec_id's of 14260 and 14261Then I run this queryselect * from arlhrl where rec_id >= 14263This returns 7 records with rec_ids of 14263 up.How come the first query doesn't return the records returned by the2nd query also?If I select for 14262 no records are returned. It is like this is aphantom record or has an end of file character in it.I tried re-creating the indexes but to no avail. If anyone has anyideas about what could be causing it or how to fix it it would be muchappreciated.Thanks in advance,Andrew
View Replies !
Looping Record By Record And Processing
I have 2 tables they are identical what i need to do is when i make a update on one table the other table needs to reflect that same table. so lets say i insert into table A i need to look in table B if the record exist if it doesnt insert it if it does update it and if it exist in table B and doesnt exist in table A delete the record but i have to do this record by record..can anyone help me with this
View Replies !
Loop Through Each Record And Then Each Field Within Each Record
I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column. Something like this maybe using a cursor or something else: For each record in my table (I'll just use the cursor) For each column in current record for cursor perform some sql based on the current column value Next Next So below, all I need to do is figure out how to loop through each column for the current record in the cursor AS DECLARE Create_Final_Table CURSOR FOR SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1, RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2, RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2 FROM EBN_TEMP1 OPEN Create_Final_Table FETCH FROM Create_Final_EBN_Table INTO @FieldName, @AcctNumber, @Screen, @CaseNumber, @BKYChapter, @FileDate, @DispositionCode, @BKUDA1, @RMSADD2, @RMSCHPNAME_1, @RMSADDR_1, @RMSCITY_1, @RMSSTATECD_1, @RMSZIPCODE_1, @RMSWORKKPHN, @BKYMEETDTE, @RMSCMPNAME_2, @RMSADDR1_2, @RMSCITY_2, @RMSSTATECD_2, @RMSZIPCODE_2, @RMSHOMEPHN, @BARDATE, @RMSCMPNAME_3, @RMSADD1_2, @RMSADD2_3, @RMSCITY_3, @RMSZIPCODE_3, @RMSWORKPHN_2 WHILE @@FETCH_STATUS = 0 BEGIN @Chapter = chapter for this record For each column in current record <---- not sure how to code this part is what I'm referring to do some stuff here using sql for the column I'm on for this row Next Case @Chapter Case 7 Insert RecoverCodeRecord Insert Status Code Record Insert Attorney Code Record Case 13 Insert Record Insert Record Insert Record Case 11 Insert Record Insert Record Insert Record Case 12 Insert Record Insert Record Insert Record END close Create_Final_Table deallocate Create_Final_Table
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 !
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 !
|
TRACKER
