Msp.sql Contains Incorrect Fieldsize for Objectsource

• Microsoft Proxy Server version 2.0

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)

WORKAROUND

To work around this issue, follow these steps:

1. 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
   )




2. Open ISQL_w from the Microsoft SQL Server 6.5 Program group.



3. Log in to SQL Server using the appropriate credentials.



4. Verify that the appropriate database has been chosen from the database drop-down list.



5. Paste the above script into the ISQL_w pane.



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




7. Close ISQL_w.



8. To complete setup to a database, please refer to the Proxy Server online documentation (http://localhost)prxdocs/htm/padlog.htm#LoggingtoaDatabase).

STATUS

Microsoft has confirmed this to be a problem in Proxy Server 2.0.

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.

1. Create a new table using the steps above, except change the name of the table to MSP_LOG_TABLE_NEW before executing the script.



2. Change logging from ODBC resource to Text File during this operation.



3. Open a command window (cmd).



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



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




6. 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).



7. Rename MSP_LOG_TABLE_NEW to MSP_LOG_TABLE.



8. Reconfigure Proxy Server to log to ODBC Resource from text files.

Q67409 Commonly Asked BCP Utility Questions in SQL

Additional query words: logging log SQL Proxy

Keywords :
Version : winnt:2.0
Platform : winnt
Issue type : kbbug