Restrictions for temporary databases

In the Cluster Edition:

• Attempting to access a local temporary database owned by another instance results in this error:

  Error Number : 969
  
  

  You can access database '<local-tempdbname>' only from its owner instance '<owner-instance-name>'. You cannot access local temporary databases from non-owner instances except to use CREATE DATABASE and DROP DATABASE with local system temporary databases.
  

  In general, you must execute stored procedures and commands that access a local temporary database from the owner instance. Alternatively, you can use the following methods to execute the operation on a local temporary database of a remote instance:

  • Use connect to instance_name to connect to the owner instance before you access any of the local temporary databases.

  • Use sp_remotesql to send the Transact-SQL statement to the remote instance. For example, to execute a query on local temporary database local_tempdb_ase1 owned by instance ase1:

    sp_remotesql "ase1", "select * from local_tempdb_ase1..sysobjects"
    

  • For stored procedures, you can execute the procedure specifying the instance name. For example, to execute sp_spaceused on local_tempdb_ase1 for instance “ase1”:

    ase1.local_tempdb_ase1.dbo.sp_spaceused
    

    Use db_instanceid to determine the owner instance ID of a local temporary database.

• Some stored procedures and commands that perform operations on all databases in a nonclustered Adaptive Server, may skip these operations on remote local temporary databases in the Cluster Edition. To make sure these operations occur in all databases, execute the operation again for each of the skipped databases from the owner instance.

  For example, if you execute sp_dbcc_faultreport without specifying a database name, it skips remote local temporary databases from the fault report.

• You cannot grant or revoke user-defined roles to users of a local temporary database.

• You cannot grant or revoke permissions on objects in a local temporary database.

• You cannot use a local temporary database as the default database for any login; any attempt to do so causes sp_addlogin and sp_modifylogin to fail.

• You cannot include a referential integrity constraint that references a column on a local temporary database except from a table on the same local temporary database. create table, alter table, and create schema fail when they attempt to create a reference to a column on a local temporary database from a different database.

• You cannot encrypt a column with an encryption key located in a local temporary database unless the table resides on the same local temporary database. create table and alter table fail when they attempt to encrypt a column with an encryption key located on the local temporary database and the table resides in another database.

• You cannot specify database recovery order for any local system temporary database. Local system temporary databases are user-created, but they are always recovered with system databases.

• You cannot use sp_configure to change configuration options from inside the local temporary database context.