Microsoft KB Archive/115402

{|
 * width="100%"|

FIX: SQL Server FixList for Version 4.21
'Article ID: Q115402

Creation Date: 25-MAY-1994

Revision Date: 29-APR-1997' 

The information in this article applies to:


 * Microsoft SQL Server, version 4.21

The following is a list of fixes and other various improvements that have been made in SQL Server version 4.21. SQL Server version 4.21 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.21

This article lists the server and tools fixes supplied with SQL Server for Windows NT version 4.21. The 4.21 server also includes the fixes originally supplied with the SQL Server for Windows NT version 4.20P33 server, please review Knowledge Base Article \[\[..\/\|Q\]\] for those fixes. SQL Server for Windows NT version 4.21 also includes DB-Library (DB-Lib) version 4.21.00. See Knowledge Base Article Q115076 for a list of the DB-Lib fixes supplied with version 4.21.00.


 * FIX: Server Not Responding to dbcancel
 * FIX: RETURN Statement After an RPC Causes Extra Result Set
 * FIX: SQL for NT Admin Backup Event
 * FIX: sp_password Does Not Validate Old Password
 * FIX: Trusted RPCs Do Not Work with Different Passwords
 * FIX: Error 816 When Many Updates Performed
 * FIX: Tape Dump's Date Is 0th Day of the Month
 * FIX: Monthly Scheduled Backup Skips Alternate Months
 * FIX: Float Data May Cause Data-Conversion Overflow in ISQL/w
 * FIX: Dump File Archive Attribute Not Set if Previously Cleared
 * FIX: Server Must Be Cycled After sp_renamedb
 * FIX: PATINDEX on CONVERT May Cause GP Fault
 * FIX: Heavy RPCs Under Stress May Cause Access Violations
 * FIX: Kill Then Net Session Loss Can Make Access Vio., Err 611
 * FIX: sp_lock Does Not Indicate Blocking Lock
 * FIX: Syntax Error in CREATE PROC May Cause Dropped Connection
 * FIX: Loading Tape Dump From SQL Admin May Fail
 * FIX: Object Manager Fails if SA's Default DB is Not Master

Description of Problems Corrected in 4.21

FIX: Server Not Responding to dbcancel

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 253 (4.2)

SYMPTOMS

After a DB-Library call returns FAIL due to DB-Library (DB-Lib) error 10024,

SQL Server connection timed out subsequent calls to dbcancel return FAIL and result in multiple 10024 errors. The current command batch is not canceled and calls to dbsqlexec will fail with error 10038: Attempt to Initiate a New SQL Server operation with results pending. CAUSE

When a query times out due to certain locking conditions, SQL Server may be unable to respond to the attention signal sent by dbcancel. When the locks that caused the original query to timeout are freed, dbcancel can be executed successfully, and subsequent calls to dbsqlexec will not generate 10038 errors.

WORKAROUND

Should dbcancel return FAIL due to a 10024 error, it is necessary to either wait for the locking condition on the SQL Server to clear, or to close the current DBPROCESS and open another.

FIX: RETURN Statement After an RPC Causes Extra Result Set

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 324 (4.2)

SYMPTOMS

An extra result set is returned from SQL Server when a stored procedure containing a remote procedure call followed by a RETURN statement is executed. This does not happen if there is no RETURN statement following the EXECUTE statement or if a local procedure is executed instead of a remote one.

For example, executing the following stored procedure replicates the above problem:

create proc rpcproc as  execute ...sp_who return go  execute rpcproc go WORKAROUND

Modify the stored procedure, if possible, to avoid the explicit return statement following the call to execute the remote procedure. Another workaround is to make sure your front-end application is handling the extra result set properly. One recommened way of doing this is to execute the call to dbresults in a WHILE loop until it returns NO_MORE_RESULTS.

SQL for NT Admin Backup Event

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 9257 (4.2)

SYMPTOMS

When a 16-bit SQL Administrator version 4.2.11 for Windows NT attempts to add a scheduled backup event to occur at a start time of a single digit (like 7:00 AM, 8:00 AM, or 8:15 PM), cause of the following:

  You may get the error: The start time entered for the scheduled backup event is out of range.  The minute part of the start time might be changed to a different value, for example, start time of 7:00 might be changed to 7:02 or 7:22.

WORKAROUND

To work around the above error of start time being out of range, enter the single digit time with a zero in the prefix. For example, instead of entering 8:15, enter start time of 08:15.

Another work around is to use the 32-bit version of SQL Administrator on Windows NT which does not show this behavior.

FIX: sp_password Does Not Validate Old Password

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 485 (4.2)

SYMPTOMS

The sa can use sp_password to change another logins password. If the sa supplies an invalid old password, SQL Server will change the password to the new value, then issue the error message:

Password has been changed. CAUSE

The sa user is the only login allowed to specify a login parameter with sp_password. In this case, the documentation states that sp_password will validate the old password if it is given, and reject it if wrong. sp_password is issuing the error message, but not stopping the password change.

This applies only to sa, as that is the only login capable of changing the password for another login. All other logins can only change their own password, and in this case the password validation is operating correctly.

WORKAROUND

Connect to SQL Server using the login ID whose password you desire to change, and execute sp_password without specifying the login_id parameter, similar to the following:

sp_password oldpassword, newpassword When executed this way, sp_password does validate the old password. FIX: Trusted RPCs Do Not Work with Different Passwords

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 525 (4.2)

SYMPTOMS

With a mapped trusted account on the remote machine, an attempt to execute a remote procedure generates the following error:

Msg 7221, Level 14, State 2: Login to site " " failed. This happens only if the password for the local account is different from the password on the mapped account on the remote machine. WORKAROUND

As a work around, change the passwords on both servers to be the same.

FIX: Error 816 When Many Updates Performed

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 513 (4.2)

SYMPTOMS

When there are many updates performed in a database configured with low open objects, SQL Server will give error 816, level 20, state 4:

Process %d tried to remove a buffer resource lock %S_BUF that it does not hold in SDES %S_SDES. The SQL Server is terminating this process. The following warnings will also be received: pss found with open sdes

process being freed while holding Dataserver semaphore In some cases, after cycling SQL Server, any attempt to create or drop a table in the database will result in a 816 error and be disconnected. CAUSE

The server is configured with a low configuration for open objects (100). The error 816 happens when the tran log is full and several 'dump tran with no_log' are issued.

WORKAROUND

Increase the configuration for open objects.

FIX: Tape Dump's Date Is 0th Day of the Month

ARTICLE ID: \[\[..\/\|Q\]\] BUG# 9566 (4.2)

SYMPTOMS

SQL Server for Windows NT tape dumps performed on the last day of a month will list as being performed on the 0th day of the following month when viewed from SQL Administrator for Windows or the SQL Tape Utility.

WORKAROUND

When a tape dump lists as being performed on the 0th day of a month, realize that it was actually performed at the same time on the last day of the previous month. The date should not affect the ability to load the dump.

FIX: Monthly Scheduled Backup Skips Alternate Months

ARTICLE ID: Q107710 BUG# 9613 (4.2)

SYMPTOMS

When a backup event is scheduled to occur monthly, the backup event may not occur every alternate month. For example, assume a monthly backup event scheduled on Oct. 26, 93 to occur on Tuesdays of fourth week every month (Tuesday monthly-week4). The next backup for that event will not occur on Nov. 23, 93. Instead, it will occur on Dec. 28, 93.

CAUSE

The problem occurs because the stored procedure sp_MSbackup_now incorrectly calculates the time elapsed for the monthly backup event.

WORKAROUND

To work around this problem you can drop and recreate the stored procedure sp_MSbackup_now by running the following scripts (script1 or script2 depending upon the operating system for the server) using any query tool like ISQL or ISQL/w.

On SQL Server for Windows NT:

use master go drop proc sp_MSbackup_now go create procedure sp_MSbackup_now as declare @now datetime, @dayofweek smallint, @hour smallint, @minute smallint, @elapsed int, @monthyear varchar(30) set nocount on

/* */ /* Get the current date and time. */ /* Parse out the relevant parts of the date */ /* */ select @now = getdate select @dayofweek = datepart(weekday,@now) select @monthyear = substring(convert(varchar(12),getdate),1,12) /* */ /* Create temporary table that holds data on what needs to be dumped */ /* */ create table #spdumptab ( id            int           Not Null, /* Unique identifier */ name           varchar(30)   Not Null, /* Name of database to be dumped */ owner          varchar(30)   Not Null, /* Name of the database owner */ ddump          varchar(30)   Null,     /* Database dump device */ ldump          varchar(30)   Null,     /* Log dump device */ datacntrltype  smallint      Null,     /* Control type */ logcntrltype   smallint      Null,     /* Control type */ status         tinyint       Null      /* extra dump parameters */ ) /* */ /* Check all the databases that are dumped daily, weekly, & biweekly */ /* Note: The dump can only occur if the start time(HH:MM) is > the last dump <= now */ /* */ insert into #spdumptab select Event_id, Database_name, Database_owner,Database_dump,Log_dump,NULL,NULL,Stat from MSscheduled_backups where Enabled = 1 /* Dump turned on */ and ((convert(smallint,Day) = @dayofweek) or Frequency = 1) /* Dump to day or Daily */ and Frequency <= 14 /* Freq daily, weekly, or biweekly */ and datediff(day, Last_dump, @now) >= convert(smallint,Frequency)         /* Freq time has elapsed */ and @now >= convert(datetime, @monthyear + Start_time) and datediff(hour, Last_dump, @now) >= convert(smallint,Frequency)*24      /* Freq time has elapsed */ and datediff(minute, Last_dump, @now) >= convert(smallint,Frequency)*24*60   /* Freq time has elapsed */ /* */ /* Check all the databases that are dumped monthly */

/* NOTE: First we get this week number, then do the same criteria as the Daily, weekly, bi-weekly dump. The dump can only occur if the start time(HH:MM) is > the last dump <= now */ /* */ declare @rundate datetime, @weekno smallint    /* Get this week number */ select @rundate = @now select @weekno = 1 while datepart(month,dateadd(day,-7,@rundate)) = datepart(month,@now) begin select @weekno = @weekno + 1 select @rundate = dateadd(day,-7,@rundate) end insert into #spdumptab select Event_id, Database_name, Database_owner,Database_dump,Log_dump,NULL,NULL,Stat from MSscheduled_backups where Enabled = 1                  /* Dump turned on */ and (convert(smallint,Day) = @dayofweek) /* Dump today */ and Frequency >= 31                        /* Freq monthly */ and (convert(smallint,Frequency) - 30) = @weekno /* Week of month */ and datediff(day, Last_dump, @now) >= 28 /* Freq time has elapsed */ and @now >= convert(datetime, @monthyear + Start_time) and datediff(hour, Last_dump, @now) >= convert(smallint,28)*24 /* Freq time has elapsed */ and datediff(minute, Last_dump, @now) >= convert(smallint,28)*24*60   /* Freq time has elapsed */ update #spdumptab set datacntrltype = (select cntrltype from master..sysdevices s where #spdumptab.ddump = s.name) update #spdumptab set logcntrltype = (select cntrltype from master..sysdevices s where #spdumptab.ldump = s.name) set nocount off /* */ /* Output the values to the daemon */ /* */ select id = id, name = name, owner = owner, ddump = ddump, ldump = ldump,dcntrl = datacntrltype, lcntrl = logcntrltype, stat = status from #spdumptab FIX: Float Data May Cause Data-Conversion Overflow in ISQL/w

ARTICLE ID: \[\[..\/\|Q\]\] BUG# 9661 (4.2)

SYMPTOMS

Certain floating point numbers are not correctly displayed by ISQL/w. For example, executing the following TRANSACT SQL statement:

SELECT 0.0006 Will generate *

(1 row(s) affected)

DB-Library: Data-conversion resulted in overflow. This is caused by the inherent loss of precision incurred when representing float data. Certain floating point values will be converted into wide character strings which cannot be displayed by ISQL/w 0.0006 but will be converted to the following string 6.000000000000001e-004. WORKAROUND

Use the TRANSACT SQL STR function when retrieving data of type FLOAT. Unless information which can only be represented as float needs to be stored, use the other available datatypes.

FIX: Dump File Archive Attribute Not Set if Previously Cleared

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 609 (4.2)

SYMPTOMS

If the archive attribute has been previously cleared, SQL Server for Windows NT does not reset the archive attribute when dumping to an existing dump file. It is common to dump to a disk file, then in a second step use a file backup program to save the disk dump file(s) to tape. Since many tape backup programs depend on the archive attribute for incremental backup, this can result in the SQL dump file not being backed up to tape.

Example:

Each day the log is dumped to disk then an automated tape backup program dumps to tape. Since many backup programs rely on the archive bit, any log dump after the first never gets saved to tape, then the disk file is overwritten by the next dump. The behavior is the same on both the NTFS and FAT file systems.

NOTE: SQL Server for OS/2 properly resets the archive attribute on existing dump files.

WORKAROUND

If using a tape or any other backup program to backup SQL Server for Windows NT disk dump files, do not rely on an incremental backup based on the archive attribute. Instead use the complete, or full backup function of the program. Alternately, you can manually turn on the archive attribute of existing dump files with the following command,

attrib +a filename where filename is the name of the disk dump file. Another option is to completely delete the existing disk dump file before doing another dump. If the file does not exist, it will be created with the archive attribute set.

FIX: Server Must Be Cycled After sp_renamedb

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 487 (4.2)

SYMPTOMS

After using sp_renamedb to rename a database, if the database context is changed to the renamed database using the following command,

USE  and the command, SELECT db_name is executed, the old name will be returned. Error messages mentioning the database name will also refer to the old rather than the new name. WORKAROUND

Shutting down and restarting SQL Server will correct the problem. Error messages and db_name will return the correct value after the server is cycled.

FIX: PATINDEX on CONVERT May Cause GP Fault

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 581 (4.2)

SYMPTOMS

Using the TEXT function PATINDEX with a static string converted to TEXT using the CONVERT function may cause a general protection fault (GP fault) on SQL Server.

WORKAROUND

Use an equivalent C or Visual Basic string function if performing DB-Library (DB-Lib) programming.

FIX: Heavy RPCs Under Stress May Cause Access Violations

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 657 and 537 (4.2)

SYMPTOMS

Heavy RPCs from multiple clients while SQL Server is under stress can cause access violations or SQL Server hangs.

WORKAROUND

Reduce frequency of RPCs.

FIX: Kill Then Net Session Loss Can Make Access Vio., Err 611

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 642 (4.2)

SYMPTOMS

If you kill processes doing heavy tempdb inserts, then terminate the client network sessions with net session/delete, under rare conditions, this may generate an access violation followed by a server hang. Often error 611 is seen. It will also sometimes happen if instead of net session/delete, you just kill all the client sessions individually by rebooting all of the client machines.

WORKAROUND

Do not terminate net connections following use of Transact SQL kill command.

FIX: sp_lock Does Not Indicate Blocking Lock

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 640 (4.2)

SYMPTOMS

Sp_lock does not correctly indicate a blocking lock. For example if a shared page lock is blocked on an exclusive table lock, sp_lock should, but does not, indicate this entry under the locktype column:

Ex_table-blk WORKAROUND

You can use the BLK column of sp_who to see what connection you are blocked on.

FIX: Syntax Error in CREATE PROC May Cause Dropped Connection

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 641 (4.2)

SYMPTOMS

A syntax error in a CREATE PROCEDURE statement may cause the client connection to be dropped with the following error messages:

Msg 213, Level 16, State 4 Insert error: column name or number of supplied values does not match table definition.

Msg 1203, Level 20, State 1 Caller of lock manager is incorrectly trying to unlock an  unlocked object. spid=5 locktype=6 dbid=2 lockid=50.

The SQL Server is terminating this process. The first error is expected, but the connection should not be dropped. This problem is seen in a CREATE PROC statement that creates a temporary table and then attempts to insert a record into the temporary table, but does not specify the correct syntax on the insert. For example: create proc testproc as  create table #testtab(pub_id char(4), pub_name varchar(40),                         city varchar(20), state char(2)) insert #testtab select pub_id, pub_name, state from publishers go In the Event viewer, the 1203 lock manager error is followed by: Msg 17655, Pss found with open sdes ... Msg 17306, Process being freed while holding Dataserver Semaphore WORKAROUND

Connect to SQL Server and correct the syntax error, then re-issue the CREATE PROC statement.

FIX: Loading Tape Dump From SQL Admin May Fail

ARTICLE ID: \[\[..\/\|Q\]\] BUG# 9677 (4.2)

SYMPTOMS

Attempting to load a tape dump into a SQL Server for Windows NT SQL Server may result in the error message:

Backup/Restore has ended in error However, the restore seems to run as expected. The tape is unloaded and you are prompted to remove the tape. The rest of the restore process, initialization and recovery, seems to run the normal amount of time. Then, when you would expect the message box to appear indicating that the load was complete, you will receive an error message indicating the load failed. This problem does not occur with SQL Server for OS/2.

CAUSE

The SQL Monitor program is not handling the tape restore correctly under certain conditions. The conditions usually, but not always, require a large database being loaded and the SQL Administrator System Timeout parameter and the SQL Monitor /sqltimeout parameter to be small values that are multiples of one another.

WORKAROUND

Load the tape dumps using the LOAD DATABASE command directly from ISQL, ISQL/w, or the query window of any other tool. In some cases, changing the SQL Administrator System Functions Timeout to a larger value (using SQL Administrator Configuration option), changing the SQL Monitor timeout value (using /sqltimeout= option) to a larger value, and making sure the two are not multiples of each other sometimes prevents the problem from occurring.

FIX: Object Manager Fails if SA's Default DB is Not Master

ARTICLE ID: \[\[..\/\|Q\]\] BUG# 9437 (4.2)

SYMPTOMS

Running OBJECT2.SQL will fail with the following error message,

The following objects were not created. Sql Object Manager will not run against this server if the SA's default database is not master. CAUSE

There is no 'use master' in the beginning of the OBJECT2.SQL. If the SA's default database is not 'master', the new objects will be created in the user db.

At the end of the script, it checks to see if the new objects exist in master.dbo.sysobjects and removes the stored procedure

'sp_MSOM_version' so that the SQL Object Manager tool cannot connect to SQL Server afterwards. WORKAROUND

Change the SA's default database to master; for example, run

'sp_defaultdb sa, master', then run the script OBJECT2.SQL. After it is done, you can change the default database back to the user database. Or add 'use master

go' at the beginning of OBJECT2.SQL.
 * }

-

 Additional query words:

Keywords : SSrvGen SSrvWinNT kbfixlist

Version : 4.21

Platform : WINDOWS

Issue type : kbref

"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.

''