Can't add rows in query since copying database from MSSQL2000 to 2

[huddie]

Can't add rows in query since copying database from MSSQL2000 to 2005

Hi,

I have an MSSQL database that I created and I access through an MS Access
2007 project as a front-end. Since moving the database from an MSSQL2000
server to MSSQL2005 I can no longer use my TapesAndJobs query to add new
rows. I can click on New (Blank) Record button and it shows a new line for a
new row to be completed. However, instead of displaying '(New)' in the
identity column (which is auto numbered and the first column) and the default
values where applicable in other columns, allowing me to complete the
details, it leaves the identity column blank and, thus, I can't add the row
after completing the other columns as it displays the following error:

"Invalid input parameter values. Check the status values for details."

Being an autonumbered identity column, I can't even fill it in manually.
Adding rows using this query worked fine before copying the SQL database from
MSSQL2000 to MSSQL2005. Even if I point the Access project back to the old
SQL2000 server it works fine again. I've tried copying the database from
MSSQL2000 to MSSQL2005 again to no avail. I've looked at database settings
on both copies and the *appear* to be the same. I've made my self an owner
in the user mappings for both the old and new database copies. The old
MSSQL2000 server is being decommissioned so I have to get this workinig.

It's worth pointing out here that I'm not a DBA or an expert in Transact-SQL
by any stretch of the imagination! However, I do believe this is a problem
relates to the database copy I did. I also believe that someone with more
MSSQL and Access experience than me could point me to the answer.

Here are the details:

* MSSQL2005 running on a 64bit cluster with Windows Server 2003
* MSSQL2000 running on 32bit Windows 2000 Server
* Access 2007 SP2 running on Windows Vista Business 32bit
* Access project in 2002 - 2003 format.

If anyone could help it would be greatly appreciated. Note that I've also
posted this to the following newsgroups:

microsoft.public.sqlserver.integrationsvcs;
microsoft.public.sqlserver.programming; microsoft.public.access;
microsoft.public.sqlserver.server

Thanks.

 

[znaika]

Hi Paul,

There is no Access project 2007.
It is only Access 2007 database application.

Create a simple table with identity integer column in SQL Server 2005.
Don't forget to add a primary key on identity column.
Link that table from sql server to Access 2007.
Add a test record from within Access 2007.
There should not be any problems.
You should see * in Access linked table when adding a new record with
primary key on that table.

Back up SQL Server 2000 database and restore on SQL Server 2005.
Make sure you tables have primary keys on it.

If it worked on SQL Server 2000 it will work on SQL Server 2005.
However, I fixed many Access applications that were misconfigured in one or
other way.

It is better to ask a software consultant to fix your Access application.

 

[huddie]

I mean a .ADP project. The file type description is Microsoft Office Access
Project (.adp).

 

[Norman Yuan]

It is very likely the ID/primary key column of the table is not set as
Identity. Do double check by opening the table in design mode. It is very
poosible when you copy a table from one SQL Server to another, the
ID/primary key column bacomes a column of "integer" type, instead of
Identity, depending on how the "copying" is done. As the other reply
suggested, backup and restore would be better approach that would keep the
database as is.

 

[huddie]

Thanks Norman.

I checked using MSSQL Management Studio. Here's the relevant specs for the
identity field:

Name: dbo.TapeJobs.No
Allow Nulls: No (greyed out)
Data Type: int (I tried changing this to uniqueidentifier and received an
error saying the conversion wasn't possible. I don't think this is necessary
anyway, right ?)
Identity specification:
Is identity: Yes
Identity increment: 1
Identity seed: 1
Indexable: yes (greyed out)

The above is from a table called dbo.TapeJobs. The funny thing is I can go
into the table and the dbo.TapeJobs.No field automatically increments
properly when I create a new record / row there. It's just that it doesn't
work in the dbo.TapesAndJobs query even though this is the main underlying
table for this query. It's strange. It worked fine on MSSQL2000.

By the way, I was a little inaccurate when I said that I copied the
database. Actually the MSSQL integration services couldn't do the copy, so I
used our backup product to back it up from MSSQL2000 and restore it to the
MSSQL2005 server. Everything else about the database looks fine except for
this one issue which really makes it very awkward to do data entry.

Can you help ?

 

[Norman Yuan]

