尝试使用IDENTITY_INSERT插入时出现奇怪的错误！ [英] Weird errors when trying to insert with IDENTITY_INSERT on!

问题描述

SQL Server 2000（下面的DDL）


如果我尝试在QA中运行此代码：


SET IDENTITY_INSERT tblAdminUsers ON

INSERT INTO tblAdminUsers

（fldUserID，

fldUsername，

fldPassword，

fldFullname，

fldPermission，

fldEmail，

fldInitials，

fldLastLogon，

fldBatch）

SELECT

fldUserID，

fldUsername，

fldPassword，

fldFullname，

fldPermission，

fldEmail，

fldInitials，

fldLastLogon，

fldBatch

FROM

[BSAVA_26-10-2006] .dbo.tblAdminUsers

SET IDENTITY_INSERT tblAdminUsers OFF


我收到一个错误：

IDENTITY_INSERT表已经开启

''BSAVA_Archive_Test_2006.dbo.GPS_CHAR''。无法执行SET操作

表格''tblAdminUsers''。


如果我尝试运行：

INSERT INTO tblAdminUsers

（fldUserID，

fldUsername，

fldPassword，

fldFullname，

fldPermission，

fldEmail，

fldInitials，

fldLastLogon，

fldBatch）

SELECT

fldUserID，

fldUsername，

fldPassword，

fldFullname，

fldPermission，

fldEmail，
fldInitials，

fldLastLogon，

fldBatch

FROM

[BSAVA_26-10-2006] .dbo.tblAdminUsers


我收到错误：

无法在表格中插入标识列的显式值
$ ID $ b''tblAdminUsers''当IDENTITY_INSERT设置为OFF时。


任何想法？仅供我参考的表格来自

[BSAVA_26-10-2006]表格。


TIA

爱德华


=====================

如果存在（ select * from dbo.sysobjects，其中id =

object_id（N''[dbo]。[tblAdminUsers]''）和OBJECTPROPERTY（id，

N''IsUserTable' '）= 1）

drop table [dbo]。[tblAdminUsers]

GO


if exists（select * from dbo .sysobjects其中id =

object_id（N''[dbo]。[GPS_CHAR]''）和OBJECTPROPERTY（id，N''IsDefault''）=

1 ）

drop default [dbo]。[GPS_CHAR]

GO


