SQL Server 2000迁移至2017后Sage ERP 5.4连接及导入故障求助

Let's break down your problem and walk through targeted fixes, since you've already ruled out basic consistency and compatibility checks:

1. Fix the "Table not found" Error in Sage ERP #

Even though the table exists, Sage might be failing to access it due to ownership or permission mismatches between SQL 2000 and 2017:

• Check the table's owner: Run sp_help 'TableName' in both 2000 and 2017. If the owner changed (e.g., from a custom user to dbo), update it with:

  sp_changeobjectowner 'OldOwner.TableName', 'dbo'
  

  (Replace OldOwner with the original owner from SQL 2000)
• Verify Sage's database login has explicit permissions: Ensure the account Sage uses has SELECT, INSERT, UPDATE, DELETE rights on the problematic table, plus EXECUTE permissions on any linked stored procedures.
• Check for case sensitivity: If your SQL 2017 collation is case-sensitive (unlikely, but possible), Sage might be querying with the wrong case for the table name. Use SQL Server Profiler to capture the exact query Sage runs—this will reveal if it's looking for tablename instead of TableName.

2. Resolve the .REC File Import Failure (error=0 native code=0) #

That generic error usually points to either a corrupted .REC file or a driver/communication issue between Sage and SQL 2017:

• Re-export the problematic .REC file from SQL 2000: Sometimes exports can get corrupted mid-process. Do this during off-peak hours to avoid network or resource interruptions.
• Split the export: If the table is large, export it in smaller chunks (e.g., by date range or ID) using Sage's export tools. Large datasets can trigger timeouts or buffer issues during import.
• Check SQL Server logs: Look in the SQL Server Error Log (under Management > SQL Server Logs in SSMS) for more detailed errors around the import time. The generic Sage error hides the actual SQL Server issue.
• Update Sage's ODBC driver: Ensure you're using a SQL Server 2017-compatible ODBC driver (ODBC Driver 17 for SQL Server) instead of the old SQL 2000 driver. Sage 5.4 might need a driver update to talk to newer SQL versions.

3. Address the Unexpected Database Size Increase (32GB → 45GB) #

SQL Server 2017's storage engine behaves differently than 2000, which explains the size jump:

• Check log file size: If you imported in full recovery mode, the transaction log might have bloated. Switch to simple recovery mode temporarily, shrink the log, then switch back:

  ALTER DATABASE YourDBName SET RECOVERY SIMPLE;
  DBCC SHRINKFILE (YourDBName_Log, 1);
  ALTER DATABASE YourDBName SET RECOVERY FULL;
  

• Rebuild indexes: SQL 2000 indexes might have migrated with fragmentation. Rebuilding them will optimize space:

  ALTER INDEX ALL ON TableName REBUILD;
  

• Check for unused space: Run sp_spaceused 'TableName' for large tables to see if data or index space is inflated. You can also enable page compression for large tables (if your SQL edition supports it) to reduce size:

  ALTER TABLE TableName REBUILD WITH (DATA_COMPRESSION = PAGE);
  

Final Checks #

Double-confirm your database compatibility level is set to 100 (SQL Server 2008)—Sage 5.4 might not support higher levels like 140 (2017). Set it with:

ALTER DATABASE YourDBName SET COMPATIBILITY_LEVEL = 100;

If none of these steps work, share the SQL Server Error Log entries from the import failure or the Profiler trace of Sage's connection—those will give us more clues to dig deeper.

内容的提问来源于stack exchange，提问作者Iftekhar Ilm