Error inserting record in IDENTITY column

Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, that should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value for the identity
column.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT

Error inserting record in IDENTITY column

Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JT
Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT

Error inserting record in IDENTITY column

Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity
value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, tha
t should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value
for the identity
column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should hav
e
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicat
e
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT

Error inserting into Table Datatype with Identity Column

Hi
We are using the code below to simulate a cursor using the Table Datatype
but we are getting an error when inserting the records.
Server: Msg 8101, Level 16, State 1, Line 19
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Line 28
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Any help would be much appreciated
Thanks
B
CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Type
VARCHAR(55))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.Output VARCHAR(8000)
DECLARE @.Imports VARCHAR(355)
DECLARE @.Description VARCHAR(355)
DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
VARCHAR(355), RowId INT IDENTITY(1, 1))
DECLARE @.count INT
DECLARE @.iRow INT
SET @.Output = ''
IF @.Type = 'All Items'
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
ORDER BY Usr_Imports.ID
END
ELSE
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
WHERE EntityRef = @.Client AND MatterNo =
@.OrderNo
ORDER BY Usr_Imports.ID
END
/** Simulate Cursor
****************************************
****************************/
SET @.count = @.@.ROWCOUNT
SET @.iRow = 1
WHILE @.iRow <= @.count
BEGIN
SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
FROM @.tblImports
WHERE RowId = @.iRow
SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
SET @.iRow = @.iRow + 1
END
RETURN @.Output
ENDAlways specify the column list in an INSERT statement:
INSERT @.tblImports (dm_ImportDesc, [Description])
SELECT dm_ImportDesc, [Description]
FROM ...
I recommend that you don't use ORDER BY in the INSERT statement. It is
not necessarily guaranteed that the IDENTITY column will be populated
in the order you specify.
If you explain your requirement I'm sure someone can suggest something
better. Why do concatentation and formatting in the database anyway?
David Portas
SQL Server MVP
--|||Ben
error clearly states that you are trying to update an identity column in the
function( not in cucrsor)
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
Post DDL
--
Regards
R.D
--Knowledge gets doubled when shared
"Ben" wrote:

> Hi
>
> We are using the code below to simulate a cursor using the Table Datatype
> but we are getting an error when inserting the records.
>
> Server: Msg 8101, Level 16, State 1, Line 19
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
> Server: Msg 8101, Level 16, State 1, Line 28
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
>
> Any help would be much appreciated
>
> Thanks
> B
>
> CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Ty
pe
> VARCHAR(55))
> RETURNS VARCHAR(8000) AS
> BEGIN
>
> DECLARE @.Output VARCHAR(8000)
> DECLARE @.Imports VARCHAR(355)
> DECLARE @.Description VARCHAR(355)
> DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
> VARCHAR(355), RowId INT IDENTITY(1, 1))
> DECLARE @.count INT
> DECLARE @.iRow INT
> SET @.Output = ''
>
> IF @.Type = 'All Items'
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> ORDER BY Usr_Imports.ID
> END
> ELSE
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> WHERE EntityRef = @.Client AND MatterNo
=
> @.OrderNo
> ORDER BY Usr_Imports.ID
> END
>
> /** Simulate Cursor
> ****************************************
****************************/
> SET @.count = @.@.ROWCOUNT
> SET @.iRow = 1
> WHILE @.iRow <= @.count
> BEGIN
> SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
> FROM @.tblImports
> WHERE RowId = @.iRow
>
> SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
> Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
> SET @.iRow = @.iRow + 1
> END
>
> RETURN @.Output
> END
>
>|||David,
It's with yukon.
http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
-oj
"David Portas"
> I recommend that you don't use ORDER BY in the INSERT statement. It is
> not necessarily guaranteed that the IDENTITY column will be populated
> in the order you specify.|||Thank you both for your input, it works perfectly now.
Regards
B
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:6FEF31BA-887B-49DA-A0DD-2EA4B07C5A71@.microsoft.com...
> Ben
> error clearly states that you are trying to update an identity column in
the
> function( not in cucrsor)
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
> Post DDL
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Ben" wrote:
>
Datatype
only be
only be
@.Type
database_default
database_default
MatterNo =
[Description]
'All|||> INSERT queries that use SELECT with ORDER BY to populate rows
> guarantees how identity values are computed but not the order
> in which the rows are inserted
Possibly, but the same claim is documented in a KB for 2000 and in that
case it is inaccurate. See the following link for Gert-Jan's repro.
Haven't tested this on 2005 but even if it works I'm not sure how
confident I would be about it, given the history and the kludgy nature
of this "feature".:
http://groups.google.co.uk/group/mi...bfd47d975aca778
Now that we have RECORD_NUMBER() the ORDER BY in an INSERT is
redundant. RECORD_NUMBER() should be preferred IMO.
David Portas
SQL Server MVP
--sql

error in using the IFF statment

Hi,
I used=IFF( Fields!used.Value=False, 'Fields!s_Count.Value','0')in my column and it shows an error. The error is as below...


c:\projects\ics\report ics 1\Stock Balance Report.rdl The value expression for the textbox 's_Count' contains an error: [BC30451] Name 'IFF' is not declared.
What does it mean that the 'IFF' is not declared? Does anyone know why..?

Hi, u can easily write the iif Condition... I am giving some Examples.....
=IFF( Fields!used.Value=False, 'Fields!s_Count.Value','0') (ur Statement)
As
=IFF( Fields!used.Value=False,Fields!s_Count.Value,"0") "Write One" Note: Don't use single Quotes ...use Double Quotes...(just as vb syntax)
and Some Examples:
1) =iif(Fields!MainNo.Value is Nothing or Fields!MainNo.Value = "",Fields!SRNo.Value,Fields!MainNo.Value)
2) Switch: and If U Want to Check Multiple Conditions u can use like this....

=Switch(
ucase(First(Fields!priorityid.Value))="LM","Lunch Motion",
ucase(First(Fields!priorityid.Value))="M","Motion",
ucase(First(Fields!priorityid.Value))="O","Oridanry",ucase(First(Fields!priorityid.Value))="FLC","Fair List Cases",
ucase(First(Fields!priorityid.Value))="S","Supplemental")

ok......then u can check if u have still errors Mail Me....praveenk_dotnet@.yahoo.com
|||How about IIF ?

Error in Identity Column

For some reason my primary key, identity column skipped a couple of numbers. It went from row 734 to 736, 737, 739

Any ideas why this would happen?

thanks

hi

735 & 738 must've been created but deleted somehow.

|||

No, it was not deleted

|||

Rick0194:

No, it was not deleted

Yes they were, that's the only way it could have been skipped. Now, it may be that they get inserted as part of a transaction and something goes wrong and rolls it back -- the identity colum's value will appear to skip the next time a good insert occurs (I just confirmed that on sql 2000)

Error in GroupBy

