Varchar(8) And Indentity

Feb 27, 2006

What is the best way to do Indentity(1,1) on fields which is varchar(8)?

View 1 Replies


ADVERTISEMENT

Indentity

Feb 20, 2006

Arkadiy writes "Why @@indentity returns NULL after inserting new row?"

View 6 Replies View Related

BCP Into An Indentity Field

Jun 15, 2000

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!

View 1 Replies View Related

Indentity Column

Feb 24, 2000

when 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.

View 1 Replies View Related

Reset Indentity

Apr 7, 2003

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

View 2 Replies View Related

Indentity Insert

Jul 20, 2005

I'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 Related

Regarding SELECT @@INDENTITY

Apr 25, 2008

IDE: 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

View 6 Replies View Related

Using The Indentity Column

Feb 22, 2008


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.

View 6 Replies View Related

Indentity In 1 Column Table

Mar 24, 2008

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)

View 14 Replies View Related

Resetting Indentity Columns

Nov 15, 1999

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

View 1 Replies View Related

Stored Prcedure And @@indentity

Jul 12, 2005

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.

View 5 Replies View Related

Access Db @@indentity How To Get New Record

Jan 4, 2005

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

View 1 Replies View Related

Alter Indentity Field

Mar 24, 2006

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

View 4 Replies View Related

Is It Possible That DTS Skipped Indentity Attribut

Sep 11, 2006

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.

View 2 Replies View Related

Id (indentity) Is Increments On Faults.

Jul 20, 2005

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 Related

MS SQL Server Indentity Jumps

Jul 20, 2005

Hi 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 Related

Drop Indentity From Column

Dec 6, 2007

I 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

View 4 Replies View Related

How To Make Indentity Column Continually?

Nov 16, 2004

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

View 9 Replies View Related

Ordering Of Newly Added Indentity Value ?

Aug 1, 2007

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

View 4 Replies View Related

Adding A Indentity Column In An Existing Table.

Feb 20, 2008



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.

View 8 Replies View Related

Problems Moving Data Over 8000k In DB2 Varchar Column Into SQL Server Varchar(max) Using SSIS

Nov 20, 2007



I have looked far and wide and have not found anything that works to allow me to resolve this issue.

I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.

I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".

[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".

[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?

View 10 Replies View Related

The Data Types Varchar And Varchar Are Incompatible In The Modulo Operator

Jan 4, 2008

I am trying to create a store procedure inside of SQL Management Studio console and I kept getting errors. Here's my store procedure.




Code Block
CREATE PROCEDURE [dbo].[sqlOutlookSearch]
-- Add the parameters for the stored procedure here
@OLIssueID int = NULL,
@searchString varchar(1000) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @OLIssueID <> 11111
SELECT * FROM [OLissue], [Outlook]
WHERE [OLissue].[issueID] = @OLIssueID AND [OLissue].[issueID] = [Outlook].[issueID] AND [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
ELSE
SELECT * FROM [Outlook]
WHERE [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
END




And the error I kept getting is:

Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 18

The data types varchar and varchar are incompatible in the modulo operator.

Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 21

The data types varchar and varchar are incompatible in the modulo operator.

Any help is appreciated.

View 5 Replies View Related

SSIS - Importing Varchar From Access Into SQL2005 As Varchar

Nov 20, 2006

For the life of me I cannot figure out why SSIS will not convert varchar data. instead of using the table to table method, I wrote a SQL query so that I could transform the datatype ntext to varchar 512 understanding that natively MS is going towards all Unicode applications.

The source fields from Access are int, int, int and varchar(512). The same is true of the destination within SQL Server 2005. the field 'Answer' is the varchar field in question....



I get the following error



Validating (Error)



Messages

Error 0xc02020f6: Data Flow Task: Column "Answer" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)


Error 0xc004706b: Data Flow Task: "component "Destination - Query" (28)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)


Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)


Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)


DTS used to be a very strong tool but a simple import such as this is causing me extreme grief and wondering of SQL2005 is ready for primetime. FYI SP1 is installed. I am running this from a workstation and not on the server if that makes a difference...

Any help would be appreciated.





View 7 Replies View Related

Datatype Question Varchar(max), Varchar(250), Or Char(250)

Oct 18, 2007



I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this.

How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?

Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?

Should I just go with char(250) and watch my db size explode?

Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.

Any insight to this would be appreciated.

View 9 Replies View Related

Difference VarChar(50) And VarChar(500)

Nov 26, 2007


Hey,

I was doing some research on how SQL stores data on disk.
MSDN states that when storring a varchar, only the length of the data itself is used plus two bytes.
So, if you store "car" in a VarChar(50) it will take 5 bytes.
But when you store "car" in a VarChar(500) it will also take 5 bytes.

What is the reason users should define the parameter lenght?
Can you use VarChar(8000) whole the time, without any drawback?


Thanks

View 3 Replies View Related

Select Convert(varchar(16), Getdate(), 101)+LEFT(REPLACE(convert(varchar, Getdate(), 108), ':', ''),4)

Sep 26, 2007



select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)

