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
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…
1) What types of JOINS exist?
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…
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;…
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.
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