| Author |
 Topic  |
|
|
Doc Orac
Starting Member
Netherlands
28 Posts |
 Posted - 10 November 2004 : 13:21:48
|
I have tried to migrate from Access 2000 to MSSQL Server 6.5, and have run into some trouble.
As stated in http://forum.snitz.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=31144, i created with setup.asp the Snitz base tables in the database and used DTS to transfer the data (so that any existing table data is deleted and enable identity insert is checked)
The 3 problems are :
1) The table FORUM_GROUP (strTablePrefix = "FORUM_") exists in the Access2000-db, but not in the SQL-db. I didn't find a reference to the table in the code. Can I ignore that table? Is the table from an old version of Snitz?
2) DTS couldn't fill FORUM_SUBSCRIPTIONS. It gave the following error:
INSERT error column1 'subscription_id', dbtype_i4, status 10:
integrity violation; attempt to insert NULL data or data which violates constraints
Looking at the tabledata, the column 'subscription_id' was empty for every record, according the preview-option in DTS.
3) After DTS is ready (with the FORUM_SUBSCRIPTIONS-error), I was able to access the forum. The forum (Access2000-db) can be found at (For example) http:\\url and the mssql-db can be found at http:\\url\mssql\. Everthings looks okey, but not Subscriptions (of course) and the Admin Options. The "Main Forum Configuration" and so on, are filled with the defaults and not with the data from the access-db.
Any hints? |
Have A Nice Day!
Doc Orac (Remco Beekmans) @ Rotterdam (NL) |
Edited by - Doc Orac on 13 November 2004 08:51:22 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 10 November 2004 : 16:49:13
|
1. Yes, you can ignore the table.
2. Remove the option for identity insert for the FORUM_SUBSCRIPTIONS table. That should account for automatic id creation, as long as the Subscription_ID column in the SQL Server is an identity field. Please check before doing the transfer to make sure it is, and change it if it's not (it should be, but check it anyway).
3. Well, maybe you forget to include the FORUM_CONFIG_NEW table in the DTS operation?
At the stage you are, you can DTS just the FORUM_SUBSCRIPTIONS table and the FORUM_CONFIG_NEW table data from the Access DB. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
Doc Orac
Starting Member
Netherlands
28 Posts |
Posted - 11 November 2004 : 16:30:05
|
Thx ruirib. I have given it a new try:
2) I tried it without "enable identity insert", no joy. SQL Query Analizer says this:
CREATE TABLE [FORUM_SUBSCRIPTIONS] (
[SUBSCRIPTION_ID] [int] NOT NULL ,
[MEMBER_ID] [int] NOT NULL ,
[CAT_ID] [int] NOT NULL ,
[TOPIC_ID] [int] NOT NULL ,
[FORUM_ID] [int] NOT NULL
) ON [PRIMARY]
GO
I don't see IDENTITY. I'm not familair with mssql, but if I look at inc_create_forum_mssql.asp, I see at all the IDENTITY entries are followed with (1,1). But not SUBSCRIPTION_ID INT IDENTITY NOT NULL.
3) Looking directly at the data from the table CONFIG_NEW (on the SQL-server), the data looks okey. But when I log in, the defaults are shown. But after changing de forum-url in de Options, the correct colors and some others fields are showing the correct value, but not all.
I suspected a problem with data in a cookie, is that possible? If the testforum works okey, after the migration, the forum will be moved from testsite http://url/mssql to the productionsite http://url by changing the connectionstring, so if the problem is in the testlocation, I could ignore it.
Any thoughts? |
Have A Nice Day!
Doc Orac (Remco Beekmans) @ Rotterdam (NL) |
Edited by - Doc Orac on 11 November 2004 17:10:29 |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 11 November 2004 : 20:56:16
|
I wonder if you're the first to try this version of Snitz on SQL 6.5 (which is ancient)? The last time I had a v6.5 SQL server was about in about 1999 or so. One problem might be ON [PRIMARY] I don't think that existed in v6.5
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 11 November 2004 : 21:12:11
|
On 2, make SUBSCRIPTION_ID an identity field, at least while you do the DTS import.
On 3, run setup.asp. That should load the values from the CONFIG_NEW table.
I don't know SQL Server 6.5, though. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Doc Orac
Starting Member
Netherlands
28 Posts |
Posted - 12 November 2004 : 02:49:42
|
quote:
Originally posted by Doug G
I wonder if you're the first to try this version of Snitz on SQL 6.5 (which is ancient)? The last time I had a v6.5 SQL server was about in about 1999 or so. One problem might be ON [PRIMARY] I don't think that existed in v6.5
Just to make sure, I looked at admin_info.asp, where I found the following:
SERVER_SOFTWARE Microsoft-IIS/6.0
DBMS Name Microsoft SQL Server
DBMS Version 08.00.0818
Very strange, I 99,999% sure that it was 6.5 a couple days ago.  
|
Have A Nice Day!
Doc Orac (Remco Beekmans) @ Rotterdam (NL) |
|
|
|
Doc Orac
Starting Member
Netherlands
28 Posts |
Posted - 12 November 2004 : 02:54:43
|
quote:
Originally posted by ruirib
On 2, make SUBSCRIPTION_ID an identity field, at least while you do the DTS import.
On 3, run setup.asp. That should load the values from the CONFIG_NEW table.
I don't know SQL Server 6.5, though.
Okey
2) How can I do that? Could you give me the sql-code for that?
3) Okey, so setup.asp to create the database, then DTS, followed by setup.asp. Okey, first try, yes, that seems to work.  While try it again with the up-to-date database. |
Have A Nice Day!
Doc Orac (Remco Beekmans) @ Rotterdam (NL) |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 12 November 2004 : 05:26:08
|
You can connect to the DB using Enterprise Manager, right? Just open the table in design view, click the SUBSCRIPTION_ID column and in the properties pane (bottom of the window) set Identity to Yes, Identity Seed to 1 and Identity Increment to 1.
On Query Analizer, you can use this:
ALTER TABLE FORUM_SUBSCRIPTIONS
DROP COLUMN SUBSCRIPTION_ID
ALTER TABLE FORUM_SUBSCRIPTIONS
ADD SUBSCRIPTION_ID int IDENTITY(1,1)
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 12 November 2004 05:26:32 |
|
|
|
Doc Orac
Starting Member
Netherlands
28 Posts |
Posted - 12 November 2004 : 12:52:25
|
Okey, thx ruiib. Here the follow up on 2)
The table has been changed and is now:
CREATE TABLE [FORUM_SUBSCRIPTIONS] (
[SUBSCRIPTION_ID] [int] IDENTITY (1, 1) NOT NULL ,
[MEMBER_ID] [int] NOT NULL ,
[CAT_ID] [int] NOT NULL ,
[TOPIC_ID] [int] NOT NULL ,
[FORUM_ID] [int] NOT NULL
) ON [PRIMARY]
But the same occurs. As I stated before, when I do a preview, the column SUBSCRIPTION_ID is empty.
So, I tried this on de sql-db:
INSERT INTO [FORUM_SUBSCRIPTIONS] (
[SUBSCRIPTION_ID],
[MEMBER_ID],
[CAT_ID],
[TOPIC_ID],
[FORUM_ID]
) VALUES ('',2,1,1,1)
Cannot insert explicit value for identity column in table 'FORUM_SUBSCRIPTIONS' when IDENTITY_INSERT is set to OFF.
and:
INSERT INTO [FORUM_SUBSCRIPTIONS] (
[MEMBER_ID],
[CAT_ID],
[TOPIC_ID],
[FORUM_ID]
) VALUES (2,1,1,1)
works okey.
So I changed the import-transformation, so that FORUM_SUBSCRIPTIONS (target sql) was filled with TOPIC_ID (base access2000) and that works. So the column FORUM_SUBSCRIPTIONS in the access2000-db is empty and DTS tries to insert an empty value in every row.
So I will try to write some asp code that fills the column FORUM_SUBSCRIPTIONS in the access2000 with an unique value, so that DTS can be used. Sounds this okey? |
Have A Nice Day!
Doc Orac (Remco Beekmans) @ Rotterdam (NL) |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Doc Orac
Starting Member
Netherlands
28 Posts |
Posted - 12 November 2004 : 17:28:12
|
quote:
Originally posted by ruirib
Remco, did you disable identity insert when doing the DTS transfer for FORUM_SUBSCRIPTIONS?
Yes, tried it before changing the table, with en without "identity insert" and also after the table change.
Every (4) times, the same error. |
Have A Nice Day!
Doc Orac (Remco Beekmans) @ Rotterdam (NL) |
Edited by - Doc Orac on 12 November 2004 17:28:59 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Doc Orac
Starting Member
Netherlands
28 Posts |
Posted - 13 November 2004 : 08:49:54
|
Thx Rui for your help.
A couple of minutes ago, I closed mine forum, did the migration, ignoring the table FORUM_SUBSCRIPTIONS, then creating the table CREATE TABLE [FORUM_SUBSCRIPTIONS] (
[SUBSCRIPTION_ID] [int] IDENTITY (1, 1) NOT NULL ,
[MEMBER_ID] [int] NOT NULL ,
[CAT_ID] [int] NOT NULL ,
[TOPIC_ID] [int] NOT NULL ,
[FORUM_ID] [int] NOT NULL
) followed by dumping the table (out of the access-db), so that I had many lines like INSERT INTO [FORUM_SUBSCRIPTIONS] ([MEMBER_ID],[CAT_ID],[TOPIC_ID],[FORUM_ID]) VALUES (472,8,1531,11); Then using the SQL Query Analizer, I filled the sql-table. Ran setup.asp and started the forum. I didn't change the access-db, because that database was working okey in production.
For now, it seems to work okey, a little bit faster then before.
Again Rui, thx. for your help. |
Have A Nice Day!
Doc Orac (Remco Beekmans) @ Rotterdam (NL) |
Edited by - Doc Orac on 13 November 2004 11:43:36 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
| |
Topic |
|
|
|
Topic Locked
