SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Archives: October 2011


When completing an installation I saw the following error – which stopped the installation .

The error occurred during the creation of  Indexed View

[2011-10-24 18:21:34.193 02944 error 'App'] A database error occurred: "ODBC error: (42000) - [Microsoft][SQL Server Native Client 10.0][SQL Server]CREATE INDEX failed because the following SET options… Read more

0 comments, 1,826 reads

Posted in SQLServer-DBA on 24 October 2011


Use this select statement to create a list of ALTER SCHEMA statements for all stored procedures in a SQL Server 2005 database. “dbo”  can be changed to whatever schema name required

USE myDb
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name 
FROM sys.Procedures p INNER…

Read more

0 comments, 1,729 reads

Posted in SQLServer-DBA on 24 October 2011


1) What types of JOINS exist?

Inner Joins


Cross Joins    (aka Cartesian Join)

2) Where are JOINS used ?

Joins are implemented in either the FROM or WHERE clause

3) Why use…

Read more

1 comments, 1,355 reads

Posted in SQLServer-DBA on 19 October 2011

SQL Server Collation

Instead of just right clicking on SSMS , these are some alternatives to locating SQL Server Collation information I use.


--To find all collations available 
SELECT * FROM fn_helpcollations()

--To find the SQL Server Collation level 
select SERVERPROPERTY('collation')

--To find the database collation level 
SELECT DATABASEPROPERTYEX('MyDatabase', 'Collation') as DB_SQLCollation;…

Read more

0 comments, 475 reads

Posted in SQLServer-DBA on 18 October 2011

SQL WAIT TYPE – OLEDB and how to reduce it

SQL Server Books online defines the OLEDB wait stat type as “Occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider”

Examples of where OLEDB wait type… Read more

1 comments, 5,788 reads

Posted in SQLServer-DBA on 15 October 2011


sys.dm_os_loaded_modules  is a SQL Server OS Dynamic Management View (DMV).

When executing the t-sql statement below – a recordset is returned . I’ve included some output further down the page :

select file_version,product_version,company,[description] from sys.dm_os_loaded_modules  


I find this dmv useful when troubleshooting corruption .

An example , is to … Read more

2 comments, 1,677 reads

Posted in SQLServer-DBA on 10 October 2011

DBCC checktable

Instead of using DBCC CHECKDB , which checks on consistency and allocation across the whole database, an alternative is DBCC CHECKTABLE , which focuses on integrity checks on tables .

This query iterates through every table and returns the  integrity check results.

The DBCC CHECKTABLE results are inserted to a… Read more

2 comments, 2,539 reads

Posted in SQLServer-DBA on 8 October 2011

TCP Chimney offload

On Windows 2003 Enterprise Edition Service Pack 2 TCP Chimney was enabled by default. It took me a while to discover this feature. But once I did find it , I turned it off.

It was causing numerous problems on many applications , particularly modelling based , long running SQL… Read more

1 comments, 1,125 reads

Posted in SQLServer-DBA on 4 October 2011

SQL Server Database SUSPECT state

According to BOL , a database in SUSPECT state is : “At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem”

 There are numerous… Read more

0 comments, 1,136 reads

Posted in SQLServer-DBA on 3 October 2011