Indentity Insert

[For example John Smith]

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.

[Nath Wilson]

Re: Indentity Insert

As long as there arent any duplicated values in what you are trying to
insert into the identity column you should have no problems.

You could also use Access' upsizing wizard to move the tables to SQL
server. This feature is available in A2k, I'm not sure about versions
below that.

http://www.cooldigitec.co.uk/njw/15-AccessToSQLServer7.asp

Is an account of my upsizing experiences.

HTH
--

Nath

https://www.cooldigitec.co.uk

(Email mangling obvious)


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[For example John Smith]

Re: Indentity Insert

"Nath Wilson" <chanester@a0l. com> wrote in message
news:3f4f61a6$0 $62079$75868355 @news.frii.net. ..[color=blue]
> As long as there arent any duplicated values in what you are trying to
> insert into the identity column you should have no problems.[/color]

OK Thanks.
[color=blue]
> You could also use Access' upsizing wizard to move the tables to SQL
> server. This feature is available in A2k, I'm not sure about versions
> below that.[/color]

It's an app rewrite, the tables are already there and slightly different.

[Pavel Romashkin]

Re: Indentity Insert

I'd hope that "someone@micros oft.com" should know their own software.

Pretty much real Pavel

For example John Smith wrote:[color=blue]
>
> 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.[/color]

[Chuck Grimsby]

Re: Indentity Insert


No, you can't rely on the Identity field keeping the same number as
the autonumber field.


On Fri, 29 Aug 2003 12:25:28 +0100, "For example John Smith"
<someone@micros oft.com> wrote:[color=blue]
>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.[/color]


--
You're Not Losing More Hair, You're Gaining More Scalp.

[For example John Smith]

Re: Indentity Insert

"Pavel Romashkin" <pavel_romashki n@hotmail.com> wrote in message
news:3F4F70C0.E 49D830E@hotmail .com...[color=blue]
> I'd hope that "someone@micros oft.com" should know their own software.[/color]

It's a big company.
[color=blue]
> Pretty much real Pavel[/color]

A real what?

[For example John Smith]

Re: Indentity Insert

An update on this, it's now failing with the message:

