I'm in the process of implementing a multi-user system containing an
adjacency list (tree structure). I'm using a TIMESTAMP field on each
record in the adjacency list in order to tell when a node has been changed
since the last read. Sometimes though, it is useful to flag a "parent" (or
all ancestors or a node) as being changed if any of its children have. Is
there any way I can force an update to the parent TIMESTAMP field without
actually modifying any of the other fields in the record? Something like
this (assuming I have a field called [Timestamp]):
UPDATE Adjacency SET [Timestamp] = [Timestamp] + 1 WHERE ID = @_In_ID
Now, will the timestamp be incremented here by 1, or will SQL server get
confused as in theory it must "update the timestamp after updating the
timestamp" for this record?
hi there, i am new to sql server database.i am doing small projects right now using asp.net and sql to create webpages (very basic webpages) My problem is:
Problem :
i have two tables .....table 1 and table 2.
Table 1 has following fields: studentid,student name,student address.
Table 2 has following fields:studentid and course . table1 student id is the primary key refrencing table 2 student id. Now i delete a record in table 1 which will in turn also get deleted in table 2 . so for eg if i have three records 1 ,2 and 3 ....then i delete 2 in table 1 ...i will have 1 and 3 in both table 1 and table 2....now i want 3 to become 2 in both table 1 and table 2...so that i dont have empty space between two student id's 1 and 3. so this is my problem....if any one can help me out with suggestions please do.
Does anyone know why an error occurs (constraints violated) when I try to have a table with an automated id incrementing by 1. I am uploading the data into the table from dts, so ignore the id field in the dts transformation.
Any ideas or help would really be appreciated, do I need to reference the id field in an activeX script?
Hello, I am making adjustments to a table in my SQL Server Database. I'm adding a collumn to my database of numeric type. I wish to write a SQL script that will go through every record in the database and give it a unique number (key) by simply incrementing each number by 1. I've inherited this database and as you have probably guessed i'm trying to add a primary key to this table as currently there isn't one!
I have no experience what so ever with T-SQL (which I presume i will need to use) as my expertise lie in web development with fairly simple SQL so go easy on me :)
Does anyone know why an error occurs (constraints violated) when I try to have a table with an automated id incrementing by 1. I am uploading the data into the table from dts, so ignore the id field in the dts transformation.
Any ideas or help would really be appreciated, do I need to reference the id field in an activeX script?
I have a database table. I need to set a particular data field to allow for Auto Incrementing. Basically I need to set up the initial integer value to start at. Then to specify the amount to increment by. What I have done so far is to right click on the field name in SQL Server 2005. Then, on the pop-up menu I select the menu option for properties. When the properties are displayed I see some fields listed in dark grey but I am not able to edit these fields. In other words, if I try to type text in this field, nothing happens. The fields I am talking about are: Identity Increment Identity Seed Is Identity Does anyone know what the problem is? What do I have to do to edit these fields.
Hi everyone. I'm a new user to ASP.NET using VB.NET and SQL Server.
I have one question.
I am currently creating a website for my dissertation whereby whenever a customer purchases a product, a number is incremented within the 'Product' table. The column that needs to be incremented is called 'ProductSold'.
I have got an Insert query to store the orders in the 'Orders' table already within my webpage. Is there a SQL Query that I could use to increment the number in the 'Product' Table straight after my Insert Query?
I really appreciate if anyone could help me in this matter!
I have a field called contact_id in a table. Datatype is integer and has an identity seed of 1 and an identity increment of 1. When new records are created, the increment sometimes skips numbers. (ex. 12,13,18,21). What's going on here? Am I losing records somewhere?
I have a field called contact_id in a table. Datatype is integer and has an identity seed of 1 and an identity increment of 1. When new records are created, the increment sometimes skips numbers. (ex. 12,13,18,21). There is absolutely no deletion of any kind going on. (i.e. My application (cold fusion) has no code whatsoever that is deleting records.) What's going on here? Am I losing records somewhere?
I am porting a database from Informix to SQL Server 2000. Several fields in the informix database are specified as SERIAL, which means that they auto-increment auto-magically if you insert a 0 into them. Well...I was looking for an equivalent in SQL Server 2000, and thought I had found it in the IDENTITY column concept.
With further investigation into IDENTITY columns, however, I discovered that only one column per table can be given the IDENTITY property. Why is this? Is there a way around that? If not, is there another way to achieve this feature in SQL Server 2000? If not, any suggestions on how I can do it?
Hi world,I would like to know how to maintain a table with a "timestamp" column. I mean a column that automatically set the current datetime when the row is updated.I don't want to go trought my application and set that in the update or insert command. I would like to know if there is a possibility of putting a trigger or something.thx
If i were to replicate a database with another where in both the databases have an auto incrementing field. What could be the problems that's goin to arise out of it. How to deal with such a scenario...?????
I need to auto increment an alphanumeric field in a table.
The process is as follows:
1. First position is static letter C for contact or static letter A for account - field contact_id 2. The following 6 positions are numeric - example of the string would be C004658 3. When new contact is entered, it should look up the highest numeric value (in this case 004658) and increment it by one - resulting in C004659
ID – Auto IncrementingNumber_of_Records – IntAccount_Number – Varchar (Format 1234)Account_Number_Instance – Varchar (Format e.g. 1234-01)Other_Field1Other_Field2…etc.
When the Account_Number was initially inserted into the table, a Stored procedure (not written be me) was used to concatenate the Account_Number and Number_of_Records. As a result the Account_Number_Instance became, e.g. 1234-01, etc.
From the ASPX form we retrieve each of the fields above, and based on the Number_of_Records an appropriate number of records were inserted for the Account_Number.
For Example:
Account_Number = 12345Number_of_Records = 4
The result inserted into the table were as follows:
ID # of Records Acct_Number New_Num Acct_Number_Instance …
1 4 12345 12345-01
2 4 12345 12345-02
3 4 12345 12345-03
4 4 12345 12345-04
Now, I would like to UPDATE this table, and based on the original Number_of_ Records (4) I want to ADD, for Account_Number (1234) add an appropriate Number_of_Instances, beginning with the next incremental number and adding what the New Number of Records input is…
ID # of Records Acct_Number New_Num Acct_Number_Instance …
1 4 12345 12345-01
2 4 12345 12345-02
3 4 12345 12345-03
4 4 12345 12345-04
5 12345 3 12345-05
6 12345 3 12345-06
7 12345 3 12345-07
I do realize that the next time I have to update this Account_Number I am also going to have to somehow ADD only one of the #_of_Records and New_Num for the Acct_Number instances to obtain the next starting number, which in the example above would be (8)…
Any and all suggestions on how to accomplish this would be greatly appreciated!
When updating a timestamp column value to null from within a java program, I get the exception below. The env is MS SQL server 2005 SP2, and MS jdbc driver version: 1.1.1501.101. Please advise of any workarounds or solutions.
Update <table_name> set <colname_oftype_timestamp> to null
java.lang.NullPointerException at com.microsoft.sqlserver.jdbc.AppDTVImpl$SetValueOp.executeDefault(Unknown Source) at com.microsoft.sqlserver.jdbc.DTV.executeOp(Unknown Source) at com.microsoft.sqlserver.jdbc.AppDTVImpl.setValue(Unknown Source) at com.microsoft.sqlserver.jdbc.DTV.setValue(Unknown Source) at com.microsoft.sqlserver.jdbc.Parameter.setValue(Unknown Source) at com.microsoft.sqlserver.jdbc.Parameter.setValue(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setNull(Unknown Source) Thanks, sm.
I am trying to replicate tables in a sql server 6.5 which contain the timestamp field. An error occurs and it shuts the replication task off and it says it cannot update the timestamp field. If I choose vertical partitioning and do not replicate the timestamp field then an error occurs that insert value list does not match column list. Is it the case that the subscriber cannot have this field in the table? Besides deleting the timestamp field from the table completely, is there another solution or am I forgetting to set something?
Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?
The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
Hi, I want to update a field in my table whose value is a 0, to a value 1. This field is of type bit and here is the SP that I wrote to achieve this. Somhow, its giving some error when I tried executing it in the Query Analyzer. What am I doing wrong here??
CREATE PROCEDURE PublishSchedule ( @SiteCode smallint, @YearMonth int ) AS
DECLARE @Active bit IF ( (SELECT COUNT(*) FROM CabsSchedule WHERE YearMonth = @YearMonth AND SiteCode = @SiteCode) > 0 ) BEGIN UPDATE CabsSchedule SET Active=1 WHERE SiteCode=@SiteCode AND YearMonth=@YearMonth END GO
All other fields are updating ok and I'm not getting an error.I am trying to update a date and time (smalldatetime) using a stored procedure.First, the info to be updated comes from a datagrid. Dim sDate As DateTime sDate = CType(e.Item.FindControl("tDate"), TextBox).TextThen, passed to the SQLDal class and then to the stored procedure..... Public Function updateData(ByVal sDate As DateTime, ByVal sp As String) 'Some items snipped for easier read Dim command As SqlCommand = New SqlCommand(sp, conn) 'Where sp is the stored procdure name command.Parameters.Add("@date", sDate)'And so on.....And then the stored procedure....@num VARCHAR(256),@date SMALLDATETIME,@contact VARCHAR(256),@notes VARCHAR(8000),@media VARCHAR(256) ASBEGIN DECLARE @errCode INT BEGIN TRAN -- UPDATE THE RECORDS UPDATE dbo.tblData SET fldDate = @date, fldContact = @contact, fldNotes = @notes, fldMedia = @media WHERE fldNum = @num <sniped>Like I said, all other fields are updated with no problems, but not the date.The date format being passed into the sp is {0:MMM dd, yyyy hh:mm tt} or Aug 05, 2005 04:39 PM Is it the format of the date? Or something else I'm not seeing...Thanks all,Zath
I have a sql database that includes a table of customer contact information. The area code for many of my customers is about to change. Is there a way to mass update the phone number field so that all phone numbers that currently start with 111 change to 222 ? Ex 1115554444 to 2225554444 ?
I try to update a field of text datatype using WRITETEXT statement. The information that I try to change has both the single(') and double (") quotes in it. How can I get it done ?
Trying to update part of a field. Currently using ColdFusion 4.0 and SQL Server 7.0. My field looks something like this: ABC.DEF.GHI and I just want to update the last 3 characters, GHI. The length of the field may change so it's not going to be 11 characters long. Any help would be appreciated.
I have 3 tables and 1 view. Which are: TOWNLAND_GEOREFERENCE_POLYGON PlanningPointLocation paflarea VIEW_paapplic
The View paaplic has 100 records and I have to do the below for all 1000 records individually. I have to update the field TP_Total in the TOWNLAND_GEOREFERENCE_POLYGON table depending on what is in the fields in the tables. I am writing the below code but am unsure if this is going to achieve what I want.
BEGIN Select file_number, land_use_code, pluse1_code From VIEW_paapplic END
BEGIN If pluse1_code = 'A' Then Select TP_Total From TOWNLAND_GEOREFERENCE_POLYGON WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland AND PlanningPointLocations.File_Number = File_Number
Update TOWNLAND_GEOREFERENCE_POLYGON SET TP_Total As TP_Total + 1 WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland AND PlanningPointLocations.File_Number = File_Number
Else If pluse1_code = 'C' Then Select Count(*) As TempCount From table paflarea Where fk_paapplicfile_nu = file_number
Select TP_Total From TOWNLAND_GEOREFERENCE_POLYGON WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland AND PlanningPointLocations.File_Number = File_Number
Update TOWNLAND_GEOREFERENCE_POLYGON SET TP_Total As TP_Total + TempCount WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland AND PlanningPointLocations.File_Number = File_Number END
ok, i am trying to update a database at work for a product we are developing.
i need to run this command update <tablenamehere> set value = Replace(value, 'GeoLynxAO_Henrico', 'GeoLynx') on every tabe in the database
is there a simple way to do this while pulling the table names out of information_schema.tables?
i have searched using google and been unable to find anything so far. the db server is running sql server express 2005 and i'm doing this from sql server management studio express
i really don't want to have to type the update statement by hand for 90+ tables................