I have the query shown below which throws an error that "JudgeName" is an
invalid column name? Is using an alias not allowed here?
Wayne
================= code ==============
SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By JudgeName, CaptionSQL BASIC RULE: YOU CAN'T USE ALIAS IN GROUP BY
Method 1:
SELECT (Names.LastName + ',' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By (Names.LastName + ',' + Names.FirstName), Caption
Method 2
SELECT a.JudgeName from
(SELECT (Names.LastName + ',' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By Caption ) a GROUP BY a.JudgeName
Regards
R.D
"Wayne Wengert" wrote:

> I have the query shown below which throws an error that "JudgeName" is an
> invalid column name? Is using an alias not allowed here?
> Wayne
> ================= code ==============
> SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
> FROM JudgeEvals
> Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
> INNER JOIN Names ON Names.NameID=Judges.NameID
> Group By JudgeName, Caption
>
>|||Wayne,
Unfortunately not. You can use the expression that makes up JudgeName. For
example,
SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By (Names.LastName + ', ' + Names.FirstName), Caption
- or -
SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By Names.LastName, Names.FirstName, Caption
Hope this helps,
Yosh
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:ugo4kVbxFHA.3644@.TK2MSFTNGP11.phx.gbl...
>I have the query shown below which throws an error that "JudgeName" is an
>invalid column name? Is using an alias not allowed here?
> Wayne
> ================= code ==============
> SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
> FROM JudgeEvals
> Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
> INNER JOIN Names ON Names.NameID=Judges.NameID
> Group By JudgeName, Caption
>|||Locally, the GROUP BY is performed before the SELECT list, hence you cannot
group by a column alias.
Either repeat the expression in GROUP BY or use a derived table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:ugo4kVbxFHA.3644@.TK2MSFTNGP11.phx.gbl...
>I have the query shown below which throws an error that "JudgeName" is an i
nvalid column name? Is
>using an alias not allowed here?
> Wayne
> ================= code ==============
> SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
> FROM JudgeEvals
> Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
> INNER JOIN Names ON Names.NameID=Judges.NameID
> Group By JudgeName, Caption
>|||...
GROUP BY LastName, FirstName, Caption
David Portas
SQL Server MVP
--

Error in function argument

I have a table with over 11,000 records and I need to do a find and replace using SET and Where conditions. Basically I have one column in the table called RealAudioLink. It contains entries like: wkdy20070416-a.rm and wkdy20070416-b.rm and conv20070416.rm.

I need the select statement to find all wkdy entries and replace those characters with Weekday. I also need it to find all dashes and small a's and b's and replace with null or nothing. Then I need it to insert a capital letter A or B in the
wkdy20070416-a.rm filename so that when it's all said and done that entry would read:

WeekdayA20070416.rm
WeekdayB20070416.rm
Conversation20070416.rm

Here is the code I am working with. It needs help. I'm close but I'm not knowledgeable with using SET or with removing dashes and inserting capital letters all in the same select statement.

Code Snippet

UPDATE T_Programs_TestCopy
(SET RealAudioLink = REPLACE(RealAudioLink, '-a', '')
AND
(SET RealAudioLink = REPLACE(RealAudioLink, 'wkdy', 'WeekdayA')
WHERE (RealAudioLink LIKE 'wkdy%'))

I've never done anything like this before so I would be very appreciative of any assistance with the select statement. I am reading up on it but it would be great to get another perspective from a more experienced sql developer.

Thanks

Here is an example of creating a custom function to make the changes, and then using that function in updating the table.

Code Snippet

CREATE FUNCTION dbo.fnCleanMyData
( @.BadDataIn varchar(2000) )
RETURNS varchar(2000)
AS
BEGIN
SET @.BadDataIn =
CASE
WHEN patindex( '%-a.rm', @.BadDataIn ) > 0
THEN replace( replace( stuff( @.BadDataIn, 5, 1, 'A' ), 'wkdy', 'Weekday' ), '-a', '' )
WHEN patindex( '%-b.rm', @.BadDataIn ) > 0
THEN replace( replace( stuff( @.BadDataIn, 5, 1, 'B' ), 'wkdy', 'Weekday' ), '-b', '' )
WHEN patindex( 'conv%', @.BadDataIn ) > 0
THEN replace( @.BadDataIn, 'conv', 'Conversation' )
END
RETURN @.BadDataIn
END
GO

DECLARE @.MyTable table
( FileNames varchar(100) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 'wkdy20070416-a.rm' )
INSERT INTO @.MyTable VALUES ( 'wkdy20070416-b.rm' )
INSERT INTO @.MyTable VALUES ( 'conv20070416.rm' )

SELECT FileNames
FROM @.MyTable

FileNames
-
wkdy20070416-a.rm
wkdy20070416-b.rm
conv20070416.rm

UPDATE @.MyTable
SET FileNames = dbo.fnCleanMyData( FileNames )

SELECT FileNames
FROM @.MyTable

FileNames
--
WeekdayA0070416.rm
WeekdayB0070416.rm
Conversation20070416.rm

Change the function name to whatever you like, and I have used a table variable in this example. Use your own table and column names.

Error in Derived Column component

I am getting this error,

[Derived Column [192]] Error: Converting an input column from type DT_STR to type DT_WSTR failed. An error occurred while performing the implicit conversion on the input column.

But I don really understand y there is a attempt to type cast at all ?

Please advise ....

thanks in advance

The expression evaluator almost always implicitly converts DT_STR columns to DT_WSTR, because all string functions are implemented for Unicode only. There are only a couple very specific exceptions.

You might try to putting a data conversion transform in your flow and doing an explicit convert from DT_STR to DT_WSTR for that column and see if you get a more helpful error message. Or, redirect that row to an error output, and inspect the data and post it here.

Thanks
Mark

|||

Mark Durley wrote:

You might try to putting a data conversion transform in your flow and doing an explicit convert from DT_STR to DT_WSTR for that column and see if you get a more helpful error message.

I thought of this earlier and when I tried with a Data Conversion Component, I was not able to locate an option as DT_WSTR. Am I missing something here?

thanks for the Help so far...

|||

In the Data Conversion transform UI, in the Data Type drop down, you should see an option:

Unicode string [DT_WSTR]

Mark

|||

In the column marked Data Type in your Derived Column Shape change the value from Unicode [DT_WSTR] to string [DT_STR]

Does this answer your question?

|||Thx for the reply :)

Error in Deploying an Analysis Services Project

Hi,

I am trying to deploy an Analysis Project and I get the error:

Error 1 The Description element at line 292, column 29 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) cannot appear under Envelope/Body/Execute/Command/Batch/Alter/ObjectDefinition/Database/Cubes/Cube/Dimensions/Dimension. 0 0

Any idea?

Thanks in advance,

--Dimitris Doukas

Hi,

This is a defect that will be addressed in the service pack(s); meanwhile, to work around it, please do the followings:

- open each cube editor and, in the first tab (Cube Structure), check each cube dimension if it has a Description set

- remove all those Descriptions (by just specifying an empty string there) and then re-deploy

Adrian.

|||

Hi Adrian,

Thanks for the hint. Actually I had to edit the XML file to erase the description clause. If you just empty the string from the BI Studio if doesn't work.

Regards,

--DImitris Doukas

Error in Alter Table Statement

Hi,
I tried to alter one column as Identity colum. I executed the following
statement.
ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
NOT NULL
But it gives the following error
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
The table has some rows. I do not want to re-set the identity value. I can
able to do this in Enterprise Manager. But I want to do in T-Sql statement.
Pls help me
Thanx
San
You cannot do that
CREATE TABLE #Test
(
col INT NOT NULL
)
GO
INSERT INTO #Test VALUES (20)
GO
ALTER TABLE #Test ADD col1 INT IDENTITY(1,1)
GO
SET IDENTITY_INSERT #Test ON
GO
INSERT INTO #Test(col1)SELECT MAX(col) FROM #Test
GO
SET IDENTITY_INSERT #Test OFF
GO
INSERT INTO #Test (col) VALUES (100)
GO
SELECT * FROM #Test
"San" <San@.discussions.microsoft.com> wrote in message
news:290563A4-569C-4136-80D7-6EEC6DDE7463@.microsoft.com...
> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1,
1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql
statement.
> Pls help me
> Thanx
>
|||Hi,
If you want to do this in t-sql you must follow the steps:
1) Create temporary table like CREATE TABLE dbo.Tmp_Table_1
(
Column_1 smallint NOT NULL IDENTITY (1, 1),
--,
) ON [PRIMARY]
2) SET IDENTITY_INSERT dbo.tmp_Table_1 ON
3) EXEC('INSERT INTO dbo.tmp_Table_1 (Col_1,--)
SELECT Col_1, -- FROM dbo.Table_1 TABLOCKX')
4) SET IDENTITY_INSERT dbo.Tmp_Table_1 OFF
5) drop table dbo.Table_1
6) sp_rename 'dbo.Tmp_Table_1','dbo.Table_1'
If you have indexes, constraints etc.. add a step to create them as well on
new table
I did not know any t-sql statement that alters a column to identity. If you
capture a trace via profiler while altering the column in Enterprise Manager
you will find out that it performs the same logic.
Hope this helps.
"San" wrote:

> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql statement.
> Pls help me
> Thanx
>

Error in Alter Table Statement

Hi,
I tried to alter one column as Identity colum. I executed the following
statement.
ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
NOT NULL
But it gives the following error
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
The table has some rows. I do not want to re-set the identity value. I can
able to do this in Enterprise Manager. But I want to do in T-Sql statement.
Pls help me
ThanxSan
You cannot do that
CREATE TABLE #Test
(
col INT NOT NULL
)
GO
INSERT INTO #Test VALUES (20)
GO
ALTER TABLE #Test ADD col1 INT IDENTITY(1,1)
GO
SET IDENTITY_INSERT #Test ON
GO
INSERT INTO #Test(col1)SELECT MAX(col) FROM #Test
GO
SET IDENTITY_INSERT #Test OFF
GO
INSERT INTO #Test (col) VALUES (100)
GO
SELECT * FROM #Test
"San" <San@.discussions.microsoft.com> wrote in message
news:290563A4-569C-4136-80D7-6EEC6DDE7463@.microsoft.com...
> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1,
1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql
statement.
> Pls help me
> Thanx
>|||Hi,
If you want to do this in t-sql you must follow the steps:
1) Create temporary table like CREATE TABLE dbo.Tmp_Table_1
(
Column_1 smallint NOT NULL IDENTITY (1, 1),
--,
--
) ON [PRIMARY]
2) SET IDENTITY_INSERT dbo.tmp_Table_1 ON
3) EXEC('INSERT INTO dbo.tmp_Table_1 (Col_1,--)
SELECT Col_1, -- FROM dbo.Table_1 TABLOCKX')
4) SET IDENTITY_INSERT dbo.Tmp_Table_1 OFF
5) drop table dbo.Table_1
6) sp_rename 'dbo.Tmp_Table_1','dbo.Table_1'
If you have indexes, constraints etc.. add a step to create them as well on
new table
I did not know any t-sql statement that alters a column to identity. If you
capture a trace via profiler while altering the column in Enterprise Manager
you will find out that it performs the same logic.
Hope this helps.
"San" wrote:
> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql statement.
> Pls help me
> Thanx
>

