SQLSYSTEMVARIABLES

When restoring an Axapta 3.0 database from a different server, you might have experienced the following error message:

[Microsoft][ODBC Database Server Driver][SQL Server] Invalid object name ‘SQLSYSTEMVARIABLES’.

The problem is your database user (default bmssa) can not access the SQLSYSTEMVARIABLES table. If the original and new SQL server use the same user id, the following query analyzer command should fix it:

exec sp_change_users_login 'update_one','bmssa','bmssa'

If that doesn’t help or user ids are different, try changing the database and object owners.

exec sp_changedbowner 'bmssa'

changes the owner of the database. I have used this script to change the owner of tables:

DECLARE @oldOwner sysname, @newOwner sysname, @sql varchar(1000)



SELECT

@oldOwner = 'AX30'

, @newOwner = 'bmssa'

, @sql = '

IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES

WHERE

QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''

AND TABLE_SCHEMA = ''' + @oldOwner + '''

)

EXECUTE sp_changeobjectowner ''?'', ''' + @newOwner + ''''



EXECUTE sp_MSforeachtable @sql


And Another one for the views:

DECLARE @oldOwner sysname, @newOwner sysname, @sql varchar(1000)



SELECT

@oldOwner = 'AX30'

, @newOwner = 'bmssa'

, @sql = '

IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.VIEWS

WHERE

QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''

AND TABLE_SCHEMA = ''' + @oldOwner + '''

)

EXECUTE sp_changeobjectowner ''?'', ''' + @newOwner + ''''



EXECUTE sp_MSforeachtable @sql