SMS and SQL

Goto the SMS Home Page
This page details problems and issues relating to SMS and it's use with SQL.

'@@servername for your export server is invalid. Please use sp_addserver'

Has anyone seen the following when running the SQL 7.0 upgrade wizard:

@@servername for your export server is invalid Please use sp_addserver

Try running 'select * from SYSSERVERS' to see that your server is listed. If it isn't run 'SP_ADDSERVER <servername>, local' to add it.
 

Adding a Second processor to a SQL Server

I know through trial and error that if you change the name of a server running SQL to avoid problems you need to change the name internally first as in my script below.

sp_dropserver oldname
go
sp_addserver newname, local
go

Now I need to know if there is anything hidden or secretive about adding a second processor to a box running both SMS and SQL. The box is a Compaq Proliant 5500 running SMS SP2 with SQL 7 SP2 on NT 4 SP4.

Watch out for the HAL - using the wrong HAL will cost you your site.
 

"Could not connect to registry key HKLM\Software\Microsoft\SMS" Database Monitor error

Since I've upgraded my Site server from NT4 SP4 to Windows 2000, SQL Server Database Monitor now says 'Could not connect to registry key HKLM\Software\Microsoft\SMS' when trying to connect to the Site server.

This is a known issue where the SMS Site database is run on a server other than the Site server and the Site server is upgraded from NT4 SP4 to Windows 2000. To get around this, run Setup on the Site server from the SP2 CD and select the option to upgrade the site. Walk through the process without making any changes. SQL Monitor will restart automatically as part of the upgrade and start working. A Site Reset does NOT resolve this problem. Microsoft Knowledge Base article 259781 'SMS: Upgrading SMS Site Server to Windows 2000 May Reset Registry Permissions' provides further information on this problem.
 

Do I need to install the OLAP Services part of SQL SP2 on a server dedicated to SMS?

No - on a SQL Server that is dedicated to SMS, OLAP services isn't required. 'SQL 7 OLAP Services' is a separate setup option when you initially install SQL. You can tell if it's installed as the 'MSSQLServerOLAPService' will be present in 'Control Panel | Services'.
 

Do I need to re-install the pre-SP2 Hotfixes after moving SQL to a different server?

I will be moving my SMS SQL server off my Central Site on to a new computer. The Central SMS Site will remain on the original server. I will do a Site reset on my Central server to point it to my new SQL computer. The SMS SQL database will remain the same (a restore). When I perform the Site reset do I need to reinstall the two hot fixes I applied as the prerequisites for SP2?

No you don't need to re-install the hotfixes.
 

Does SMS use DTS?

Data Transformation Services (DTS), isn't used by default on a SQL server that is dedicated to storing the SMS databases. DTS is used when doing data exports, converting output to ASCII, etc.
 

How do I get the SQL agent to autostart after a server reboot?

How do you automatically start the agent following a reboot of the machine?

There are a number of times when you want to ensure that the Agent starts automatically:

  • When the entire server is rebooted
  • When either SQL Server or the Agent stops unexpectedly
  • When SQL Server is rebooted

Solutions in order:

  • Change the parameters in the Services of NT to start both SQL Server and the Agent automatically upon boot up
     
  • In SQL Server Agent Properties (Management:SQL Server Agent:Advanced) under the 'Restart Services' section ensure that both 'Auto restart SQL Server if it stops unexpectedly' and 'Auto restart SQL Server Agent if it stops  unexpectedly' are checked.
     
  • Write an 'autoload' script that fires up the Agent from the command line

The Agent does not automatically start when SQL Server is fired up as a normal start (without a full reboot of NT).
 

How to Create Queries in SQL Views

Contributed By: Cliff Hobbs [MVP SMS]
Want to know how to create Queries in SQL Views? Here's how...  [Go to article]
 

How to Find Out Which SQL Service Pack You Are Running

Contributed By: Carl Dillow, Cliff Hobbs - MVP SMS, Heine Jeppesen and Robert.Mahle
So there you are sat in front of your SQL box. How do you which version/ service pack it's running?

To find out which SQL Service Pack your SQL Server is currently running, run the following statement from the SQL Server Query Analyzer window:

SELECT @@version

The result of this query is:
 
SQL 6.0

6.00.121 if no service pack is applied
6.00.124 if Service Pack 1 is applied
6.00.139 if Service Pack 2 is applied
6.00.151 if Service Pack 3 is applied
 
SQL 6.5

