This section includes error messages for the Adaptive Server Sequencer. The Sequencer is the subsystem that controls command execution. The Sequencer handles the processing between steps in commands and also handles the procedural steps between commands such as if, goto, and while.
16
Invalid column name '%.*s'.
This error occurs when Adaptive Server tries to use a column name that does not exist.
Adaptive Server supports delimited identifiers for table, view, and column names. Delimited identifiers are object names enclosed within double quotation marks. Using them allows you to avoid certain restrictions on object names. Delimited identifiers can begin with nonalphabetic characters, including characters that would not otherwise be allowed. Delimited identifiers may even be Transact-SQL reserved words.
Delimited identifiers may cause some SQL statements to fail. For example, Error 207 would be raised for the column “none” in the following example:
1> select au_name, "none", au_fname 2> from pubs2..authors 3> go
Check the spelling of the column name specified in the error message. If it is wrong, correct it in your SQL statement and run it again.
If this error message is being displayed because of delimited identifiers, either use single quotes instead of double quotes or turn delimited identifiers off. To turn delimited identifiers off, do the following:
1> use database_name 2> go
1> set quoted_identifier off 2> go
When delimited identifiers is turned on, delimiting strings with double quotes causes Adaptive Server to treat them as identifiers.
Refer to the Transact-SQL User's Guide for information about delimited identifiers.
All versions
16
%.*s not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
This error occurs when Adaptive Server tries to use an object name that does not exist. If the object does exist, you may need to include the owner's name in the object name.
If the object is not owned by the user who is attempting to access it, and it is not owned by the database owner (“dbo”), all references to it must include the owner name. For example, if “user1” creates a table called test, all other users must prefix the table name with the owner name “user1” every time they refer to the object. The complete name in this example is user1.test. This requirement is based on Adaptive Server's naming convention for database objects:
database.owner.object_name.column_name
The default value for database is the current database and the default value for owner is the current user. Remember that the owner is part of the object name and it is therefore possible for two different users to have two different tables with the same object_name in the same database, for example, user1.test and user2.test. Refer to the Transact-SQL User's Guide for more details on naming conventions.
Temporary tables reside in tempdb and are automatically dropped when the user process or Adaptive Server connection that created them is exited. Furthermore, users cannot share temporary tables, even if they are created by the “dbo.”
Including the fully qualified object name in an application may complicate the maintenance of the application. For example, if all references to a table include the database name, changing the database name could become quite difficult.
To resolve this error, refer to one of the following sections, depending on how much you know about the object in question.
You can either use the sp_help procedure to display the owner (if the procedure is executed with no parameters), or query the system catalog to determine the object's owner and type. For example, to determine the name and type of the object table1, use the following query:
1> select owner = user_name(uid), name, type 2> from sysobjects where name = "table1" 3> go
If no rows are returned by this query, the object may reside in a different database or may not exist at all.
Avoid this error using either procedure:
Include the owner's name in the object name. For example:
1> select * from user1.table1 2> go
Have the “dbo” create the object. This allows any user to find the object without specifying an owner name.
The error can be avoided by:
Moving to the correct database with the use database_name command.
Fully qualifying the object name with the database name. For example:
1> select * from database1.user1.table1 2> go
The owner name is not needed if you own the object or if it is owned by the “dbo.” For example:
1> select * from database1..table1 2> go
The appropriate permissions must also be set to allow access to this object. If these permissions are not provided, a 229 or 230 error results.
All versions
16
Insert error: column name or number of supplied values does not match table definition.
This error occurs when there is a mismatch between the information specified in an insert statement and the definition of the table.
Following are some examples of insert statements for the pubs2.titles table that cause this error (assuming that the titles table has nine columns):
No column names are specified after the table name in an insert statement and there are two or more columns in the table.
1> insert titles values ('this is a test')
2> go
Msg 213, Level 16, State 4: Line 1: Insert error: column name or number of supplied values does not match table definition.
No values are specified in an insert statement.
1> insert titles (title_id, title) values () 2> go
Msg 213, Level 16, State 3: Line 1: Insert error: column name or number of supplied values does not match table definition.
Check your insert statement to make sure you have specified column names and values for the columns.
If this error message is being displayed while an application is being run, use the following information to determine which insert statement is causing the problem.
Data Workbench
To see the SQL text being sent by Data Workbench to Adaptive Server, use the environment variable RECTFOS. When RECTFOS is defined, the application records the SQL text in a file that you can review for debugging a new application or diagnosing other problems.
Before starting Data Workbench, set the value of RECFTOS to the file name where you want the text to be saved:
In UNIX, type:
% setenv RECFTOS filename
In OpenVMS type:
$ define recftos filename
A new file is created for each connection made to Adaptive Server. The files are named sequentially as each connection is opened: filename.0;1 (OpenVMS), filename.1 (UNIX), and so on.
This feature is present in Data Workbench 2.2 and later releases.
Use unsetenv (UNIX) or deassign (OpenVMS) to turn off logging when you no longer need to save the SQL text being sent.
Open Client Applications
To see the SQL text being sent by Open Client applications, use the dbrecftos call. Refer to the Open Client DB-Library Reference Manual for details.
All versions
20
Attempt to automatically drop temporary table failed.
Adaptive Server goes through four phases while processing a query:
Parsing – checks the query to make sure the syntax is correct
Normalization – resolves the column and table names and generates a query tree
Compilation – performs optimization and generates a query plan
Execution – carries out the query plan
During the first three phases of query processing, Adaptive Server creates two types of temporary tables:
Temporary tables to aid in table resolution. This is necessary if the table will not exist until the command has been executed. For example:
1> create table x [information about table x] 2> select * from x 3> go
In this case, Adaptive Server creates a temporary copy of table x so it can resolve the select command.
Other temporary tables prefaced with “#”, that exist for the life of the session (or the life of the procedure, if they are created in the procedure).
Error 216 occurs when Adaptive Server is unable to drop one of the temporary tables it created. It is probably caused by an Adaptive Server problem.
Error 216 occurs with the following states:
State |
Meaning |
|---|---|
1 |
During the renormalization phase, if Adaptive Server cannot drop a temporary table it created, Error 216 occurs with State 1. These are definition-time temporary tables. |
2 |
During the normalization phase, if Adaptive Server cannot drop a temporary table it created during the parsing phase, Error 216 occurs with State 2. |
3 |
During an abort of the normalization phase, if Adaptive Server cannot drop a temporary table, Error 216 occurs with State 3. |
4 |
When a process is killed, if Adaptive Server fails to clean up temporary tables, Error 216 occurs with State 4. These can be definition-time or normal temporary tables. |
5 |
If there is an error during the normalization phase, and Adaptive Server cannot drop a temporary table it created during normalization, Error 216 occurs with State 5. |
6 |
Similar to State 5. |
7 |
During recompilation of a lightweight procedure, if Adaptive Server is unable to drop a definition-time temporary table, Error 216 occurs with State 7. |
9 |
If there is an error during creation of a temporary view, and Adaptive Server cannot drop a temporary table it created during normalization, Error 216 occurs with State 9. |
10 |
When processing nested SQL (for example, nested SQL statements within a Java method), if Adaptive Server cannot drop a temporary table at cleanup, Error 216 occurs with State 10. |
11 |
Similar to State 10. |
12 |
When processing client or language cursors, if Adaptive Server cannot drop a temporary table at cleanup, Error 216 occurs with State 12. |
13 |
When regenerating a stored procedure during upgrade, if Adaptive Server cannot drop a normalization-time temporary table at cleanup, Error 216 occurs with State 13. |
14 |
Similar to State 13, but it applies to definition-time temporary tables. |
Temporary tables left behind are not in themselves a problem. However, they do take up space in tempdb, and tempdb may eventually fill up. If you think this is a potential problem, shut down and restart Adaptive Server. This will re-create tempdb and thus drop all temporary tables.
216 can result from running out of locks. Check the ASE error log; if the 216 error is preceded by 1204 errors in the log, you may not have enough locks configured. Follow the instructions in the 1204 writeup elsewhere in this manual.
If Error 216 persists, call Sybase Technical Support.
All versions
11
Cannot run query--referenced object (name %.*s) dropped during query optimization.
This error occurs when Adaptive Server fails to build an execution plan for a stored procedure or an ad-hoc query because a referenced object was not found.
Some reasons for this error include:
The value of the number of open objects configuration parameter is too low.
A problem exists in Adaptive Server.
This is not a serious error as it does not indicate corruption. However, it may prevent you from running some stored procedures and ad-hoc queries.
This error usually means a problem has occurred with Adaptive Server.
Using sp_configure, review the value of the configuration parameter number of open objects and increase this value if feasible. For information about memory-related configuration parameters, refer to:
“Setting Configuration Parameters” in the System Administration Guide
Performance and Tuning Guide
If you cannot increase the value of number of open objects or the 225 error occurs again, call Sybase Technical Support for assistance.
All versions
16
%s command not allowed within multi-statement transaction.
SQL commands are grouped into the following categories:
SQL commands that are not allowed in transactions at all.
SQL commands, such as Data Definition Language (DDL) commands, that are allowed in transactions only if the required database option (ddl in tran) is set to TRUE.
SQL commands that are allowed only if the transaction affects some other database. These commands include create table, drop table, and other commands that are run across databases to create or drop objects in another database when the database in which the objects are being created or dropped has the database option ddl in tran set to TRUE.
Error 226 occurs when Adaptive Server detects a command that is not allowed in a multi-statement transaction. A multi-statement transaction is a set of commands prefaced with the begin transaction command.
The following commands are never allowed in multi-statement transactions:
alter database
create database
dbcc reindex, dbcc fix_text
disk init
drop database
dump database, dump transaction
load database, load transaction
select into
set transaction isolation level
truncate table
update statistics
setuser
The following commands are not normally allowed in multi-statement transactions but you can use them if you use sp_dboption to set ddl in tran to TRUE first:
create default, create index, create procedure, create rule, create schema, create table, create trigger, create view
drop default, drop index, drop procedure, drop rule, drop table, drop trigger, drop view
grant
revoke
If the command is allowed in a multistatement transaction when ddl in tran is set to TRUE, set ddl in tran to TRUE before running the transaction. Setting ddl in tran to TRUE causes locks on system tables and this can affect performance. You can check the current setting of ddl in tran with sp_helpdb.
If the command is never allowed in a multi-statement transaction, execute it outside the multi-statement transaction.
Some applications take SQL statements as input and run them for you. If the application uses begin and commit or rollback transaction to surround those statements, Error 226 may occur. Refer to the user guide for your application to determine if this is the case.
All versions
14
%s permission denied on object %S_OBJID, database %S_DBID, owner %.*s
This error occurs when Adaptive Server attempts to access an object for which you do not have the appropriate permission.
Error 229 can occur during any transaction which involves objects, such as bcp, select, or update.
This error causes a rollback of the current transaction. Remaining commands in the batch are not executed and processing continues at the next batch.
Check permission on the object named in the error message:
1> use database_name 2> go
1> sp_helprotect object_name 2> go
where database_name is the name of the database in the error message and object_name is the name of the object in the error message.
Grant the needed permission to the object (you either need to own the object or be the “sa” to do this). For example, to give Mary permission to insert into and delete from the titles table, type the following:
1> grant insert, delete 2> on titles 3> to mary 4> go
Refer to “Managing User Permissions” in the Security Administration Guide for detailed information about permissions.
All versions
16
Arithmetic overflow error for type %s, value = %f.
This error occurs when Adaptive Server detects an arithmetic overflow error during execution of a SQL statement. Arithmetic overflows occur when a variable has too few places to store the converted data. In particular, Error 232 is raised when an overflow occurs for a variable of datatype float.
Error 232 can occur during execution of the convert function. It can also occur during implicit conversion within Adaptive Server, either during a stored procedure run or during execution of a SQL statement which converts a float value to some other datatype.
Determine which variable is causing the problem based on the datatype reported in the error message output and the SQL statement on which the error occurred. If the error occurs within a stored procedure, an additional error message will be displayed showing the name of the stored procedure and the line number where the error occurred.
Increase the size of the variable that is causing the error. If it is in a procedure, re-create the procedure.
If this error occurs on a Sybase-supplied system stored procedure, contact Sybase Technical Support.
All versions
16
The column %.*s in table %.*s does not allow null values.%S_EED
During query processing, Adaptive Server performs an action called normalization, during which it resolves column and table names and generates a query tree. During normalization, when Adaptive Server prepares the target list for processing an insert or update, it checks to make sure nulls are allowed. Error 233 will be raised at compile time if, from within a stored procedure, you try to insert null values in a column which does not allow nulls.
Error 233 is raised with the following states:
State |
Meaning |
|---|---|
1 |
Nulls are not allowed, the column is in the target list, and the value being inserted is null. |
2 |
No default exists, nulls are not allowed, and the column is not in the target list. |
3 |
For inserts to temporary tables, Adaptive Server checks to be sure all “not null” columns are listed explicitly. If a “not null” column is not listed in the insert statement, the error is raised with State 3. |
When you create a table, you can explicitly define whether each column should allow null values. If you do not specify NULL or NOT NULL for a column when you create the table, the default value will be NOT NULL. If you use sp_dboption to set allow nulls by default to TRUE for the database, any new table that is created will have columns with the default value NULL.
To determine whether a column has NULL or NOT NULL defined, enter commands like the following, replacing “pubs2” by the database in which your table resides and “titleauthor” by the table in which your column resides:
1> use pubs2 2> go
1> sp_help titleauthor 2> go
Name Owner Type ------------------------------------------------------------ titleauthor dbo user table Data_located_on_segment When_created ------------------------------ ----------------------------- default Oct 27 1994 10:09AM
Column_name Type Length Prec Scale Nulls Default_name Rule_name Identity ------------------------------------------------------------------------ au_id id 11 NULL NULL 0 NULL NULL 0 title_id tid 6 NULL NULL 0 NULL NULL 0 au_ord tinyint 1 NULL NULL 1 NULL NULL 0 royaltyper int 4 NULL NULL 1 NULL NULL 0
The Nulls column indicates whether null values are allowed. A value of 0 for the column means nulls are not allowed; a value of 1 means null values are allowed.
To change the default for a column from NOT NULL to NULL:
Use bcp to copy the data out of the existing table.
If you want the modified table to have the same name as the existing table, drop the old table.
Re-create the original table, specifying NULL for the column you want to change.
Use bcp to put back the data for the table.
If you want new tables in the database to have the default NULL, use the following commands for the database:
1> use master 2> go
1> sp_dboption database_name, 2> "allow nulls by default", true 3> go
1> use database_name 2> go
1> checkpoint 2> go
where database_name is the name of the database whose behavior you want to change.
All versions
16
Scale error during %S_MSG conversion of %s value ’%s’ to a %s field.
Error 241 occurs when ASE attempts to convert a given value from one numeric or decimal datatype to another, but is unable to do so because the scale of the receiving field (the "to" field in the message) is not large enough to accommodate the converted value.
The error is raised during implicit conversions, which occur when Adaptive Server performs certain types of comparisons, and while the server stores and retrieves data. Adaptive Server automatically handles conversions between compatible datatypes, but may be unable to handle the numeric or decimal conversion due to the reasons mentioned above.
For example:
1> declare @numvar numeric(5,2) 2> select @numvar = 38.4434 3> go
Msg 241, Level 16, State 2: Server ’vin_125_ocstest’, Line 2: Scale error during implicit conversion of NUMERIC value ’38.4434’ to a NUMERIC field.
The scale of the numeric field numvar (2) is too small to accept the inserted value.
Options for resolving the error include
Setting arithabort numeric_truncation off, OR
Using the convert function, OR
Declaring a large enough scale explicitly.
Setting arithabort off
You can use arithabort to direct Adaptive Server not to abort the query due to a numeric truncation error:
1> set arithabort numeric_truncation off 2> go 1> declare @numvar numeric(5,2) 2> select @numvar = 38.44*.72 3> select @numvar 4> go
(1 row affected) -------- 27.67
Using the convert function
You can use the general-purpose convert function to specify the output:
1> declare @numvar numeric(5,2) 2> select @numvar = 38.44 3> select @numvar=convert(numeric(5,2),@numvar*.72) 4> select @numvar 5> go
(1 row affected) (1 row affected) -------- 27.67 (1 row affected)
Declaring the Variable’s Scale
You can declare an appropriately large scale when defining the variable. Instead of
1> declare @x decimal 2> select @x = 12.345
use
1> declare @x decimal (5,3) 2> select @x = 12.345
The set arithabort and set arithignore options specify behavior following loss of precision or (for numeric and decimal types) loss of scale. For more information see:
"Arithmetic Errors" in the Transact-SQL User’s Guide.
"System and User-Defined Datatypes" in the Reference Manual.
In writing your SQLJ java methods (ASE 12.5 and higher), you can avoid 241 errors by making sure that input parameters for the function calling the java method use the proper scale and precision, as described in Declaring the Variable’s Scale under Action above.
All versions
16
Arithmetic overflow during %S_MSG conversion of %s value '%s' to a %s field.
Error 247 occurs when Adaptive Server attempts to convert a given value from one data type to another, but is unable to do so because of incompatibilities between the datatypes, or when the receiving field (the "to" field in the message) does not have a range large enough to accomodate the converted value.
The error may be raised during either explicit or implicit conversions. Explicit conversions occur when a query explicitly requests a conversion with the convert, inttohex, or hextoint functions.
Implicit conversions occur when Adaptive Server is required to perform certain types of comparisons between heterogeneous datatypes, and when the server stores and retrieves data. Adaptive Server automatically handles many such conversions from one datatype to another, but may be unable to handle the conversion due to the reasons mentioned above.
For example:
1> create table deliver_dates 2> (itemno int, 3> dlydate smalldatetime) 4> go 1> insert into deliver_dates 2> values (42298, '12/12/2080') 3> go
Line 1: Arithmetic overflow during implicit conversion of VARCHAR value '12/12/2080' to a SMALLDATETIME field.
The date value being inserted is outside the range of SMALLDATETIME.
Check the following problem areas when you see Error 247:
Is the range of the receiving datatype large enough to accomodate the converted value?
Are the sending and receiving datatypes (the "of" and "to" datatypes in the message, respectively) compatible? For details refer to "Datatype Conversion Functions" in the Reference Manual.
Is the query attempting to display a FLOAT value by converting it to numeric datatype? FLOATs can only be displayed this way if no decimal digits are lost. Try increasing the precision of the numeric field so that this condition is met.
If the query is updating a table, is there a trigger on the table that attempts to place the new value into another table? The receiving field in the second table may not have the correct range for the operation.
Have the following information ready when you call Sybase Technical Support:
Server version and SWR rollup level
Text of all error messages
Text of the query which produces the error.
All versions
16
Implicit conversion from datatype '%s' to '%s' is not allowed. Use the CONVERT function to run this query.
When performing certain types of comparisons, and while storing and retrieving data, Adaptive Server automatically handles many conversions from one datatype to another. These are called implicit conversions. You can explicitly request other conversions with the convert, inttohex, and hextoint functions.
Error 257 is raised when Adaptive Server is unable to do an implicit conversion because of incompatibilities between the datatypes.
Corrective action depends on the exact situation in which the error occurred. Here are some typical situations where Error 257 is raised, and how you can correct the problem:
If you attempt to perform comparisons on integer data with the like keyword; you must use the convert function on integer data if you want Adaptive Server to treat it as character data for comparisons.
If you are using embedded SQL, and Adaptive Server is unable to perform an implicit conversion between a database column and the corresponding host language variable. Consider using a different host variable to accomplish the conversion. For example, in a COBOL application a column of money datatype requires a COMP-2 host variable.
If you attempt to insert quoted values for integer data. For example the following will raise Error 257:
1> create table citycodes 2> (cityname char(12), citycode smallint) 3> go
1> insert into citycodes
2> values ('Detroit', '123')
3> go
Quotes are not allowed around the city code in this insert statement.
When a statement implicitly exceeds the maximum length of a character datatype. For example, suppose columnX is defined as varchar(255). If you submit a query like
1> select * from tableX 2> where columnX like '.....%'
and there are 255 characters preceding `%', Error 257 is raised. Adaptive Server treats the comparison string as containing more than 255 characters and assumes that it is a TEXT datatype, which it cannot convert implicitly.
For detailed information about datatype conversions, refer to the section “Datatype Conversion Functions” of “Using the Built-In Functions in Queries” in the Reference Manual.
Before calling Technical Support, have the following information available:
The SQL statement generating the error
sp_help for any tables involved
Server version and SWR Rollup level
Server error log
Text of all error messages
All versions
16
Ad-hoc updates to system catalogs not enabled. A user with System Security Officer (SSO) role must reconfigure system to allow this.
System tables are all the Sybase-supplied tables in the master database and in each user database that begin with “sys” and have an ID value in the sysobjects table of less than or equal to 100. The system catalog is the collection of system tables.
Error 259 occurs when you try to directly modify an Adaptive Server system table without first using sp_configure to set the allow updates configuration parameter. Setting this variable allows the System Administrator (“sa”) to make changes to system tables.
WARNING! Serious problems can result from an incorrectly modified system table! Make changes only if Sybase Technical Support or this manual instructs you to do so.
Do not use any of the procedures in this section before reading all warnings and cautions! Incorrect alterations of a system table can result in database corruption and data loss.
To successfully modify the system catalog as directed by Technical Support or this guide, use the following procedure:
As “sa,” use sp_configure to allow changes to the system catalog:
1> sp_configure "allow updates", 1 2> go
Make changes to the system catalog as directed by Technical Support or elsewhere in this guide. Always use begin transaction when changing a system table, to protect against errors which could corrupt your database.
WARNING! Be sure to include the next step to protect your system.
Immediately following the changes, use sp_configure to disallow changes to the system catalog (the normal state for Adaptive Server):
1> sp_configure "allow updates", 0 2> go
You must return Adaptive Server to its normal state, or anyone who can log on as “sa” can make changes to the system tables, or create stored procedures which do so.
If you update sysusages, sysobjects, sydatabases, or sysdevices, restart Adaptive Server. You need to do this because structures related to those tables are stored in cache and will not be updated until you restart Adaptive Server.
Refer to “allow updates to system tables” configuration parameter in the System Administration Guide and “sp_configure” in the ASE Reference Manual for more information about (sp_configure) allow updates.
All versions
16
Insufficient result space for %S_MSG conversion of %s value '%s' to a %s field.
Error 265 can be raised in the following situations:
Conversions to character data fails with Error 265 if you attempt a conversion that would cause truncation of data.
Error 265 can also be raised in some conditions when certain system stored procedures are executed.
Examples and corrective action are provided in the next section.
The following sections describe some scenarios under which Error 265 occurs and ways of dealing with those situations.
Conversions of integer to character data will fail if the target format is not large enough to accommodate the data:
1> select convert (char(1), 500) 2> go
Msg 265, Level 16, State 1: Server 'mfg1', Line 1: Insufficient result space for explicit conversion of INT value '500' to a CHAR field.
To correct this problem, choose a larger target format (char(3) in the example).
Conversions of floating point to character data will fail if the target format is not large enough to accommodate the data:
1> select convert (char(10), 3.1415e) 2> go
Msg 265, Level 16, State 1: Server 'mfg1', Line 1: Insufficient result space for explicit conversion of FLOAT value '3.1415000000000002' to a CHAR field.
As in this example, the trailing part of the source value reported in the error message can be different from the entered value. This is because FLOAT is an approximate numeric datatype whose internal representation (and rounding upon display) are platform-dependent.
To correct this problem, choose a larger target format for display. Use the str function to determine the necessary format. The required format varies depending upon the number being converted and the accuracy of floating point numbers supported by your platform. To guarantee success, use a target of 25 characters.
If loss of precision (rather than display format) is a concern in the application, consider using an exact numeric datatype such as integer, numeric or decimal.
Refer to the Transact-SQL User's Guide for information about the str function.
All versions
10
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = %ld, Current count = %ld.
When a stored procedure is run, Adaptive Server maintains a count of open transactions, adding 1 to the count when a transaction begins, and subtracting 1 when a transaction commits. When you execute a stored procedure, Adaptive Server expects the transaction count to be the same before and after the stored procedure execution. Error 266 occurs when the transaction count is different after execution of a stored procedure than it was when the stored procedure began.
The 266 error occurs most often when stored procedures are executed in chained mode. In chained mode, if there is no explicit begin transaction statement, an implicit begin transaction is issued. A begin transaction statement, whether implicit or explicit, increments the session transaction count by 1.
In chained mode, the following statements cause an implicit begin transaction to be issued: select, update, delete, insert, and fetch. (The fetch command begins a transaction only when close on endtran is off in chained mode and cursors can remain open across transactions. Refer to “set” in the Reference Manual for a discussion of the close on endtran option of the set command.)
Each begin transaction statement in a stored procedure, whether implicit or explicit, must be balanced by a commit transaction statement, which decrements the session transaction count by 1. Otherwise, the transaction count remains higher when the stored procedure exits than it was at the beginning of execution.
In addition, Error 266 occurs when you are using nested procedures, and procedures at each level of nesting include begin, commit, and rollback transaction statements. If a procedure at a lower nest level opens a transaction and one of the called procedures issues a rollback transaction, Error 266 occurs when you exit the nested procedure. The following example illustrates this situation:
1> create procedure proc1 2> as 3> begin transaction . . execute proc2 4> commit transaction 5> go
1> create procedure proc2 2> as 3> begin transaction . . 4> rollback/commit transaction 5> go
If proc2 executes a rollback transaction, it returns with Error 266.
In the following example, execution of the stored procedure “test” results in a 266 error:
1> use pubs2 2> go
1> create procedure test as 2> select * from titles 3> go
1> sp_procxmode test, chained 2> go
1> set chained on 2> go
1> execute test 2> go
The transaction count is 0 before executing stored procedure “test”. When the stored procedure is executed, its select statement causes a transaction to begin implicitly. Since there is no balancing commit transaction for the implicit begin transaction the transaction count is not decremented in the stored procedure. The stored procedure returns to the caller with a transaction count of 1. A non-fatal 266 error is raised.
To balance the implicit begin transaction within a stored procedure, issue a commit statement before exiting the stored procedure, as in the following example:
1> use pubs2 2> go
1> create procedure test as 2> select * from titles 3> commit transaction 4> go
1> sp_procxmode test, chained 2> go
1> set chained on 2> go
1> execute test 2> go
The transaction count is 0 before stored procedure execution. When the stored procedure is executed, the select results in an implicit begin transaction and the transaction count is incremented to 1. The commit transaction in the stored procedure decrements the transaction count to 0. The transaction count is the same at the end of stored procedure execution as it was in the beginning.
You can use the @@trancount global variable to check the transaction count at the beginning and end of the procedure. If the count is not equal, commit or roll back transactions as appropriate.
It is safer to commit within the stored procedure than
to open the transaction explicitly before executing the stored procedure.
Opening the transaction before executing the stored procedure carries
the risk of leaving a transaction open if the execute procedure
statement fails and causing other problems as your procedures get
more complicated.
For more information on transactions within stored procedures and the @@trancount global variable, refer to the section “Checking the Transaction Nesting Level with @@trancount” of “Using Batches and Control-of-Flow Language” in the Transact-SQL User's Guide.
All versions
16
You can't run SELECT INTO in this database. Please check with the Database Owner.
This error occurs when you try to add rows to a table via the select into clause in a database that has the sp_dboption option select into/bulkcopy parameter disabled.
If this error occurs while trying to select into a temporary table, or while running a Sybase-supplied stored procedure, it is likely that the select into/bulkcopy option is not enabled in tempdb. Some stored procedures, for example sp_help and sp_helpsort, trigger this error because they select into temporary tables (which all belong to tempdb) to get reports from the system tables, and to update them. If tempdb does not have the sp_dboption option select into/bulkcopy enabled, Error 268 will occur.
When you install Adaptive Server, the select
into/bulkcopy option is enabled in tempdb and
turned off in all other databases.
The sp_dboption option select into/bulkcopy must be enabled to perform nonlogged operations. For example:
Performing a select into a permanent (nontemporary) table.
Performing a fast bulk copy with the bcp utility. Tables without triggers or indexes use the fast version of bcp by default, that is, they are not logged in order to save time.
Executing the Transact-SQL command writetext (using the with log option of writetext causes it to be logged, so the select into/bulkcopy parameter is not needed).
Executing the DB-Library routine dbwritetext.
WARNING! You cannot dump the transaction log to a device after performing a nonlogged operation. Always perform a dump database after the nonlogged operation is complete, since performing nonlogged operations leaves changes recorded in the transaction log unrecoverable.
Ask the System Administrator (“sa”) or database owner (“dbo”) to enable the sp_dboption option select into/bulkcopy for all databases affected by the query (including tempdb if applicable). The “sa” or “dbo” can use this procedure:
Use the master database and change the option:
1> use master 2> go
1> sp_dboption database_name, 2> "select into/bulkcopy", true 3> go
1> use database_name 2> go
1> checkpoint 2> go
To verify that the change is active, use sp_helpdb:
1> sp_helpdb database_name 2> go
In the output, the phrase select into/bulkcopy should appear, indicating that the option is enabled.
WARNING! Dump your database before disabling the select into/bulkcopy option. If you insert nonlogged data (via a nonlogged operation) into the database, you will not be able to dump the transaction log to a device, and will not be able to recover your data.
By default, the select into/bulkcopy option is disabled when a database is first created. To change this default, use the procedure above to allow the option in the model database.
If a table has indexes or triggers, then bcp will not run in the fast mode, so you do not need to set the select into/bulkcopy option to run bcp.
You cannot dump the transaction log to a device after performing a minimally logged operation. Trying to dump the transaction log after a minimally logged operation causes error messages that instruct you to dump the database instead.
All versions
16
There was a transaction active when exiting the stored procedure '%.*s'. The temporary table '%.*s' was dropped in this transaction either explicitly or implicitly. This transaction has been aborted to prevent database corruption.
When exiting a stored procedure, Adaptive Server checks to see whether there is an active transaction (a transaction that has not been committed) and then checks to see whether any temporary objects exist for that uncommitted transaction.
If temporary objects exist, the transaction is rolled back, the temporary objects are dropped, and Error 277 is raised.
In stored procedures, make sure all begin transaction statements have corresponding commit transaction or rollback transaction statements.
In chained mode, if there is no explicit begin transaction statement, an implicit begin transaction is issued. If you are using chained mode, an explicit commit transaction or rollback transaction statement is required to end the transaction.
If you confirm that all begin transaction statements have corresponding commit transaction or rollback transaction statements, check to see whether the stored procedure is exiting without completing its processing.
All versions