创建默认dbo.GPS_CHAR AS''''


CREATE TABLE [dbo]。[tblAdminUsers]（

[fldUserID] [int] IDENTITY（1,1）NOT NULL，

[fldUsername] [varchar]（20）COLLATE Latin1_General_CI_AS NULL，

[fldPassword] [varchar]（20）COLLATE Latin1_General_CI_AS NULL，

[fldFullname] [varchar]（50）COLLATE Latin1_General_CI_AS NULL，

[fldPermission] [smallint] NULL，

[fldEmail] [varchar]（50）COLLATE Latin1_General_CI_AS NULL，

[fldInitials] [varchar]（3）COLLATE Latin1_General_CI_AS NULL，

[fldLastLogon] [smalldatetime] NULL，

[fldBatch ] [char]（1）COLLATE Latin1_General_CI_AS NULL

）ON [PRIMARY]

GO

解决方案

据我所知，你已经为另一个表设置了identity_insert。你需要在插入tblAdminUsers之前将它设置为关闭。

所以（如果我没有遗漏的话）：


设置identity_insert BSAVA_Archive_Test_2006.dbo.GPS_CHAR OFF


然后用

SET IDENTITY_INSERT tblAdminUsers ON

insert。 ..


SET IDENTITY_INSERT tblAdminUsers OFF


< te ******** @ hotmail.comwrote in message

news：11 ******************** @ m73g2000cwd.googlegrou ps.com ...

SQL Server 2000（下面的DDL）


如果我尝试在QA中运行此代码：


SET IDENTITY_INSERT tblAdminUsers ON

INSERT INTO tblAdminUsers

（fldUserID，

fldUsername，

fldPassword，

fldFullname ，

fldPermission，

fldEmail，

fldInitials，

fldLastLogon，

fldBatch ）

SELECT

fldUserID，

fldUsername ，

fldPassword，

fldFullname，

fldPermission，

fldEmail，

fldInitials ，

fldLastLogon，

fldBatch

FROM

[BSAVA_26-10-2006] .dbo.tblAdminUsers

SET IDENTITY_INSERT tblAdminUsers OFF


我收到错误消息：

IDENTITY_INSERT表已经开启

' BSAVA_Archive_Test_2006.dbo.GPS_CHAR。无法执行SET操作

表格''tblAdminUsers''。


如果我尝试运行：

INSERT INTO tblAdminUsers

（fldUserID，

fldUsername，

fldPassword，

fldFullname，

fldPermission，

fldEmail，

fldInitials，

fldLastLogon，

fldBatch）

SELECT

fldUserID，

fldUsername，

fldPassword，

fldFullname，

fldPermission，

fldEmail，
fldInitials，

fldLastLogon，

fldBatch

FROM

[BSAVA_26-10-2006] .dbo.tblAdminUsers


我收到错误：

无法在表格中插入标识列的显式值
$ ID $ b''tblAdminUsers''当IDENTITY_INSERT设置为OFF时。


任何想法？仅供我参考的表格来自

[BSAVA_26-10-2006]表格。


TIA

爱德华


=====================

如果存在（ select * from dbo.sysobjects，其中id =

object_id（N''[dbo]。[tblAdminUsers]''）和OBJECTPROPERTY（id，

N''IsUserTable' '）= 1）

drop table [dbo]。[tblAdminUsers]

GO


if exists（select * from dbo .sysobjects其中id =

object_id（N''[dbo]。[GPS_CHAR]''）和OBJECTPROPERTY（id，N''IsDefault''）=

1 ）

drop default [dbo]。[GPS_CHAR]

GO


创建默认dbo.GPS_CHAR AS''''


CREATE TABLE [dbo]。[tblAdminUsers]（

[fldUserID] [int] IDENTITY（1,1）NOT NULL，

[fldUsername] [varchar]（20）COLLATE Latin1_General_CI_AS NULL，

[fldPassword] [varchar]（20）COLLATE Latin1_General_CI_AS NULL，

[ fldFullname] [varchar]（50）COLLATE Latin1_General_CI_AS NULL，

[fldPermission] [smallint] NULL，

[fldEmail] [varchar]（50）COLLATE Latin1_General_CI_AS NULL，

[fldInitials] [varchar]（3）COLLATE Latin1_General_CI_AS NULL，

[fldLastLogon] [smalldatetime] NULL，

[fldBatch] [char] （1）COLLATE Latin1_General_CI_AS NULL

）ON [PRIMARY]

GO

您是否注意到您已将前缀fld-在所有列上？

这不仅仅是破坏数据干扰并违反

ISO-11179规则的好方法，但它也告诉我们你不知道waht

列与字段完全不同。同样，愚蠢的，冗余的tbl-

前缀。


你桌上没有钥匙。身份永远不可能是一个关系

键。我可以插入相同的用户数据1000次，你不会检测冗余。您没有默认值或约束。什么

he3ck是一批？它看起来像是某种旗帜，但我们不会在SQL中使用

。


你所做的是模仿一副打卡或者磁带文件。

清理数据元素并给自己一个关键和约束，更多

像这样：


CREATE TABLE AdminUsers

（user_name VARCHAR（20）NOT NULL PRIMARY KEY，

password VARCHAR（20）NOT NULL

CHECK（LEN（密码） 5）， - 其他规则？

full_name VARCHAR（50）NOT NULL， - 修剪空格？

permission_code INTEGER DEFAULT 0 NOT NULL，

email_addr VARCHAR（50）NOT NULL

CHECK（<<< grep pattern match>>），