When you say "query", you mean it is a view or it is a stored procedure?

 

[huddie]

Norman,

Sorry about my bad terminology - again, I'm not a DBA and I'm pretty
inexperienced ;-) It is a view, not a stored procedure.

 

[huddie]

Norman, I answered your post, can you help please ?

--
--
Paul Anderson

Norman,

Sorry about my bad terminology - again, I'm not a DBA and I'm pretty
inexperienced ;-) It is a view, not a stored procedure.

 

[huddie]

1. TapesAndJobs Vie
-----------------------------------------------------------------------------------------------------
USE [NICISTapeTrackerSQL]
GO
/****** Object: View [dbo].[TapesAndJobs] Script Date: 09/16/2009
10:35:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[TapesAndJobs]
AS
SELECT dbo.TapeJobs.No, dbo.TapeJobs.JobNo, dbo.TapeJobs.TapeNo,
dbo.Jobs.No AS Expr1, dbo.Tapes.No AS Expr2, dbo.Tapes.Status,
dbo.Tapes.Pool,
dbo.Tapes.Location, dbo.Jobs.Date,
dbo.Jobs.Successful, dbo.Jobs.RetentionRule,
dbo.RetentionRules.RetentionPeriod,
dbo.RetentionRules.RetentionCycles
FROM dbo.TapeJobs LEFT OUTER JOIN
dbo.Tapes ON dbo.TapeJobs.TapeNo = dbo.Tapes.No LEFT
OUTER JOIN
dbo.RetentionRules INNER JOIN
dbo.Jobs ON dbo.RetentionRules.No =
dbo.Jobs.RetentionRule ON dbo.TapeJobs.JobNo = dbo.Jobs.N
-----------------------------------------------------------------------------------------------------
2.1 Jobs Tabl
-----------------------------------------------------------------------------------------------------

<- I've had to remove this because it's too big and I get the following
USENET error: Your post is too long. Please reduce the amount of text. (Limit
= 30000, Current = 96763) ->

-----------------------------------------------------------------------------------------------------
2.2 Tapes Tabl
-----------------------------------------------------------------------------------------------------

<- I've had to remove this because it's too big and I get the following
USENET error: Your post is too long. Please reduce the amount of text. (Limit
= 30000, Current = 96763) ->

-----------------------------------------------------------------------------------------------------
2.3 TapeJobs Tabl
-----------------------------------------------------------------------------------------------------

USE [NICISTapeTrackerSQL]
GO
/****** Object: Table [dbo].[TapeJobs] Script Date: 09/16/2009 10:46:25
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TapeJobs](
[No] [int] IDENTITY(1,1) NOT NULL,
[JobNo] [int] NOT NULL,
[TapeNo] [int] NOT NULL,
CONSTRAINT [PK_TapeJobs] PRIMARY KEY CLUSTERED
(
[No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=1 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=765 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'109' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AllowValueListEdits',
@value=False , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnCount', @value=N'4' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHeads', @value=N'-1' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1080 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidths',
@value=N'1701.000;1701.000;1701.000;1701.000' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'111' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_LimitToList', @value=N'-1' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ListItemsEditForm', @value=NULL ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ListRows', @value=N'20' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_RowSource', @value=N'dbo.Jobs' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ShowOnlyRowSourceValues',
@value=False , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AllowValueListEdits',
@value=False , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnCount', @value=N'4' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHeads', @value=N'-1' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1140 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidths',
@value=N'1701.000;1701.000;1701.000;1701.000' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'111' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_LimitToList', @value=N'-1' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ListItemsEditForm', @value=NULL ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ListRows', @value=N'20' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_RowSource', @value=N'dbo.Tapes' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ShowOnlyRowSourceValues',
@value=False , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DefaultView', @value=0x02 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Filter', @value=NULL ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_FilterOnLoad', @value=False ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_HideNewField', @value=False ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderBy', @value=NULL ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOn', @value=False ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOnLoad', @value=True ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Orientation', @value=0x00 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TableMaxRecords', @value=10000 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TotalsRow', @value=False ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs'
GO
ALTER TABLE [dbo].[TapeJobs] WITH CHECK ADD CONSTRAINT [FK_TapeJobs_Jobs]
FOREIGN KEY([JobNo])
REFERENCES [dbo].[Jobs] ([No])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[TapeJobs] CHECK CONSTRAINT [FK_TapeJobs_Jobs]
GO
ALTER TABLE [dbo].[TapeJobs] WITH NOCHECK ADD CONSTRAINT
[FK_TapeJobs_Tapes] FOREIGN KEY([TapeNo])
REFERENCES [dbo].[Tapes] ([No])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[TapeJobs] CHECK CONSTRAINT [FK_TapeJobs_Tapes]

-----------------------------------------------------------------------------------------------------
2.4 RetentionRules Table
-----------------------------------------------------------------------------------------------------

<- I've had to remove this because it's too big and I get the following
USENET error: Your post is too long. Please reduce the amount of text. (Limit
= 30000, Current = 96763) ->

-----------------------------------------------------------------------------------------------------
3. Trace Results - Filtered by all TSQL statements only
-----------------------------------------------------------------------------------------------------

I've never run this tool before and am not particularly sure whether this
satisfies your request, but here goes:

<- I've had to remove the trace results from here because they are too big
and I get the following USENET error: Your post is too long. Please reduce
the amount of text. (Limit = 30000, Current = 96763) ->

SET NO_BROWSETABLE OFF
go
EXEC sp_fkeys NULL, NULL, NULL, 'RetentionRules', 'dbo', 'NICISTapeTrackerSQL'
go
EXEC sp_fkeys NULL, NULL, NULL, 'Tapes', 'dbo', 'NICISTapeTrackerSQL'
go
EXEC sp_fkeys NULL, NULL, NULL, 'Jobs', 'dbo', 'NICISTapeTrackerSQL'
go
EXEC sp_fkeys NULL, NULL, NULL, 'TapeJobs', 'dbo', 'NICISTapeTrackerSQL'
go
SET ROWCOUNT 10000 SET NO_BROWSETABLE ON
go
SET ROWCOUNT 0 SET NO_BROWSETABLE OFF
go
EXEC sp_MShelpcolumns N'dbo.TapeJobs' , NULL, 'id', 1
go
EXEC sp_MShelpcolumns N'dbo.Jobs' , NULL, 'id', 1
go
EXEC sp_MShelpcolumns N'dbo.Tapes' , NULL, 'id', 1
go
EXEC sp_MShelpcolumns N'dbo.RetentionRules' , NULL, 'id', 1
go
SELECT (3)
go
SELECT (7)
go
SELECT (1)
go
SELECT (getdate())
go
SELECT * FROM "dbo"."Tapes"
go
SET NO_BROWSETABLE ON
go
SELECT *, sql_variant_property(value, 'basetype') AS type FROM
::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'Tapes',N'column',NULL) ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC
go
SELECT (getdate()) AS Column1, (3) AS Column2, (7) AS Column3, (1) AS
Column4
go
SET ROWCOUNT 10000
go
SET FMTONLY ON select "No" from "NICISTapeTrackerSQL"."dbo"."Tapes" WHERE
1=2 SET FMTONLY OFF
go
SET ROWCOUNT 0 SET NO_BROWSETABLE OFF
go
SET FMTONLY ON select "No","JobNo","TapeNo" from
"NICISTapeTrackerSQL"."dbo"."TapeJobs" WHERE 1=2 SET FMTONLY OFF
go
SET NO_BROWSETABLE ON
go



Please keep in mind that I am not a DBA and neither I nor my organisation
have access to one. I apologise therefore for the poor presentation here,
but I'm doing the best I can. I really appreciate your help on this and look
forward to hearing back from you. If you need to use e-mail for larger
quantities of information, such as the full trace, e-mail me at
paul.anderson@[email protected] (take the NOSP@M bit out).

 

[huddie]

Russell,

Thanks for your response and sorry for the delay getting back to you. I'm
just back from holiday. We will need to find some other media for
transferring data such as logs and tables, as USENET is limiting the amount
of text that can be posted. That is why I was unable to supply all the
information you requested. I've provided my e-mail address in the previous
post. Please can you suggest an alternative if you don't want to use e-mail
as USENET is totally inadequate.

I think the best thing to do here would be for me to send you as much as
possible so you can obtain what you need to look at. Sorry again, but, as
mentioned before, I'm not a DBA and do not have access to one :-(

Look forward to hearing from you again.

Thanks.


--
--
Paul Anderson

Paul,

As I understand it, you are using either INSERT or UPDATE statements against
your NICISTapeTrackerSQL view. Is that correct? The rules for updateable
views have been refined to avoid certain errors with views that refer to
multiple tables, as your view does. Perhaps that is what is getting you.

These are the topics "Modifying Data Through a View" for SQL Server 2000 and
2005:
http://msdn.microsoft.com/en-us/library/aa933146(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/ms180800(SQL.90).aspx

You will see that they are not identical, but both point to using an INSTEAD
OF trigger to pass through the updates you want to the underlying tables
that make up the view. This might be what you need to look into.

With regard to the code, thank you for what you provided, but much is still
missing. You should be able to provide the scripts for the other tables.
Just leave out all of the "EXEC sys.sp_addextendedproperty" from the reply.
(I have no use for that information, which is just documentation.)

Also, it would be very helpful to see the actual INSERT statement that
Access is trying to create.

RLF

1. TapesAndJobs View
-----------------------------------------------------------------------------------------------------
USE [NICISTapeTrackerSQL]
GO
/****** Object: View [dbo].[TapesAndJobs] Script Date: 09/16/2009
10:35:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[TapesAndJobs]
AS
SELECT dbo.TapeJobs.No, dbo.TapeJobs.JobNo, dbo.TapeJobs.TapeNo,
dbo.Jobs.No AS Expr1, dbo.Tapes.No AS Expr2, dbo.Tapes.Status,
dbo.Tapes.Pool,
dbo.Tapes.Location, dbo.Jobs.Date,
dbo.Jobs.Successful, dbo.Jobs.RetentionRule,
dbo.RetentionRules.RetentionPeriod,
dbo.RetentionRules.RetentionCycles
FROM dbo.TapeJobs LEFT OUTER JOIN
dbo.Tapes ON dbo.TapeJobs.TapeNo = dbo.Tapes.No LEFT
OUTER JOIN
dbo.RetentionRules INNER JOIN
dbo.Jobs ON dbo.RetentionRules.No =
dbo.Jobs.RetentionRule ON dbo.TapeJobs.JobNo = dbo.Jobs.No
-----------------------------------------------------------------------------------------------------
2.1 Jobs Table
-----------------------------------------------------------------------------------------------------

<- I've had to remove this because it's too big and I get the following
USENET error: Your post is too long. Please reduce the amount of text.
(Limit
= 30000, Current = 96763) ->

-----------------------------------------------------------------------------------------------------
2.2 Tapes Table
-----------------------------------------------------------------------------------------------------

<- I've had to remove this because it's too big and I get the following
USENET error: Your post is too long. Please reduce the amount of text.
(Limit
= 30000, Current = 96763) ->

-----------------------------------------------------------------------------------------------------
2.3 TapeJobs Table
-----------------------------------------------------------------------------------------------------

USE [NICISTapeTrackerSQL]
GO
/****** Object: Table [dbo].[TapeJobs] Script Date: 09/16/2009
10:46:25
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TapeJobs](
[No] [int] IDENTITY(1,1) NOT NULL,
[JobNo] [int] NOT NULL,
[TapeNo] [int] NOT NULL,
CONSTRAINT [PK_TapeJobs] PRIMARY KEY CLUSTERED
(
[No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=1 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=765 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'109'
,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AllowValueListEdits',
@value=False , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnCount', @value=N'4' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHeads', @value=N'-1' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1080 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidths',
@value=N'1701.000;1701.000;1701.000;1701.000' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'111'
,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_LimitToList', @value=N'-1' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ListItemsEditForm', @value=NULL
,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ListRows', @value=N'20' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_RowSource', @value=N'dbo.Jobs'
,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ShowOnlyRowSourceValues',
@value=False , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'JobNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AllowValueListEdits',
@value=False , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnCount', @value=N'4' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHeads', @value=N'-1' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1140 ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidths',
@value=N'1701.000;1701.000;1701.000;1701.000' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'111'
,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_LimitToList', @value=N'-1' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ListItemsEditForm', @value=NULL
,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ListRows', @value=N'20' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TapeJobs',
@level2type=N'COLUMN',@level2name=N'TapeNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_RowSource', @value=N'dbo.Tapes'