Error 544 - [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
explicit value for identity column in table 'zstblApplicati onFunction' when
IDENTITY_INSERT is set to OFF. (ODBC.QueryDef) Error 3146 - ODBC--call
failed. (DAO.QueryDef)

In Profiler, the SQL trace shows:

<---------------------------------------------------
SET IDENTITY_INSERT "dbo"."zstblApp licationFunctio n" ON
go
exec sp_executesql N'INSERT INTO "dbo"."zstblApp licationFunctio n"
("ApplicationFu nctionID","Sort Order","ParentA pplicationFunct ionID","Applica t
ionFunctionName ","DisplayName" ,"Hyperlink","F ormHelpID","IsM enuItem","IsSub M
enuItem","IsEnf orced","SysIsIn sert","SysIsSel ect","SysIsUpda te","SysIsDelet e
","SysIsExecute ") VALUES
(@P1,@P2,@P3,@P 4,@P5,@P6,@P7,@ P8,@P9,@P10,@P1 1,@P12,@P13,@P1 4,@P15)', N'@P1
int,@P2 int,@P3 int,@P4 varchar(50),@P5 varchar(50),@P6 varchar(8000),@ P7
int,@P8 bit,@P9 bit,@P10 bit,@P11 bit,@P12 bit,@P13 bit,@P14 bit,@P15 bit',
1, 3, NULL, 'SECURITYMENU', 'Security', NULL, NULL, 1, 0, 0, 0, 1, 0, 0, 0
go
IF @@TRANCOUNT > 0 ROLLBACK TRAN
go
---------------------------------------------------->

If I copy and paste that into Query Analyser it inserts the record. When
Access issues these commands to the server it fails. Odd.

[John Bell]

Re: Indentity Insert

Hi

The problems is the GO after SET IDENTITY_INSERT .. ON

I suggest you do this in a stored procedure.

John

"For example John Smith" <someone@micros oft.com> wrote in message
news:3f533f94$0 $10781$afc38c87 @news.easynet.c o.uk...[color=blue]
> An update on this, it's now failing with the message:
>
> Error 544 - [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
> explicit value for identity column in table 'zstblApplicati onFunction'[/color]
when[color=blue]
> IDENTITY_INSERT is set to OFF. (ODBC.QueryDef) Error 3146 - ODBC--call
> failed. (DAO.QueryDef)
>
> In Profiler, the SQL trace shows:
>
> <---------------------------------------------------
> SET IDENTITY_INSERT "dbo"."zstblApp licationFunctio n" ON
> go
> exec sp_executesql N'INSERT INTO "dbo"."zstblApp licationFunctio n"
>[/color]
("ApplicationFu nctionID","Sort Order","ParentA pplicationFunct ionID","Applica t[color=blue]
>[/color]
ionFunctionName ","DisplayName" ,"Hyperlink","F ormHelpID","IsM enuItem","IsSub M[color=blue]
>[/color]
enuItem","IsEnf orced","SysIsIn sert","SysIsSel ect","SysIsUpda te","SysIsDelet e[color=blue]
> ","SysIsExecute ") VALUES
> (@P1,@P2,@P3,@P 4,@P5,@P6,@P7,@ P8,@P9,@P10,@P1 1,@P12,@P13,@P1 4,@P15)',[/color]
N'@P1[color=blue]
> int,@P2 int,@P3 int,@P4 varchar(50),@P5 varchar(50),@P6 varchar(8000),@ P7
> int,@P8 bit,@P9 bit,@P10 bit,@P11 bit,@P12 bit,@P13 bit,@P14 bit,@P15[/color]
bit',[color=blue]
> 1, 3, NULL, 'SECURITYMENU', 'Security', NULL, NULL, 1, 0, 0, 0, 1, 0, 0, 0
> go
> IF @@TRANCOUNT > 0 ROLLBACK TRAN
> go
> ---------------------------------------------------->
>
> If I copy and paste that into Query Analyser it inserts the record. When
> Access issues these commands to the server it fails. Odd.
>
>[/color]

[For example John Smith]

Re: Indentity Insert

"John Bell" <jbellnewsposts @hotmail.com> wrote in message
news:3f5343ad$0 $250$ed9e5944@r eading.news.pip ex.net...[color=blue]
> Hi
>
> The problems is the GO after SET IDENTITY_INSERT .. ON
>
> I suggest you do this in a stored procedure.[/color]

I got another table with an Identity column and this works fine. The GO
doesn't seem to be a problem there (I think they're just put in by Profiler
anyway for the purpose of pasting into QA) and it works OK from QA with the
GOs in place..

A Stored procedure would be difficult as the data is coming from an Access
database although in the case of this particular table it comes from another
SQL database but I did try importing that one to Access and then running the
append query from the Access database. I can look at doing a SP for the ones
that do come from another SQL database (these are new ones added to the
application recently in the re-write) but since I'm on like table 3 out of
60 I'm not that confident the rest will go as smoothly as the first 2.

I'm pasting the troublesome table script below, this may provide a clue,
could be the self join FK that doesn't exist on any table I've transferred
thus far.

--- table script
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[zstblApplicatio nFunction_zstbl ApplicationFunc tion_FK1]')
and OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[zstblApplicatio nFunction] DROP CONSTRAINT
zstblApplicatio nFunction_zstbl ApplicationFunc tion_FK1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[zstblApplicatio nFunction_zstbl Permission_FK1]') and
OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[zstblPermission] DROP CONSTRAINT
zstblApplicatio nFunction_zstbl Permission_FK1
GO

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

CREATE TABLE [dbo].[zstblApplicatio nFunction] (
[ApplicationFunc tionID] [int] IDENTITY (1, 1) NOT NULL ,
[SortOrder] [int] NULL ,
[ParentApplicati onFunctionID] [int] NULL ,
[ApplicationFunc tionName] [varchar] (50) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[DisplayName] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL
,
[Hyperlink] [varchar] (512) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[FormHelpID] [int] NULL ,
[IsMenuItem] [bit] NOT NULL ,
[IsSubMenuItem] [bit] NULL ,
[IsEnforced] [bit] NOT NULL ,
[SysIsInsert] [bit] NOT NULL ,
[SysIsSelect] [bit] NOT NULL ,
[SysIsUpdate] [bit] NOT NULL ,
[SysIsDelete] [bit] NOT NULL ,
[SysIsExecute] [bit] NOT NULL ,
[SysTimeStamp] [timestamp] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[zstblApplicatio nFunction] WITH NOCHECK ADD
CONSTRAINT [zstblApplicatio nFunction_PK] PRIMARY KEY CLUSTERED
(
[ApplicationFunc tionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[zstblApplicatio nFunction] ADD
CONSTRAINT [PrimaryKey_UC1] UNIQUE NONCLUSTERED
(
[ApplicationFunc tionName]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IDX_ParentAppli cationFunctionI D] ON
[dbo].[zstblApplicatio nFunction]([ParentApplicati onFunctionID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [zstblApplicatio nFunction_AK2] ON
[dbo].[zstblApplicatio nFunction]([DisplayName]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

ALTER TABLE [dbo].[zstblApplicatio nFunction] ADD
CONSTRAINT [zstblApplicatio nFunction_zstbl ApplicationFunc tion_FK1] FOREIGN
KEY
(
[ParentApplicati onFunctionID]
) REFERENCES [dbo].[zstblApplicatio nFunction] (
[ApplicationFunc tionID]
),
CONSTRAINT [zstblFormHelp_z stblApplication Function_FK1] FOREIGN KEY
(
[FormHelpID]
) REFERENCES [dbo].[zstblFormHelp] (
[FormHelpID]
)
GO

[Erland Sommarskog]

Re: Indentity Insert

For example John Smith (someone@micros oft.com) writes:[color=blue]
> An update on this, it's now failing with the message:
>
> Error 544 - [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
> explicit value for identity column in table 'zstblApplicati onFunction'
> when IDENTITY_INSERT is set to OFF. (ODBC.QueryDef) Error 3146 -
> ODBC--call failed. (DAO.QueryDef)
>
> In Profiler, the SQL trace shows:[/color]

Hm, this is certainly a desprate idea, but check that you have the
same value for SPID for the commands.

Or, um, wait there is one more possibility. Under which user do you
run this from Access? And from Query Analyzer? SET IDENTITY_INSERT
is not permitted for plain users. Of course you should get an error
message for that too, and maybe you are if you sift through the
error messages.

And, oh, it would be appreciated if you fixed your name and e-mail
address.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[John Bell]

Re: Indentity Insert

Hi

Sorry, this is not transaction specific, but you should still put this in a
SP to solve the problems!

John

"For example John Smith" <someone@micros oft.com> wrote in message
news:3f536684$0 $12653$afc38c87 @news.easynet.c o.uk...[color=blue]
> "John Bell" <jbellnewsposts @hotmail.com> wrote in message
> news:3f5343ad$0 $250$ed9e5944@r eading.news.pip ex.net...[color=green]
> > Hi
> >
> > The problems is the GO after SET IDENTITY_INSERT .. ON
> >
> > I suggest you do this in a stored procedure.[/color]
>
> I got another table with an Identity column and this works fine. The GO
> doesn't seem to be a problem there (I think they're just put in by[/color]
Profiler[color=blue]
> anyway for the purpose of pasting into QA) and it works OK from QA with[/color]
the[color=blue]
> GOs in place..
>
> A Stored procedure would be difficult as the data is coming from an Access
> database although in the case of this particular table it comes from[/color]
another[color=blue]
> SQL database but I did try importing that one to Access and then running[/color]
the[color=blue]
> append query from the Access database. I can look at doing a SP for the[/color]
ones[color=blue]
> that do come from another SQL database (these are new ones added to the
> application recently in the re-write) but since I'm on like table 3 out of
> 60 I'm not that confident the rest will go as smoothly as the first 2.
>
> I'm pasting the troublesome table script below, this may provide a clue,
> could be the self join FK that doesn't exist on any table I've transferred
> thus far.
>
> --- table script
> if exists (select * from dbo.sysobjects where id =
>[/color]
object_id(N'[dbo].[zstblApplicatio nFunction_zstbl ApplicationFunc tion_FK1]')[color=blue]
> and OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
> ALTER TABLE [dbo].[zstblApplicatio nFunction] DROP CONSTRAINT
> zstblApplicatio nFunction_zstbl ApplicationFunc tion_FK1
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[zstblApplicatio nFunction_zstbl Permission_FK1]') and
> OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
> ALTER TABLE [dbo].[zstblPermission] DROP CONSTRAINT
> zstblApplicatio nFunction_zstbl Permission_FK1
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[zstblApplicatio nFunction]') and OBJECTPROPERTY( id,
> N'IsUserTable') = 1)
> drop table [dbo].[zstblApplicatio nFunction]
> GO
>
> CREATE TABLE [dbo].[zstblApplicatio nFunction] (
> [ApplicationFunc tionID] [int] IDENTITY (1, 1) NOT NULL ,
> [SortOrder] [int] NULL ,
> [ParentApplicati onFunctionID] [int] NULL ,
> [ApplicationFunc tionName] [varchar] (50) COLLATE
> SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
> [DisplayName] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT[/color]
NULL[color=blue]
> ,
> [Hyperlink] [varchar] (512) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
> [FormHelpID] [int] NULL ,
> [IsMenuItem] [bit] NOT NULL ,
> [IsSubMenuItem] [bit] NULL ,
> [IsEnforced] [bit] NOT NULL ,
> [SysIsInsert] [bit] NOT NULL ,
> [SysIsSelect] [bit] NOT NULL ,
> [SysIsUpdate] [bit] NOT NULL ,
> [SysIsDelete] [bit] NOT NULL ,
> [SysIsExecute] [bit] NOT NULL ,
> [SysTimeStamp] [timestamp] NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[zstblApplicatio nFunction] WITH NOCHECK ADD
> CONSTRAINT [zstblApplicatio nFunction_PK] PRIMARY KEY CLUSTERED
> (
> [ApplicationFunc tionID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[zstblApplicatio nFunction] ADD
> CONSTRAINT [PrimaryKey_UC1] UNIQUE NONCLUSTERED
> (
> [ApplicationFunc tionName]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [IDX_ParentAppli cationFunctionI D] ON
> [dbo].[zstblApplicatio nFunction]([ParentApplicati onFunctionID]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [zstblApplicatio nFunction_AK2] ON
> [dbo].[zstblApplicatio nFunction]([DisplayName]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[zstblApplicatio nFunction] ADD
> CONSTRAINT [zstblApplicatio nFunction_zstbl ApplicationFunc tion_FK1][/color]
FOREIGN[color=blue]
> KEY
> (
> [ParentApplicati onFunctionID]
> ) REFERENCES [dbo].[zstblApplicatio nFunction] (
> [ApplicationFunc tionID]
> ),
> CONSTRAINT [zstblFormHelp_z stblApplication Function_FK1] FOREIGN KEY
> (
> [FormHelpID]
> ) REFERENCES [dbo].[zstblFormHelp] (
> [FormHelpID]
> )
> GO
>
>
>[/color]

[Trevor Best]

Re: Indentity Insert

On Mon, 1 Sep 2003 20:11:40 +0000 (UTC) in comp.databases. ms-access,
Erland Sommarskog <sommar@algonet .se> wrote:
[color=blue]
>And, oh, it would be appreciated if you fixed your name and e-mail
>address.[/color]

I bet it's not the first time someone followed the instructions in
Outlook Express to the letter <g>

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)

[For example John Smith]

Re: Indentity Insert

"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns93E9E18 2DF083Yazorman@ 127.0.0.1...
[color=blue]
> Hm, this is certainly a desprate idea, but check that you have the
> same value for SPID for the commands.[/color]

Bang! Hit the nail squarely on the head, thanks for that pointer. It appears
that Access does issue a SET IDENTITY_INSERT ON command prior to the insert
but doesn't turn it off again afterward. My own passthrough query to turn it
off is running under a different SPID.

So it appears to be an Access bug.
[color=blue]
> Or, um, wait there is one more possibility. Under which user do you
> run this from Access? And from Query Analyzer? SET IDENTITY_INSERT
> is not permitted for plain users. Of course you should get an error
> message for that too, and maybe you are if you sift through the
> error messages.[/color]

sa. I did find an error message to that effect, level 16 but it appears
Access ignores anything lower than 18.
[color=blue]
> And, oh, it would be appreciated if you fixed your name and e-mail
> address.[/color]

I don't wish to dwell on this but...
The fake email is clear, I don't want spam nor do I want to enter into email
correspondance, I've answered as many if not more questions in these groups
than I've asked and have fallen into the trap of taking problems into email
and then the other party expecting me to be their personal programming slave
or I have to ignore or tell the person outright that they're overstepping
the bounds of my generosity, either way it can end in tears (for them, not
me, you see it's for eveyone else's protection <g>). As for the name, one
could use an obvious fake, a fake that looks real or a real name (or a real
one that looks like an obvious fake), you still wouldn't know me from Adam.

Some people post anonymously because they know they are acting the fool and
even use remaillers to hide all trace (I don't BTW), others perhaps,
hypothetically (nudge wink) using a company account don't want bosses or
clients to see them asking questions on a public forum.

[Erland Sommarskog]

Re: Indentity Insert

For example John Smith (someone@micros oft.com) writes:[color=blue]
> I don't wish to dwell on this but...
> The fake email is clear, I don't want spam nor do I want to enter into
> email correspondance, I've answered as many if not more questions in
> these groups than I've asked and have fallen into the trap of taking
> problems into email and then the other party expecting me to be their
> personal programming slave or I have to ignore or tell the person
> outright that they're overstepping the bounds of my generosity, either
> way it can end in tears (for them, not me, you see it's for eveyone
> else's protection <g>). As for the name, one could use an obvious fake,
> a fake that looks real or a real name (or a real one that looks like an
> obvious fake), you still wouldn't know me from Adam.[/color]

I also answer lot of questions, and sometimes get them in the mail
too, but I have never found it to be a problem with people asking for
too much.

A lot of people these days uses fake names and addresses, which in my
opinion is a pity, because being a long-timer in news I recall the days,
when replying by mail was the natural thing when the discussion was not
really of general interest. This is why I insist on using my real name and
mail address in public.

But even if you use a fake address, there are fake address which are less
appropriate than others. Unless you are a Microsoft emplyoess, I don't
you should use an fake address their domain.
--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Chuck Grimsby]

Re: Indentity Insert

On Tue, 2 Sep 2003 20:44:17 +0000 (UTC), Erland Sommarskog
<sommar@algonet .se> wrote:[color=blue]
>But even if you use a fake address, there are fake address which are less
>appropriate than others. Unless you are a Microsoft emplyoess, I don't
>you should use an fake address their domain.[/color]

Or at least, add ".invalid" to the end of the email address, which is
the (RFC) accepted method for saying "this email address is fake".


--
You Will Become Rich And Famous Unless You Don't.