User forums now online! Please see the latest post in the Announcements forum for more information.

Author Topic: Errors converting TP7 SQL Server database to TP8.  (Read 1801 times)

0 Members and 1 Guest are viewing this topic.

nzmike

  • Member
  • **
  • Posts: 9
    • View Profile
Hi guys,

First, thanks for coming back from the dead and offering support again - I'd given up on TP8 completely as not one of my emails on this subject (or any other) has been answered since Jan 2010 when I first installed it so it's nice to see you back.

Anyway, enough complaining! As the title suggests, I am unable to convert my SQL Server 2005 database from the TP7 schema to the TP8 one as I get many many errors.

I am a professional SQL server and .Net developer so I am very happy to experiment with the script you had to do the conversion.... if only I could get it.  I am really, really keen to switch to TP8 (I paid for it after all!) but I can't until the conversion script works as I have a massive DB of images in my TP7 database and I can't switch to any other format as all my supporting code depends on my TP db being in SQL Server.

I have posted the output of the conversion tool in a seperate post as it was too long to include in this.

TIA for any help or advice you can offer.

Mike

nzmike

  • Member
  • **
  • Posts: 9
    • View Profile
Errors converting TP7 SQL Server database to TP8.
« Reply #1 on: 2011-05-28 02:27:30 »
Error log from the conversion tool - see post above.  I've marked in red where I think at least some of the errors are - seems to trying to drop tables that are views and create tables that haven't been dropped!

Opening database for conversion.
Identifying database...
Database server type is Microsoft SQL Server.
Mappings: {'TEXT': 'ntext', 'COUNTER': 'int identity primary key clustered', 'FLAG': 'bit', 'INT64': 'bigint', 'DATE': 'varchar(32)', 'ID': 'int', 'INT32': 'int', 'INT16': 'smallint', 'BIN64': 'binary(64)', 'BLOB': 'image', 'WCHAR': 'nvarchar'}
Removing pre-v7 cruft.
Exec (nocheck): drop table [ActiveUsers]
Exec (nocheck): drop table [CurrentUsers]
[3701-42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'CurrentUsers', because it does not exist or you do not have permission
Exec (nocheck): drop table [Logins]
[3701-42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'Logins', because it does not exist or you do not have permission
Exec (nocheck): drop table [DoesPathHaveThumbs]
[3701-42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'DoesPathHaveThumbs', because it does not exist or you do not have permission
Exec (nocheck): drop table [GalleryThumbStart]
[3705-42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use DROP TABLE with 'GalleryThumbStart' because 'GalleryThumbStart' is a view. Use DROP VIEW

Exec (nocheck): drop table [GetThumbnail]
[3701-42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'GetThumbnail', because it does not exist or you do not have permission
Exec (nocheck): drop table [KeywordByFile]
[3705-42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use DROP TABLE with 'KeywordByFile' because 'KeywordByFile' is a view. Use DROP VIEW

Exec (nocheck): drop table [KeywordGroupCount]
[3701-42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'KeywordGroupCount', because it does not exist or you do not have permission
Exec (nocheck): drop table [ThumbnailGallery]
[3705-42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use DROP TABLE with 'ThumbnailGallery' because 'ThumbnailGallery' is a view. Use DROP VIEW
Exec (nocheck): drop table [ThumbnailPath]
[3705-42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use DROP TABLE with 'ThumbnailPath' because 'ThumbnailPath' is a view. Use DROP VIEW
Exec (nocheck): drop table [ThumbnailUDF]
[3705-42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use DROP TABLE with 'ThumbnailUDF' because 'ThumbnailUDF' is a view. Use DROP VIEW

Exec (nocheck): drop table [UpdatePathNames]
[3701-42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'UpdatePathNames', because it does not exist or you do not have permission
Creating new tables.
Exec (nocheck): create table [ThumbsPlusDatabase] (
[attribute] nvarchar(50) not null primary key,
[value]     nvarchar(255) not null
)
[2714-42S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'ThumbsPlusDatabase' in the database
Exec (nocheck): create table [FoundLists] (
[idList"     int not null primary key,
[ListName]   nvarchar(255) not null,
[TableName]  nvarchar(255) not null,
[SQLText]    ntext,
)
[2714-42S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'FoundLists' in the database
Exec (nocheck): alter table [FoundLists] add constraint [U_FoundListName] unique ([ListName])
[1750-42000] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'U_FoundListName' in the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors
Exec (nocheck): create table [FoundThumbs] (
[idList]     int not null,
[idThumb]    int not null,
[flags]      int not null,
)
[2714-42S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'FoundThumbs' in the database
Exec (nocheck): alter table [FoundThumbs] add constraint [PK_FoundThumbs] primary key ([idList], idThumb])
[8180-42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string ')'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared
Setting database attributes.
  setting attribute 'thumbnail_width' = '100'.
[3621-01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK__ThumbsPlusDataba__4D94879B'. Cannot insert duplicate key in object 'dbo.ThumbsPlusDatabase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated
Upgrade failed at: 'select * from [DatabaseInfo]'.

Conversion failed!

hfremuth

  • Full Member
  • ***
  • Posts: 29
    • View Profile
Errors converting TP7 SQL Server database to TP8.
« Reply #2 on: 2011-05-31 10:12:12 »
nzmike,

I moved you thread to Confirmed Issues although I can't reproduce this (I don't have an SQL Server here). It seems to work with MqSQL, see these posts: [msg]143[/msg] and [msg]30[/msg] - maybe that helps a bit.

nzmike

  • Member
  • **
  • Posts: 9
    • View Profile
So, where to from here...?!
« Reply #3 on: 2011-06-01 02:04:14 »
I appreciate this is now a known issue but I know realistically it may take you a very long while before you can look at this and in the meantime I'm missing out on all the new features of TP8... and I've had it licensed (but unuseable) since it first came out (early 2010 IIRC).  I promised my clients lots of new features when TP8 came out and it's pretty embarrassing not to have been able to deliver them.  (Not to mention I've lost a couple of clients due to this.)

So, as I said, in my original post is there any way at all I can just get the current script you have to do the conversion - or perhaps just one to create a new, empty SQL Server DB for TP8?  (I'm puzzled as to why there is no such script on your site as there was for TP7.)

I design, build and program SQL Server DB's for a living so I am absolutely happy to play with this myself and can guarantee whatever I break I will not hold Cerious in any way responsible - as long as I have backups of my TP7 DB I should be fine.  (And besides, I'd only experiment with test DB's anyway).

Also, if I do work out a script that works for me I am 100% happy to then share it here for Cerious or anyone else in the same situation as me to use (though the same applies - I can't be responsible for anyone else's database breaking or not working etc).

Apologies for being so darned persistent but as you can probbaly tell I'm desperate to get TP8 going with SQL Server so I'd *REALLY, REALLY* appreciate being able to try and get this sorted ASAP even it means do it manually myself.

Thanks,

Mike

hfremuth

  • Full Member
  • ***
  • Posts: 29
    • View Profile
So, where to from here...?!
« Reply #4 on: 2011-06-01 09:59:38 »
> Also, if I do work out a script that works for me I am 100% happy to then share it here for Cerious or anyone else in the same situation as me to use (though the same applies - I can't be responsible for anyone else's database breaking or not working etc).

Mike - your offer to help here sounds awesome. Cerious is a small company, and issues like that require a ton of time to be solved (development and testing). Getting a helping hand from an expert is priceless. Please email me (I don't have your address) and I will bring this to Phillips attention ASAP!

Thanks!

Sjoinca

  • Member
  • **
  • Posts: 8
    • View Profile
So, where to from here...?!
« Reply #5 on: 2011-06-11 12:43:38 »
Nzmike, you may want to check out ThumbsPlus_8_001.schema and the Python script upgradeserver.py in the Thumbsplus program dir.  You will see what the new V8 table formats should be and the upgrade script goes through each V7 table to alter the tables to V8.  This same procedure could have been written in SQL rather than Python and fed to the sql engine.

Hope this helps.  I am not an expert in either language so I can not offer much futher help.

Sjoinca

nzmike

  • Member
  • **
  • Posts: 9
    • View Profile
So, where to from here...?!
« Reply #6 on: 2011-06-21 05:00:29 »
Thanks - I will try that as I've heard nothing back from Philip who I thought was going to contact me perhaps with the script I could have worked on.  :-(

hfremuth

  • Full Member
  • ***
  • Posts: 29
    • View Profile
So, where to from here...?!
« Reply #7 on: 2011-06-21 08:41:24 »
> Thanks - I will try that as I've heard nothing back from Philip who I thought was going to contact me perhaps with the script I could have worked on.  :-(

I gave him the info you mailed me and will remind him about it. It may still be in the stack of items that are in the to-do list....

nzmike

  • Member
  • **
  • Posts: 9
    • View Profile
So, where to from here...?!
« Reply #8 on: 2011-06-22 05:50:48 »
OK, thanks - I'm going to be on holiday pretty soon until the beginning of August so won't get a chance until then to look at it.... still happy to try and help if I can though.

bdsfcaus

  • Guest
Errors converting TP7 SQL Server database to TP8.
« Reply #9 on: 2011-07-22 19:22:07 »
I used v7 for several years and also purchased v8 quite a while ago and have never been able to use it (using/converting my large v7 database) until yesterday.

v7 was using mysql 4.x and worked fine.

i could not convert to v8 using the menu utility (similar errors).

I submitted this problem many times and never heard back from ayone at cerious.

I registered here today just to post my solution (for mysql)...

create new blank tp8 db (access version)

convert this empty db to mysql using menu utility (it works with empty db)
exit tp8

use mysql gui tool query browser...
(i now have 2 mysql tp db's in mysql, thumbs7 and thumbs8; thumbs7 has 300k+ thumbnails, thumbs8 has 0)

for every table (that has data) i manually enter the query...
"insert into thumbs8.[tablename] select * from thumbs7.[sametablename];"

the biggest problem is table 'thumbnail'; the new version has more fields than the old, and even worse is the fact that tp7 field 'filesize' has been renamed to 'filelength' in tp8 (a foolish mistake, and unnecessary).

so i needed to specify each v7 fieldname into the insert query for the thumbnail table.

next i had to update thumbs8.thumnail.filelength via an update query with 'update thumbs8.thumnail set filelentgth=thumb7.thumbnail.filesize where thumb8.thumbnail.idThumb = thumb7.thumbnail.idThumb;"

done.

set up ODBC for thumbs8 and it's working.

An awful lot of time and frustration spent over fixing software that I paid for.
This is not freeware. It should not be so hard.

ps: i am writing this from memory--the sql syntax may not be correct.

bdsfcaus

  • Guest
Errors converting TP7 SQL Server database to TP8.
« Reply #10 on: 2011-07-22 20:30:44 »
(sorry for the typos above)(and below)...

also...

a lot of my saved FoundFiles (and there are many) included an SQL "where tn.filesize >= #####" (200000, 1000000, etc bytes)

i had to fix this with...

update thumbs8.findcriteria set `value` = replace(`value`,'filesize','filelength') where `value` like '%filesize%';

more repercussions from the unnecessary fieldname change

leedell

  • Member
  • **
  • Posts: 4
    • View Profile
Errors converting TP7 SQL Server database to TP8.
« Reply #11 on: 2011-07-25 14:54:48 »
So, from what I've seen, the best approach to actually get going is:

Export Existing TP7 Client/Server DB to one or more text files
Create New TP8 Client/Server DB
Connect to TP8 Client/Server DB using TP8
Import Data from TP7 export file(s)

Given how long the failed database conversion took, I expect that this will probably be time consuming for my database AND will require multiple text files to keep them of reasonable size (under 2GB each would probably be sanest).

However, it presents me with an opportunity of sorts.
Currently, I'm running TP7 with an MS SQL Server 2000 back end running under MS Windows 2000 Server.  I've had to hold off on upgrading/replacing the server system simply because MS SQL 2K has compatibility issues under newer versions of WIndows and a lot of newer hardware (read that: hard drive controllers and motherboards) don't include drivers for Win 2K.

Switching to a different DB back end would allow me to upgrade to a newer system (with faster CPU, faster bus, faster and more RAM and faster and bigger HDs) running...say...Debian Linux and postgreSQL 9.

Does anyone see any caveats to this approach?