Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

enter image description hereTwo 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**.*

share|improve this question
1  
Could you post a diff of your DB settings instead of that huge chunk of code? Would be easier to check that way. –  dezso Feb 25 '13 at 15:00
    
Also where exactly do you come across the error "missing index"? Showing the code that causes this error, and the actual, complete error message would be helpful. –  Aaron Bertrand Feb 25 '13 at 15:26
    
I have now edited the question and could not find much difference on DB setting,s FDT gives me error, checked mssql logs could not find any, FDT log as well do not show any error just a message saying Table Art misses Index. So had to use profiler, Will it help if I add some T-sql commands that profiler finds when it I run the program across working DB? –  tough Feb 25 '13 at 15:37
    
@AaronBertrand I have now added some codes that the profiler shows while running the program to insert products in Art table of Test/working DB. HOpe it helps to diagnose the problem. –  tough Feb 25 '13 at 16:23
    
Still don't see the actual error message anywhere. –  Aaron Bertrand Feb 25 '13 at 16:57

1 Answer 1

up vote 2 down vote accepted

You need to compare the results of these two statements:

exec [Fakt999]..sp_indexes_rowset N'Art',NULL,NULL

exec [Fakt000]..sp_indexes_rowset N'Art',NULL,NULL

A lot of ERP systems have procedures to check the schema to verify that it matches the application metadata about the database so that the queries being generated don't error out. The error that you are saying occurs, isn't one from SQL, it is likely from the application and the difference in results from the above queries is likely what the root cause is.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.