Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Archives: October 2011

SET ARITHABORT ON

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,174 reads

Posted in SQLServer-DBA on 24 October 2011

ALTER SCHEMA

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

0 comments, 724 reads

Posted in SQLServer-DBA on 24 October 2011

JOIN IN SQL FAQ

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

1 comments, 756 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, 266 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, 2,077 reads

Posted in SQLServer-DBA on 15 October 2011

sys.dm_os_loaded_modules

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

0 comments, 544 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, 1,485 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, 545 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, 746 reads

Posted in SQLServer-DBA on 3 October 2011