Archive for the ‘SQL 2005’ Category

SQL Server 2005 SP3 and above is supported on Windows 7. Trying to install this I ran into the catch 22 situation: it won’t let me install SQL Server as only SP 3 and over is supported. I can’t install the service pack until SQL is installed. You can have hours of fun like this.

You may be able to get a SQL Server 2005 disc with SP3 applied but I couldn’t find one. What I did find was that it let me install developer edition. After that I installed SP4. Then I upgraded to Enterprise.

That all sounds too easy…

Of course it didn’t let me install developer edition just like that. It had to fail a couple of times first. The failure reported that SQLNCLI.msi was missing. It was there so I tried again. It failed. I then did some searching and found that this was because an earlier version of the client was installed. I went to add remove programs and removed it. After that the install went through OK.

This link will take you to my previous post on upgrading SQL Server 2005

With SQL Server 2005 moving the master database also meant moving the resource database. The resource data and log files had to go to the same directory as the master data file. To complicate matters you had to move the master then start SQL Server in single user mode before running a prepared script to Alter the resource DB. Then stop SQL Server and start it all up again.

From SQL 2008 you don’t have to do that any more. Resource can not be moved. It is deployed with the binaries and stays there. And…well it doesn’t feel right any more. Before SQL Server 2005 moving the system databases was awkward. SQL 2005 made it look easy, but at least it added the Resource bit to make if feel tricky. Now…now you just run an Alter DB script for temp, model, and MSDB, then change the start-up parameters for Master. Stop the SQL Service, move the files (not temp as that will be rebuilt in the new location, you just need to delete the old files) then fire it up again.

See my previous post on moving system databases.

Restore MSDB

Posted: December 1, 2010 in DBA, SQL 2005

A few jobs were deleted when they shouldn’t have been. This was on a Dev server so the easiest way to fix it was restore to MSDB.

How to Restore

Create a new database called msdbTemp
Restore the last good backup of msdb over msdbTemp
Now backup msdbTemp
Stop the SQL Agent – and kill any other processes in MSDB
Restore MSDB from database MSDBTemp – use overwrite for the data and log files.
Re-start the SQL Agent
Take a backup of MSDB

Following some 3rd party scripts to set up replication I got this error:

The Distributor has not been installed correctly. Could not enable database for publishing

To fix this I ran these three scripts.

sp_adddistributor ‘NameofDistributorServer’;
sp_adddistributiondb N’NameofDatabaseBeingReplicated’;
sp_adddistpublisher @publisher = ‘NameofDistributorServer’, @distribution_db = N’NameofDatabaseBeingReplicated’;

This then allowed me to run:

use master;
exec sp_replicationdboption N’NameofDatabaseBeingReplicated ‘, N’publish’, N’true’

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click:


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)

For help, click:

GRANT EXECUTE ON xp_prop_oledb_provider TO [UserDomain\Account Name];

BIDS Helper is deploying packages…
Deploying to SQL Server MSDB on server: NAMEOFSERVER
Error : BIDS Helper encountered an error when deploying package PACKAGENAME.dtsx!
“c:\Program Files\Microsoft SQL Server\90\DTS\binn\dtutil.exe” /FILE “PATHTOPACKAGE.dtsx” /DestServer NAMEOFSERVER /COPY SQL;”Package” /Q
exit code = 6
Microsoft (R) SQL Server SSIS Package Utilities
Version 9.00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2004. All rights reserved.
Could not save package “PACKAGE” because of error 0x80004005.
Description: Cannot open database “msdb” requested by the login. The login failed.
Source: Microsoft SQL Native Client

Had to give the user’s account access to msdb and the dts_admin and dts_operator roles.

SSMS short cuts

Posted: January 25, 2010 in SQL 2005, SSMS

Although I have been using SSMS for a number of years now I didn’t realise you could create your own short-cuts until I saw it mentioned on the UK SQL Server User Group:
SSMS allows you to create your own short cut keys:

Under the Menu Tools Select Options

Under Environment >> Keyboard there is a list of shortcut commands.

A Couple are pre-populated Alt_F1 sp_help; Ctrl+1 sp_who; Ctrl+2 sp_lock;

I have added:
Ctrl+F1: master..xp_fixedDrives; Shows how much space is available on the fixed drives

Ctrl+3: Select * from sys.databases; Lists all databases

Ctrl+4: SELECT name AS ‘File Name’ , physical_name AS ‘Physical Name’, size/128 AS ‘Total Size in MB’, size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’ FROM sys.database_files; Shows how much space is available in SQL Data files

Ctrl + 5: DBCC SQLPERF(logspace); shows log files, the size of the file and % free space.
I have since added a couple more. One shows server waits and another shows server activity in a bit more details than sp_who2. I think I’ll keep these as floating short cuts and change them depending on what I am working on or investigating.