6.50.201 if no service pack is applied
6.50.213 if Service Pack 1 is applied
6.50.240 if Service Pack 2 is applied
6.50.258 if Service Pack 3 is applied
6.50.281 if Service Pack 4 is applied
6.50.415 if Service Pack 5 is applied
6.50.416 if Service Pack 5a is applied
 
SQL 7.0

7.00.623 if no service pack is applied
7.00.699 if Service Pack 1 is applied
7.00.842 if Service Pack 2 is applied
7.00.961 if Service Pack 3 is applied
7.00.1063 if Service Pack 4 is applied
 
SQL 2000

8.00.194 if no service pack is applied
8.00.384 if Service Pack 1 is applied
8.00.534 if Service Pack 2 is applied
8.00.578 if Q316333 is applied to Service Pack 2
 
Note: The numbers returned correspond to the EXE version of 'SQLSERVR.EXE'.
 

How to Remove a SQL Service Pack

Contributed By: Cliff Hobbs [MVP SMS]
 Here's hoping you never need to do this, but just in case Knowledge Base Article 314823 'HOW TO: Remove a SQL Server Service Pack' tells you how.
 

Is SQL 2000 supported by SMS 2.0?

Contributed By: Cliff Hobbs [MVP SMS]
If you plan to use SQL 2000 with SMS pre-SP2 you MUST install SQL 2000 BEFORE you install SMS. If you install SMS first and then try to install SQL you will only see the options of selecting SQL 6.5 or 7.0 and not SQL 2000. If you try to proceed with the installation you'll get an error message along the lines that SQL Version 8 has been detected and the installation cannot continue.

Make sure you are using a proper SMS SP2 CD to install SMS from once you've installed SQL as using either just the upgrade files to take an installation up to SP2 and some of the earlier SP2 CD's don't recognise SQL 2000. Microsoft is officially supporting SMS 2.0 using SQL 2000 with some limitations. Check out Knowledge Base Article 271937 'SMS: Systems Management Server 2.0 Support of SQL Server 2000'.
 

Is there a way to Configure SQL to send me an email when a database grows?

In SQL 7.0 I want to have the database files automatically grow but I would also like to notified when that happens. Has anyone set up an event to send an e-mail when a database files grows?

