Fatal error 8646 occurred when performing an "ALTER PARTITION FUNCTION"

Today I was just doing some tests to create a partitioned table with a sliding window on an existing table.

So I created my partition function and partition scheme, and did a drop index with move to my partition scheme on the existing table. Everithing (selects, inserts,...) worked well, until I wanted to Alter my Partition function to do an extra RANGE SPLIT. Than I did get a Fatal Error 8646... error message. Fortunately, I remembered some problems with indexes and replication from the past who gave the same "8646" error message. And when looking at the table, I saw there was also an existing non-clustered index. After I dropped the non-clustered index, the problem was solved.

I tried to reproduce the same error with a smaller table, but didn't get any "succes".

sp_MSforeachtable and sp_MSforeachdb

How simple can things be?

I just discovered these "undocumented" sp's. No more cursor creations for these kind of queries ;-). In combination with all those dbcc and dm_... statements and views (i.e. DBCC CLEANTABLE), a good thing to know. They both work in 2000 and 2005 (in 2005 there is no extra filtering on schema level :-s )


Examples:

USE AdventureWorks
go

EXEC sp_MSforeachtable 'select ''?'',count(*) from ?'



DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'

EXEC sp_MSforeachdb @command

Linked Server failure for IBM OLE DB Providers on SQLServer2005 X64 Cluster

I've Created Linked servers to IBM AS400 machines using the IBMDA400 and IBMSQL400 OleDb providers. If I failover, or take off-line and bring back on-line the SQL Resource Group, I get this Error:

Msg 7340, Level 16, State 4, Line 1
Cannot create a column accessor for OLE DB provider "IBMDA400" for linked server "ATCOAIRC".

Solution:

- Browse in the Management Studio to the linked Server, request the properties, and close this window again everytime SQLServer Service starts ;-)

Or, if you don't want to do this manually - Duhhh...

- USE master;
GO

EXEC sp_configure 'show advanced option', '1';
RECONFIGURE

EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE
GO

CREATE PROC usp_enum_oledb_providers
AS

exec sp_enum_oledb_providers

GO

exec sp_procoption N'usp_enum_oledb_providers', 'startup', 'on'


Note: I didn't have this problem with SQLServer or Oracle Linked Servers.

IBM Ole DB error in SQLServer 2005 on x64

Error:

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "IBMDA400" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "IBMDA400" for linked server "(null)".

Or

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
--> An exception occured while executing a Transact statement or batch.
--> Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB Provider "IBMDA400" for linked server.



Solution:

1) Make sure on your SQL Server 2005 x64 (64 bit) machine that "Allow inprocess" is checked for the linked server provider (IBMDASQL in my case).
--> In SQL Mgmt Studio, connect to the SQL Server Database Engine and go to Server Objects->Linked Server->Providers in the Object Explorer.
--> Right-click on IBMDASQL and select Properties. Check the "Allow inprocess" box in the Provider Options dialog that comes up.

2) Enable Ole Automation Procedures;

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Login "error States" in SQL Log

States Description

2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required

SQL Server Management Studio takes a long time to load, how to improve the load performance of this tool?

In general the SQL Server Management Studio takes at least 10 to 15 seconds to load on a user machine. Sometimes the SSMS tool can take a long time to load when there are a lot of SQL Server instances, this problem may also occur on the new 64-bit SQL Server 2005 platform.
One resolution is to add a switch to the Management Studio shortcut as defined below. Access the below screen by going to Start > Programs > SQL Server 2005 program group > 'SQL Server Management Studio' program, right-clicking will get you the following screen by opening the General tab, then select shortcut tab to add 'nosplash' switch:

Also the user needs to tweak the Internet Explorer security settings as follows:
Uncheck two options in Internet Explorer's Advanced Security settings. Namely:
"Check for publisher's certificate revocation"
"Check for server certificate revocation".
Both these steps combined should improve the load time of SQL Server Management Studio.

Moving msdb

Moving MSDB is more complicated than other system databases and requires you to add trace flags and parameters to the SQL Server startup to detach msdb, then remove them and restart SQL Server to attach it:

Start SQL Server with -c, -m and -T3608 and then perform a detach. You would then remove these parameters before attaching MSDB.

Moving System Databases

If you are moving system databases (master, msdb, model, tempdb), you would change the location of master by changing the startup parameters, bringing down the instance, and moving the master.mdf and master.ldf files.
Secondly you need to address the model database, because tempdb is reallocated every time the instance starts. This reallocation requires the existence of model to be successful.
The last one you have to re-address is the msdb, because msdb is used by the SQL Server agent and has no bearing on the stability of the database engine.