All SYSNOTES not showing

[atlanticdit]

Hi All,

We've just moved our Macola database system from Pervasive SQL 2000 to MS SQL 2000. One table, SYSNOTES, did not come over in the conversion. I was able to get most of the notes back from duplicate databases that were already in SQL format. 236 records were still in a BTR file that I was able to convert to a text file and then import into the SYSNOTES table. Now these records show up in the SYSNOTES table but do not show up in Macola.

I've tried to reboot the Macola server and rebuild the SYSNOTES file. I've also tried to export the SYSNOTES table to a working SQL database and then back to see if that would work but it didn't. Any suggestions on how to get those 236 records to display in Macola?

Thanks,
Dave

 

[atlanticdit]

We're on Macola version 7.6.3

 

[dgillz]

How did you go about importing these records? The standard Macola import in System Managre?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[kscheeler]

You should be able to export the SYSNOTES table from a Btrieve database, copy the SYSNOTES.TXT file into the data folder for the SQL database and then log into SQL and import them in. If you choose Append and don't get any records, it sees them already there based on the key structure.


Kevin Scheeler

 

[atlanticdit]

Kevin,

Thanks. Yes, that is what I did. I made the BTR file a text file and then imported it from the SQL database. It accepted the appended records and I can see them in the SQL SYSNOTES file. The 236 records are in the table. For some reason though, they are not showing up in Macola and therein lies my dilemma.

Dave

 

[kstickler]

Can you confirm that there are no duplicates of the A4GLIDENTITY field? I believe this is like a "record number" field and needs to be unique. I don't recall that the field existed in the BTR version of the file....at least I don't remember seeing it.

Kim

 

[atlanticdit]

Yes, kimmay, there are all unique. When I did the import it wouldn't let me import the text file unless the A4GLIDENTITY was unique. I gave it a range of numbers that I knew weren't in the table. When I imported the text file it then changed the A4GLIDENTITY to its own auto generated number.

 

[kscheeler]

Make sure you have an index listed in either query analyzer or enterprise manager. If not, run the following script against your database
--------------------------------------------------
CREATE UNIQUE CLUSTERED INDEX [ISYSNOTES_SQL0] ON [dbo].[SYSNOTES_SQL]([note_name], [name_value], [note_dt], [note_tm]) ON [PRIMARY]
GO
--------------------------------------------------

You can also run the following script to rebuild the index if it already exists.

--------------------------------------------------
dbcc dbreindex (sysnotes_sql, '', 80)
go
--------------------------------------------------

Also enter a new note for a screen where you know a note should show up. Then select all fields in query analyzer and compare the record that you just entered that does show up to one of the ones that were imported but don't to see where the differences are.


Kevin Scheeler