Microsoft SQL Server Tips & Tricks

Article
8/6/2021

Tips and Tricks for the SQL Server Enthusiast

Sql Server 2005 Resource Taking long time to come online on Windows Cluster with Resource Manager Creation Failed reported in Errorlog

Recently we faced an issue where in clustered instance of sql server 2005 was taking long time to...

Author: Parikshit Savjani Date: 10/14/2009

Script to find the Objects Owned by Orphaned Users

Below is the script that would first identify and display Orphaned users. Then this output is passed...

Author: Parikshit Savjani Date: 10/13/2009

Receive a warning about the network binding order on the Setup Support Rules page when install SQL Server 2008 in a failover cluster

While installing sql server 2008 setup we might face following warning in the installation window...

Author: Parikshit Savjani Date: 10/08/2009

How to identify Orphaned Logins and Objects owned by Oraphaned Logins

Below is the script that would first identify and display Orphaned users. Then this output is passed...

Author: Parikshit Savjani Date: 10/08/2009

How to change the Service account and restart the Service using WMI.

Often we required the service account to be changed and this requires a restart of the service for...

Author: Parikshit Savjani Date: 10/08/2009

Performance Benefits of using Expression over User Defined Functions

Summary No matter how simple a UDF is, there's a large performance penalty paid when they're used....

Author: Rajesh Setlem Date: 10/06/2009

Cannot Alter Schema of Replicated Article

Suppose you encounter an error message wherein you have a replication setup and want to change the...

Author: Parikshit Savjani Date: 10/01/2009

Behavior of WITH NOWAIT option with RAISERROR in SQL Server

In some situations you would like to use WITH NOWAIT option in SQL Server RAISERROR statement, but...

Author: Parikshit Savjani Date: 10/01/2009

SQL Server 2005 Integration service upgrade fails from RTM to SP3 however Database service succeeds

It’s a very rare scenario witnessed when an service pack upgrade from RTM to SP3 fails for SSIS...

Author: Parikshit Savjani Date: 09/29/2009

Stemming Functionality in Full Text Search using CONTAINS

Many a times you may have encountered issues related to Full Text Search functionality in SQL Server...

Author: Parikshit Savjani Date: 09/28/2009

BCP command fails with ERROR:"User name not provided…", when both SQL Server 2000 and SQL Server 2005 Tools are installed on machine

Here’s the first ever issue I’ve encountered with SQL Server BCP Utility. Issue: Executing below BCP...

Author: Parikshit Savjani Date: 09/24/2009

SQL browser service is not running on server.

We ran into a situation when SQL browser service was not running on the server, we followed the...

Author: AmruthaVarshiniJ Date: 09/18/2009

OOM error when we access Schema changes report from SSMS

If your profiler traces have a lot of information collected in the traces, then you might encounter...

Author: Parikshit Savjani Date: 09/09/2009

SQL Server Analysis Services Port (SQL 2005 / 2008)

Default Port:2383 You can change default Port for AS in msmdsrv.ini file of the Instance. Port Used...

Author: Parikshit Savjani Date: 09/08/2009

A delete statement in SQL Server 2000 takes around 1.5 GB of Transaction Log Space, Where as in SQL 2008 it can take around 3.2 GB!

There are a lot of Changes in the Transaction Log Architecture of SQL 2000 and SQL 2008. There is a...

Author: sequelguy Date: 08/28/2009

Installation of SQL 2005 on Windows 2003 Cluster fails with error "The drive specified cannot be used for program location. Program files must be installed on a valid local disk available on all cluster nodes"

While installing SQL Server 2005 on a windows server 2003 cluster the installation fails. After you...

Author: sequelguy Date: 08/28/2009

Sometimes you do not see the proper event description of SQL Server events in the application log

Sometimes you do not see the proper event description of SQL Server events in the application log....

Author: sequelguy Date: 08/27/2009

Query to the SYSTEMINDEX to read the Microsoft search results fails when using Search.CollatorDSO provider

We can query the results from the Microsoft search through SQL server. If we create a linked server...

Author: sequelguy Date: 08/27/2009

Unable to failover a Named instance of SQL server 2005 in cluster or unable to bring a Named Instance of SQL server 2005 Online

The problem is that we have a Named instance of SQL Server 2005 in a 2 node cluster. While trying to...

Author: sequelguy Date: 08/20/2009

Unable to install SQL Server 2008 Standard Edition on a Windows VISTA machine

The error message we get is as below, <Error Message> 02/04/2009 14:33:20.418 02/04/2009...

Author: sequelguy Date: 08/20/2009

