Using Sql Server SQL 8
I'm trying to INSERT records into a "can software package" batch
table. I have a work-table that mimics the batch table. After
manipulating the records in the work-file I want to INSERT them into
the batch table.
The problem is the batch table in the can software has a trigger on
the batch table which is going to force me to INSERT one record at a
time. I've always been able to do an INSERT with no problem.
The batch table has pretty basic columns:
BatchID
BatchDate
SeqNumber
These three fields are the key and then just some miscellaneous
columns. Any easy way to loop thru my work-file to insert these
records. Never done a loop in SQL so an example would be really
really appreciated. I have a sequence number so I was hoping to do a
While loop but I really don't know enough about creating a loop to
make that call. Thanks in advance for any help.
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!!!
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row? thanks
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
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
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)
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 don€™t 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).
I would like to return the latest time stamp as well as the earliest time stamp out of a series of records.The key would be the same ID so i need a function or query that can search through each record and find the earliest time for that ID and the latest time.
ID LatestTime EarliestTime 1 05:00:00 02:00:30
I have achieved the latest time by using a RowNumber function and returning the latest record. The stickler is how to find the earliest record as well.
Code: CREATE TABLE [dbo].[DigiTracker]( [DigitrackID] [int] IDENTITY(1,1) NOT NULL, [contact_id] [int] NOT NULL,
[Code]....
-- not accurate b/c it returns 0 for all TimePerPageSec SELECT DATEDIFF(second,min(createDt),max(createDt)) AS TimePerPageSec FROM DigiTracker GROUP BY contact_id, ZinePageNumber time spent (in seconds) between each record
I want to know how long does each of the script piece take. So how can I keep the records of time spent and save them as a file or save them into a table?
I have a table with several datetime fields but they all list just the correct date. The time is always 12:00:00 AM. I don't have a field that records the actual time a record was created. I have a timestamp column, but it appears to get updated everytime the DB backs up. Is there a way using the date fields I have or otherwise, to determine the time a record was created?
We have a website that allows people to enter tasks and they are then given a stageID. ie 1 which is review for when they are first entered into our database. If they have not been reviewed in 5 minutes I want them to update automatically and move onto the next stage. I have tried using a stored procedure that adds the reocrd with a waitfor command but that doesn't work because it locks the connection ofcourse until it's finished the stored procedure which means the webpage doesn't come back for 5 minutes which we don't need. I'm asking if there is any other way I can do this without a waitfor command or somehow use it so that it doesn't hold the current connection.
CREATE TABLE GuestAccount ( Id_Guest smallint, SeqNo int IDENTITY(1,1), account money Null PRIMARY KEY(Id_Guest,SeqNo) ) Q? I want my SeqNo start from 1 for new Id_Guest is it possible ?
I am not sure how to go about doing this. I have a record that has a start time of 1 am and a stop time of 9pm (same day for simplicity) and I want to know how many hours during a peek time and how many were not.
For example, the application starts and inserts into the data base the start time of 1am and then the user stops the app at 9pm. Lets say the peek hours are 1pm to 7pm. I know i can do a date diff function to get how long the app ran for but how can I get the amount of hours it ran during the peek time? I know there has to be some mathematical solution to this but it is escaping me at the moment. I want to do this over many records so a a cte or pivot table is the end solution for performance.
This is a beginner's question so bear with me - I have a SSIS package that builds an Analysis Services fact table - depending on user input, this table is either re-built from scratch or any additional records since it was last run are created.
Where is the best place to store the time a package was last run - in a seperate table in the database or somewhere in the fact table?
Working with SQL 7 and Visual Basic 5.0: We have experienced a lock situation and we do not know how it happened nor how to solve it:
When trying to update a record we get the following message.
Run time Error 3197: The Microsoft Jet Database engine stopped the process because you and another user are attempting to change the same data at the same time.
And for sure, ONLY one user is connected at the time. Apparently, the record is marked and can be read but CAN NOT be updated. Fortunately the record CAN be deleted !!!
The only way we found was using the VISDATA.exe that comes with Vbasic, to delete the record and add a new record with the same information.
Since this condition makes any updating program to be aborted, it's a big problem. How this happened? Is there any way to prevent this? Is there a way to detect this in advance? Is there a way to correct this situation automatically, such as rebuild or check the Data Base?
Your advise will be greatly appretiated
TIA Gerardo Alvarez asaca@asaven.com asaca@telcel.net.ve Bienvenido a nuestro Site en http://www.asaven.com
I want to check a record in my table which has time ins and time outs feilds. I want to check on insertionof any new record that a new record should not be inserted on same timings and before 45 mins of timein and after 45 mins of timeout. How do i do it sql query ?
I'm writing a classic ASP application that records all logging of userlogins on our support site. The logging is a rolling window of how manypeople have logged in for a given month, i.e. tracked by a 'lastlogin'field so the tracking is done in a date range. So, for the month ofJanuary I would record lastlogin dates between January 1 and January31. My question is this...on the last day of the month (example beingJanauary)at 23:59:59PM I want to write the total number of users thathad a lastlogin date within the month of January and write that totalnumber to another table so I have a point-in-time figure for Januarylogins and how many users logged in in the month of January. How do Ido that? How can I have the sql server dynamically check the last dayof each month at 23:59:59PM and then run that query to obtain the totalnumbers users that logged in and then record that value to anothertable?? Do I use a SQL Job to do that?Any help would be greatly appreciated! Thanks!
I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .
Ok this is a simple question but i cant seem to find out how since i had the access of a query builder for web matrix.I have the following:A Table called Customer, with Cust_Name, Cust_phoneI have modified my CreateUserWizard to accomodate mroe text boxes which would finally store to vales for Cust_Name, Cust_phone.Question is how can i perform an INSERT query? or rather i noticed that the query builder doesnt allow me to, instead the query builder is towards inserting new records thur GridView and FormView.I've designed a query to handle wildcards i.e @Cust_Nameso i'm thinking if i can have a working insert statment i can do something liketbCust_name.txt = @Cust_Namethen finally perform the insert queryAny ideas? thanks
I am trying to pull the records which are being affected i.e, comparing the values and if not same then populating the record.
I am using the below condition in where clause however i am getting runt time error as "Conversion failure when converting date and/or time from character string"
Condition in Where clause:
cast(isNull(tab1.Col1,'') as datetime) <> cast(isNull(tab2.col1,'') as datetime)Â
Hello I’m using express edition to create my trail test application. Below is the code that I have and I’m trying to insert data to the database table named “Coin�.
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnview.Click
Dim s As String = txtCname.Text
'Dim myConnection As New SqlConnection(myConnString)
Hi I have a table in sql server with a numeric field as Primary Key. When i insert a new record i need that primary key increments automatic (like access auto increment) because i want avoid the possibility of duplicate Primary Keys. Is that possible? Thank you
hi all,i have a question that how to get id afer insert a new record.i just read from the following article, but still can't get the meaning:http://forums.asp.net/1008337/ShowPost.aspx and my code Dim sql_3 As String sql_3 = "insert into paper_type (name, enable,creation_date,lastupdate,user_id) values " sql_3 += "('" & name & "','" & enable & "', getdate() ,getdate ," & user_id & ")" Call OpenRs(sql_3) ' I want to get the id immediately, but how?? (i am using sql server) can somebody help me to solve the problem??thanks a lot
I'm trying to make a website that people can upload file to the server, then the webpage will automatically insert the username and the file name into the database. I have thought about this for couple days but still got nothing. can some one help me? can some one post a very simple aspx file that can insert a record to the sql database with C#? Not the code that generated by ASP.net 2.0! I want to see how to access the database manually. Thank you.
I am trying to do an update to a database with the code below, the code will work in generating a single record (if i exclude the IF statement) but i need it to create multiple new records based on the IF statement (or something similar) i have in the code. It needs to create updates for every record that has a parentguid that matches the one specified in the code. the idea is, the hierachy structure in the tables are:
i want it so that when i input a new part it updates to all the locations. The locations all have the same parentguid (the one specified in the code) With regards to the stored procedure it executes, this stored procedure creates the entry. I don't really want to change the stored procedure because other functions reference and use it.