user_initials VARCHAR（3）DEFAULT''''NOT NULL，

lastlogon_date DATETIME

DEFAULT CURRENT_TIMESTAMP NOT NULL，

batch_foobarflag CHAR（1）NOT NULL）;它是什么？

我可以插入相同的用户数据1000次，你不会

检测冗余。您没有默认值或约束。什么

然后在具有IDENTITY属性的列上放置一个唯一约束。

email_addr VARCHAR （50）NOT NULL

CHECK（<<< grep pattern match>>），

你到底怎么样？当SQL Server

只能通过CLR访问外部内容时，执行<< grep模式匹配>？记住你是

的倡导者，他说应该没有CLR，一切都应该是标准的SQL。


和这个组中的其他帖子一样，我们正在等待一个答案 - 这可以轻松，支持和维护
在数据库外可以重复使用

使用CLR函数和正则表达式.NET类。
< blockquote class =post_quotes>
密码VARCHAR（20）NOT NULL

CHECK（LEN（密码）5）， - 其他规则？

再次，为了实现严格的密码（模拟windows严格的策略）

你如何在约束中的标准SQL中做到这一点而不求助

很多LIKES，CASTS和CASE声明？简短回答 - 你再也不能使用CLR功能获得
了。

你所做的是模仿一副穿孔卡或一个磁带文件。

清理数据元素并给自己一个关键和约束，更多

像这样：

你提倡的是过时的编程技术，而不是关注微软的

关于产品使用的建议，而不是关注声音和专业

开发，可维护性和支持策略 - 声音就像一个

牛仔对我来说。

email_addr VARCHAR（50）NOT NULL

在哪里定义一个电子邮件地址只能是50个字符？

是工业标准吗？


-

Tony Rogerson

SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - 来自的技术评论SQL

服务器顾问
http://sqlserverfaq.com - 免费视频教程

" - CELKO - " < jc ******* @ earthlink.netwrote in message

news：11 ********************* @ f16g2000cwb .googlegro ups.com ...

您是否注意到您已将前缀fld-在所有列上？

这不仅仅是破坏数据干扰并违反

ISO-11179规则的好方法，但它也告诉我们你不知道waht

列与字段完全不同。同样，愚蠢的，冗余的tbl-

前缀。


你桌上没有钥匙。身份永远不可能是一个关系

键。我可以插入相同的用户数据1000次，你不会检测冗余。您没有默认值或约束。什么

he3ck是一批？它看起来像是某种旗帜，但我们不会在SQL中使用

。


你所做的是模仿一副打卡或者磁带文件。

清理数据元素并给自己一个关键和约束，更多

像这样：


CREATE TABLE AdminUsers

（user_name VARCHAR（20）NOT NULL PRIMARY KEY，

password VARCHAR（20）NOT NULL

CHECK（LEN（密码） 5）， - 其他规则？

full_name VARCHAR（50）NOT NULL， - 修剪空格？

permission_code INTEGER DEFAULT 0 NOT NULL，

email_addr VARCHAR（50）NOT NULL

CHECK（<<< grep pattern match>>），

user_initials VARCHAR（3）DEFAULT''''NOT NULL，

lastlogon_date DATETIME

DEFAULT CURRENT_TIMESTAMP NOT NULL，

batch_foobarflag CHAR（1）NOT NULL）;这是什么？

SQL Server 2000 (DDL below)

If I try to run this code in QA:

SET IDENTITY_INSERT tblAdminUsers ON
INSERT INTO tblAdminUsers
(fldUserID,
fldUsername,
fldPassword,
fldFullname,
fldPermission,
fldEmail,
fldInitials,
fldLastLogon,
fldBatch)
SELECT
fldUserID,
fldUsername,
fldPassword,
fldFullname,
fldPermission,
fldEmail,
fldInitials,
fldLastLogon,
fldBatch
FROM
[BSAVA_26-10-2006].dbo.tblAdminUsers
SET IDENTITY_INSERT tblAdminUsers OFF

I get an error:
IDENTITY_INSERT is already ON for table
''BSAVA_Archive_Test_2006.dbo.GPS_CHAR''. Cannot perform SET operation
for table ''tblAdminUsers''.

If I try to run:
INSERT INTO tblAdminUsers
(fldUserID,
fldUsername,
fldPassword,
fldFullname,
fldPermission,
fldEmail,
fldInitials,
fldLastLogon,
fldBatch)
SELECT
fldUserID,
fldUsername,
fldPassword,
fldFullname,
fldPermission,
fldEmail,
fldInitials,
fldLastLogon,
fldBatch
FROM
[BSAVA_26-10-2006].dbo.tblAdminUsers

