Microsoft KB Archive/218435

= Msp.sql Contains Incorrect Fieldsize for Objectsource =

Article ID: 218435

Article Last Modified on 8/12/1999

-

APPLIES TO


 * Microsoft Proxy Server 2.0 Standard Edition

-



This article was previously published under Q218435



SYMPTOMS
When you log to a SQL Server database, logging may stop without any errors.



CAUSE
This is due to a malformed SQL Server script (Msp.sql) that is shipped with Proxy Server that automatically creates the MS_LOG_TABLE for logging to an ODBC resource. The script can be found in the winnt\Help\Proxy\Misc directory. (Note: the location may vary depending on where you installed Windows NT.)

The fieldsize for "objectsource" is incorrectly set to 10 bytes instead of 25 bytes. (The documentation states this size correctly. Only the script is incorrect.) Some of the values that are passed to this fieldsize are larger than 10 bytes (for example "NotModified"). Therefore, SQL Server fails to allow the field to be updated, which causes Proxy Server to stop logging to SQL Server.

Obtaining an ODBC trace shows the following error:

inetinfo f0:ff EXIT SQLExecute with return code -1 (SQL_ERROR)

HSTMT 0x09a51f58

DIAG [01004] [Microsoft][ODBC SQL Server Driver]Fractional truncation (0)

DIAG [22001] [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'objectsource' of table 'PROXY.dbo.MSP_LOG_TABLE' cannot accept 11 bytes (10 max). (8152)

DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Command has been aborted. (3621)

NOTE: This problem does not occur when logging to Microsoft Access.



WORKAROUND
To work around this issue, follow these steps:  Copy the following modified script to create the appropriate table:

CREATE TABLE MSP_LOG_TABLE ( ClientIP varchar(32),

ClientUserName varchar(32),

ClientAgent varchar(128),

ClientAuthenticate varchar(5),

logTime datetime,

service varchar(12),

servername varchar(32),

referredserver varchar(32),

DestHost varchar(32),

DestHostIP varchar(32),

DestHostPort int,

processingtime int,

bytessent int,

bytesrecvd int,

protocol varchar(8),

transport varchar(8),

operation varchar(8),

uri varchar(255),

mimetype varchar(32),

objectsource varchar(25),

resultcode int,

CacheInfo int

)

 Open ISQL_w from the Microsoft SQL Server 6.5 Program group. Log in to SQL Server using the appropriate credentials. Verify that the appropriate database has been chosen from the database drop-down list. Paste the above script into the ISQL_w pane. Click Query and choose Execute. You should receive the following status message:

This command did not return data, and it did not return any rows.

</li> Close ISQL_w.</li> To complete setup to a database, please refer to the Proxy Server online documentation (http://localhost)prxdocs/htm/padlog.htm#LoggingtoaDatabase).</li></ol>

NOTE: These steps are only for creating the correct table. Please refer to the documentation for complete information on setting up ODBC Database access.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in Proxy Server 2.0.

<div class="moreinformation_section">

MORE INFORMATION
If Proxy Server has already been configured to log to SQL Server 6.5, then a new table needs to be created and the data from the original MSP_LOG_TABLE needs to be copied to a file using BCP (or some other utility) and then copied back into this newly created table using BCP (or another utility). It cannot be copied using BCP in "native" mode, because it will fail to copy to the new table.

If you use BCP, perform the following steps:

NOTE: Please be advised that the steps are manipulate data with SQL Server 6.5. If there are doubts about following the below steps, please refer to the SQL Server Documentation or contact Microsoft Product Support to help with the BCP utility. <ol> Create a new table using the steps above, except change the name of the table to MSP_LOG_TABLE_NEW before executing the script.</li> Change logging from ODBC resource to Text File during this operation.</li> Open a command window (cmd).</li> From the command window, use BCP to copy the data from MSP_LOG_TABLE to a text file:

bcp dbname..MSP_LOG_TABLE out filename.dat /c /t !{& /Usa /Psa_password

The above syntax is intended to be run from the SQL Server computer itself and not from a remote computer. To run BCP from a remote computer, use the /S Servername switch.</li> When BCP has completed the above command, use BCP again to copy the data into the MSP_LOG_TABLE_NEW table that was created in step 1. Use the following syntax:

bcp dbname..MSP_LOG_TABLE_NEW in filename.dat /c /t !{& /Usa /Psa_password /b5000

</li> When the data has been successfully copied and verified, drop the original MSP_LOG_TABLE in SQL Server (for more information on this, please refer to the SQL Server 6.5 documentation).</li> Rename MSP_LOG_TABLE_NEW to MSP_LOG_TABLE.</li> Reconfigure Proxy Server to log to ODBC Resource from text files.</li></ol>

The newly corrected table is now in place with the original data. SQL Server should now log appropriately. For additional information about the Bulk Copy Program (BCP), please see the following article in the Microsoft Knowledge Base:

67409 Commonly Asked BCP Utility Questions in SQL

Additional query words: logging log SQL Proxy

Keywords: kbbug kbpending KB218435

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.