Two databases with same structure at least for my eye. One of them was used as training/testing, where I can add list of products in Art table via a program's extension FDT but not in Art table of another database. The databases have following properties.
EXEC [fakt000].sys.sp_addextendedproperty @name=N'user15', @value=N'sysadmin'
GO ---Extra line of code in error producing DB, I am using user:sa to --login
SET COMPATIBILITY_LEVEL = 80
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [dbo].[sp_fulltext_database] @action = 'disable'
end
GO
common properties in both DB 999 and DB 000
SET ANSI_NULL_DEFAULT OFF GO
SET ANSI_NULLS OFF
GO
SET ANSI_PADDING OFF
GO
SET ANSI_WARNINGS OFF
GO
SET ARITHABORT OFF
GO
SET AUTO_CLOSE OFF
GO
SET AUTO_CREATE_STATISTICS ON
GO
SET AUTO_SHRINK OFF
GO
SET AUTO_UPDATE_STATISTICS ON
GO
SET CURSOR_CLOSE_ON_COMMIT OFF GO
SET CURSOR_DEFAULT GLOBAL GO
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET RECURSIVE_TRIGGERS OFF
GO
SET DISABLE_BROKER
GO
SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
SET DATE_CORRELATION_OPTIMIZATION OFF
GO
SET TRUSTWORTHY OFF
GO
SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
SET PARAMETERIZATION SIMPLE
GO
SET READ_COMMITTED_SNAPSHOT OFF
GO
SET HONOR_BROKER_PRIORITY OFF
GO
SET READ_WRITE
GO
SET RECOVERY SIMPLE
GO
SET MULTI_USER
GO
SET PAGE_VERIFY TORN_PAGE_DETECTION
GO
SET DB_CHAINING OFF
GO
The program generates a error message saying table Art misses index when I use fakt000 as a destination database, while using fakt999 as database I have no problems.
I do have checked the table Products in both the databases seems similar in name of indexes.
To solve this issue I have rebuilt all the indexes in fakt000.dbo.Art still no luck.
I do have change the collation to be similar and again rebuilt all indexes in fakt000.dbo.Art
I do have added same tables in fakt000 but not in fakt999 database. I do not think this would cause any of such error, as error states fakt000.dbo.Art missing index
I have ran profiler to see what commands do the program use and where it may have missed, seems too complex to me, I have enlisted it down,
Select * from version
go
exec sp_provider_types_rowset NULL,NULL
go
exec [Fakt000]..sp_tables_rowset;2 NULL,NULL
go
exec [Fakt000]..sp_indexes_rowset N'Art',NULL,NULL
go
exec [Fakt000]..sp_indexes_rowset N'ArtPris',NULL,NULL
go
SET LOCK_TIMEOUT 10000
go
select serverproperty(N'instancename') --here seems to arise the problem
go
select ServerProperty('ErrorLogFileName')-- may be here, I am not sure what!
go
create table #err_log_tmp(ArchiveNo int, CreateDate nvarchar(24), Size int)
insert #err_log_tmp exec master.dbo.sp_enumerrorlogs
SELECT
er.ArchiveNo AS [ArchiveNo],
CONVERT(datetime, er.CreateDate, 101) AS [CreateDate]
FROM
#err_log_tmp er
ORDER BY
[ArchiveNo] ASC
drop table #err_log_tmp
go
create table #err_log_tmp(ArchiveNo int, CreateDate nvarchar(24), Size int)
insert #err_log_tmp exec master.dbo.sp_enumerrorlogs 2
SELECT
er.ArchiveNo AS [ArchiveNo],
CONVERT(datetime, er.CreateDate, 101) AS [CreateDate]
FROM
#err_log_tmp er
ORDER BY
[ArchiveNo] ASC
drop table #err_log_tmp
go
select serverproperty(N'instancename')
go
select ServerProperty('ErrorLogFileName')
go
EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'desc'
go
I am not sure what to do next? to solve it out.
I can insert some products into table using insert but not from program.
Profiler gives me these codes when it successes in test DB fakt999
exec [Fakt999]..sp_tables_rowset;2 NULL,NULL
go
exec [Fakt999]..sp_indexes_rowset N'Art',NULL,NULL
go
exec [Fakt999]..sp_indexes_rowset N'ArtPris',NULL,NULL
go
exec sp_provider_types_rowset NULL,NULL
go
exec [Fakt999]..sp_tables_rowset;2 NULL,NULL
go
exec [Fakt999]..sp_columns_rowset N'Art',NULL,NULL
go
Select * From Art Where Artikelnr='$MALL$'
go
declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 nvarchar(30)'
,N'Select Apris,inpris,Medelpris,Ändrad,Låstinpris,Sortimentstatus
,VariantRadTyp,VariantKolTyp
from art Where Artikelnr=@P1',N'73974'
select @p1
go
Select * from ArtPris Where Artikelnr='73974'
go
Select * from artlev where artikelnr='73974'
go
UPDATE Art SET ArtikelNr = '73974', RevideradDag = '2013-02-25'
, Ändrad = 9 WHERE ((Artikelnr='73974'))
go
exec sp_unprepare 2
go
NOTE:The program FDT is a ERP software whose source code or support is out of scope.
*Edit: Sorry I may have been confused myself in writing the name of table in both databases as products instead of **Art**.*
