Prevent Data Being Inserted Twice
Nov 14, 2006
I have a table with 3 columns: ID, Status, DateTime.
I created a stored procedure to insert a staus value for each ID. This will run every hour. The DateTime stores the time, date when the Status was inserted.
If the procedure was to be run a second time in hour window I do not want any Status to be inserted.
Note: that I cannot rely on the procedure being run at exactly the right time - if it was scheduled to run on the hour (i.e at 1:00, 2:00, 3 :00 etc) but didn't run until 1:20 it sould still be able to run at 2:00.
Does anyone know if there is anyway I can gaurd against this?
View 3 Replies
ADVERTISEMENT
Jan 16, 2008
Ok, this is a really stupid question, but I can't seem to find an answer I understand. In my SQL database I have a a table called MasterSkillList, to which the user can write by using a little web form with a text box and a drop down list. The table has 2 fields, Skill and Attribute. Skill is the primary key, as no skill can appear twice. What I want to do is prevent just that, I don't want people to enter the same skill more than once. So how do I tell the user that the entry allready exists in the database?
My C# Code is as follows:1 protected void btnSubmit_Click(object sender, EventArgs e)
2 {
3 srcAddSkill.InsertParameters["Skill"].DefaultValue = txtSkillName.Text;
4 srcAddSkill.InsertParameters["Attribute"].DefaultValue = ddlAbility.SelectedValue;
5 try
6 {
7 srcAddSkill.Insert();
8 lblErrorMessage.Text = "The skill '" + txtSkillName.Text + "' has been added. It is based on a character's " + ddlAbility.SelectedItem + " score.";
9 lblErrorMessage.Visible = true;
10 txtSkillName.Text = "";
11 }
12 catch (Exception ex)
13 {
14 lblErrorMessage.Text = "An exception has occurred. " + ex.Message;
15 lblErrorMessage.Visible = true;
16 }
View 6 Replies
View Related
Mar 14, 2008
We are running SQL Server 2005 express on Windows 2003. The database server gets significant amounts of data.
Because of the 4GB data limit we have a daily cron task which goes through and deletes data older then 90 days.
We would like a way to archive this data instead of deleting it. Is there any way to take data and compress it and store it in a different way, so that if needed, customers can query directly out from the compressed data? Cleary querying from compressed would be slower but that is ok.
Any other solutions that would allow us to archive data instead of deleting it? Thanks.
View 10 Replies
View Related
Jan 10, 2007
Hello,
is there a way to say to SQL Server to make the data not readable?
Regards
Markus
View 16 Replies
View Related
Dec 31, 2006
I have a table storing only 2 FKs, let's say PID, MID
Is there any way that I can check distinct data before row is added to this table?
For example, current data is
PID MID------------100 2001100 2005101 3002102 1009102 7523102 2449
If my query is about to insert PID 100, MID 2001, since it's existing data, i don't want to add it. Can I use trigger to solve this issue?
Thanks.
View 2 Replies
View Related
Jun 11, 2006
Suppose I have a database storing some confidential information, such
as legal information, medical or financial records, etc., and a
Web site with a membership system so that only authorized users can
view the information.
I understand I can encrypt the information, and the user's passwords,
so that if the database is compromised it still shouldn't be possible
for an outsider to view the confidential information.
However, what about people who have legitimate access to the database,
such as the DBA, Web developer, etc., but who should not be able to
view the confidential information? For example, even though the
user's password was encrypted, what would stop the DBA from replacing
the user's password with his own (encrypted) password, then logging in
and viewing the user's info, then copying back the original encrypted
password? Or, adding a new user for himself with whatever
permissions he chooses?
View 5 Replies
View Related
Apr 20, 2015
1. How to estimate how many free RAM I need. If I plan to create five 2 Gb memory optimized tables...10 Gb RAM for data and how many additional memory?
2. How to prevent memory overflow if there will be too much data in those tables? I dont want that my server down entirely
View 3 Replies
View Related
Sep 20, 2006
hi..
How do i prevent other users from changing the data of my tables? Means one can change data using only my login rest others cannot even DBA or also from server administrator
View 1 Replies
View Related
Oct 18, 2007
If a user is a member of a role which would allow him to execute a proc which updates a table, and he is then granted db_denydatawriter , can he still update the table through the proc? SS2000 SP4.
Thanks,
michael
Since posting this I tested the scenario and YES! The user was still able to execute an update proc even though the user excuting the proc was granted db_denydatawriter.
View 1 Replies
View Related
Jul 23, 2005
Hi all!In a insert-trigger I have two joins on the table named inserted.Obviously this construction gives a name collition beetween the twojoins (since both joins starts from the same table)Ofcourse I thougt the usingbla JOIN bla ON bla bla bla AS a_different_name would work, but itdoes not. Is there a nice solution to this problem?Any help appriciated
View 1 Replies
View Related
Oct 22, 2006
I made ahuge load script in SQL Server 2000 as i load data from manytables(select some of each one collumns) into one single table and iwant to test the loaded data against the selected data to make surethat the loaded data is the same the selected datais there a code or tool to make this test or monitoring ?? pleaseurgent ....
View 2 Replies
View Related
Oct 4, 2006
Hi, I have a stored procedure that looks like this:...WHILE @@FETCH_STATUS = 0BEGINDECLARE @MyCount int ; EXEC spLoanQuestionnaireCriteria @AuditSelectedLoanID, @Criteria, @MyCount OUTPUTEND ...SELECT * FROM #Categories... Executing this stored procedure will return me 1 table for each time the EXEC statement is called that only has on column (MyCount)I really don't need this data to be returned, it is only used for some internal calculations in the stored procedureThe stored procedure should only return the results from SELECT * FROM #Categories in 1 table.Is there a Keyword I can use to exclude the EXEC results being returned to the dataset? Thanks in advance,Andre
View 2 Replies
View Related
Jul 31, 2015
In my database some of the store procedures getting the data from xml nodes.so I need to implement the validation to xml data for prevent sql injection.
View 0 Replies
View Related
Nov 16, 2015
I'm trying to find a way to maintain the data that was UPDATEd in a table from being overwritten.
I used UPDATE in a table to include missing data. However, since I do not have access to the original table from where the data is derived, when I run my query, it knocks out the data in my table that was updated, because it is linked to the orginal table that has the blank fields. A few users do not have an employee email, and this has to be updated with a personal email account for now.
My update works fine, but is there a way I can use the statement in my query so that it updates each time it is run to maintain the user email address? This will solve the problem of not having to change the original table that I do not have access to.
if exists (select * test..dbo.Employee)
truncate TABLE
test.. dbo.Employee
insert into test.. dbo.Employee (EmployeeNumber
, EmplUsername
[Code] ....
View 5 Replies
View Related
May 6, 2008
I'm running this procedure which insert into table_name(id, name.....) select id, name.... from table_name. For some reason the tempdb data file grow up to 200GB. The tempdb is set to expand unrestricted by 10%. How can I prevent that from hapening? Thanks.
View 5 Replies
View Related
Oct 25, 2007
dear all,
I have a WCF service which is host in a console application for the time beeing.
This service provide methods for retriving history data when request.
So far so good.
My WCF service need also to know when a particular table (ALARMS tabel ) gets updated with DELETE, INSERT, OR UPDATE. This because my client application (WinForm) need to refresh a datagrid binding to that ALARMS table.
In other words my WCF servcie would send a callback event to my client when it as been notice for a change..
But my problem is how my WCF service can be notify from an update in my SQL table ?
I have tried SQLDependency class, but I give up was not working properly...and hard to know the xact context you are runing on.
Was thinking also of having a timer whcih pool every 1s by calling my tabel store procedure and verifiy is somerow ha changed....
What to do, how to do, what is the best way and thred safe method
Thnaks for help and advise
regards
serge
View 8 Replies
View Related
Jun 5, 2006
is there any way of getting the identity without using the "@@idemtity" in sql??? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me???
View 1 Replies
View Related
Dec 26, 2007
I represent a software development house and we have developed a client server system based on SQL Server. Most of our customers have already purchased Enterprise License of SQL Server, therefore they own the SA Login and Password. We are bound to attach our Database with their Server on their machine.
My question is how can we stop a System Administrator of SQL Server to view our Database Structure, Queries, Data installed on their SQL Server on their machine.
Our database structure is a trade secret and we cant reveal the structure to the client.
please answer this question by email to me at farhandotcom@gmail.com
Thanks & Regards
Farhan
View 1 Replies
View Related
Feb 22, 2007
I am using SQL Server 2000.I want to create an after insert trigger on one of my tables, but I have forgotten how I reference the inserted data to do some business logic on it. Can someone please help. Thanks Jag
View 1 Replies
View Related
Sep 10, 2014
Data is not getting insert into table. Below mentioned is the query used. I need getting the data inserted to the table
DECLARE @FilterTable Table
(
ColumnName varchar(50),
Value varchar(250),
Type varchar(50)
[code]....
View 1 Replies
View Related
Jul 20, 2005
hi thereCreated sproc - it stops dead in the first lineWhy ????Thanks in advanceCREATE PROCEDURE [dbo].[test] ASinsert into timesheet.dbo.table1 (RE_Code, PR_Code, AC_Code, WE_Date,SAT, SUN, MON, TUE, WED, THU, FRI, NOTES, GENERAL, PO_Number,WWL_Number, CN_Number)SELECT RE_Code, PR_Code, AC_Code, WE_Date, SAT, SUN, MON, TUE,WED, THU, FRI, NOTES, GENERAL, PO_Number, WWL_Number, CN_NumberFROM dbo.WWL_TimeSheetsWHERE (RE_Code = 'akram.i') AND (WE_Date = CONVERT(DATETIME,'1999-12-03 00:00:00', 102))GO
View 6 Replies
View Related
Nov 1, 2006
i have a oledb destination in my data flow pointing to table ABC and an
error output if the insert failed..follow the error output, i have a
lookup on table ABC which doesn't seem to work..is it possible to
access new data in table ABC follow the error output?
thanks
View 1 Replies
View Related
Feb 14, 2008
hi iam working for a stored procedure where i am inserting data for a table in a database and after inserting i must get the ID in the same procedure.later i want to insert that output ID into another table inthe same stored procedure.
for example:
alter procedure [dbo].[AddDetails]
(
@IndustryName nvarchar(50),
@CompanyName nvarchar(50),
@PlantName nvarchar(50),
@Address nvarchar(100),
@Createdby int,
@CreatedOn datetime
)
as
begin
insert into Industry(Industry_Name,Creadted_by,Creadted_On) OUTPUT inserted.Ind_ID_PK values(@IndustryName,@Createdby,@CreatedOn)
insert into Company(Company_Name,Company_Address,Created_by,Created_On,Ind_ID_FK) OUTPUT inserted.Cmp_ID_PK values(@CompanyName,@Address,@Createdby,@CreatedOn)
insert into Plant(Plant_Name,Created_by,Creadted_On,Ind_ID_FK,Cmp_ID_FK)values(@PlantName,@Createdby,@CreatedOn,@intReturnValueInd,@intReturnValueComp)
end
Here iam getting the output ID of the inserted data as OUTPUT inserted.Ind_ID_PK and later i want to insert this to the company table into Ind_ID_FK field.how can i do this.
Please help me, i need the solution soon.
View 11 Replies
View Related
Mar 25, 2005
Hi im having problems as im new to ASP.NET C#
i have created a button to add details into a SQL database but i want to check the details before i insert the new values from the textboxes
can anyone help....... this is what i have to insert into the database........i just want some help to compare the user name eg... if user name exists a message will appear telling the user to change a different user name
Thanks
private void Button1_Click(object sender, System.EventArgs e)
{
string connectionString = "server='(local)'; trusted_connection=true; database='tester'";
//System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
conn.Open();
string commandString = "INSERT INTO Users (UserName, Password) " + "Values(@UserName, @Password)";
//SqlCommand dbCommand = new SqlCommand (commandString, dbconn);
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
//System.Data.SqlClient.SqlCommand myCmd = new System.Data.SqlClient.SqlCommand(queryString, conn);
dbCommand.CommandText = commandString;
dbCommand.Connection = conn;
SqlParameter unParam = new SqlParameter ("@UserName", SqlDbType.NVarChar, 60);
unParam.Value = txtUser.Text;
dbCommand.Parameters.Add(unParam);
SqlParameter paParam = new SqlParameter ("@Password", SqlDbType.NVarChar, 60);
paParam.Value = txtPassword.Text;
dbCommand.Parameters.Add(paParam);
dbCommand.ExecuteNonQuery();
conn.Close();
Response.Redirect ("WebForm1.aspx");
}
View 1 Replies
View Related
Mar 12, 2006
Hi,I am using web matrix, and I am trying to insert a data into a MSDE database. I have used webmatrix to generate the update code, and it is executed when a button is pressed on the web page. but when the code is executed I get the error:Syntax error converting the varchar value 'txtAmountSold.text' to a column of data type int.So I added the following code to try to convert the data, but i am still getting the same error, with txtAmountSold.text replaced with "test"dim test as integer
test = Convert.ToInt32(txtAmountSold.text)Here is the whole of the function I am using:Function AddItemToStock() As Integer dim test as integer test = Convert.ToInt32(txtAmountSold.text) Dim connectionString As String = "server='(local)Matrix'; trusted_connection=true; database='HawkinsComputers'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _ "], [Description], [image], [OnOffer], [OfferPr"& _ "ice], [OfferDescription], [AmountInStock], [AmountOnOrder], [AmountSold]) VALUES ('CatList.SelectedItem.text', 'txtType.text', 'txtname.text', 'txtmanufacturer.text'"& _ ", convert(money,'txtPrice.text'), 'txtWeight.text', 'txtDescription.text', 'txtimage.text', 'txtOnOffer"& _ ".text', convert(money,'txtOfferPrice.text'), 'txtOfferDescrip"& _ "tion.text', 'txtAmountInStock.text', 'txtAmountOnOrder.text', 'test')" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try Return rowsAffected End FunctionAny help in solving this problem would be greatly appreciated, as I am really stuck for where to go next.
View 2 Replies
View Related
Sep 22, 2015
I have a transactional publication done between two databases on different Virtual machines connected with a network. I noticed that for some reason Every time replication is happening (which is every 2,5 seconds) my data is deleted on the target DB (subscriber) then re inserted.
this should not happen, old data should remain then only the new data should be inserted.
View 3 Replies
View Related
Sep 23, 2014
I tired to insert data from xml to a table .but only null value is getting inserted into the table. The query is attached below.
create table Detail_test1
(
DetailIdInt,
LaIdInt,
OcCodeVarchar,
BIdINT,
RateINT)
Declare @xml XML
set @xml='<ArrayOfBDetailLine>
[Code] ....
View 1 Replies
View Related
Nov 2, 2015
I would like to know how data is inserted and selected from table internally. Like how buffers are created and how they are executed.
Eg. Table1
col1 col2 col3
1 a 1
2 b 2
I want to know how this data is inserted into tables. How many buffers are created ? Does it depends on the columns? How buffers are executed?
View 7 Replies
View Related
Apr 9, 2015
I need to find the history in SQl server.
I need to find the data, When was data last inserted/updated/deleted in a specific table?. is it possible?
Table name: Employee
View 1 Replies
View Related
Feb 25, 2014
We are having xml data in a column. Is it possible to write a trigger to generate a mail if particular kind of data get inserted in a tag.
For ex:
<File AF="910" PTO="ATN_P76035_PSQO" NNO="54545465" KTNNN="AX2" KL="" AD="99" PrqnT="AX2" Stab="21545" KE="45454" TE="65465" Rsaa="BBBB" AK="54544.AX2.POEAX2.546546546.NONTP.NONTP" AK2="">
In the above xml data if we have the value 21545 in Stab tag the trigger needs to be executed and mail needs to be sent to a distribution list.
View 1 Replies
View Related
Oct 6, 2004
Hi folks,
Table:
a int,
b int,
c int,
d text
I need to change my AFTER - Trigger from this (example!):
select * into #ins from inserted
to something like
select *(without Text / nText / image -columns) into #ins from inserted.
So I tried to build a string like this: (using INFORMATIONSCHEMES)
select @sql = 'select a,b,c into #ins from inserted'
exec(@sql)
a,b,c are not of Text, nText or Image datatype.
After executing the trigger, I get an error, that inserted is unknown.
Does anyone know how to solve this ?
Thx.
View 5 Replies
View Related
Nov 4, 2015
Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.
Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)
I get the following error: Invalid object name 'inserted'
I know I can do:
Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)
But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?
View 2 Replies
View Related
Jul 20, 2005
Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO
View 2 Replies
View Related
