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

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
GO
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name 
FROM sys.Procedures p INNER…

Read more

1) What types of JOINS exist?

Inner Joins

Outer Joins (LEFT JOIN, LEFT OUTER JOIN, RIGHT JOIN, RIGHT OUTER JOIN, FULL JOIN , FULL OUTER JOIN)

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

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

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

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

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

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

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