Microsoft KB Archive/189588

= SMS: SQL Transfer Manager Errors When Moving SMS Database =

Article ID: 189588

Article Last Modified on 10/27/2006

-

APPLIES TO


 * Microsoft Systems Management Server 1.2 Standard Edition

-



This article was previously published under Q189588



MORE INFORMATION
Sp_site.sql is the script that the Systems Management Server Setup program runs to create the sp_GetSubSites and sp_GetSubGroupSites stored procedures. Manually running this script creates the appropriate temporary tables, and also drops and re-creates the necessary Systems Management Server stored procedures.



SYMPTOMS
When using SQL Transfer Manager to move the SMS database from one SQL Server to another, it is possible to encounter errors similar to the following:


 * Error::

In file DATABASE.SMS.PRC in statement on line 1

Transfer Status: Creating Stored Procedures on destination database

Invalid object name '#Sites'.


 * Error::

In file DATABASE.SMS.PRC in statement on line 1

Transfer Status: Creating Stored Procedures on destination database

Invalid object name '#Sites'.


 * Error::

In file DATABASE.SMS.PRC in statement on line 105

Transfer Status: Creating Stored Procedures on destination database

Invalid object name 'dbo.sp_GetSubGroupSites'.


 * Error::

In file DATABASE.SMS.PRC in statement on line 108

Transfer Status: Creating Stored Procedures on destination database

Invalid object name '#Sites'.


 * Error::

In file DATABASE.SMS.PRC in statement on line 108

Transfer Status: Creating Stored Procedures on destination database

Invalid object name '#Sites'.


 * Error::

In file DATABASE.SMS.PRC in statement on line 108

Transfer Status: Creating Stored Procedures on destination database

Invalid object name '#Sites'.


 * Error::

In file DATABASE.SMS.PRC in statement on line 160

Transfer Status: Creating Stored Procedures on destination database

Invalid object name 'dbo.sp_GetSubSites'.



CAUSE
These errors indicate a problem with creating the SMS stored procedures. This can happen if the required temporary tables are not present.



WORKAROUND
To work around this problem and re-create these procedures manually on the destination server, perform the following steps:


 * 1) Start SQL Enterprise Manager.
 * 2) On the Tools menu, click SQL Query Tool.
 * 3) Select the SMS database.
 * 4) click the Load SQL Script toolbar button.
 * 5) Navigate to the Sms\Site.srv\.bin\Sp_site.sql file.
 * 6) Click the Execute Query toolbar button.

Additional query words: prodsms

Keywords: kbdatabase kbprb KB189588

-

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

© Microsoft Corporation. All rights reserved.