Snitz import for dummies (MS-SQL or MS-Access)

[shlomot]

Yes I'm a dummy, and yes, I've gone through two hellish weeks trying to migrate from Snitz to vBulletin.

A lot of reading, consulting, and trial-and-errors have led to a smooth migration from the old forum (http://www.topsynergy.com/forum/) to the new one (http://www.topsynergy.net/). I'll be happy to share this knowledge with other stragglers.

Please post your questions here and I'll gradually enrich this post with answers as we go along.

Last but not least, thank you Steve (Impex developer) and Jerry (Snitz import developer) for your continuous support.

Important: Impex for tier-1 forums (such as Snitz) requires both old and new databases to reside on the same physical server (I assume that this is due to transaction management and locking strategy). Fat chance one can get an MS-SQL and MySQL databases on the same server, not to speak of shared hosting.

Hence, I decided to go for MySQL to MySQL import and dump my MS-Access or MS-SQL database to a temporary MySQL database that I set up just for this purpose.

Then, I used a converter that I purchased from Intelligent Converters (http://www.convert-in.com/) in order to create the dump file. (Dumping the Microsoft's Enterprise Manager won't help a bit because they script the dump to be read only by other MS database).

[ALcorn]

Oh, really interesting! Can you describe the process of migration from Snitz MSSQL to vBulletin MySQL? Thank you

[shlomot]

Oh, really interesting! Can you describe the process of migration from Snitz MSSQL to vBulletin MySQL? Thank you
The process is described in great details in the online manual (http://www.vbulletin.com/docs/html/impex), so there's no real need to repeat it here.

Please ask about specific deliberations or challenges that you are facing, and keep the cheer :). Today I performed my final run and it took less than 15 minutes from start to end without any obstacles.

I'll gladly help you, just be specific :)

[Jerry]

Important: Impex for tier-1 forums (such as Snitz) requires both old and new databases to reside on the same physical server (I assume that this is due to transaction management and locking strategy). Fat chance one can get an MS-SQL and MySQL databases on the same server, not to speak of shared hosting.

Not at all, this is due to databases not allowing in most cases remote access.

If both databases allow remote access, you can have the source on one server, ImpEx on a second and vBulletin and its database on a 3rd.

[gpspassion]

Currently giving a Snitz->Vbulletin conversion a shot and I'm facing a problem with the import.

I've made sure the Snitz mssql database can be accessed from the new vbulletin server on the default 1433 port (I can telnet) and have activated php_mssql in WAMPS but the Impex gives me many errors in step 1 :"Check and update database" starting at:

"ACTIVE_USERS NOT found.
ALBUM NOT found.
ALBUM_CAT NOT found.
ALBUM_CONFIG NOT found.
etc..."

Is there a way I can test that php_mssql is working properly and able to connect to that remote database ? Do I need to specify the 1433 port somewhere ?

TIA

[ALcorn]

gpspassion, try the following PHP script:

<?
$ser="SERVEUR"; #the name of the SQL Server
$db="Runtime"; #the name of the database
$user="sa"; #a valid username
$pass=""; #a password for the username

# one line
$conn=odbc_connect("Driver={SQL Server};Server=".$ser.";Database=".$db,$user,$pass);
# one line

?>

[ALcorn]

And this one:

<?
$conn=mssql_connect("SERVEUR","sa",'password');
if ($conn) {
echo "GOOD";
}
else {
echo "BAD".mssql_get_last_message();
}

?>

[gpspassion]

Thanks, worked a charm (added <?php at the beginning), so it seems my setup is correct, wonder why impex errors out then.

[EDIT] - second one doesn't though, it seems it's missing the database name ?

Might try the MSSQL->MYSQL route with this converter http://www.kofler.cc/mysql/mssql2mysql.html#guiversion and the import into MySQL on the vbulleting server.

[ALcorn]

The're also some interesting tips about debugging connection problems to MSSQL server from php (http://fr3.php.net/function.mssql-connect).

Seems like the version of ntwdblib.dll file do matter (http://www.userscape.com/helpdesk/index.php?pg=kb.page&id=13).

[gpspassion]

Thanks, looks like the connection is ok based on your sricpt.

So I tried to import a 732MB .sql in MySql and naturally it was too large so I bumped up the limits to 1000MB, but now I am getting time-outs, and the data is not getting imported correctly...don't know if the limit can be raised with shlomot must have figured that out, hopefully he is still around !

One problem after the other, eh ! sigh...

[ALcorn]

How you import the dump in MySQL? Via console or phpMyAdmin? Big dumps should be imported via the mysql console.

[gpspassion]

Yes, being rather new at this I was using phpMyAdmin, not sure what the syntax in the mysql console should be, will try to look it up.

[ALcorn]

mysql -h host -u user -p dbname < dump.sql

[gpspassion]

Thanks had just found this mysql -u username databasename < filename.txt but it seemed to be missing handles, yours looks better, let me drop what got imported so far and try that.

[gpspassion]

No luck so far, it is complaining about the syntax, maybe the path to the file. Ok, I was trying to type that from the console, duh, couldn't work, let me try from the DOS prompt, can it be done directly in the console?

UPDATE - it's crunching...

This script looked promising http://www.ozerov.de/bigdump.php but I am getting "At this place the current query includes more than 300 dump lines." errors...

[ALcorn]

try the following (assuming your database is in standard latin1 encoding):
mysqlimport -h host -u username -p --character-sets-dir=latin1 dbname c:\dbdump.sql

But there could be some problems with delimiters, fields enclosing caracters etc...

If this is not working as is, then try to include the following parameters in my.ini file and restart Mysql then:
[mysqlimport]
fields-terminated-by=;
default-character-set=latin1
fields-enclosed-by="
lines-terminated-by=\r\n

[kerrya]

I am trying to convert a very small Snitz forum over to the latest version of vB.

I downloaded the trial version of Intelligent Converters, and did a direct import into MySQL database.

I had set up the MySQL database thru phpMYadmin and left it empty, leaving the converter to populate the fields.

Two issues.
1) Many table names are failing. ie. FORUMS_CATEGORY winds up as forums_cat_gory

2) The target database tabe names are in lowercase. It seems like IMPEX is seeking uppercase tabel names as IMPEX is not finding the table names that do come over correctly.

Any help appreciated.

Thanks
Kerry

[Jerry]

Not sure what's going on with the table name though you can re-name it, unless it's a newer version I haven't seen.

In ImpExConfig you have this directive you can set at the bottom :

define('lowercase_table_names', true);

[kerrya]

Thanks Jerry, with that last message I think I am almost home.

The problem with the table names seems to be restricted to the trial version of the Intellegent Converters software. When I purchased the full version the problem went away.

I have done the import, and I went into maintenence and ran all of the index updates, and all is looking good except for one thing.

When I click on memberlist i see only the admin. When I go into the CP, I see them all and they are all editable.

The forums and posts came in fine, and the posts all have the right username attached.

Any idea why members dont show?

Thanks for your help
Kerry

[kerrya]

Users are resetting their passwords, but getting a message saying:

You have been banned for the following reason:
No reason was specified.
Date the ban will be lifted: Never

In the CP, if I click on banned users I get nothing. Nobody is banned.

Any ideas?
Thanks
Kerry

[kerrya]

OK. Figured out banned users. I'm just now noticeing not all posts and threads came in.

Oh well. retry :)
Kerry

[Steve Machol]

Are you sure it's not this:

How Do I Set Forums to Display All Posts
http://www.vbulletin.com/forum/showthread.php?t=191791

[kerrya]

Steve! You did it. That was it exactly.
Thank you so much.
Kerry

[Steve Machol]

You're welcome. :)