|
FIX: SQL Server FixList for Version 4.20bArticle ID: Q108144Creation Date: 05-DEC-1993 Revision Date: 15-APR-1997
- Microsoft SQL Server version 4.2b for OS/2 The following is a list of fixes and other various improvements that have been made in SQL Server version 4.2b. Version 4.2b includes server patches K6 through K11. SQL Server version 4.2b is now available from your primary support provider. For more information, contact your primary support provider. Please note that workarounds have been provided for your information only. It is not necessary to implement these workarounds if you have the updated software.
List of Problems Corrected in 4.20aK6:
Description of Problems Corrected in 4.20aK6:
FIX: 30-Byte Column Name Causes Error on Select Into ARTICLE ID: Q93908 BUG# OS/2: 1522 (4.2)
SYMPTOMS A column name in SQL Server may legally have up to 30 characters. However, if a column name is exactly 30 characters long, a select into a temporary table with the column will generate error 103:
The identifier that starts with <column name> is too long. Maximum length is 30 characters.For example, the following will generate a 103 error:
create table longname
(abcdefghijklmnopqrstuvwxyz1234 char(4))
go
select abcdefghijklmnopqrstuvwxyz1234 = '1234'
into #temptab
from longname
go
CAUSE SQL Server does not correctly identify a column name as legal if the column name is 30 characters long and referenced in a SELECT INTO statement that is selecting data into a temporary table.
WORKAROUND Use a CREATE TABLE statement to create the temporary table, then an INSERT statement to insert the data:
create table #temptab
(abcdefghijklmnopqrstuvwxyz1234 char(4))
go
insert #temptab
select abcdefghijklmnopqrstuvwxyz1234
from longname
FIX: SELECT CONVERT(char(1), STUFF('',1,0,NULL)) GP Faults ARTICLE ID: Q94567 BUG# OS/2: 1535 (4.2a)
SYMPTOMS The following SELECT statement causes a general protection fault (GP fault):
SELECT CONVERT(char(1), STUFF('',1,0,NULL))
CAUSE SQL Server 4.2a does not handle the NULL correctly in this convert statement.
FIX: Executing Stored Procedure Gives Error 707 ARTICLE ID: Q94723 BUG# OS/2: 1544 (4.2)
SYMPTOMS When executing a stored procedure, an error 707:
system error detected when trying to free memory at address 0x%lxor a protection violation may occur.
WORKAROUND To work around this problem, upgrade to SQL Server version 4.2aK6.
Problem Corrected in 4.20aK7:
FIX: System Cannot Be Restarted with a Full Master..Syslogs ARTICLE ID: Q95336 BUG# OS/2: 1559 (4.2)
SYMPTOMS If a SQL Server is shutdown with a full master..syslogs, it may fail to restart. On the attempted restart, the recovery of the master DB may fail with an 1105 error on master..syslogs, the master DB is marked suspect, and SQL Server terminates.
CAUSE During system initialization, SQL Server recovers all outstanding, uncompleted transactions in all databases. After completing the recovery, SQL Server attempts to write a checkpoint record to the database syslog. If the master..syslogs cannot accept this checkpoint record, SQL Server marks the master DB as suspect and terminates.
WORKAROUND Sites with a version of SQL Server earlier than version 4.2aK7 must contact their primary support provider for instructions on restarting the system. Starting with version 4.2aK7, SQL Server will complete its startup under these conditions. The sa user can then dump transaction master with no log to create space in the master..syslogs table.
List of Problems Corrected in 4.20aK8:
Description of Problems Corrected in 4.20aK8:
FIX: Table Insert from View Might GP Fault Server ARTICLE ID: Q87481 BUG# OS/2: 1426 (4.2)
SYMPTOMS SQL Server general protection faults (GP faults) when inserting into a table using a SELECT statement from a view defined as a two-table join, whose columns contain the aggregate function AVG(). The following is a sample query:
create table t1(c int) create table t2(c int) insert t1 values(1) insert t2 values(1) go create view v (c, av) as select t1.c, avg(t1.c) from t1,t2 where t1.c=t2.c group by t1.c go create table t3(c1 int, c2 int) /* Note c2 is int */ insert t3 select * from v /* Insert AVG() result */ go /* into int column */CAUSE SQL Server incorrectly handles the automatic type conversions when using a view to insert into a table. Note that column c2 in table t3 is defined as int, but the result of AVG() should be float. The problem occurs when trying to insert the result of AVG() into the int column.WORKAROUND The workaround is to declare the column that receives the AVG() result as a float. In the sample query, if you declare column c2 in table t3 as a float, the problem goes away.
FIX: Insert with LIKE and ORDER BY from System Tables ARTICLE ID: Q88569 BUG# OS/2: 1451 (4.2) and 1452 (1.11)
SYMPTOMS SQL Server may cause a general protection fault (GP fault) when a SELECT statement concatenates text characters onto a column value from a certain system table (sysobjects, sysdatabases, or sysdevices) in the master database and assigns the result to a local variable. The GP fault occurs when that SELECT statement contains a LIKE operator and an ORDER BY clause. For example, the following SELECT statement causes a GP fault:
DECLARE @t varchar(255)
SELECT @t="AAA"+name
FROM master..sysobjects
WHERE name LIKE "syso%"
ORDER BY name
GO
CAUSE SQL Server handles the insertion incorrectly when it inserts a value coming from a column value into a local variable while it is using the LIKE operator and the ORDER BY clause to obtain the value from certain system tables in the master database.
WORKAROUND Break the single SELECT statement into two parts. For example, if you want to insert the last row of the qualified data rows into the local variable, break the query into these two parts:
FIX: Select from a View with Recreated Tables May GP Fault ARTICLE ID: Q93420 BUG# OS/2: 1529 (4.2a)
SYMPTOMS When selecting from a view that is defined as a three table join, and if one of these tables has been recreated since the view was defined, SQL Server may general protection fault (GP fault). For example, assume the view is defined as:
create table A (id char(5)) create table B (id char(5)) create table C (id char(5)) go create view V as select A.id from A,B,C where A.id=B.id and A.id=C.id goIf table C is dropped and recreated again after the view is defined (which is allowed), the query:
select * from Vwill cause SQL Server to GP fault.
CAUSE SQL Server incorrectly handles the queries on views that are defined with a three or more object join, if some of the objects are recreated after the views are defined.
WORKAROUND The workaround is to drop and recreate the view.
List of Problems Corrected in 4.20aK9:
Description of Problems Corrected in 4.20aK9:
FIX: Error 511 when INSERT into Large Table ARTICLE ID: Q93237 BUG# OS/2: 1542 (4.2a)
SYMPTOMS When a row is inserted into a table created with a large number of large varchar columns, you may encounter the following error:
Updated or inserted row is bigger than the maximum size allowed for this table. (Msg 511, Level 16, State 2)For example, if you create a table with 127 varchar(255) columns, the table will be created without any problems, but you will get the above error when you attempt to insert the row. A look at the row in sysindexes for the table will show a negative number for maxlen field.
WORKAROUND Try creating the table with smaller size of the varchar columns or reduce the size of the table.
FIX: STR() Function with Length of 255 Causes GP Fault ARTICLE ID: Q94421 BUG# OS/2: 1573 (4.2)
SYMPTOMS Issuing a query that uses the STR() function to convert a floating point number to a string may cause SQL Server to general protection fault (GP fault).
CAUSE SQL Server incorrectly handles the STR() function when a length of 255 is used. For example, the following query will cause a GP fault:
select STR(0.1, 255, 5) goIf a length of less than 255 is used, the query works properly.
FIX: Negative DPAGES May Cause an INSERT/SELECT to GP Fault ARTICLE ID: Q98321 BUG# OS/2: 1588 (4.2)
SYMPTOMS The following query causes a general protection fault (GP fault) in SQL Server 4.2:
INSERT tableA
SELECT colA1
FROM tableA A,
tableB B
WHERE A.colA1 = B.colB1
NOTE: The above query causes a GP fault only if the DPAGES column in
SYSINDEXES for tableA is negative.
WORKAROUND Run DBCC CHECKTABLE on the table. This corrects the dpages count. Note that if DBCC CHECKDB is run regularly, the chances of running into this problem diminish.
FIX: Client Not Notified when Locks Are Exceeded ARTICLE ID: Q95584 BUG# OS/2: 1597 (4.2)
SYMPTOMS When SQL Server runs out of locks, the client application is not notified of the error. However, the following error is logged in the SQL Server ERRORLOG:
SQL Server has run out of LOCKS. Re-run your command when there are fewer active users, or ask your System Administrator to reconfigure SQL Server with more LOCKS. (Msg 1204, Level 19, State 1)CAUSE SQL Server incorrectly handles the out of locks condition, and rather than sending the 1204 error to both the client and the SQL Server ERRORLOG, it only sends it to the ERRORLOG.
FIX: UNION of Two SELECTs with Subqueries May Cause GP Fault ARTICLE ID: Q98323 BUG# OS/2: 1603 (4.2)
SYMPTOMS The following statement may cause a general protection fault (GP fault) in SQL Server version 4.2:
SELECT DISTINCT A.col1
FROM tab A
WHERE 0 <> (SELECT SUM(col2)+SUM(col3)
FROM tab B
WHERE A.col1=B.col1)
UNION
SELECT DISTINCT A.col1
FROM tab A
WHERE 0 <> (SELECT SUM(col2)+SUM(col3)
FROM tab B
WHERE A.col1=B.col1)
MORE INFORMATION The problem occurs when the subquery returns more than one row. In that case, SQL Server should raise error message 512:
Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <=, >, >=, or when the subquery is used as an expressionand terminate the query instead of attempting to evaluate the second SELECT involved in the UNION.
FIX: Uninitialized Variables in Empty Trigger Causes Trap ARTICLE ID: Q95802 BUG# OS/2: 1604 (4.2) and 1178 (1.11)
SYMPTOMS When one or more local variables are declared in a trigger (for UPDATE, INSERT, or DELETE) and no value is assigned to any one of these local variables and there are no other statements defined within the trigger, SQL Server will TRAP D upon execution of the trigger. The TRAP D occurs after the trigger is created and then SQL Server is shutdown and restarted once. After the trap occurs on the server, the client connections will be lost. PSTAT indicates that SQLSERVR.EXE is still running but all other attempts to log into SQL Server will fail until the process executing SQLSERVR.EXE is stopped and then SQL Server is restarted.
WORKAROUND Add an additional valid statement to the trigger, or ensure that all variables are initialized to a valid value.
FIX: CONVERT() Does Not Handle Styles 13 and 113 Correctly ARTICLE ID: Q96094 BUG# OS/2: 1616 (4.2)
SYMPTOMS When converting a DATETIME data type to character with the Transact- SQL CONVERT() function, styles 13 and 113 do not convert as stated in the documentation.
CAUSE In the "Language Reference" guide under CONVERT, it states that styles 13 and 113 should be in the form of:
DD MMM YYYY hh:mi:ss:mmmm(24h)However, style 13 comes out with the month as a number and the year as only the last two digits:
DD MM YY hh:mi:ss:mmmm(24h)and style 113 comes out with the month as a number:
DD MM YYYY hh:mi:ss:mmmm(24h) FIX: 511 on SELECT INTO with over 127 varchar(255) ARTICLE ID: Q97426 BUG# OS/2: 1637 (4.2)
SYMPTOMS A SELECT INTO that references a table with more than 127 varchar(255) columns will fail with the following error:
Error 511: Updated or inserted row is bigger than maximum size (-nnnnn) allowed for this table.(Where -nnnnn is a large negative number.) Assuming a table named "bigtab" has been defined with more than 127 varchar(255) columns, the following SELECT will get the 511 error:
select * into newtab from bigtabThe table newtab will have been created and is fully functional, but no data rows will have been inserted.
WORKAROUND If a 511 error is detected on a SELECT INTO from a table with over 127 varchar(255) columns, a SELECT based INSERT can be used to copy over the data rows to the new table. For example:
insert newtab select * from bigtab List of Problems Corrected in 4.20aK10:
Description of Problems Corrected in 4.20aK10:
FIX: Correlated Subquery with Aggregate f'n Causes GP Fault ARTICLE ID: Q98322 BUG# OS/2: 1596 (4.2)
SYMPTOMS The following query causes a general protection fault (GP fault)in SQL Server version 4.2:
SELECT C.col1
FROM tab1 C, tab1 P
WHERE P.col1 = C.col1 and
P.col2 = C.col5 and
P.col3 = ( SELECT max(col3)
FROM tab1
WHERE col2 = C.col5 )
The query causes a GP Fault only if there is a clustered index on
table tab1 which includes col2 as one of the indexed columns.
WORKAROUND Run update statistics on the table and run the query again. If that does not help, drop the clustered index or replace it with a clustered index that does not include col2. Alternatively, the index could be replaced by a non-clustered index if appropriate.
FIX: SELECT INTO Variable and Table May Trap Server ARTICLE ID: Q101810 BUG# OS/2: 1626 (4.2)
SYMPTOMS Executing a SELECT INTO statement that uses invalid syntax by assigning results to a local variable may trap SQL Server. The following example traps the server:
declare @db_num smallint select @db_num = max(dbid) into #temp from sysdatabasesWORKAROUND The above syntax is invalid. The same effect can be achieved by using either of the following two sets of statements:
declare @db_num smallint select @db_num = max(dbid) from sysdatabasesor
select max(dbid) into #temp from sysdatabasesIf you want to assign to a variable and send data to a new table, use both statements.
FIX: Incorrect Error Message if Device Unavailable ARTICLE ID: Q101728 BUG# OS/2: 1641 (4.2)
SYMPTOMS If a database device is not available to SQL Server during startup, SQL Server should issue error 840:
Device '%. *s' (with physical name '%. *s', and virtual device number %d) has not been correctly activated at startup time. Please contact the System Administrator.Instead SQL Server issues error 822:
Could not start I/O for request %S_BLKIOPTR.This condition usually occurs if the device's .DAT file is deleted while SQL Server is not running. If the .DAT file resides on a network file server, it may occur if the SQL Server server loses its connection to the file server.
FIX: Join and Ordering of Bit and Text Results Traps Server ARTICLE ID: Q98165 BUG: OS/2: 1656 (4.2)
SYMPTOMS When executing a query that involves a join and includes bit and text values in the results set, SQL Server traps. The trap may appear or disappear as rows are added, deleted, or modified in the table.
CAUSE For certain distributions of data involving NULL text columns, a query using a join and returning bit and text data types may cause SQL Server to trap. The trap typically occurs when the last text value returned in the results set is NULL.
WORKAROUND If NULL text columns must be used, ensure that bit and text values are not returned as the results of a single query.
FIX: Store Procedure with UNION and ORDER BY Causes Errors ARTICLE ID: Q98861 BUG# OS/2: 1676 (4.2)
SYMPTOMS A stored procedure that contains a UNION clause where the first SELECT clause has an INTO and the second SELECT clause has an ORDER BY, when executed, will return the following messages to the application:
Internal error -- Unable to open table at query execution time. (Msg 202, Level 11, State 1). A transaction begun in this stored procedure that did updates in tempdb is still active. This will cause a corruption of tempdb that will exist until the server is rebooted. All BEGIN TRANs must have matching COMMITs or ROLLBACKs. (Msg 277, Level 16, State 1). Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = 0, Current count = 1. (Msg 266, Level 16, State 1).Message 611 may also be sent to the errorlog:
Attempt made to end a transaction that is idle or in the middle of an updateAfter one of these messages is generated, the transaction count is increased by one and issuing a COMMIT TRAN terminates the front end while issuing a ROLLBACK that causes the server to GP Fault. For example, the following stored procedure will exhibit the problem:
create procedure testproc as select * into #work from authors union select * from authors order by address go execute testprocWORKAROUND As a workaround, you can avoid the use of a UNION clause by using the temporary table created by the first SELECT clause to insert the results from the second SELECT clause. Finally, you can retrieve results from the temporary table using SELECT and ORDER BY clauses, like:
create proc testproc as select * into #work from authors insert #work select * from authors select * from #work order by address go execute testproc FIX: Uncommitted WRITETEXT May Cause 7902 Error ARTICLE ID: Q99513 BUG# OS/2: 1681 (4.2) and 1682 (1.11)
SYMPTOMS After inserting TEXT data into a table, DBCC CHECKDB and/or CHECKTABLE on that table reports the following error:
Data size mismatch occurred while checking TEXT/IMAGE values. The first page for this value is: <page #>. There were xxx bytes found, which is different from the expected data length of 0 bytes. (Msg 7902, Level 16, State 1)CAUSE For this error to occur, a text column must first be updated and the text value set to NULL. Then, a WRITETEXT command is issued within a transaction to insert data into the text column which was just previously set to NULL. If that transaction is then rolled back, SQL Server does not correctly initializes the text length back to 0 bytes, and a DBCC CHECKTABLE or CHECKDB will report the 7902 error.
WORKAROUND If this error is encountered, one of the following methods can be used to correct it: - DELETE the offending row. - UPDATE the row and set the text column to NULL. - Issue an INSERT or WRITETEXT to insert new data into the text column. FIX: Network Error During RPC May Hang New Connections ARTICLE ID: Q100958 BUG# OS/2: 1695 (4.2)
SYMPTOMS If a serious network error occurs while a site handler is active, and a Remote Procedure Call (RPC) is attempted across that site handler, future connections to the originating SQL Server may stop responding or hang. Current connections are unaffected. On some versions of SQL Server, one additional connection can be made before the new connections stop responding, and on others the problem starts with the next connection. This problem can be replicated by disconnecting the network between two machines that have active site handlers. Initiate another RPC (such as server2...sp_who) and wait for the error to be reported. Now log in to the SQL Server that originated the RPC. If you are able to log in, log out and try again. Existing connections should be OK, however, every attempt to log in to the SQL Server should not respond.
WORKAROUND If you are experiencing this problem, try setting the timeout parameter to False. This causes the site handler to quit after one minute of inactivity. This minimizes the amount of time the site handler is active. Because the problem only happens when a network error occurs, this may minimize the occurrence.
List of Problems Corrected in 4.20aK11:
Description of Problems Corrected in 4.20aK11:
FIX: Join Between Two Tables with 1=2 in WHERE Clause ARTICLE ID: Q96856 BUG# OS/2: 1639 (4.2a)
SYMPTOMS A join of two large tables with 1=2 in the WHERE clause causes SQL Server to general protection fault (GP fault). Each of the tables has 100 int columns. The minimum configuration that can cause the trap is 99 rows in the first table and 233 rows in the second table. Deleting one row or column from either of the tables will not cause a GP fault. Changing the datatype of the columns does not affect the behavior. The following query causes the trap:
select t1.c1 from t1,t2 where t1.c50=t2.c51 and 1=2CAUSE SQL Server does not handle large table joins with 1=2 in the WHERE clause correctly.
WORKAROUND If possible, break up large table into smaller tables with fewer columns, or reduce the number of rows in the table.
FIX: Multiple Procedures in Transaction Causes GP Fault ARTICLE ID: Q97955 BUG# OS/2: 1669 (4.2)
SYMPTOMS Executing a user-defined transaction that calls multiple stored procedures that do inserts, updates, or deletes to tables may cause a general protection fault (GP fault) in SQL Server. The SQL Server errorlog may contain the following warning just prior to the GP fault:
Warning: OPEN OBJECTS parameter may be too low; attempt was made to free up descriptors in localdes(). Run sp_configure to increase parameter value.CAUSE SQL Server incorrectly handles the execution of stored procedures that do updates within a user-defined transaction when the value of open objects is low.
WORKAROUND To workaround this problem, reduce the number of stored procedures that are being executed within the user-defined transaction. If that option is not feasible, use the sp_configure system procedure to increase the value for open objects. This may resolve the GP faults, or may lessen the frequency of them.
FIX: 803 Error on Select Based Insert ARTICLE ID: Q104967 BUG# OS/2: 1692 (4.2)
SYMPTOMS Under certain rare circumstances, a select based insert can receive an 803 error:
Unable to place buffer 0x%ld holding logical page %ld in sdes for object '%s' - either there is no room in sdes or buffer already in requested slot.Under rare circumstances, an insert of the form: insert table803 select * from source_tableon a table with two or more non-clustered indexes can result in an 803 error. This error is very dependent on the data in the indexes at the time the operation is attempted.
WORKAROUND This condition may sometimes be addressed by dropping any non-clustered indexes on the table, then retrying the operation. If it succeeds, the indexes can be recreated and normal operations continued.
FIX: SELECT Local Variable with FOR BROWSE Drops Connection ARTICLE ID: Q104784 BUG# OS/2: 1705 (4.2)
SYMPTOMS A SELECT with an assignment to a local variable in the SELECT clause and a FOR BROWSE clause may trap the server or disconnect the user's connection with an error:
10008 Bad TokenThis only happens when a unique index exists on the table being selected from. For example: DECLARE @ordnum int
SELECT @ordnum = OrderNumber FROM SaleOrder
WHERE OrderNumber = 1
FOR BROWSE
WORKAROUND Do not use the FOR BROWSE clause in a Transact SQL statement when values are being selected into local variables. The FOR BROWSE clause is designed to be used with DB-Library applications, and is not designed to be used in cases like this.
FIX: Clients Hang Issuing Concurrent WRITETEXTs ARTICLE ID: Q104113 BUG# OS/2: 1711 (4.2) and 1712 (1.11)
SYMPTOMS When multiple clients are inserting or updating text and/or image data in a table via WRITETEXT, as well as selecting from that table, one or more of the clients may hang. Issuing an sp_who shows the hung client(s) having a state of Awaiting Command, and sp_lock shows that no locks are preventing the client(s) from continuing. New connections can be established to SQL Server, indicating SQL Server itself is not hung.
CAUSE Under certain conditions, depending on where in the table the concurrent clients are updating and selecting data, SQL Server may cause the client application to hang indefinitely. This can happen with both the logged and non-logged syntax of WRITETEXT.
FIX: SELECT INTO with Outer Join Can Trap Server ARTICLE ID: Q100088 BUG# OS/2: 1713 (4.2a)
SYMPTOMS Using the SELECT INTO command with an outer join between at least two tables and a GROUP BY that includes columns from both tables including a money type from the second table can cause SQL Server to trap with the following SYS1943 error message:
A protection violation has occurred.The minimum conditions required for replication are the following:
/*****************************************************/
/* Set up the two tables */
/*****************************************************/
use pubs
go
drop table t1
go
create table t1
(c1 char(2) null,
c2 varchar(10) null,
c3 varchar(10) null)
go
drop table t2
go
create table t2 (c4 char(2),
c5 money)
go
/**************************************************/
/* The query */
/**************************************************/
/* Need to insert data or query will not crash. */
select c2, c3, c5
into #temp_snapshot
from
t1, t2
where
t1.c1 *= t2.c4
group by
c2, c3, c5
go
FIX: Update Trigger Fails After Upgrading to 4.20K8 ARTICLE ID: Q102416 BUG# OS/2: 1727 (4.2)
SYMPTOMS After upgrading to SQL Server version 4.20k8, an update trigger may fail with the following type of message:
Invalid column name ''. (Msg 207, Level 16, State 3). Invalid column name ''. (Msg 207, Level 16, State 3). Invalid column name ''. (Msg 207, Level 16, State 3).The column names contain indecipherable characters. This can also happen if a dump made on a server running a version earlier than 4.20k8 is loaded into a server running 4.20k8.
WORKAROUND Drop and recreate the UPDATE trigger to avoid this problem.
FIX: Insert Into View Causes GP Fault ARTICLE ID: Q102417 BUG# OS/2: 1729 (4.2)
SYMPTOMS An insert into a view after dropping an underlying table may cause a general protection fault (GP fault). A typical scenario would be as follows:
Make sure you recreate all underlying tables before an INSERT into a view.
FIX: RPC Uses Case Sensitive Passwords ARTICLE ID: Q104785 BUG# OS/2: 1730 (4.2)
SYMPTOMS When trying to initiate a Remote Procedure Call (RPC) from one untrusted server to another, you get error message:
7221, Login to site server2 failed.Other logins on the same server, however, can use the same RPC with no problems.
CAUSE When SQL Server initiates an RPC between two servers who are not in a trusted relationship, the password that the user logged on with will be compared to the password for that id on the remote machine. The comparison of the two passwords will be a character for character comparison. Even if the two servers have been installed with a case insensitive sort order, the comparison of the passwords during the RPC call will be case sensitive.
WORKAROUND Ensure that the passwords on each server are the same case, even though the servers may be case insensitive. When logging in, ensure that your password is entered in the same case as the password on the server.
FIX: String Functions for Char Datatype in a View ARTICLE ID: Q101883 BUG# OS/2: 1734 (4.2)
SYMPTOMS Using string functions RTRIM, LOWER, UPPER, LTRIM, and Reverse on a char datatype column of a View returns the result as padded withblanks to be 256 character in length. Datalength function does return correct length. This does not happen for varchar datatype column in the view. Using the base table instead of the view also returns correct results. For example, in the following script, function RTRIM returns results padded with blanks to be 256 characters in length.
create table T1 (textdat char(30))
go
insert into T1 (textdat) values ('a')
insert into T1 (textdat) values ('ab')
go
create view VT1 (textdat) as select textdat from T1
go
select rtrim(textdat) from VT1 /* incorrect */
go
WORKAROUND Note that the problem does not occur on the varchar datatype columns in a view. Therefore, you may modify your view creation statement as:
create view VT1 (textdat) as
select convert(varchar(30),textdat) from T1
This causes the results to be correctly returned for the string
functions. Alternately, you can also convert the char value to a
varchar before applying the rtrim or other string functions; for
example:
select rtrim(convert(varchar(30),textdat)) from VT1 FIX: Error 4305 Loading Log Dump ARTICLE ID: Q104117 BUG# OS/2: 1741 (4.2) and 1740 (1.11)
SYMPTOMS Loading a transaction log dump may fail with the following 4305 error:
Specified file <dump file> is out of sequence. Current time stamp is <date/time 1> while dump was from <date/time 2>. (Msg 4305, Level 16, State 1)CAUSE SQL Server incorrectly allows a DUMP TRANsaction command to be issued while inside a user-defined transaction. If after dumping the transaction log, the user-defined transaction is rolled back, any attempt to later load that transaction log dump will fail with the 3305 error.
WORKAROUND Do not issue a "DUMP TRANsaction" command while you are inside of a user-defined transaction. To verify this, issue a "select @@trancount". If this returns any value other than zero, your connection is inside of a transaction, and the transaction should either be committed or rolled back before issuing the DUMP TRANsaction command.
FIX: Trap on Insert with Select Union on Same Table ARTICLE ID: Q104101 BUG# OS/2: 1755 (4.2)
SYMPTOMS An insert with a select union on the same table will trap the SQL Server. The following will cause a general protection fault (GP fault) in SQL Server:
create table testtrap (cola int) go insert testtrap select * from testtrap union select * from testtrap FIX: SHOWPLAN ON and SELECT INTO Involving UNION ARTICLE ID: Q95132 BUG# OS/2: 1591 (4.2)
SYMPTOMS Under SQL Server 4.2 and 4.2a, attempting to execute a SELECT INTO statement using the UNION operator will cause the server to general protection fault (GP fault) if SHOWPLAN is set to ON. This behavior is independent of any WHERE or ORDER BY clauses within the SELECT statements.
WORKAROUND By setting SHOWPLAN OFF, the SELECT INTO statement will execute without causing a GP fault.
FIX: Dividing by Negative Money Value Causes Timeslice Error ARTICLE ID: Q95561 BUG# OS/2: 1574 (4.2)
SYMPTOMS When dividing by certain negative money values, the operation may cause SQL Server to generate a timeslice error:
timeslice -1501, current process infected.SQL Server will temporarily lock up until the timeslice is detected. At that time, the process is terminated and SQL Server resumes as normal. Note: Timeslice errors may cause SQL Server version 1.11 to general protection fault (GP fault) (bug# 1574/1575).
CAUSE SQL Server does not correctly handle division of certain negative money values. Values that are know to cause problems are -$6.53, -$6.54, and -$6.55.
FIX: Index Incorrectly Chosen when Highest Key Value Used ARTICLE ID: Q105347 BUG# OS/2: 1666 (4.2)
SYMPTOMS A query that uses the highest key value of an index runs very slowly. Using showplan indicates that the optimizer is not using the index when it should, or using it when it should not. This only happens when the high value is used in a greater than comparison (>), or in the high value of a between statement. Using the highest key in an equality test (=), or a value one above or below will show the index being used correctly.
CAUSE If the highest key value is used in a greater than statement, the optimizer will incorrectly not use a beneficial index, and perform a table scan instead. If the highest key value is used as the high value in a between statement, the optimizer will incorrectly use an index when it should do a table scan. Using the index, in this case, causes more page I/Os to be performed than would be in a table scan, causing the query to run much longer than it should.
WORKAROUND For lookup tables and other tables in which the data content is known beforehand, make sure that the highest value is not used in the comparison operator.
MORE INFORMATION The following script demonstrates the behavior. use pubs go create table optim ( number int not null,
filler char(10) not null)
go
declare @counter int
select @counter=1
while @counter < 768
begin
insert optim values (@counter, 'some text')
select @counter=@counter + 1
end
go
create nonclustered index number_index on optim (number)
go
set showplan on
set statistics io on
go
select * from optim where number > 766
go
select * from optim where number > 767
go
select * from optim where number > 768
go
/* Remove the noexec on if you want to see the io */ /* counts, but at least you will get to see the plan */ /* without getting 70 rows back. */set noexec on go select * from optim where number between 700 and 766 go select * from optim where number between 700 and 767 go select * from optim where number between 700 and 768 go set noexec off go set showplan off set statistics io off drop table optim go
FIX: INSERT INTO a Temporary Table Can Cause GP Fault ARTICLE ID: Q105918 BUG# OS/2: 1760 (4.2)
SYMPTOMS An INSERT INTO a temporary table can cause SQL Server to general protection fault (GP fault).
CAUSE The following circumstances are required in order to receive the GP fault: - The INSERT INTO the temporary table must be within a stored procedure. - You must be inserting into the temporary table using a SELECT statement that returns at least part of the temporary table in question.- It will only occur with specific datasets. |
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.
©1997 Microsoft Corporation. All rights reserved. Legal Notices.