Posted: August 14, 2012 in DBA, Maintenance, SQL 2000
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.
Open the job and add this -S serverName\Instance Name before -PlanId.
Then at the end add –SupportComputedColumn
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:
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.
I ran this script:
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.
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…
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
Posted: May 17, 2012 in Permissions
Tags: SQL Security
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
You should be able to deny access through a different route:
DENY VIEW DEFINITION TO UserName;
This will deny view definition to all objects
GRANT EXECUTE ON xp_readerrorlog TO [DomainName\AD USER or Group];
They should now be able to run this: