Help: How To Detect Inserts, Updates, Deleted On A Table From Within C++ Application?
Jul 20, 2005
Hopefully someone can at least point me in the right direction for more
research (e.g.: correct terminology). My only previous experience was just
dumping data into a database using ODBC, and that was some years ago so now
mostly forgotten.
I need to write an NT Service/Application (in C/C++) that will be getting
data sent to it via SQL Server 2000. The data will arrive in my SQL Server
(read-only access), via replication of tables from another remote SQL
Server.
My application needs know when new row are inserted, or updated so it can to
read this data (needs to be quick/timely so hopefully no polling) to then
interface with other remote proprietary systems.
T.I.A.
PS: If you can recommend appropriate books on SQL Server 2000 that would
also be useful.
Hi... I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc... ALTER PROCEDURE [dbo].[usp_Import_Plan] @ClientId int, @UserId int = NULL, @HistoryId int, @ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE @Count int, @Sproc varchar(50), @Status varchar(200), @TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.' UPDATE Statements..Plan SET PlanName = PlanName1, Description = PlanName2 FROM Statements..Plan cp JOIN ( SELECT DISTINCT PlanId, PlanName1, PlanName2 FROM Census ) c ON cp.CPlanId = c.PlanId WHERE cp.ClientId = @ClientId AND ( IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'') OR IsNull(cp.Description,'') <> IsNull(c.PlanName2,'') )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.' END ELSE BEGIN SET @Status = 'No records were updated in Plan.' END
SET @Status = 'Adding plan information to Plan table.' INSERT INTO Statements..Plan ( ClientId, ClientPlanId, UserId, PlanName, Description ) SELECT DISTINCT @ClientId, CPlanId, @UserId, PlanName1, PlanName2 FROM Census WHERE PlanId NOT IN ( SELECT DISTINCT CPlanId FROM Statements..Plan WHERE ClientId = @ClientId AND ClientPlanId IS NOT NULL )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.' END ELSE BEGIN SET @Status = 'No information was added Plan.' END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
I am looking for pros and cons for the following scenarios:
When a table contains a unique key constraint is it viable to always do an insert and immediately check the @@ERROR value and if @@ERROR states a duplicate key exception then perform an update statement?
Another possible solution would be to always check if the key exists and then do the insert / update based upon that result. This method will always require two steps.
Hello,Can someone point me to getting the total number of inserts and updates on a tableover a period of time?I just want to measure the insert and update activity on the tables.Thanks.- Vish
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
I have a number of columns with predefined character length but user can input more from gui. i want to trucncate automatically to the desired length and insert or update the database right now it does not allow me to update , or insert the values can i do it and how this is urgent
Can you help me to resolve my problem ? I have to do a simple daily backup system. Source : Flat File; Destination : SQL Server. I want to use the Slowly Changing Dimension component to backup only the new and updated row from my source (Flat File) and put them into SQL Server.
But how can I do to detect deleted rows from my source ?
Any suggestions ?
If it's not clear enough, please ask for more details !
Using Trigger(After Insert) in tbl1 to insert the data of tbl1.Pid to tbl2.Pid and tbl3.Pid . tbl1 will be uploaded with new rows and existing rows will be updated from the csv file.
Trigger in tbl1 works fine by inserting data to tbl2.Pid and tbl3.Pid.
My questions are : 1. Is it possible to use trigger to update tbl2 and tbl3 if records exist if not insert new record to colum Pid in tbl2 and tbl3 from Pid of tbl1
2.Or is it possible to use sql procedure to do cascaded updates or inserts by merging datasets from tables and csv file using asp.net
3.Or How can I use single Trigger to insert if not exist and update if data exist.
4.Or Do I have to use two trigger one for insert and one for update. If this is the case how do check for data existance.
After insert Triger I am using at the moment is below (replied as an answer in this forum for my previous thread)
create trigger t1 on tbl1 after insert as begin declare @id int select @id=pid from inserted insert into tbl2(pid) values(@id) insert into tbl3(pid) values(@id) end
Hi, i have the following sp. im using vs.net2005, sqlserver2005.
now how do i implement functions like - > okay using datasets, exception is thrown that the row has been modified and i get to inform the user, reload the modified row, .
how do i get same functionality using sp? is that possible ? how/
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[sp_Insert_Or_Update_Bill]
@BillID_All uniqueidentifier,
@BillID uniqueidentifier,
@Pono nvarchar(25),
@Date_ smalldatetime,
@SupplierCode nvarchar(25),
@Reference nvarchar(25),
@AmountType nchar(10),
@BillType nvarchar(25),
@TypeCode nchar(10),
@AmountFC decimal(18,2),
@ROE decimal(9,2),
@Currency nchar(5),
@LinkBill uniqueidentifier,
@multiplicityID uniqueidentifier,
@payment_or_bill smallint
as
If Exists(Select * from bill where billid_all= @BillID_All and amounttype = @amounttype)
begin
update bill
SET [BillID_All] = @BillID_All
,[BillID] = @BillID
,[Pono] = @Pono
,[Date_] = @Date_
,[SupplierCode] = @SupplierCode
,[Reference] = @Reference
,[AmountType] = @AmountType
,[BillType] = @BillType
,[TypeCode] = @TypeCode
,[AmountFC] = @AmountFC
,[ROE] = @ROE
,[Currency] = @Currency
,[LinkBill] = @LinkBill
,[multiplicityID] = @multiplicityID
,[payment_or_bill] = @payment_or_bill
where billid_all= @BillID_All and amounttype = @amounttype
Can someone point me to some code that properly prepares a string for an INSERT or UPDATE into a char or varchar column? I need to be able to handle single & double quotes and any other possible issues. thanks
I'm a relative novice on SQL Server and am a complete beginner at SQL, so am looking for a little help.
I currently use a DTS package to perform inserts / updates to a "production" table.
The DTS package transforms a comma separated file into a "temporary" table that is truncated / cleared before the load starts.
The temporary table has a column denoting Insert or Update. The production table is almost identical, however, doesn't contain the Insert / Update column. The DTS package then, depending upon the Insert / Update flag, either inserts data into the production table or updates data in the production table.
When the DTS package has completed, I'd like to be able to run an SQL Query that validates everything in the "temporary" table is identical to that in the "production" table, which it should be.
I have managed to do some queries to verify that everything has loaded / updated i.e. select primary_key from temporary table where primary_key not in (select * from production table), however, what I haven't been able to do is verify that all the columns on the temporary table match the values in the production table (excluding the Insert / Update flag).
I tried concatenating the columns in each table and comparing the concatenated values, however, this failed due to the different data-types, i.e. decimal, text etc.
We have a weird problem in our test SQL 2005 SP1 - Windows 2003 Enterprise SP1 - Server (after upgrading from SQL 2000).
While changing data via JBOSS - JDBC connection, we get all our data modifications rolled back.
Selects are fine. Same SQL 2005 Inserts/Updates through JBOSS are fine on other machines with Windows XP (JBOSS is on the same machine there, while in troubled configuration it's on its own server).
But in production imitation environment we can't modify data.
Hi,Before stepping into ado.net code to perform an insert or update, the insert / update has already taken place, just on starting the debugger. I use VS 2005 on SQL Server 2000. This did not happen with VS 2003 and SQL Server 2000.Anyone else encountered this?
Hello people!Here is the situation:We have an old propriatary database that is used for daily tasks. Not much we can do with it, but we have to use it.I can create basically a read only connection to it through ODBC.I would like to, on a timed interval copy certain data for reporting from this slow thing to my SQL server so that I can learn to program, and create some cool reports etc without having to wait on this server all day.So here is what I don't quite understand.I had originally planned on just deleting the contents of the tbl on my SQL server just before I populated it each time, but found out that my AutoNumber field will continue to increase, and I'm assuming that eventually I'm going to run into a problem as a result.Should I be doing some kind of update instead? if so do I need to first CHECK if the record exisit, if not then do an insert, if so do an update type thing?Or is there a way to basically do it in one command?I hope this makes sense. I would show you some code but there really isn't much to show you other than my insert statement :->Thanks for any advice!Josh
I am kinda new with SQL and am trying to get a count of on the number of updates and or inserts to any given or group of tables and cannot get the syntax correct...can anyone help with this? Thank you in advance. Colin P.
I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.
Why does it do this?
How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?
This managed application was written to run on a Symbol 3090 Win CE 5.0 scanning device. We are using the symbol provided classes to access the scanning interface, and SQL Compact database on the device to collect the scanned data, and then using merge replication to synchronize scanned data when the device is docked. The problem we have experienced seems to be releated to the performance when inserting and updating records in the database.
We have tested some randomly generated 1000 records and inserting/updatating into a database. At first the time to commit a record increases when the database is flushing into the memory (The flush interval in the connection string property is 10 seconds by default). and then as the database size grows increasing the time to commit every single record which is causing the application to perform slowly as they scan items into the database. However, the device program memory remains consistant as they are scan items. From our tests, I found the time to execute either a update/insert command on 2MB sqlMobile database (upto 10000 records, depending on the size of the columns) is taking nearly 2 to 2 and half seconds to complete. Below is the only code I am executing,
Hi, I have the following problem: Within a VBScript, I use a component (written in C++ I think with use of ADO) for sending "Insert", "Update" Statements to an SQL Server 2000 for inserting, updating data. If I insert 100 - 120 Records in a Loop, all works fine. If I insert 1000 records, approximately 150 records will be inserted very quick, then the program pause fo approx. 8 - 15 minutes and then it proceed for the next 150 recs, pause for 8 - 15 minutes and so on.
If I use a SQL Server 2005 for the database, all works fine. The same happens with another customer and another program written in Visual Basic 6.0 with ADO. The access to SQL 2000 pause and with SQL 2005 all works fine. It seems to me that this is a problem with some buffers, timeout, or so. Has anyone an idea on what screw I can turn?
Ok, I think this may have a simple answer. Basically I have no problems in setting up QueryString/Control/etc parameters when I use SELECT in the Configure Data Source Wizard as it prompts me for the necessary parameters. But when I try to use the Configure Data Source Wizard with an UPDATE, INSERT or DELETE it does NOT prompt me for the required parameters.Is this a bug or am I just missing something? Do I have to put them in manually or something?Thanks!
I have a 14GB database whose data content is legacy and is described as static. The log file is significantly large and continues to change size mostly increasing by 2-5GB a day (~60GB now) I have observed over the past two days; it shrank once unexpectly by a few GB. The instance is hosting other databases such as: EnterpriseVaultDirectory, EnterpriseVaultMonitoring, EnterpriseVaultStore, and NetPerfMon - might these seemingly unrelated data sources be involved?
I am trying to a trace to find traffic against the tables, no such luck.
Web applications are playing against it for queries but there should be no UPDATEs beign applied. I can only suspect that other unknown applications are performing operations but have yet to find unexplained connections.
Are there any other reasons why this type of log file activity would happen merely due to queries or stored procedure calls?
Lets also state, "mirroring, indexing, replication" are not at play. I know logging "Full" is not necessary as "Simple" should suffice but I am still hunting down why UPDATEs might be getting through. I realize I might adjust the migrated SQL 2000 security model to deny updates to find what breaks but would rather not take that iniative yet.
The installation is a fresh SQL 2005 Standard setup with SP2 applied; the databases were upgraded.
We have a SQLServer 2005 Enterprise merge replication publication with SQL Mobile 3.0 subscribers (Windows Mobile 5.0 and 6.0). We do not use pre-computed partitions due to trigger performance issues with an SSIS/ETL application that supplies data to the merge database. We do use the "Optimize" (=true) option, though we have tried this both ways with no significant differences. We use filters and joins for each worker ID (as HOST_ID) from the subscriptions.
The sync times become increasingly worse after we run the snapshot and bring the publication online. I have tried rerunning the snapshots, this helps little, as it often behaves like the subscription was set to reinitialize and forces a big sync (reload of all data) to the subscriber. We have tried much of the obvious (e.g., flattening filters and joins, adding indexes, etc.).
When users are synchronizing, we watch replication monitor and notice that a lot of time is spent processing "enumerating inserts and updates for article [any article]", especially processing the many generations and batches. This is true for any follow-up syncs after the 1st big sync (initializing the subscription).
I read several posts regarding the batches and generations of changes, and decided to try increasing the €œDownloadGenerationsPerBatch€?. I tried adding this parameter to the snapshot agent job, and the job fails each time with a vague message, even with the default value of 100. How do you change this parameter for SQLServer 2005 Enterprise?
here's the scenario..I am running a DTS to collect the summarized info from Oracle databaseinto SQL server. I then have a update job which updates mytransactional table from the summarized table.The update takes a very long time (~ 3 minutes)even though it hasaround 1500 rows which causes the application to timeout. I want thisjob to be done in less than a minute.Thoughts on improving performance. Is stored procedure a way to go?(I have used Isolation,row hints etc etc..nothing seems to be working)AJ
I have SQL Server 2000 (SP4) running on an Windows 2003 SP 1 server. I have started to get the error message "Connection to application object failed - Ensure no program modules have been deleted" when I go into Enterprise Manager.
I have tried reinstalled SQL Server and applied SP4 but to no effect. Can anyone help?
Hi,I want to check with VB.net whether a field in a SQL-table is NULL or not.This code doesnot work:If xxx = NULL then<statements>End IfI got the error, that NULL is not supported ?How do I code the check ???Help is appreciated, Gr.
when i run in query analyzer , using "exec SP_TEST" , it work and display result
but when run Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:TEST.TXT -c -Slocalhost -Usa -Ppassword'
it give error "SQLState = S0002, NativeError = 208" and show Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.
Regarding to my previous post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=553652&SiteID=1, just wondering if there's a way to detect any changes which is made on the existing indexes in a published table so that the device can determine whether reinitialization of subscrption is needed before every synchronization.
Hi,I would have used the aspnet membership tool to auto-create all the ASP.NET membership tables. However, the hosting company don't allow remote connections which meant I had to create the tables by hand, scripting the tables using script to CREATE using management studio.However, I noticed one of the tables has data without any users: aspnet_SchemaVersions, which causes an error when trying to log onto my site.The fix is to make sure the table has the 4-5 rows of data in it (which is missing off the live server). Its just a few rows of data, but I want to script the inserts for each row so I don't have to type them in using myLittleAdmin (the host's web version of management studio). Can anyone point me in the right direction?
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
