I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open() sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table scb = New SqlCeCommandBuilder(sda) sda.Update(dataset) cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open() Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.
If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.
We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.
We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:
/* Different functions to get current timestamp all have been tested to produce the same results */ /* SELECT GETDATE() GO SELECT CURRENT_TIMESTAMP GO SELECT {fn Now()} GO */ /* Use these statements to delete the tables to allow recreate of the tables */ /* DROP TABLE COMMIT_TEST DROP TABLE COMMIT_TEST_UPDATE */ /* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */ CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */ GO CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */ GO /* Use these statements to delete the triggers to allow reinsert */ /* drop trigger LOG_COMMIT_TEST_INSERT drop trigger LOG_COMMIT_TEST_UPDATE drop trigger LOG_COMMIT_TEST_DELETE */ /* Create insert, update and delete triggers */ create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as begin declare @time datetime select @time = getdate()
insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE) select PKEY, getdate() from inserted end GO create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as begin declare @time datetime select @time = getdate()
update COMMIT_TEST_UPDATE set LAST_UPDATE = getdate() from COMMIT_TEST_UPDATE, deleted, inserted where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY end GO /* In our application deletes should never occur so we dont log when they get modified we just delete them from the UPDATE table */ create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as begin if ( select count(*) from deleted ) > 0 begin delete COMMIT_TEST_UPDATE from COMMIT_TEST_UPDATE, deleted where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY end end GO /* Delete any previous inserted record to avoid errors when inserting */ DELETE COMMIT_TEST WHERE PKEY = 1 GO /* What is the current date/time */ SELECT GETDATE() GO BEGIN TRANSACTION GO /* Insert a record into the primary table */ INSERT COMMIT_TEST (PKEY) VALUES (1) GO /* Simulate additional processing within this transaction */ WAITFOR DELAY '00:00:10' GO /* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */ COMMIT TRANSACTION GO /* get the current date to show us what date/time should have been committed to the database */ SELECT GETDATE() GO /* Select results from the table we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */ /* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */ SELECT * FROM COMMIT_TEST GO SELECT * FROM COMMIT_TEST_UPDATE
Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).
hi.. i want to store value of time in the database i.e. the 'time in' and 'time out' value for a particular entry of an employee. i have take datetime as a datatype in MS SQL 2000 database and my language is vb.net. How can i store time value in my database?
Is there any way in SQL Server 2005 to store only time and not date ? Even if there is no datatype which supports it, is there a round about way of doing things ?
Hi,I have created a wizard form to collect user information.When use click Finish button all the details added to the SQL server database.That part is ok.But my problem is this Through my interface I am giving user to select the date and time.(I have used AJAX datepicker and MaskedEdit control)After user type the date (Normal format is - HH:MM:SS) and click finish button I check the database.It automatically Inserted the date also (Jan 1 1900)So I dont want this date part and I just want Time only.How do I do this ??(I have used datatype as : nvarchar instead of datetime .Because of if i use datatime it automatically inserts both data and time values)
Hello, I have a textbox on my formview which brings in a time from an sqldatasource. The time is displayed as 09:00:00 in the sql select statement i have a convert function - CONVERT (varchar, Time, 8) I am trying to alter the text e.g change to 09:30:00 and send it back to the database however i am having a lot of problems with that. Anyone know what i have to do to sort this. I think it might be something to do with the sql insert or the insert parameters as i dont know how to change the time String back to DateTime. Any help appreciated. Thanks in advance Mike.
my problem is that while i insert Time into datetime coloumn , it shows only 01/01/1900.it doesn't not shows '01/01/1900 12:00:00 AM' I run the following query insert into table1 values('1900/01/01 12:00:00 AM','1900/01/01 11:59:59 PM')result column1 Column2 01/01/1900 01/01/1900 11:59:59 PMhow can i solve this problem
i need to insert data into 2 tables. first in one, and the id of the register i just inserted is a field from the register in the other table (+ other data). inserting in this 2 tables should be invisible to the user so it has to be done automatically. the dumb way i guess would be using 2 ADODB.recordsets (rs,rs1). first insert in one store the id in a var (after rs.update, rs.movelast, var=rs.fields("id")) and after this inserting a register in the new recordset (rs1)
Hi everyone:Using Sql Server SQL 8I'm trying to INSERT records into a "can software package" batchtable. I have a work-table that mimics the batch table. Aftermanipulating the records in the work-file I want to INSERT them intothe batch table.The problem is the batch table in the can software has a trigger onthe batch table which is going to force me to INSERT one record at atime. I've always been able to do an INSERT with no problem.The batch table has pretty basic columns:BatchIDBatchDateSeqNumberThese three fields are the key and then just some miscellaneouscolumns. Any easy way to loop thru my work-file to insert theserecords. Never done a loop in SQL so an example would be reallyreally appreciated. I have a sequence number so I was hoping to do aWhile loop but I really don't know enough about creating a loop tomake that call. Thanks in advance for any help.
I need a little help. I want to load data to sdf database, with Visual Studio 2008. The database is on my PC. I want to copy to PPC, when it has the data. I thought it is easy... First I created a windows form application, and the database. I tried with TableAdapter.Insert(..), no exception, or error, but the database was still empty. DataSet.table.Rows.Add(newtableRow)+tableAdapter.Update works the same. My second attempt was to create a dataGridview. It can show data from the database, but can't insert. No error, but when I restart the application, the new row disappear. (Of course insert function is enabled.)
I was not able to realize the problem. The db is not read-only, and the conncection string works fine, and dataTableAdapter's insert method is also good.
HiLets say I have a user registration table and I want to know since when the member joined the club.How do I make the insert clause to include the date or date+time ?TIAGuy
Hello,I have 4 tables having Customer, Customer_personal_info, Customer_Financial_info, Customer_Other_infoIn this Customer table had a primary key CustomerID , related with every other table with fkey.I want to insert data into four tables using one form having TABs .I created class and storedProcedures to insert row for each table.How to execute all four classes using beginTrans-commitTrans-Rollback-EndTrans. Thanking you,
I am doing a temporary retro-upgrade right now. So, I know this isn't exactly in the scope of ASP.Net. Ordinally my posts are. However, I need a VBScript example of how to insert the Date only into the DateTime field of an SQL 2000 Server. By default, if you try to, the server automatically adds the date "1/1/1900". Can anyone help me please?
in my database , the column timeRequest appear : 1/1/1900 11:59:05 AM I dont want the date by default to appear, i want only the time like : 11:59:05 AM in my database, Anyone can help me?
I would like to be able to insert a time value only into a SQL 7 table colum which has been set as a datetime datatype. When I insert '12:34:44 PM' into the colum it actually inserts '1900-1-1 12:34:44 PM'. An Access table will allow you to insert the time value without adding the '1/1/1900' date value. Can this be done in SQL7?
Can one of you help me with using a cursor that would insert only 100 rows at a time from source table 1 to target table 2. I am not able to loop beyond the first 100 rows.
Here is what I have till now:
CREATE procedure Insert100RowsAtaTime AS SET NOCOUNT ON
declare @Col1 int declare @Col2 char(9) DECLARE @RETURNVALUE int DECLARE @ERRORMESSAGETXT varchar(510) DECLARE @ERRORNUM int DECLARE @LOCALROWCOUNT int
declare Insert_Cur cursor local fast_forward FOR SELECT top 100 Col1,Col2 from Table1 WHERE Col1 not in ( SELECT Col1 /* Col1 is PK. This statement is used to prevent the same rows from being inserted in Table 2*/ from Table2)
set @RETURNVALUE = 0 set @ERRORNUM = 0
BEGIN
open Insert_Cur fetch NEXT from Insert_Cur into @Col1, @Col2 while (@@FETCH_STATUS = 0) insert into Table2 (Col1,Col2) select @Col1,@Col2
SELECT @ERRORNUM = @@ERROR, @LOCALROWCOUNT = @@ROWCOUNT IF @ERRORNUM = 0 BEGIN IF @LOCALROWCOUNT >= 1 BEGIN SELECT @RETURNVALUE = 0 END ELSE BEGIN SELECT @RETURNVALUE = 1 RAISERROR ('INSERT FAILS',16, 1) END END ELSE BEGIN SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages] WHERE error = @@ERROR RAISERROR (@ERRORMESSAGETXT, 16, 1) SELECT @RETURNVALUE = 1 END
I'm trying to populate the sp_fact_sit_budget_test2 table with the employee_key from the base_employee_test2 table where the person_id's match and the the base_employee_test2 time_added_fis<= max sp_fact_sit_budget_test2 time_added_fis. So for the data shown below the sp_fact_sit_budget_test2 employee_key should get the value 312436.
CREATE TABLE [dbo].[SP_FACT_SIT_BUDGET_TEST2] ( [TIME_ADDED_FIS] datetime NULL, [PERSON_ID] int NULL, [EMPLOYEE_KEY] int NULL) ON [PRIMARY] GO
insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' CREATE TABLE [dbo].[BASE_EMPLOYEE_TEST2] ( [PERSON_ID] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TIME_ADDED_FIS] datetime NULL, [LAST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [FIRST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [EMPLOYEE_KEY] int NULL) ON [PRIMARY] GO
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123) insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123) insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-23 00:00:00','x','Guillermo',312436) insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-13 00:00:00','x','Guillermo',332196) insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-16 00:00:00','x','Guillermo',336180)
Hi all, I have a question about inserting records into sql server. Iam brand new to sql server, always using oracle previously. Beforetoday, I had always written statement such as this:INSERT INTO TABLE (COL1) VALUES SYSDATE;How is this accomplished in sql? I am using a datetime data type, Ihope that is correct . . .Thank you for your help.Ryanp.s. I tried getdate(), getdate, sysdate, and current_timestamp. Allto no avail :(
I have written a master proc which calls another proc (say proc1). This proc1 has insert-exec statements, for eg insert into #temp exec proc1. i.e. multiple times the proc would be nested.
This the err thrown : An INSERT EXEC statement cannot be nested.
i want to insert only time in my sql server database.but when m try to insert time in database it takes time+default date. i want to eliminate this default date.
It seems inserting records takes a relatively long time. My guess is it needs time to allocate disk space for the extra space needed. Assuming this is true, are there any DB settings that allow auto space allocation in bigger chunk? I am looking for something like "DB growth factor" or " Table growth factor"
I have a formview that uses a predefined dataset based on a cross table query. When the formview is in insert mode I need to insert the data into two seperate tables. Essentially I have tblPerson and tblAddress and my formview is capturing username, password, name, address line1, address line 2, etc. I presume I need to use a stored procedure to insert a row into tblPerson and then insert a row intp tblAddress. This is easy enough to do but the tables use RI and tblPerson has an imcremental primary key which needs to be innserted into a foreign key field in my address row. How do I do this? I'm using SQL Server.
hi guys im having real problems and dont know how to solve it at all i have a web app which allows users to enter information through edit boxes when they submit the imformation it gets added into my SQL database. does anyone know how to get the Date and Time when they insert the information and store in another column of type datetime in SQL database the web app is written in C# hope someone can help thanks
I am new to sql database programming. developing an application using C# and sql server 2005. i am having problems with date insertion to database. I use datatimepicker control to get date input. here is my code. in table i use datetime column type.
Prior to insert (into #JobListTable) I need to change the value of the 't2.plant_id' column (from #EquentialJobListTable). This column will have a value of 110, 300 or 320. If 110 then value should be SBGB2, if 300 then values should be RGWP and if 320 value should be RGWP. Is this possible to do?
INSERT#JobListTable ( job_date, job_number, job_phase, qty_received, plant_id ) SELECTt2.job_date, t2.job_number, t2.job_phase, t2.qty_received, t2.plant_id FROM#EquentialJobListTable AS t2 LEFT JOIN#JobListTable AS t1 ON t1.job_date = t2.job_date AND t1.job_number = t2.job_number AND t1.job_phase = t2.job_phase WHEREt1.job_date IS NULL
There are two applications running on different server say ServerA and ServerB. Both applications are using same database server SQL Server 2005 say ServerB. Called the application as ApplicationA and ApplicationB with respect to Server names
It means for ServerA the database is remote and for ServerB, database is local.
Both the applications are Java application and using datasource to connect to the database. The driver used are SQL Server 2000 driver (which includes 3 jars). This can be a question that why 2000 driver is used for 2005. The reason is, application on ServerA is getting error while using SQL Server 2005 as Driver not proper.
Problem Area:
When ApplicationB (local to database) is doing some DB operations (which includes select and then batch insert), ApplicationA (remote) is trying to insert a record which is taking too long time (around 40 sec.). This is causing timed out in ApplicationA.
ApplicationA is inserting the data into the same table from where ApplicationB is selecting the data.
