Hi Guys,
I'm using SQL server 2000.
How do I alter column/field from type int (with Identity = Yes Not For Replication) to just normail int field. No more identity. I want it to be done using SQL script( sql query analyzer).
Please help me on this, thx
Regards,
Shaffiq
Hi folks!
I need to populate a table having an identity field with data (basically I want to make a copy of a table with identity column on another server). When I use BCP to insert data then the values in the identity field is not the same as in the data file but gets incremented from the original.
eg. if the records in the flat file are-
1, 'test1'
2, 'test2'
Now if i insert this into the table (say the last identity value was 100) then rows get inserted as-
101, 'test1'
102, 'test2'
Is there any way by which I can retain the values of the identity column to be same as in the file?
Thanks!
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedHow can I alter a table turning ON or OFF an IDENTITY field ?
for example:
if I had my DB with Client_ID as an I IDENTITY field and for some reason it has
changed to just INT (with no IDENTITY) - how can I tell it to be IDENTITY field again ?
+
Does anyone knows an article on database planning ?
(I wanna know when should I use the IDENTITY field)
Arkadiy writes "Why @@indentity returns NULL after inserting new row?"
View 6 Replies View Relatedwhen I use the code
SET IDENTITY_INSERT tss_Cable ON
go
DECLARE @@nextidentval int
SELECT @@nextidentval = MIN(IDENTITYCOL) + IDENT_INCR(tss_Cable)
FROM tss_Cable t1
WHERE IDENTITYCOL BETWEEN IDENT_SEED(tss_Cable) AND 2147483646
AND NOT EXISTS (SELECT * FROM tss_Cable t2
WHERE t2.IDENTITYCOL = t1.IDENTITYCOL IDENT_INCR(tss_Cable))
go
SET IDENTITY_INSERT tss_Cable OFF
I get the following error.
error line 2 Incorrect syntax near IDENT_INCR.
error line 6 incorrent syntax near IDENT_INCR.
I would like to reset the identity without droping the table. I'm using
sql 6.5. I looked at Mark Lessard 12/17/99 respose on this issue and changed
the code to match his version but the same error occured.
How do reset identity seed on table, for example I have table that has gap in the identity such as (1,2,3,5,6, etc..) and I want reset the entire table.
Thank You,
John
What is the best way to do Indentity(1,1) on fields which is varchar(8)?
View 1 Replies View RelatedI'm doing a data transfer from Access to SQL Server, I wish to keep theidentity column (autonumber) values as all the data is already related. Itried the first table append query including the identity column, it worked.Was this fluke? Will it always work? I was under the impression that I wouldhave to issue a "set identity_insert on" before doing this. The SQL databasewill have absolutely no data before the transfer routines are run.
View 15 Replies View RelatedIDE: Visual Studio 2008
DB: SSCE 3.5
OS: WinXP SP2 (CHS)
.....
Code Snippet
String str_sql_insert = "INSERT INTO Person (Name, ShortName, Description, Tag) VALUES ('" + this.TextBox.Text.Trim() + "', '" + this.ShortNameTextBox.Text.Trim() + "', '" + this.DescriptionTextBox.Text.Trim() + "','" + this.TagComboBox.SelectedValue.ToString().Trim() + "); SELECT @@INDENTITY";
SqlCeConnection tempConn = dbman.getConn();
SqlCeCommand tempComm = new SqlCeCommand(str_sql_insert, tempConn);
tempConn.Open();
Object ob = tempComm.ExecuteScalar();
.....
When I run the application there is a error report on the last line of the code above:
[ Token line number = 1,Token line offset = 127,Token in error = SELECT ]
When I copy the SQL string into the QueryBuilder in the VS2008 it is works and returned a correct value.
I want to know is there something wrong with the sql string or the ExecuteScalar() cant execute 2 steps command?
thanks
As an example I have three tables
Table1
ChildName (nvarchar)30
ChildID (INT) Set as €˜IDENTITY€™
Table2
ParentName(nvarchar)30
ParentID (INT) Set as €˜IDENTITY€™
Table3
ChildID (INT)
ParentID (INT)
I have the following code.
Dim dbcnn As SqlClient.SqlConnection = New SqlClient.SqlConnection(dbsettings.getDatabaseString)
Dim dbcmd As SqlClient.SqlCommand
dbcmd_AddDriverRoute = New SqlClient.SqlCommand("usp_Insert", dbcnn)
dbcmd.CommandType = CommandType.StoredProcedure
dbcmd.Parameters.AddWithValue("@ChildID ", cmb_child.SelectedItem)
dbcmd.Parameters.AddWithValue("@ParentID ", "@@IDENTITY")
dbcnn.Open()
dbcmd.ExecuteScalar()
dbcnn.Close()
basically what I want to do is when a child is added I want to be able to insert ParentID (INT) Set as €˜IDENTITY€™ value into table3 ParentID (INT) .
I have my sql statement in place I am not sure how to take an identity value stored in the db.
i have table with one column
CREATE TABLE [dbo].[tblTest](
[ID] [int] IDENTITY(1,1) NOT NULL
how do i insert values.(i,e identity)
I would like to reset the Identity column in a table so it starts at the begining. I am planning to clean out this table.
Thanks
Gary
I'm trying something new, and have hit a snag.
basically, I'm attempting to use two INSERT commands in a single SP. I want to use the PK from the new entry and apply that to the second.
here's the code;
:::::::::::::::::::::::::::::::::::::::::::::::::: :::::::::::::::
CREATE PROCEDURE spInsertNewUser
@fn varchar(50),
@ln varchar(50),
@add varchar(50),
@add2 varchar(50),
@ct varchar(50),
@cty varchar(50),
@st varchar(4),
@pc varchar(50),
@ph varchar(50),
@cp varchar(50),
@fx varchar(50),
@em varchar(50),
@pw varchar(50),
@uid varchar(50),
@rc bit,
--@indentity int OUTPUT,
@cid int
AS
SET NOCOUNT ON
INSERT INTO tblUserInfo
(
firstName,
lastName,
address,
address2,
city,
county,
state,
postalCode,
phone,
cellPhone,
fax,
email,
pword,
userUID,
regConfirmed
)
VALUES (
@fn,
@ln,
@add,
@add2,
@ct,
@cty,
@st,
@pc,
@ph,
@cp,
@fx,
@em,
@pw,
@uid,
@rc
)
SELECT @@IDENTITY FROM tblUserInfo
--SET @indentity = @@IDENTITY
INSERT INTO tblUserToCountySubscribedLKP (
UserID, CountyID
) VALUES(
@@indentity, @cid
)
:::::::::::::::::::::::::::::::::::::::::::::::::: :::::::::::::::
I'm sure the problem is largely due to the fact that I am close to completely clueless on such things, but a bit of timely advice from the sage sql wizards here would be most welcome and greatly appreciated.
Hello folks,
I am using the following INSERT statement to add a new record in my table. I want to be able to get the Autonumber UserID field of the newly created record but I am getting the following error: "Characters found after end of SQL statement"
Code:
INSERT
INTO tblUser
(LoginName, UserPassword, EmailAddress, City)
Values
(@User, @UsrPassword, @EmailAddress, @City);
SELECT @@IDENTITY As 'Identity'
What is wrong in my statement?
How do I retrieve the Autonumber field of the newly created record?
How do I handle if the LoginName and/or EmailAddress already exists?
I want to be able save this INSERT statement as an Access Query and call it from my C# code.
I would appreciate any help.
Thanks
Mike
I imported a table using DTS.
I run SQL statements in my original server database. It works well.
But when I run the same instructions within a stored procedure in my new workstation where I improrted the table I get this error:
Server: Msg 515, Level 16, State 2, Procedure InsertFichierPrix, Line 11
Cannot insert the value NULL into column 'Id', table 'myDBLive.dbo.FichierPrix'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Here is the SQL statements that I run on the original Database:
insert into fichierprix(nomfichier, version, descriptionfr, typeclient,....) values(@NomFichier,'Actuelle', @NomFourFr, 'MembreAcheteur', ....)
And here is my SP that I run on the new imported database:
CREATE PROCEDURE InsertFichierPrix @NomFichier varchar(50), @NomFr varchar(50),@NomAn varchar(50)
AS
declare @NomFourFr varchar(50)
declare @NomFourAn varchar(50)
SET @NomFourFr='liste fournisseur ' + @NomFr
set @NomFourAn='liste fournisseur ' + @NomAn
insert into fichierprix(nomfichier, version, descriptionfr, typeclient,...) values(@NomFichier,'Actuelle', @NomFourFr, 'MembreAcheteur'...)
GO
And here is the execution of my Stored proc on the destination database:
execute insertfichierprix @NomFichier='myfilename2', @NomFr='fournifr1',@NomAn='Fourniang1'
Thank you for helping me.
Hi,When i eg. manually ad entries to a table and, cancels the insert Ms SQLincrement the counter on the ID anyway. Is there a way to avoid thisbehavior?RegardsAnders
View 1 Replies View RelatedHi AllI have a table in SQL Server with ID having indentity inrement by one.Table has not any trigger. Frequently ID in the table jumps.Any help !!!Thanks
View 2 Replies View RelatedI am trying to remove the Indentity property from a column so I can do some clean up. Then will need to add it back. Adding Indentity property back is not a problem, but cant figure out how to remove it in the first place.
Any help would be greatly apprecaited.
Thanks
Dave
Hello, everyone:
I have a table with an indentity column as first column. At beginning it is continue such as 0-50. I delete last 20 columns by hand. The 0-31 is left. When the new data is inserted, I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.
Thanks a lot
ZYT
Hi There
Can anyone tell me how an identity column decides what number to give which row.
I always thought that it was in order of the clustered index.
But i have found the following:
If you have Table A with a clustered index(not unique) say on the first 3 columns.
If the table is empty and you add an identity column, then afterward load it with data the value of the identity column seems to match the clustered index.
But if the table has no identity column and it is filled with data, and then you add an identity column the number for the identity column seems to be all over the place.
So basically how does a newly added identity column know what number to give each row, what if the clustered index is not unique? Is it random ?
Thanx
Dear Friends,
I need a SQL Query to add a identity coloumn for anExisting table. (ie) when i try to alter the table i want to add an identity coloumn.
Thanks in advance.
I would like to add an Identity to an existing column in a table using astored procedure then add records to the table and then remove the identityafter the records have been added or something similar.here is a rough idea of what the stored procedure should do. (I do not knowthe syntax to accomplish this can anyone help or explain this?Thanks much,CBLCREATE proc dbo.pts_ImportJobsas/* add identity to [BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL/* add records from text file here *//* remove identity from BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] NOT NULLreturnGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOhere is the original tableCREATE TABLE [ItemTest] ([BarCode Part#] [int] NOT NULL ,[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Description] DEFAULT (''),[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]DEFAULT (0),[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]DEFAULT (0),[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT(0),[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT(getdate()),CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED([BarCode Part#]) ON [PRIMARY]) ON [PRIMARY]GO
View 2 Replies View RelatedI am using sql server ce.I am changing my tables sometimes.how to use 'alter table alter column...'.for example:I have table 'customers', I delete column 'name' and add column 'age'.Now I drop Table 'customers' and create again.but I read something about 'alter table alter column...'.I use thi command but not work.I thing syntax not true,that I use..plaese help me?
my code:
Alter table customers alter column age
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies View RelatedI need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE"Â .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
View 2 Replies View RelatedDECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________
The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect