Muthusamy Anantha Kumar

This article examines how to upload multiple files to a SQL Server table. There are many ways to do this. The method I would like to introduce takes advantage of the OSQL.exe utility and the TEXTCOPY.exe utility. Pre-requisite SQL Server 2000 client is installed on the machine where you are running this batch file SQL […]

Latency in a replication environment is the time it takes for a transaction to be transmitted from a publisher to the distributor and from the distributor to a subscriber. In this article, I am going to discuss how to find and monitor the latency frequency in a transactional replication environment. Pre-requisite Both Publisher and Subscriber […]

In part I of this article, we discussed how to compare the query results executed on two different servers. In part II, we are going to discuss how to compare results of the same query on the same or different server but on different databases. This article takes advantage of a MS-DOS batch file and […]

In the Database administration world, it is often necessary to run a query on the production box and then run the same query on the QA or UAT box and compare the results. In this article, I would like to introduce a method that takes advantage of an MS-DOS batch file and SQL Server utility, […]

The system table, "sysperfinfo," is the representation of the internal performance counters of SQL Server. This article demonstrates how to retrieve and store information from "sysperfinfo" to a CSV file, so that it can be analyzed. Step 1 Create the following procedure, "usp_WriteToFile," as shown in Fig 1.1. set quoted_identifier off go use master go […]

and OBJECTPROPERTY(id, N’IsUserTable’) = 1) drop table [BlockLastBatch] GO CREATE TABLE [BlockLastBatch] ( [spid] [int] NULL , [blocked] [int] NULL , [last_batch] [datetime] NULL ) ON [PRIMARY] GO Step 2 Create procedure usp_writetofile as shown in the figure Fig 1.1 Fig 1.1 Source Code set quoted_identifier off go use master go if exists (select * […]

Part of Database Administrator’s job is to monitor blocking in SQL Server. By taking advantage of the Microsoft Knowledge Base article 271509, we can monitor the blocking and capture the SQL statements that are involved in the blocking to a file. This can be accomplished by updating the procedure from KB Article 271509 in such […]

This article examines how to take advantage of VB Script and MS-DOS batch files to restore multiple DTS packages stored in the form of structured storage files from one folder to a SQL Server box. When many DTS packages reside as structured storage files, it can be tiresome to restore those DTS packages one by […]

In a large IT department, it can be difficult to locate where new SQL Server installations were made and how many of those SQL Servers were patched, etc. This article explains how to scan the entire network for a range of IP addresses to find which boxes have SQL Server instances installed. The OSQL utility […]

Scalar sub-queries return exactly one column value from one row. Scalar sub-queries can be used in CASE expressions, WHERE clauses, ORDER BY clauses and SELECT clauses. This article reviews a few examples of how to use scalar sub-queries in most situations. Scalar Sub-Query in CASE Expression First, create a table with sample data as shown […]

MAK examines how to save all of the DTS packages from SQL Server to a structured storage file automatically by taking advantage of Microsoft’s stored procedure, "DTSRUN.exe." As you know, DTS can have many versions. This procedure will dump every version of the same DTS package as different file. Step 1 Create the folder "DTS" […]

MAK examines how to automate the "Generate SQL Script" by using a stored procedure to take advantage of "scptxfr.exe" provided by Microsoft. Step 1 Create the folder "MSSQLScripts" on the SQL Server box’s hard disk drive. Eg: MKDIR d:\MSSQLScripts Step 2 Execute the source code below in query analyzer. [refer Fig:1.1] This will create the […]