Error in a query ?

Hi,

When I compiled the below I get the following error :

The column prefix 'cat' does not match with a table name or alias name used in the query"

What might be the error cause ?

Please adivce.

Thanks,
Sam

CREATE VIEW colview
AS
SELECT
wi.wiciu,
wi.ttldfg,
wi.lngcdedfg,
lng.desccdedfgd AS LNGCDETXTSDGF,
wi.durhhdfg,
wi.durmmdfg,
wi.durssdfg,
CAST(DATEPART(YYYY, cprdtsdf) AS VARCHAR) + CAST(DATEPART(MM, cprdtsdf) AS VARCHAR) + CAST(DATEPART(DD, cprdtsdf) AS VARCHAR) AS CPRDTFXGV,
wi.cprnrsdf,
wi.recdindsdf,
wi.postdtsdf,
CAST(DATEPART(YYYY, wi.cretssdf) AS VARCHAR) + CAST(DATEPART(MM, wi.cretssdf) AS VARCHAR) + CAST(DATEPART(DD, wi.cretssdf) AS VARCHAR) AS CRETSFG,
wi.creuidg,
wi.lstupddtfdg,
wi.upddfguid,
'DOMDG' AS CAT,
cat.dessdfccde AS CATTXTSDF,
cat.lngsdf,
wi.discatdsf,
mdc.desccde AS DISCATTXTSDF,
wi.txtmusrelsdf,
tmr.desccdesdf AS TXTMUSRELTXT,
wi.musarrdsf,
mac.dessdfccde AS MUSARRTXT,
wi.lyrsdfcarr,
lac.dedsfsccde AS LYRCARRTXT,
wi.vrsdftyp,
vrt.dessdfccde AS VRTYPTXT,
wi.exrsdfptyp,
exrp.desdfsccde AS EXRPTYPTXT,
wi.cpssdfttyp,
cps.desdfsccde AS CPSTTYPTXT
FROM cds_lkup cat,
cds_lkup mdc RIGHT OUTER JOIN tbltitle wi ON mdc.txtcde = LTRIM(RTRIM(wi.discat))
AND mdc.lkupid = 'MUSDISCAT'
AND mdc.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup tmr ON tmr.txtcde = LTRIM(RTRIM(wi.txtmusrel))
AND tmr.lkupid = 'TXTMUSREL'
AND tmr.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup mac ON mac.txtcde = LTRIM(RTRIM(wi.musarr))
AND mac.lkupid = 'MUSARRCDE'
AND mac.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup lac ON lac.txtcde = LTRIM(RTRIM(wi.lyrcarr))
AND lac.lkupid = 'LYRADPCDE'
AND lac.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup vrt ON vrt.txtcde = LTRIM(RTRIM(wi.vrtyp))
AND vrt.lkupid = 'VRTYP'
AND vrt.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup exrp ON exrp.txtcde = LTRIM(RTRIM(wi.exrptyp))
AND exrp.lkupid = 'EXCPTYP'
AND exrp.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup cps ON cps.txtcde = LTRIM(RTRIM(wi.cpsttyp))
AND cps.lkupid = 'CPSTTYP'
AND cps.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup lng ON lng.txtcde = wi.lngcde
AND lng.lkupid = 'LNG'
AND lng.dmncde = 'WRK'
AND cat.txtcde = 'DOM'
AND cat.lkupid = 'CAT'
AND cat.dmncde = 'WRK'
WHERE (cat.lng = mdc.lng
OR mdc.lng IS NULL)
AND (cat.lng = tmr.lng
OR tmr.lng IS NULL)
AND (cat.lng = mac.lng
OR mac.lng IS NULL)
AND (cat.lng = lac.lng
OR lac.lng IS NULL)
AND (cat.lng = vrt.lng
OR vrt.lng IS NULL)
AND (cat.lng = exrp.lng
OR exrp.lng IS NULL)
AND (cat.lng = cps.lng
OR cps.lng IS NULL)
AND (cat.lng = lng.lng
OR lng.lng IS NULL)Try wrapping you as values in double quotes so more like this...

