SQL Server General DBA

Change Edit Top 200 Rows value in SSMS

One seemingly minor change to SQL Server Management Studio (SSMS) in SQL Server 2008 is that by default it will only allow the visually edit the top 200 rows . This is primary to save on memory which can be drained by loading numerous rows into the SSMS visual editor. If, however, you don’t want […]


SQL Server has the ability to provide the information on  specific columns which  been modified by triggers, by using the COLUMNS_UPDATED function. COLUMNS_UPDATED is an in-built function of SQL server that helps to identify the columns in the table or view that were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern showing which columns […]

Import dbase (dbf) Files using SQL Server.

SQL Server integration Service (SSIS) can be used to import data from heterogeneous data sources ranging from SQL Server, Oracle to CSV, XML files. To Import a single DBF file to SSIS 1. Open Microsoft Visual Studio 2008 and create a SSIS project. 2. Drag and Drop a Data flow task to Control flow and […]

Sending Email to Different Domains using SQL Server

Out of the  box, SQL Server’s database mail feature does not allow sending mails to different domains. Error message is: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2009-11-30T09:44:39). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: […]

SQL Server Upgrade Issues

Upgrading the SQL Server version can have a major impact the applications using the databses. For example, from SQL Server 2005 onwards you are required to use  col IS NULL instead of Col = NULL and so any applications using the later will break. Using  Upgrade Advisor for SQL Server is a good start for any SQL Server however […]

SQL Server Agent job syspolicy_purge_history is failing in the cluster environment.

When you install or upgrade SQL Server 2008 or R2 you will  see a new SQL Server Agent job named syspolicy_purge_history. In fact this is the only job you will see after installing a brand new SQL Server 2008 instance. This job is relevant to Policy Based Management and by default it has a daily […]

SQL Agent job getting suspended.

When you run some SQL Server agent jobs they will be suspended and you will not be permitted to re-run them. This is due the issues with the SQL Server Agent subs system information which  can be obtained from themsdb.dbo.syssubsystems table. To do this execute: SELECT * FROM msdb.dbo.syssubsystems The output for this is shown […]

Queries which include DMFs return a syntax error

When I execute queries which include DMFs the queries return a syntax error. For example: SELECT — TOP 20 qs.sql_handle, qs.execution_count, qs.total_worker_time AS Total_CPU, total_CPU_inSeconds = –Converted from microseconds qs.total_worker_time/1000000, average_CPU_inSeconds = –Converted from microseconds (qs.total_worker_time/1000000) / qs.execution_count, qs.total_elapsed_time, total_elapsed_time_inSeconds = –Converted from microseconds qs.total_elapsed_time/1000000 –, st.text –, qp.query_plan from sys.dm_exec_query_stats as qs CROSS APPLY […]

Could not find stored procedure ‘dbo.sp_MSins_dboTest’

Transaction Replication is failing due to the above error and this error can be shown in the Replication monitor. If you run the following query in your publication database, you will see the following results. SELECT name,          del_cmd,          ins_cmd,          upd_cmd          FROM dbo.sysarticles This means that the above […]

How to change server name when replication is enabled.

How to change server name when replication is enabled. The following steps will change the server name when replication is enabled: 1.       Backup all the replication stored procedures. 2.       Script the replication objects 3.       Disable distributors and publications 4.       Change the server name (This will require rebooting the server) 5.       Log into SQL Server preferably […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |