| Author |
 Topic  |
|
|
Swn-Y-Mor
New Member
90 Posts |
 Posted - 12 August 2006 : 05:32:36
|
I have just converted my database from Access to SQL Server 2000 but I get the following error creating a new topic.
Microsoft OLE DB Provider for SQL Server error '80040e2f'
Cannot insert the value NULL into column 'TOPIC_ID', table 'MyDatabase.myUserId.FORUM_TOPICS'; column does not allow nulls. INSERT fails.
/forum/post_info.asp, line 1005
Line 1005 :-
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
Any suggestions? |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Swn-Y-Mor
New Member
90 Posts |
Posted - 12 August 2006 : 13:30:33
|
I did follow your suggestions to the letter. I printed the link you provided before starting my conversion, and found it very helpful.
Thanks for the polite, encouraging, helpful response. |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 12 August 2006 : 14:48:16
|
I am sorry, but even if you think you did, you did not. The error occurs because that column is not set as an identity column. If you had created the tables using setup.asp, as recommended in the link, I can assure you that the column would be an identity column. Even if you used setup.asp, then when using DTS to import the access data, it's very important that you make sure that the destination tables are already created, by choosing the proper table name from the drop down, at the adequate step in the import process.
When checking identity insert for each table, make sure that you have an option to delete existing data. If you don't, then you're not choosing the proper destination table and, again, DTS will recreate the table, with a "bad" structure, resulting in your current situation. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Swn-Y-Mor
New Member
90 Posts |
Posted - 13 August 2006 : 05:30:02
|
Thank You for your reply. I am still struggling.
I double checked my settings during Import of data using DTS wizard, and my Column Mappings 'Delete Rows in Destination Table' is checked and 'Enable Identity Insert' is checked (see below)
What should my Transformations 'Copy the Source Columns directly to Destination Columns' be set to?
Should 'TOPIC_ID', table 'MyDatabase.myUserId.FORUM_TOPICS' Allow Nulls? Why would my insert be trying to insert a Null?
|
Edited by - Swn-Y-Mor on 13 August 2006 05:33:03 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 13 August 2006 : 05:36:59
|
I am thinking that the forum and DTS are using different tables... Are those screens from the real import data process? And is the error message really
Cannot insert the value NULL into column 'TOPIC_ID', table 'MyDatabase.myUserId.FORUM_TOPICS'; column does not allow nulls. INSERT fails.
If it is, you're dealing with two different users (MyAdmin and myUserID) and thus different tables... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Swn-Y-Mor
New Member
90 Posts |
Posted - 13 August 2006 : 05:53:21
|
| Sorry, I photoshoped my real Admin and Database names - they are both the same, and yes, real import Data process |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 13 August 2006 : 06:03:05
|
Better run setup.asp again, this time running setup.asp like this: setup.asp?RC=5
Then do the import as shown in the screens you just posted. Don't forget you need to do that for every single table in the database. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Swn-Y-Mor
New Member
90 Posts |
Posted - 13 August 2006 : 06:06:51
|
OK Thank You.
I will run Setup.asp?RC=5 again
I did do it for every single table. |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 13 August 2006 : 06:32:28
|
| your images show the import for the table forum_A_reply which does not ave an identity field anyway. Your problem is with the table creation not with the data transfer. |
|
|
|
Swn-Y-Mor
New Member
90 Posts |
Posted - 14 August 2006 : 05:57:13
|
My Forum has now been converted to SQL Server - I have learnt a lot in the process.
I re-created the basic database using setup.asp?RC=5 and rerun the MOD bits
Thank you for all your help, and I apologise for being sarcastic in one of my replies ruirib - frustration got the better of me. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
| |
Topic |
|
Topic Locked