SELECT P.StatusID as "Stat" FROM Product P WHERE 1=1 AND StatusID = 11 AND TradeDate >= 'Jul 19 2004'
order by "Stat"|||Hi,

Wil this solve my problem, I don't think so. Since the alias names also works without that double quotes.

Please explain bit more.

Thanks,
Sam|||Sorry my mistake I was skim reading the question and not really paying much attention...

I suspect you have a problem because you are creating a table alias that is the same as a column alias...|||Hi,

Even the same alias name for a table and column is allowed in SQL Server. Try it out..Its working fine. Here I have a doubt whether I am doing things wrongly while joining the columns with prefix...But I am not sure of it...|||I doubt whether this is the error :

I have defined 'CAT' as an alias to a table which is not involved in JOINED tables ( as you can see from the FROM clause of the query), but I have used this prefix in 'ON' clause in the query. Hence the error. But if I use the other prefix for the same table which involves in JOIN tables defined, then this error does not comes.

Eg :-

select sno from fr1 cat, fr1 mdc left join fr2 wi on cat.sno = wi.frsno

The above will cause error when prefix 'cat' is used in 'ON' clause, since it is not defined in JOINed tables.

But if I use like the below one, then there is no error :

select sno from fr1 cat, fr1 mdc left join fr2 wi on mdc.sno = wi.frsno

Am I right on the above said.

Please advice.

Thanks,
Sam