SEO

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.

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-...SQLServer7.asp

Is an account of my upsizing experiences.

HTH
--

Nath
www.cooldigitec.co.uk
(Email mangling obvious)


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

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

OK Thanks.

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

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

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

Pretty much real Pavel

For example John Smith wrote:
>
> 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.

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@microsoft.com> wrote:
>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.


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

"Pavel Romashkin" <pavel_romashkin@hotmail.com> wrote in message
news:3F4F70C0.E49D830E@hotmail.com...
> I'd hope that "someone@microsoft.com" should know their own software.

It's a big company.

> Pretty much real Pavel

A real what?

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 'zstblApplicationFunction' 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"."zstblApplicationFunction" ON
go
exec sp_executesql N'INSERT INTO "dbo"."zstblApplicationFunction"
("ApplicationFunctionID","SortOrder","ParentApplic ationFunctionID","Applicat
ionFunctionName","DisplayName","Hyperlink","FormHe lpID","IsMenuItem","IsSubM
enuItem","IsEnforced","SysIsInsert","SysIsSelect", "SysIsUpdate","SysIsDelete
","SysIsExecute") VALUES
(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P1 2,@P13,@P14,@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.

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@microsoft.com> wrote in message
news:3f533f94$0$10781$afc38c87@news.easynet.co.uk. ..
> 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 'zstblApplicationFunction'
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"."zstblApplicationFunction" ON
> go
> exec sp_executesql N'INSERT INTO "dbo"."zstblApplicationFunction"
>
("ApplicationFunctionID","SortOrder","ParentApplic ationFunctionID","Applicat
>
ionFunctionName","DisplayName","Hyperlink","FormHe lpID","IsMenuItem","IsSubM
>
enuItem","IsEnforced","SysIsInsert","SysIsSelect", "SysIsUpdate","SysIsDelete
> ","SysIsExecute") VALUES
> (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P1 2,@P13,@P14,@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" <jbellnewsposts@hotmail.com> wrote in message
news:3f5343ad$0$250$ed9e5944@reading.news.pipex.ne t...
> Hi
>
> The problems is the GO after SET IDENTITY_INSERT.. ON
>
> I suggest you do this in a stored procedure.

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].[zstblApplicationFunction_zstblApplicationFunction_ FK1]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[zstblApplicationFunction] DROP CONSTRAINT
zstblApplicationFunction_zstblApplicationFunction_ FK1
GO

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

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

CREATE TABLE [dbo].[zstblApplicationFunction] (
[ApplicationFunctionID] [int] IDENTITY (1, 1) NOT NULL ,
[SortOrder] [int] NULL ,
[ParentApplicationFunctionID] [int] NULL ,
[ApplicationFunctionName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DisplayName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Hyperlink] [varchar] (512) COLLATE SQL_Latin1_General_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].[zstblApplicationFunction] WITH NOCHECK ADD
CONSTRAINT [zstblApplicationFunction_PK] PRIMARY KEY CLUSTERED
(
[ApplicationFunctionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[zstblApplicationFunction] ADD
CONSTRAINT [PrimaryKey_UC1] UNIQUE NONCLUSTERED
(
[ApplicationFunctionName]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IDX_ParentApplicationFunctionID] ON
[dbo].[zstblApplicationFunction]([ParentApplicationFunctionID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [zstblApplicationFunction_AK2] ON
[dbo].[zstblApplicationFunction]([DisplayName]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

ALTER TABLE [dbo].[zstblApplicationFunction] ADD
CONSTRAINT [zstblApplicationFunction_zstblApplicationFunction_ FK1] FOREIGN
KEY
(
[ParentApplicationFunctionID]
) REFERENCES [dbo].[zstblApplicationFunction] (
[ApplicationFunctionID]
),
CONSTRAINT [zstblFormHelp_zstblApplicationFunction_FK1] FOREIGN KEY
(
[FormHelpID]
) REFERENCES [dbo].[zstblFormHelp] (
[FormHelpID]
)
GO

For example John Smith (someone@microsoft.com) writes:
> 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 'zstblApplicationFunction'
> when IDENTITY_INSERT is set to OFF. (ODBC.QueryDef)Error 3146 -
> ODBC--call failed. (DAO.QueryDef)
>
> In Profiler, the SQL trace shows:

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/techinf...2000/books.asp