From above query I get

mmddyyyyhhmm

but it' s yyyy and hour can not be separated

04/12/200702:05

How can I separated the year and hour ?

Thanks
Daniel

View 2 Replies View Related

Converting Date To Varchar? And Varchar To Date?

Jul 31, 2007

I have a column of data in a table that has date formatted as '2006-03-26 00:00:00.000'

What T-SQL command that will alter the column so that it is now Varchar '03-26-2006'?I also want to know how to do the opposite... if I have '03-26-2006' via command, how do I convert the column of the table to be datetime from varchar

View 1 Replies View Related

Varchar(MAX)

Sep 28, 2007

varchar(MAX)
Is there any advantage/disadvantage using varchar(MAX) instead of varchar(1000)

View 2 Replies View Related

Varchar

May 1, 2008

 in my website  the user enter some text contenti want to store that content into DB table,the table uses varchar datatype and have the maxlength(8000) but the user enters morethan 8000 chars , the DB not insert the data, it gives error,here iam using stored procedures(SP) ,pass the values through parameters .i am tried text data type but i cant insert. how can i resolve the problem. it won't accept the text data type as a SP parameter i want to retrive the data from db and display in a text box. 

View 4 Replies View Related

Why To Use Varchar(Max)

Jun 17, 2008

hi i m bit confused about  using varchar(Max) what is the advantage of using it over conventional varchar .
kindly explain in detail.

View 2 Replies View Related

Varchar Were Cut Somehow!!!

Dec 29, 2005

Hi All,

I am trying to save a data into table
this is the table
   task_id    int    4   
    group_id    int    4   
    from_name    varchar    50   
    from_address    varchar    50   
    email_subject    varchar    100   
    body_format    tinyint    1   
    body_content    varchar    8000   
    priority    tinyint    1   
    create_date    datetime    8   
notice body_content is varchar 8000. I am using store procedure to save it

CREATE PROCEDURE sp_AddEmailTask (
@GroupId    int,
@FromEmail    varchar(50),
@Subject    varchar(100),
@Format        int,
@Body        varchar(7000)

)
AS
Set nocount on

insert into email_task
(
group_id,
from_address,
email_subject,
body_format,
body_content,
create_date
)
values
(
@GroupId,
@FromEmail,
@Subject,
@Format,
@Body,
getdate()
)

set nocount off
GO


However, somehow the body_content were cut. After saving sucessly, I
see the data in body_content is not what I want to save. Can anyone
explain this?

Thanks very much in advance.
Jusitn

View 3 Replies View Related

When Should I Use VarChar(MAX) ?

Apr 1, 2006

Should I replace all my varchar fields to varchar(MAX) ?

What are the performance penalties to I get?

View 3 Replies View Related

Varchar(400)

Aug 18, 2005

I have a field varchar(400) when I run a query in query anal. it is not displaying all text. What should I do in order to display all data?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved