Microsoft KB Archive/98219

{|
 * width="100%"|

FIX: SQL Server Fixlist 4.2a K4 and K5 (Part 2 of 3)
'Article ID: Q98219

Creation Date: 29-APR-1993

Revision Date: 28-APR-1997' Fixes introduced in the SQL Server 4.20K5 Server

The following is a list of problems, and various other improvements that were discovered during internal testing, that have been fixed in SQL Server version 4.2a. Version 4.2a includes server patches K4 through K5. 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.

MISCELLANEOUS:

DOS DB-LIBRARY 4.2 Creates Larger .EXEs Than v. 1.1-1.19 ID: Q86039

SYMPTOMS

An MS-DOS DB-LIBRARY (db-lib) application compiled with db-lib version 4.2 is approximately 52K larger than if compiled with db-lib versions 1.1 through 1.19. This increase occurs even if the application does not call any of the new functions included in  db-lib version 4.2. CAUSE The cursor and RPC (remote procedure call) functions included in  version 4.2 of the MS-DOS db-lib are statically linked incorrectly with applications even if the functions are not used. Therefore, the executable size is approximately 38K larger.

The additional 14K increase is expected and includes all TDS (tabular data stream) version 4.2 handling code (integral to db-lib  functionality). STATUS This problem was corrected in db-lib version 4.20.21 and SQL Server version 4.2a. >>>>>>>>>>>>>>>>>>> bcp_init Calls Fail After bcp_exec Fails ID: Q87228

SYMPTOMS

Calls to bcp_init return FAIL after receiving a SQL Server error during a previous call to bcp_exec. CAUSE The bcp_exec call does not perform the proper cleanup when a SQL Server error is encountered. STATUS This problem was corrected in db-lib version 4.20.21 and SQL Server version 4.2a. >>>>>>>>>>>>>>>>>>> Select convert(real,i) in SQL Admin Returns 0 ID: Q87231

SYMPTOMS

In the SQL Administrator Query window, when you execute a query to  convert a char, varchar, integer, real, or float value into a real value, no rows are returned in the result. If a table has one of  the display columns in the select as a conversion to real, that column is missing from the result. For example, if the set nocount option is off and the Attach Query to output option is on, then this query

select convert(real,1)

returns the following:

select convert(real,1) (1 row affected)

Similarly,

select &quot;title&quot;, convert(real,5)

returns the following:

select &quot;title&quot;,convert(real,1)

-     title (1 rows affected) WORKAROUND Execute the query from SAF, ISQL, or another front end. STATUS This problem was corrected in SQL Administrator version 4.20.1 and SQL Server 4.2a. >>>>>>>>>>>>>>>>>>> An Illegally Formatted .FMT File GP Faults BCP ID: Q87371

SYMPTOMS

The bulk copy program (BCP) GP faults if you use version 4.2 of BCP to copy data in or out of SQL Server with an .FMT file that incorrectly uses quotation marks (&quot;) as part of the field or record terminator.

Quotation marks are used incorrectly as part of the terminator if  the quotation mark (&quot;) is not preceded by a backslash (\). A  terminator that should appear as &quot;,&quot; should appear in the .FMT file as &quot;\&quot;,\&quot;&quot;. This notifies BCP that the character following the backslash (\) is to be taken as a literal. WORKAROUND To avoid this problem, ensure that the .FMT file contains a  backslash and quotation mark (\&quot;) to represent a quotation mark (&quot;) terminator. STATUS This problem was corrected in the bulk copy program version 4.20.21 and SQL Server 4.2a. >>>>>>>>>>>>>>>>>>> Dbcursoropen Fails and Select Returns Nothing ID: Q87372

SYMPTOMS

The dbcursoropen statement fails with the following messages if  the select statement issued in the DBCUROSOPEN generates no   results:

Cursor Statement generated no results Cursor Open Failed WORKAROUND To open a cursor on a empty table or a select statement that generates no results, add a dummy row of data to the table so there is at least one row that satisfies the select statement. STATUS This problem was corrected in db-lib version 4.20.21 and SQL Server version 4.2a >>>>>>>>>>>>>>>>>>> BCP Creates Bad .FMT File for Quote Terminators ID: Q8747

SYMPTOMS

Version 4.2 of the bulk copy program (BCP) incorrectly creates format (.FMT) files when quotation marks (&quot;) are going to be part of the field or record terminator. Instead of placing a \&quot; in the terminator string in the .FMT file, BCP places only a &quot;. This results in an unusable .FMT file. WORKAROUND To avoid this problem, do not use an .FMT file. Instead, have BCP prompt you for the necessary information. Using the BCP prompts, you can copy data in or out of SQL Server. STATUS This problem was corrected in the bulk copy program version 4.20.21 and SQL Server 4.2a. >>>>>>>>>>>>>>>>>>> Improperly Calling SqlWinExit Results in UAE/GP ID: Q87538

SYMPTOMS

Calling SqlWinExit without previously calling SqlInit$ results in  an unrecoverable application error (UAE) under Microsoft Windows operating system version 3.0 or a general protection fault (GP fault) under Microsoft Windows operating system version 3.1. WORKAROUND SqlInit$ initializes the Visual Basic Library for SQL Server. You must call this function before calling any other functions or  procedures in the library. Similarly, SqlWinExit informs the library that the Windows application is about to exit. Therefore, in order to avoid this problem, insure that the application calls SqlInit$ when it starts, and only calls SqlWinExit when it is  closed. STATUS Microsoft has confirmed this to be a problem in the Microsoft SQL Server Programmer's Toolkit (PTK) for Visual Basic version 4.2. This problem was corrected in the PTK for Visual Basic version 4.20.21 and in SQL Server version 4.2a.

This problem does not occur in the Visual Basic Library for SQL Server version 1.0. >>>>>>>>>>>>>>>>>>> BCP Fails to Load Data When Skipping Columns ID: Q87539

SYMPTOMS

The bulk copy program (BCP) cannot load rows into a table when you use a format file (BCP.FMT) to skip over columns in the data file. This problem occurs only when the data file is character delimited. If the data file is of fixed length, the problem does not occur.

The following sample file does not load correctly:

Table Definition ---     create table bcp_test (id int, lname char(20))

Data File -     324,Bill,Johnson 543,Lisa,Jones 984,Tom,Smith

BCP.FMT --     4.2      3      1     SYBCHAR   0   10   &quot;,&quot;   1   ID      2     SYBCHAR   0   20   &quot;,&quot;   0   FNAME 3    SYBCHAR   0   25   &quot;\r\n&quot;   2   LNAME

Note: By using a zero value in column number six on line number four in the BCP.FMT file, we are telling BCP to skip over the second field in the data file. However, due to the current problem, BCP cannot load any of the data. WORKAROUND There are two workarounds available:

- Use a fixed-length data file rather than a character-delimited file. The current version of BCP properly loads data in a    fixed-length format.

- Use versions 1.x of BCP. Copy the versions 1.x executable file to your machine and rename the file to OLDBCP. The 1.x versions of BCP are linked statically and do not depend on any DLLs. You can use this option unless the table contains data of the SMALLDATETIME, REAL, or SMALLMONEY data type. Note: The format files between versions 1.x and 4.2 of SQL Server are not compatible; to use versions 1.x    of BCP, you must recreate the format file using versions 1.x of BCP. STATUS This problem was corrected in the bulk copy program version 4.20.21 and SQL Server 4.2a. >>>>>>>>>>>>>>>>>>> Scheduled Backups Do Not Occur at Correct Time ID: Q87799

SYMPTOMS

In SQL Administrator, if you add a scheduled backup event earlier in the same day than the time of the event, backups do not occur at  the correct time. For example, if at 4:00 P.M. on May 17, 1992, you added a backup event scheduled at 1:00 A.M. (1:00 A.M. < 4:00  P.M.), the actual backup will occur on May 18, 1992, after and around 4:00 P.M. (the exact time after 4:00 P.M. depends on the  backuptime parameter value for SQL Monitor) and not around 1:00 A.M. Similarly, the next backup will occur on May 19, 1992, after and around 4:00 P.M. CAUSE This problem occurs because the backups are done only if 24 hours have passed since the last backup and if the current time is  greater than the scheduled time. When you add an event, say at 4:00 P.M. for a backup to occur at 1:00 A.M., the last backup value for that event is set to 4:00 P.M. on that day. Accordingly, at 1:00 A.M. the next day, less than 24 hours have passed and therefore the backup does not occur. On the other hand, when SQL Monitor wakes up  after 4:00 P.M., it will perform the backup because 24 hours have passed since the last dump and the current time is greater than the scheduled backup time.

This problem does not occur if you schedule the event to start later in the day than the time you add the event. WORKAROUND To work around this problem, update the Last_dump field of the MSscheduled_backups table for the Event_id being scheduled. The exact steps for doing this are:

1. Add an event for a scheduled backup and write down the Event_id. Suppose the event is scheduled at 1:00 A.M. Let today's date be     May 18, 1992.

2. If the current time is not greater than the scheduled time for that event, skip to step 4.

3. Update the Last_dump value in the MSscheduled_backups table for this event to the starting time value of the backup event. The SQL command for that is

Update MSscheduled_backups set last_dump=&quot;17 May 1992 1:00 AM&quot; where Event_id=

These steps ensure that the backup will occur at the correct time. STATUS Microsoft has confirmed this to be a problem in SQL Administrator version 4.2. This problem was corrected in SQL Administrator version 4.20.1 and SQL Server 4.2a. >>>>>>>>>>>>>>>>>>> SQL Admin: Actual Startup Time for Scheduled Backup ID: Q87834

SYMPTOMS

Using SQL Administrator, when daily backup events are scheduled, the actual start time for the backup event shifts every day by  approximately the amount of time taken for the backup and the SQL Monitor &quot;backuptime&quot; parameter value. For example, if two daily backup events are scheduled to start at 6:00 P.M. and 6:40 P.M. and the SQL Monitor &quot;backuptime&quot; parameter is set to 15 minutes, if SQL Monitor wakes up at 5:47 P.M. and every 15 minutes thereafter, the actual startup times may shift successively each day, as follows:

Event ID  Actual Start Time   End Time -

1  April 17 1992 6:02 P.M.   April 17 1992 6:02 P.M.      2   April 17 1992 6:47 P.M.   April 17 1992 6:47 P.M.      1   April 18 1992 6:17 P.M.   April 17 1992 6:17 P.M.      2   April 18 1992 7:02 P.M.   April 17 1992 7:02 P.M.      1   April 19 1992 6:32 P.M.   April 17 1992 6:32 P.M.      2   April 19 1992 7:17 P.M.   April 17 1992 7:17 P.M.

This case assumes that the actual backup time is less than a minute. CAUSE This problem occurs because of calculation drift. When SQL Monitor wakes up and finds that less than 24 hours have passed since the last backup was finished, it does not do the backup at that time. Accordingly, the backup is done when it wakes up next time. This happens every time SQL Monitor is supposed to perform the backup; hence, the successive backups are shifted each day. WORKAROUND To specify the exact or near-exact time to start a backup, you must periodically update the Last_dump column of the table MSScheduled_backups to match the date of the last dump but at the specified start time. For example, if the last dump is on 4/18/92 at 6:17 P.M. and your start time is at 6:00 P.M. for the event_id 1, execute the following:

Update MSScheduled_backups set last_dump='4/18/92 6:00PM' where event_id= 1

Therefore, on 4/19/92, the backup for event_id 1, the backup will occur at 6:02 P.M..

Another way to reduce the drift is to set the SQL Monitor &quot;backuptime&quot; parameter to a lower value, say from 15 minutes to 1 or 2 minutes. This reduces the drift over time. STATUS Microsoft has confirmed this to be a problem in SQL Administrator version 4.2. This problem was corrected in SQL Administrator version 4.20.1 and SQL Server 4.2a. >>>>>>>>>>>>>>>>>>> BCP Does Not Insert Default Values ID: Q87921

SYMPTOMS

When the bulk copy program (BCP) is used to copy data into a column that does not allow NULLs but has a default bound to it, the following error will occur:

DB-LIBRARY error:

Attempt to bulk-copy a NULL value into a Server column which does not accept NULL values. CAUSE If there is a default bound to the column that does not allow NULL values, BCP should put the default value in the row when it  encounters a missing value (no entry) for that column. Instead, it  generates the above error. WORKAROUNDS To work around this problem, use versions 1.x of BCP.

Another workaround is to bulk copy the data into another table where the column allows NULL values, update the rows containing the NULL values to the default value, then select the rows into the original table. STATUS Microsoft has confirmed this to be a problem in the bulk copy program version 4.2. This problem was corrected in the bulk copy program version 4.20.21 and SQL Server 4.2a, which are available from Microsoft Product Support Services. For more information, please contact your primary support provider. >>>>>>>>>>>>>>>>>>> SQL Admin Query Returns * for Many Floats ID: Q88109

SYMPTOMS

Many floating-point numbers (for example, .07) cause SQL Administrator to return an asterisk (*). STATUS This problem was corrected in SQL Administrator version 4.20.1 and SQL Server 4.2a. >>>>>>>>>>>>>>>>>>> Some SQL Admin Queries Fail to Complete When 0 Rows ID: Q88388

SYMPTOMS

When you attempt to execute a query from SQL Administrator, the query fails to complete and eventually times out. After the time-out occurs, selecting the option to continue the operation again results in the query timing out. If you select the option to  cancel the query after the time-out occurs, a Windows version 3.0 unrecoverable application error (UAE) or Windows version 3.1 general protection fault (GP fault) occurs. CAUSE This problem can occur when a query contains a WHERE clause that returns zero rows and thus should return &quot;0 rows affected&quot;. WORKAROUND Execute the query from ISQL or SAF. STATUS Microsoft has confirmed this to be a problem in SQL Administrator version 4.2. This problem was corrected in SQL Administrator version 4.20.1 and SQL Server 4.2a. >>>>>>>>>>>>>>>>>>> BCP Does Not Truncate Datetime Data Type ID: Q88420

SYMPTOMS

The following statement appears on page 80 of the &quot;System Administrator's Guide&quot; for SQL Server version 4.2:

When datetime data is stored as a character string of less than 26 bytes, the data is silently truncated.

This statement indicates that you should be able to list the length of a character field in the format file as eight characters in  order to make the Bulk Copy Program (BCP) grab the date portion of   the Datetime data field and truncate the remainder.

However, that is not what happens. Instead of truncating the date, BCP copies all the data. The Datetime field ends up containing the entire 26-character string. WORKAROUND When you use BCP, load the entire Datetime field. Then create a  duplicate table and use SELECT INTO to load it. This will allow you to convert the Datetime field in the duplicate table to a Character field of the desired length. STATUS This problem was corrected in the Bulk Copy Program version 4.20.21 and SQL Server 4.2a. >>>>>>>>>>>>>>>>>>> Unexpected EOF Using BCP with -L and -Fn Switches ID: Q88485

SYMPTOMS

When loading data using the bulk copy program (BCP) with both the -Fn and -L switches, you may see the following spurious error message:

DB-LIBRARY error: Unexpected EOF encountered in BCP data-file.

N rows copied.

This error appears only when you use both the -L and -Fn switches and &quot;n&quot; is greater than 1. WORKAROUND Ignore the error message. The rows specified by the combination of  the -F and -L switches were loaded. The error message does not appear in versions 1.1 or 1.11. STATUS This problem was corrected in the bulk copy program version 4.20.21 and SQL Server 4.2a. >>>>>>>>>>>>>>>>>>> SQL Admin Scheduled Backup: Out of Range Start Time ID: Q88488

SYMPTOMS

When adding a scheduled backup event, entering a start time of  hh:59 AM or PM generates an error box containing the following message:

&quot;The start time entered for the scheduled backup event is out of range&quot;

For example, entering start times such as 11:59 AM, 1:59 PM, and 3:59 AM generate the error message. WORKAROUND Change your scheduled backup event start time to a value in a form other than hh:59. STATUS Microsoft has confirmed this to be a problem in SQL Administrator version 4.2. This problem was corrected in SQL Administrator version 4.20.1 and SQL Server 4.2a. >>>>>>>>>>>>>>>>>>> Unable to Start After Installing Utilities ID: Q88490

SYMPTOMS

After installing SQL Server version 4.2, executing the following command

net start sqlserver

returns this error:

NET3062: The sub-service SQL Server failed to start.

In addition, attempting to start SQL Server from the command line using the following command

sqlservr -dc:\sql\data\master.dat -ec:\sql\log\errorlog

returns this OS/2 error:

SYS1041: The name specified is not recognized as an internal or     external command, operable program or batch file.

Inspecting the \SQL\BINP directory shows that there is no  SQLSERVR.EXE file. CAUSE The setup program incorrectly removes the SQLSERVR.EXE file from the \SQL\BINP directory if option 2 from the Setup menu is chosen, after option 1 from Setup has already been executed.

The first setup option says

1. Install SQL Server and its utility programs on a network server or on a stand-alone computer...,

while the second setup option says:

2. Install the SQL Server utility programs on a network work-station.

When installing SQL Server on the server, you need to execute only the first option, which installs the SQL Server executable file as  well as the various utility programs. You need to execute the second option from Setup only if you are installing the utilities on an OS/2 machine that will be a client.

Page 15 of the &quot;User's Guide&quot; for Microsoft SQL Administrator for Windows says that you should perform this procedure on an OS/2 workstation or server prior to installing SQL Administrator. Because of this problem, disregard step number 3 on that page if  you are installing SQL Administrator on the SQL Server server. WORKAROUND If the SQLSERVR.EXE file is no longer in the \SQL\BINP directory, you can copy it from the original SQL Server disks by doing the following:

1. Insert the OS/2 Setup disk from the SQL Server package in the disk drive.

2. Type the following and press ENTER after each line (this     assumes the disk is in drive A):

copy a:\unpack.exe c:\sql\binp copy a:\sqlservr.ex@ c:\sql\binp

Note: If you have installed SQL Server on a drive or directory other than C:\SQL, use that path as the target for the COPY command.

3. Change to the proper directory by typing the following and pressing ENTER after each line:

c:        cd c:\sql\binp

4. Type the following command and press ENTER to unpack the SQLSERVR file:

unpack sqlservr.ex@ sqlservr.exe

5. Verify that the SQLSERVR.EXE file exists, and delete the SQLSERVR.EX@ file. STATUS This problem was corrected in SQL Server version 4.2a.
 * }

-

 Additional query words:

Keywords : SSrvGen kbfixlist

Version : 4.2a

Platform : OS/2

Issue type : kbref

"THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED &quot;AS IS&quot; 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.

''