I get the error:
Cannot insert explicit value for identity column in table
''tblAdminUsers'' when IDENTITY_INSERT is set to OFF.

Anyone any ideas? FYI the tables I''m INSERTing into were scripted from
the [BSAVA_26-10-2006] tables.

TIA

Edward

=====================
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[tblAdminUsers]'') and OBJECTPROPERTY(id,
N''IsUserTable'') = 1)
drop table [dbo].[tblAdminUsers]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[GPS_CHAR]'') and OBJECTPROPERTY(id, N''IsDefault'') =
1)
drop default [dbo].[GPS_CHAR]
GO

create default dbo.GPS_CHAR AS ''''

CREATE TABLE [dbo].[tblAdminUsers] (
[fldUserID] [int] IDENTITY (1, 1) NOT NULL ,
[fldUsername] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[fldPassword] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[fldFullname] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[fldPermission] [smallint] NULL ,
[fldEmail] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[fldInitials] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[fldLastLogon] [smalldatetime] NULL ,
[fldBatch] [char] (1) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

解决方案

As far as I can see, you have set identity_insert on for another table. You
first need to set it to off before inserting into tblAdminUsers.
So (if I''m not missing something):

set identity_insert BSAVA_Archive_Test_2006.dbo.GPS_CHAR OFF

and then go with the
SET IDENTITY_INSERT tblAdminUsers ON
insert...

SET IDENTITY_INSERT tblAdminUsers OFF

<te********@hotmail.comwrote in message
news:11********************@m73g2000cwd.googlegrou ps.com...

SQL Server 2000 (DDL below)

If I try to run this code in QA:

SET IDENTITY_INSERT tblAdminUsers ON
INSERT INTO tblAdminUsers
(fldUserID,
fldUsername,
fldPassword,
fldFullname,
fldPermission,
fldEmail,
fldInitials,
fldLastLogon,
fldBatch)
SELECT
fldUserID,
fldUsername,
fldPassword,
fldFullname,
fldPermission,
fldEmail,
fldInitials,
fldLastLogon,
fldBatch
FROM
[BSAVA_26-10-2006].dbo.tblAdminUsers
SET IDENTITY_INSERT tblAdminUsers OFF

I get an error:
IDENTITY_INSERT is already ON for table
''BSAVA_Archive_Test_2006.dbo.GPS_CHAR''. Cannot perform SET operation
for table ''tblAdminUsers''.

If I try to run:
INSERT INTO tblAdminUsers
(fldUserID,
fldUsername,
fldPassword,
fldFullname,
fldPermission,
fldEmail,
fldInitials,
fldLastLogon,
fldBatch)
SELECT
fldUserID,
fldUsername,
fldPassword,
fldFullname,
fldPermission,
fldEmail,
fldInitials,
fldLastLogon,
fldBatch
FROM
[BSAVA_26-10-2006].dbo.tblAdminUsers

I get the error:
Cannot insert explicit value for identity column in table
''tblAdminUsers'' when IDENTITY_INSERT is set to OFF.

Anyone any ideas? FYI the tables I''m INSERTing into were scripted from
the [BSAVA_26-10-2006] tables.

TIA

Edward

=====================
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[tblAdminUsers]'') and OBJECTPROPERTY(id,
N''IsUserTable'') = 1)
drop table [dbo].[tblAdminUsers]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[GPS_CHAR]'') and OBJECTPROPERTY(id, N''IsDefault'') =
1)
drop default [dbo].[GPS_CHAR]
GO

create default dbo.GPS_CHAR AS ''''

