Microsoft KB Archive/923116

= Error message after you install the Systems Management Server 2003 site database on Microsoft SQL Server 2005: &quot;Message ID : 619&quot; =

Article ID: 923116

Article Last Modified on 11/15/2006

-

APPLIES TO


 * Microsoft Systems Management Server 2003 Service Pack 2

-





SYMPTOMS
After you install the Microsoft Systems Management Server (SMS) 2003 site database on Microsoft SQL Server 2005, you may receive the following site server status error message:

Type Milestone

Severity: Warning

Message ID : 619

ProcessID: 722

Trhead ID: 6344

Component: SMS_STATUS_MANAGER

Microsoft SQL Server reported SQL message 102, severity 15: [42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'd'. : sp_InsStatusMessageAttribute

Additionally, new site status messages are not inserted into the site database. The Inboxes\Statmgr.box\Retry folder contains a backlog of .sql files.

The Statmgr.log file may contain the following entries:


 * exec sp_InsStatusMessageAttribute 63,14176,402,'CCP00013','

',14177,402,' ','

',14178,402,' ','  ',14179,402,'  ','

',14180,402,' ,'

',14181,402,' ','

--Entries omitted for space--

',14236,402,'SMS ',' date

' SMS_STATUS_MANAGER date time 4144 (0x1030)


 * [42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'd'. : sp_InsStatusMessageAttribute SMS_STATUS_MANAGER date time 4144 (0x1030)

ERROR: Could not add a SQL command to the transaction. Most likely, there is a problem with the SQL server or our connection to it. The transaction will be rolled back. SMS_STATUS_MANAGER date time 4144 (0x1030)

Note To install the SMS 2003 site database on SQL Server 2005, you must have SMS 2003 Service Pack 2 installed.



RESOLUTION
To resolve this issue, apply the hotfix described in the following Microsoft Knowedge Base article:

925335 FIX: Error message when you use a label after a Transact-SQL query in SQL Server 2005: &quot;Incorrect syntax near 'X'&quot;



WORKAROUND
You may experience an error when you try to install the database of the SMS 2003 site on SQL server 2005. To resolve this problem, the final action in the IF clause must be enclosed in a BEGIN/END statement. To do this, follow these steps:  Open SQL Server Management Studio, expand Databases, expand System Databases, expand SMS Site Database, expand Programmability, and then expand Stored Procedures. Right-click sp_InsStatusMessageAttribute. Point to Script Stored Procedures, point to Alter to, and then click New Query Editor Window.  Find the following section at the end of the procedure. IF (@NumRows < 63) GOTO done IF (SELECT RecordID FROM StatusMessageAttributes WHERE RecordID = @RecordID_63 AND AttributeID = @AttributeID_63 AND AttributeValue = @AttributeValue_63) IS NULL INSERT INTO StatusMessageAttributes VALUES (@RecordID_63, @AttributeID_63, @AttributeValue_63, @Time_63)    done: END GO   Add a BEGIN/END statement to the INSERT section that resembles the following statement. IF (@NumRows < 63) GOTO done IF (SELECT RecordID FROM StatusMessageAttributes WHERE RecordID = @RecordID_63 AND AttributeID = @AttributeID_63 AND AttributeValue = @AttributeValue_63) IS NULL BEGIN INSERT INTO StatusMessageAttributes VALUES (@RecordID_63, @AttributeID_63, @AttributeValue_63, @Time_63) END   done: END GO  Click Execute, and then close SQL Server Management Studio.</li></ol>

If there is a backlog of .sql files in the retry folder, SMS tries to process the oldest file every 10 minutes. After this stored procedure is modified, the backlog can be processed without restarting either SQL Server or SMS.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

Keywords: kberrmsg kbsmsadmin kbtshoot kbprb KB923116

-

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

© Microsoft Corporation. All rights reserved.