The 'files_growth.SQL' script (which you can download from http://www.databasejournal.com/scripts/article.php/1493181) performs the following:

  • add a user-defined error message to state that file size has changed
     
  • create an alert for that message. This alert will send email to
     
  • create a table to store file size for each file within a database
     
  • create stored procedure to compare the current file size and the stored file size. If file size has changed this stored procedure will raise the error and it will trigger the alert.
     
  • create a scheduled job to run the procedure regularly. You can modify the schedule accordingly to your requirements.

Before executing the 'file_growth.sql' script you need to replace string with an appropriate operator's name and replace with the database name where you will create the 'usp_dba_monitor_file_growth' stored procedure and 'dba_file_info' table. Make sure that SQL Mail and SQL Server Agent are running.

You can modify the schedule accordingly to your requirements.
 

'Setup initialization error' When Trying to Install SQL 2000 Service Pack 3a

Contributed By: Cliff Hobbs [MVP SMS]
 If you're seeing the above error when trying to install SQL 2000 SP3a then we've found one solution that should help…  [Go to article]
 

SQL 2000 Service Pack 3a Released

Contributed By: Cliff Hobbs [MVP SMS]
 Microsoft have released SQL 2000 SP3a which you can find more details on in this article…  [Go to article]
 

SQL mail requires Outlook to be running

I've been attempting to get SQL Mail up and running with SQL Server 7.0. I've followed all the directions (from BOL, MS documents, and 3rd party FAQs), and it seems as if I've almost got it. I setup a mail profile, which is then recognized in the SQL Mail Properties dialog box. I start the Mail service and it seems to work - I get the heart-warming 'Mail Sent' message when testing with xp_sendmail.

However the test mail didn't arrive at its destination so I started Outlook. Sure enough, the test mail was sitting there in the Outbox folder. As soon as Outlook was started it went ahead and pushed all the messages through, and they arrived correctly at their destination. So at this point we can only have SQL Mail working if I stay logged in with  Outlook running at all times, which as you can imagine is undesirable.

I'm sure this is supposed to work without having to launch Outlook. I'm out of ideas, as well as hair, so I was hoping someone might know what I missed. This happens on both NT 4.0 and Windows 2000. I am using Outlook 2000 as well.

Within Outlook, there is an option to force mail to be delivered once it is sent. Once this is set the mail was delivered instantly.

I also had this problem until I changed the mode of Outlook 2000 from 'Internet Only' to 'Corporate / Workgroup'. You can check to see what you are running on the about screen of Outlook. You can change you settings by going to:

Tools | Options | Mail Services | Reconfigure Mail Support

After trying the above I still was unable to send SQL Mail through Outlook without launching Outlook to deliver the mail. I got so disgusted I deleted all my profiles, removed Outlook 2000 from the machine and prepared to give it one more try, after which I would admit defeat.

I went to grab the Office 2000 Premium CD from our software library and in the process stumbled over a stand-alone Outlook 2000 CD in our MSDN Back Office set of CDs. I installed that one instead and BAM! It worked like a charm. Took 15 minutes from beginning to end, with nary a problem along the way. It hooks up with our IMAP mail server and works beautifully, without needing a MAPI server like Exchange.

So for some of you who were experiencing the same problem you may want to try Outlook that comes with Back Office instead of Microsoft Office. I have no idea what the technical differences is between them, but it did the trick for me.
 

SQL Query to Select Hardware and Software Components

Contributed By: Cliff Hobbs [MVP SMS]
Here's a SQL Query to Select Hardware and Software Components...  [Go to article]
 

SQL Software Based View

Contributed By: Cliff Hobbs [MVP SMS]
Check this out for a SQL Software based View...  [Go to article]
 

SQL View to do Hardware Queries

Contributed By: Cliff Hobbs [MVP SMS]
Want a SQL View to do Hardware Queries? Here's one courtesy of Dave Bernett...  [Go to article]
 

'Table corrupt' when running DBCC Checkcatalog against SMS database

I am getting ready to apply SP2 to SMS.  One of the steps is to run a 'DBCC CheckDB' and 'CheckCatalog' check.  The DBCC CheckDB came back without any errors, but I got the following error from the CheckCatalog.  Has anyone else seen this, and if so is there an easy fix?

Server: Msg 2513, Level 16, State 15, Line 1
Table Corrupt: Object ID 322100188 (object 'Summarizer_ComponentTallys')
does not match between 'SYSREFERENCES' and 'SYSCOLUMNS.'.
DBCC results for 'current database'.

As detailed in Microsoft Knowledge Base article 242953 '"Table Corrupt" When Running DBCC CheckCatalog Against SMS Database' you can safely ignore these messages.  SQL 7.0 automatically generates these two tables in each database, but they are not used by SMS.
 

"Unable to get SQL connection. SQL Error: Login incorrect."

I just reset the password of our SQLAdmin ( SQL Server account, which is used to connect SMS DBs on SQL server) account. We use integrated security. SQLAdmin is an SQL Server login. But after resetting this password, I am not able to start SMS console and connect SMS DBs. The 'SMSProvider.log' says the following:

E:\OPALSP1\sdk_provider\nt\smsprov\SspObjectQuery.cpp(1642) : Unable to get SQL connection. SQL Error: Login incorrect.

I am able to connect to SQL Server using SQLAdmin account through SQL Enterprise Manager. But, SMS is not able to connect to Site DB. I have restarted all the services like MSSQL server, SMS_EXECUTIVE and Site_Component_Manager etc. I also set reset the password to new password by going to SMS Site Properties | Accounts | SQL Server Account.

At last I have understood that this password (SQLAdmin's ) has been hard coded somewhere. It is still trying to connect using old password.

  1. I tried connecting using SQL Enterprise manager using the new password. It worked.
     
  2. Since only SMS is having problems to connect to SQL DBs, I thought SMS services are cashing the credentials information, I have restarted the SMS services. But, this didn't work.
     
  3. I set the password to old one. It started working.

This is fully an interesting and weird problem. Any clues??

To change this password successfully, run SMS Setup and do a Site reset.
 

What SQL settings should I change for SMS?

The only SQL-related setting Microsoft recommend you change is the amount of memory SQL uses. Do not allow SQL to allocate memory as required as it will eventually consume all of the available memory on your SMS Servers causing lockups and other problems.

The recommendation is to limit the amount of RAM used by SQL to the maximum of server memory less 128-196 MB of RAM.

You should not disable the autogrow feature for the SMS SQL databases.
 

Where Can I Find Reference Material for the WQL/WMI Flavour of SQL?

Contributed By: Cliff Hobbs [MVP SMS]
 Looking for some reference material for the WQL/WMI flavour of SQL?  [Go to article]
 
© FAQShop.com 2003 - 2008

Goto the SMS Home Page

 Email the Author