CREATE TABLE [dbo].[tblAdminUsers] (
[fldUserID] [int] IDENTITY (1, 1) NOT NULL ,
[fldUsername] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[fldPassword] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[fldFullname] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[fldPermission] [smallint] NULL ,
[fldEmail] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[fldInitials] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[fldLastLogon] [smalldatetime] NULL ,
[fldBatch] [char] (1) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

Did you notice that you have put the prefix "fld-" on all the columns?
This is not just a great way to destroy a data dictioanry and violate
ISO-11179 rules, but it also tells us that you have not idea waht
columns are nothing like fields. Likewise, the silly, redundant "tbl-"
prefix.

You have no key on the table. Identity cannot ever be a relational
key. I can insert the same user data 1000 times and you will not
detect the redundancy. You have no defaults or constraints. What the
he3ck is a batch? It looks like a flag of some kind, but we do not use
those in SQL.

What you did was mimic a deck of punch cards or a magnetic tape file.
Clean up the data element and get yourself a key and constraints, more
like this:

CREATE TABLE AdminUsers
(user_name VARCHAR(20) NOT NULL PRIMARY KEY,
password VARCHAR(20) NOT NULL
CHECK (LEN(password) 5), -- other rules?
full_name VARCHAR(50) NOT NULL, -- trim spaces?
permission_code INTEGER DEFAULT 0 NOT NULL,
email_addr VARCHAR(50) NOT NULL
CHECK (<<grep pattern match>>),
user_initials VARCHAR(3) DEFAULT '' '' NOT NULL,
lastlogon_date DATETIME
DEFAULT CURRENT_TIMESTAMP NOT NULL,
batch_foobarflag CHAR(1) NOT NULL); what is it?

I can insert the same user data 1000 times and you will not

detect the redundancy. You have no defaults or constraints. What the

Then put a unique constraint on the column that has the IDENTITY propety.

email_addr VARCHAR(50) NOT NULL
CHECK (<<grep pattern match>>),

How on earth are you going to do a <<grep pattern match>when SQL Server
can only access external stuff like that via CLR? Remember you are the
advocate who says there should be no CLR, everything should be standard SQL.

Like the other post in this group we are waiting on an answer - this can
easily, supported and maintainable oh and re-useable outside the database
using a CLR function and the regex .NET class.

password VARCHAR(20) NOT NULL
CHECK (LEN(password) 5), -- other rules?

Again, for implementing a strict password (simulate windows strict policy)
how would you do that in standard SQL in a constraint without resorting to
lots of LIKES, CASTS and CASE statements? Short answer- you can''t without
using a CLR function again.

What you did was mimic a deck of punch cards or a magnetic tape file.
Clean up the data element and get yourself a key and constraints, more
like this:

What you advocate is dated programming techniques, not following Microsofts
recommendations on product use and not following sound and professional
strategies for development, maintainability and support - sounds like a
cowboy approach to me.

email_addr VARCHAR(50) NOT NULL

Where is it defined that an email address can only be 50 characters long? Is
that an industrial standard?

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.netwrote in message
news:11*********************@f16g2000cwb.googlegro ups.com...

Did you notice that you have put the prefix "fld-" on all the columns?
This is not just a great way to destroy a data dictioanry and violate
ISO-11179 rules, but it also tells us that you have not idea waht
columns are nothing like fields. Likewise, the silly, redundant "tbl-"
prefix.

You have no key on the table. Identity cannot ever be a relational
key. I can insert the same user data 1000 times and you will not
detect the redundancy. You have no defaults or constraints. What the
he3ck is a batch? It looks like a flag of some kind, but we do not use
those in SQL.

What you did was mimic a deck of punch cards or a magnetic tape file.
Clean up the data element and get yourself a key and constraints, more
like this:

CREATE TABLE AdminUsers
(user_name VARCHAR(20) NOT NULL PRIMARY KEY,
password VARCHAR(20) NOT NULL
CHECK (LEN(password) 5), -- other rules?
full_name VARCHAR(50) NOT NULL, -- trim spaces?
permission_code INTEGER DEFAULT 0 NOT NULL,
email_addr VARCHAR(50) NOT NULL
CHECK (<<grep pattern match>>),
user_initials VARCHAR(3) DEFAULT '' '' NOT NULL,
lastlogon_date DATETIME
DEFAULT CURRENT_TIMESTAMP NOT NULL,
batch_foobarflag CHAR(1) NOT NULL); what is it?

这篇关于尝试使用IDENTITY_INSERT插入时出现奇怪的错误！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！