INFORMATIONAL: Understanding and Troubleshooting MSP Error: 29512 SQL Server Setup was unable add user , when you try to install SQL Server 2005

Good morning Folks! Today I will take you through a setup issue that many of you might have run into...

Author: sequelguy Date: 08/20/2009

[DTS PACKAGE FAILS TO OPEN WITH ERROR Dbnetlib][ConnectionRead (WrapperRead()).] General network error

We have observed a number of cases where in the dts package fails to open from the enterprise...

Author: sequelguy Date: 08/20/2009

Analysis Services Preallocate Memory Setting – Insight

Q: What is Preallocate Setting? Answers: Preaallocate Setting specifies a certain percentage of...

Author: sequelguy Date: 08/19/2009

SQL Server 2008 Profiler pads the datetime datatype with 0s when tracing for a ADO.Net application

We have seen that when we use a sql server 2008 profiler trace to trace RPC calls coming from an...

Author: Parikshit Savjani Date: 08/13/2009

How to change the IP ALL TCP Port to a static port from a Dynamic Port using WMI

Many a time there is a need to programmatically change in the SQL Server configuration settings....

Author: Parikshit Savjani Date: 08/11/2009

Unable to run a job in SQL server 2000 service pack 4

Problem Description in our scenario: ============================ We have a default instance of SQL...

Author: Parikshit Savjani Date: 08/10/2009

As most of the times we do it in a simple way descirbed below. but when we try to do the same...

Author: Parikshit Savjani Date: 07/24/2009

Upgrade advisor returns error while upgrading an instance of SQL Server 2000 to SQL Server 2005.

Problem description: Upgrade advisor returns error while upgrading an instance of SQL Server 2000 to...

Author: Parikshit Savjani Date: 07/15/2009

Error: SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)SSMS

Ideally DTS Packages should be upgraded to SSIS packages as we move from sql 2000 to sql 2005/sql...

Author: Parikshit Savjani Date: 07/09/2009

Tampering master.vmp file may result in losing all Analysis Services Databases:

The master.vmp file is the master version map that contains the GUIDS for all of the objects and the...

Author: sequelguy Date: 07/08/2009

Installing Hotfix on SQL Server may throw error 29538 and fail

In this case we were installing hotfix https://support.microsoft.com/?kbid=960089 Microsoft SQL...

Author: sequelguy Date: 07/08/2009

[BCP command calling Stored procedure with dynamic SQL and not having the first statement as a Select fails with the following Error "Error = Microsoft][SQL Native Client]BCP host-files must contain at least one column"

EnvironmentSQL Server 2005 AND SQL Server 2008. The same command works fine in SQL SERVER 2000.Steps...

Author: Rajesh Setlem Date: 06/29/2009

Exception when selecting from SYSINDEXES in SQL Server 2005

During our course of troubleshooting queries taking long time for compilation, co-incidentally we...

Author: Rajesh Setlem Date: 06/25/2009

How to uninstall HOTFIXES manually when the base SQL 2000 is not there in the box

Consider a scenario where SQL 2000 has been uninstalled and Hotfixes are still visible. In my case...

Author: sequelguy Date: 06/12/2009

Upgrade from SQL 2005 to SQL 2008 might fail with MsiGetProductInfo error

Consider a scenario where an upgrade from SQL 2005 to SQL 2008 fails with the following messages...

Author: sequelguy Date: 06/12/2009

Unable to start SQL Server Agent

We are unable to start SQL Server Agent. It was giving the following error when we were trying to...

Author: sequelguy Date: 06/12/2009

Owner of the job will not be able to modify/edit the job In SQL SERVER

Problem Description: ========================= The owner of the job is a Windows login in SQL and...

Author: Parikshit Savjani Date: 06/08/2009

Attempt to take Differential or Transaction Log backups using SQL DMO in a job goes for full database backup always

Problem: You have a VB script to take full database backup and transaction log backup using...

Author: Rajesh Setlem Date: 06/05/2009

Merge Agent fails with The Merge failed to retrieve the snapshot schema script - Merge Replication with Web Synchronization using FTP Over Internet

When you configure a merge replication with web synchronization using FTP over internet, you might...

Author: Parikshit Savjani Date: 06/03/2009

Share via

Microsoft SQL Server Tips & Tricks

[DTS PACKAGE FAILS TO OPEN WITH ERROR Dbnetlib][ConnectionRead (WrapperRead()).] General network error

[BCP command calling Stored procedure with dynamic SQL and not having the first statement as a Select fails with the following Error "Error = Microsoft][SQL Native Client]BCP host-files must contain at least one column"

Additional resources