Maintenance Plan fails as Quoted Identifier is set to off

Problem

We have some SQL Server 2000 instances where Identity Checks and Index Reorganisations were failing. The error stated that Quoted Identifier was set to OFF.

Solution

Open the job and add this -S serverName\Instance Name before -PlanId.

Then at the end add –SupportComputedColumn

Note

If the maintenance plan itself is changed then the job definition will change and you’ll need to re-fix them.

This solution comes from an MS article which you can read in full here:

http://support.microsoft.com/kb/902388

SQL says a File is missing when sysFiles says it isn’t.

This happened on a SQL 2000 system. I wanted to change the autogrowth on a file from 10% to a MB setting. I did this through the GUI and got an error saying the system couldn’t find the file I was trying to change.

I then tried to change it using ALTER Database. It still said there was no such file.

I right-clicked on the DB and checked the files again. Then I had a look in dbo.sys.files and could see it. I went through windows Explorer and took a look at the file. It was there and in the place where SQL was telling me it was – yet it still insisted it wasn’t there.

Solution

I ran this script:

SELECT *
FROM master..sysaltfiles
WHERE dbid= DB_ID();

This time the file had a different logical name. It was actually a better name than the one sysfiles was claiming. So I ran a script to change th efile’s name to the one sysAltFiles had:

ALTER Database DBName
MODIFY FILE (NAME = ‘DBName_data’, NEWNAME = ‘ DBName _Data’);

Now I was able to get in and change the autogrow setting.

DBCC Shrinkfile and the Schema Changes Report

This is obvious when you think about it, but it caught me out. We have a set minimum size for our databases of 50MB. It’s slightly abstract and 30MB would probably suit just as well. We also have a reporting system which lets us know if a file is getting full – so we could grow it manually rather than wait for AutoGrow.

I saw a report on a file to say it was full and that the file was only 1MB in size. The file was the TempDB log file. I logged in and grew it to 50MB. The next day I had the same report show up. Logged in again grew it to 50MB, certain that I’d done all this yesterday. So I right-clicked on the database and went to Reports >> Schema Changes – my changes were there but no others.

I checked that there were no jobs shrinking the file and that AutoShrink was turned off.

Today I grew the file to 50MB and then shrank it using DBCC Shrinkfile. Then I checked the report again. There was nothing to show that the file had been shrunk. I said at the beginning it was obvious. The report shows schema changes and so only operations which use Alter Database Modify File show in the report. DBCC commands are not schema changes and so won’t show.

In the meantime I’m going to run a trace and catch that DBCC shrink file command…

Install SQL Server 2005 on Windows 7

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

SSIS Package Location

To amend the default path for the SSIS package store amend this file on the server:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

<Folder xsi:type=”FileSystemFolder”>

<Name>File System</Name>

<StorePath>E:\SSIS</StorePath>

</Folder>

Deny Select on System Objects

For Meta data tables users can see records for each object they have permission for.

So if I have 10 tables and I deny access to one of them for you, you will only see nine items if you run Select * from sys.Objects

http://msdn.microsoft.com/en-us/library/ms187113.aspx

You should be able to deny access through a different route:

DENY VIEW DEFINITION TO UserName;
This will deny view definition to all objects

Permission to view the SQL Error Logs

USE Master;

GRANT  EXECUTE ON xp_readerrorlog TO [DomainName\AD USER or Group];

They should now be able to run this:

exec xp_readerrorlog;