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.tblAdminUs ers
SET IDENTITY_INSERT tblAdminUsers OFF
I get an error:
IDENTITY_INSERT is already ON for table
'BSAVA_Archive_ Test_2006.dbo.G PS_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.tblAdminUs ers
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 10 8222
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_T est_2006.dbo.GP S_CHAR OFF
and then go with the
SET IDENTITY_INSERT tblAdminUsers ON
insert...
SET IDENTITY_INSERT tblAdminUsers OFF
<te********@hot mail.comwrote in message
news:11******** ************@m7 3g2000cwd.googl egroups.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.tblAdminUs ers
SET IDENTITY_INSERT tblAdminUsers OFF
I get an error:
IDENTITY_INSERT is already ON for table
'BSAVA_Archive_ Test_2006.dbo.G PS_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.tblAdminUs ers
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_TIMESTA MP NOT NULL,
batch_foobarfla g 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*******@eart hlink.netwrote in message
news:11******** *************@f 16g2000cwb.goog legroups.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_TIMESTA MP NOT NULL,
batch_foobarfla g CHAR(1) NOT NULL); what is it?
--CELKO-- wrote:
[incredibly valuable insight snipped]
Many thanks for the lesson O great master. The very words of wisdom
that fall from your lips are enough to render us mere mortals shriven
in your sight. We, your humble acolytes, realise that you are
omnicscient, and that therefore you know that we sometimes inherit data
structures that were generated by others, who do not have your
boundless wisdom, but it is not always possible to rewrite applications
end-to-end in order to conform to your Holy Writ.
However, could you not find it within your bountiful beneficence to
answer the question?
Edward
MC wrote:
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_T est_2006.dbo.GP S_CHAR OFF
and then go with the
SET IDENTITY_INSERT tblAdminUsers ON
insert...
SET IDENTITY_INSERT tblAdminUsers OFF
Thanks for at least trying to answer the question! Unfortunately, your
suggestion:
set identity_insert BSAVA_Archive_T est_2006.dbo.GP S_CHAR OFF
returns the error:
'BSAVA_Archive_ Test_2006.dbo.G PS_CHAR' is not a user table. Cannot
perform SET operation.
Since the intent of the operation is to create an archive database
(which is, for this purpose, read-only) I have removed the IDENTITY
attribute from the fldUserID column.
However, I'm still interested to know why this has happened, if anyone
has any ideas!
Edward
Hi Edward,
Just ignore celko, he's an arrogant idiot with little real industrial
experience; he teaches standard sql and database design and that's it, but
that seems to have been picked up through doing a maths degree or something;
the old self-taught problem some people have....
Anyway, MC's answer should give you what you need.
Tony.
--
Tony Rogerson
SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant http://sqlserverfaq.com - free video tutorials
<te********@hot mail.comwrote in message
news:11******** **************@ h48g2000cwc.goo glegroups.com.. .
>
--CELKO-- wrote:
[incredibly valuable insight snipped]
Many thanks for the lesson O great master. The very words of wisdom
that fall from your lips are enough to render us mere mortals shriven
in your sight. We, your humble acolytes, realise that you are
omnicscient, and that therefore you know that we sometimes inherit data
structures that were generated by others, who do not have your
boundless wisdom, but it is not always possible to rewrite applications
end-to-end in order to conform to your Holy Writ.
However, could you not find it within your bountiful beneficence to
answer the question?
Edward
Tony Rogerson wrote:
Hi Edward,
Just ignore celko, he's an arrogant idiot with little real industrial
experience; he teaches standard sql and database design and that's it, but
that seems to have been picked up through doing a maths degree or something;
the old self-taught problem some people have....
Anyway, MC's answer should give you what you need.
Tony.
Thanks Tony - I'd sort of worked out that Celko had self-esteem issues!
However, MC's answer doesn't give me what I need - in fact, the more I
delve, the weirder it gets.
I tried re-scripting the database without the Defaults - since it's
going to be a read-only archive they're not important.
I hadn't noticed that there was something really weird about the INSERT
error:
IDENTITY_INSERT is already ON for table
'BSAVA_Archive_ Test_2006.dbo.G PS_CHAR'. Cannot perform SET operation
for table 'tblAdminUsers'
It references a database called "'BSAVA_Archive _Test_2006". However
this is NOT either of the two databases that I'm operating on! In
fact, it's an old test database so I dropped it.
Now I get the following error message:
"Could not find database ID 56. Database may not be activated yet or
may be in transition."
I'm completely baffled!
Edward
Could it be that you are prefixing the wrong database on the SET IDENTITY
INSERT ?
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }
Can you post the complete script you are trying to run.
Also, the output from PRINT @@VERSION
--
Tony Rogerson
SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant http://sqlserverfaq.com - free video tutorials
<te********@hot mail.comwrote in message
news:11******** *************@k 70g2000cwa.goog legroups.com...
>
Tony Rogerson wrote:
>Hi Edward,
Just ignore celko, he's an arrogant idiot with little real industrial experience; he teaches standard sql and database design and that's it, but that seems to have been picked up through doing a maths degree or something; the old self-taught problem some people have....
Anyway, MC's answer should give you what you need.
Tony.
Thanks Tony - I'd sort of worked out that Celko had self-esteem issues!
However, MC's answer doesn't give me what I need - in fact, the more I
delve, the weirder it gets.
I tried re-scripting the database without the Defaults - since it's
going to be a read-only archive they're not important.
I hadn't noticed that there was something really weird about the INSERT
error:
IDENTITY_INSERT is already ON for table
'BSAVA_Archive_ Test_2006.dbo.G PS_CHAR'. Cannot perform SET operation
for table 'tblAdminUsers'
It references a database called "'BSAVA_Archive _Test_2006". However
this is NOT either of the two databases that I'm operating on! In
fact, it's an old test database so I dropped it.
Now I get the following error message:
"Could not find database ID 56. Database may not be activated yet or
may be in transition."
I'm completely baffled!
Edward
Tony Rogerson wrote:
Could it be that you are prefixing the wrong database on the SET IDENTITY
INSERT ?
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }
Can you post the complete script you are trying to run.
Also, the output from PRINT @@VERSION
I can't post the whole script as it's more than 5,000 lines, though if
you're amenable I could email it to you and you could post your
findings back here?!?
The output from PRINT @@VERSION is:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Edward This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: For example John Smith |
last post by:
I'm doing a data transfer from Access to SQL Server, I wish to keep the
identity column (autonumber) values as all the data is already related. I
tried 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 would
have to issue a "set identity_insert on" before doing this. The SQL database
will have absolutely no data before the transfer routines are run.
|
by: Jon Combe |
last post by:
I have created the following test SQL code to illustrate a real
problem I have with some SQL code.
CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Jon Combe'
, 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Bill
Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
|
by: Douglas Buchanan |
last post by:
I am using the following code instead of a very lengthly select case
statement.
(I have a lot of lookup tables in a settings form that are selected
from a ListBox. The data adapters are given a similar name to the
table. Rather than making a long Select Case that could become
obsolete if lookup tables are added and the source table of the
ListBox is edited I came up with this code.)
This code works but of course it gives me build...
|
by: serge |
last post by:
I have a scenario where two tables are in a One-to-Many relationship
and I need to move the data from the Many table to the One table so
that it becomes a One-to-One relationship.
I need to salvage the records from the many table and without going
into detail, one of the reasons I can't do the opposite as
there are records in the ONE table that I need to keep even if they
don't have any child records in the MANY table.
Below I created...
|
by: Jim in Arizona |
last post by:
I'm going insane! I don't know if it's just that the .net 2.0 framework is
buggy or if it really is my code.
This is pretty hard to explain since I can't even begin to nail down why
this is happening.
I have two text boxes. One is for the ID number, which when postback occurs,
inserts into the related table as the foreign key.
The other text box is for notes that go along side the foreign key of the
the related table. Basically, the...
| |
by: Kevin O'Donovan |
last post by:
Hi,
Is anyone else experiencing this, or better yet, have a fix for it:
We have VS.Net 2003 from the MSDN Universal subscription installed on 3
workstations. We have about 10 or so projects ranging from a handful of
forms to 50 or so. On all but the smallest projects there will be some forms
or user controls which display this problem. What happens is that whenever
you are editing the code for the affected object, a number of things...
|
by: nicolas.bouchard |
last post by:
I am developing an integration process between two databases. One of
them is a SQL Server 2000 and the other is using MSDE 2000. The
integration process is done in C# (VS2003).
The main database is the SQL Server, the MSDE will contain a really
small subset of the data found on the main. To help diminish the amount
of time taken to develop an integration process between those
databases, the same structure are found on both side. The only...
|
by: Christopher Lusardi |
last post by:
How can I fix this? When I do the below I get the error message:
"Cannot insert explict value for identity column in table
'Employees' when IDENTITY_INSERT is set to OFF."
To get this message, I click the Add button to add a new row to the
database, and then I click the Update button to save the new database
to the external memory.
The methods I used are below.
|
by: TamaThps |
last post by:
Hi, I'm using visual studio 2008 and normally when I get an error it shows what line it is on and which file etc. The error I'm getting I don't know how to solve or even what the problem is. This is the whole output when I try to run the program.
"1>------ Build started: Project: corysid, Configuration: Debug Win32 ------
1>Compiling...
1>implementation.cpp
1>Linking...
1>actualmain.obj : error LNK2019: unresolved external symbol "public:...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |