|
|
 |
Send fax from SQL Server using Microsoft Word
|
| Total Hit (9841) |
This article explains how you can create a Microsoft Word document from T-SQL and fax it through a method exposed by its Automation object model.
You can create and destroy OLE Automation object using the sp_OACreate and sp_OADestroy, whereas you can use the sp_OAMethod to invoke a method, and t
....Read More |
Rating
 |
|
|
|
Statistic functions that take Null values into account
|
| Total Hit (2841) |
Statistic functions - that is MAX, MIN, SUM, AVG, VAR, VARP, STDEV and STDEVP - don't take Null values into account. Usually this isn't a problem with the SUM function, but it can be an issue with the others. For example, you can evaluate the average value of a group of records keeping Null values i
....Read More |
Rating
 |
|
|
|
The ALL clause can speed up UNION statements
|
| Total Hit (2827) |
If you omit the ALL clause in an UNION statement, SQL Server must delete duplicate values, which in turn means that it has to sort the two sub-resultsets that have to be combined. Needless to say, this is a time-consuming operation.
In most cases, you decide whether to use the ALL clause dependin
....Read More |
| Rating
|
|
|
|
The fastest way to delete all the rows in a table
|
| Total Hit (2816) |
The standard way to delete all the rows in a SQL Server table is a DELETE statement without a WHERE clause:
«Code LangId=6»
DELETE FROM MyTable
«/Code»
However, in most cases you can speed up your code remarkably with the TRUNCATE TABLE command: «Code LangId=6»
TRUNCATE TABLE MyTable «/Code
....Read More |
| Rating
|
|
|
|
Understanding the TOP WITH TIES clause in SELECT queries
|
| Total Hit (3037) |
The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group. To see what this means in practice, execute the following query against the Pubs database in SQL Server 7.0:
«Code LangId=6»
SELECT TOP 5 price, titl
....Read More |
| Rating
|
|
|
|
Update SQL Server database with Updategrams
|
| Total Hit (2643) |
You can use XML to transfer both data and commands. In this column I'll show how you can update a database by means of XML commands sent over the HTTP protocol and, even more interesting, how to perform bulk uploads with these commands.
An updategram is a piece of XML data that contains informati
....Read More |
| Rating
|
|
|
|
Update SQL Server records via HTTP
|
| Total Hit (2189) |
Thanks to the SQL Server extensions for the Web and XML, it is now possible to query a SQL Server database (and get the result as XML) as well as insert, update, and delete records in a database. In this tip I'll show a few examples of this technique. Here's a first example that shows how you can se
....Read More |
| Rating
|
|
|
|
Programmatically start and stop SQL Server main service
|
| Total Hit (3159) |
Using the SQL-DMO object model you can programmatically start, stop, pause, and continue the main SQL Server service. In the following code snippet the server is "MyServer", and "sa" / "mypwd" is the user name and password of a user that is allowed to start/stop the service:
«Code LangId=6»
' N
....Read More |
| Rating
|
|
|
|
Extract records by their record number
|
| Total Hit (2931) |
SQL Server, and the SQL language in general, doesn't support record numbers, so you can't extract a set of records if you know their position in the resultset. This missing capability would be extremely convenient when displaying pages of data in an ASP program. For example, if each page contains 10
....Read More |
| Rating
|
|
|
|
How to improve performance with pinned tables
|
| Total Hit (2526) |
SQL Server experts know that the DB engine never accesses directly the data pages on disk; rather it uses a special module called "Cache Manager". It is possible, by using some T-SQL commands, to force the selected tables to stay in the buffer for a longer time. This technique increases the performa
....Read More |
| Rating
|
|
|
|
How to restore a SQL Server database marked as "suspect"
|
| Total Hit (10675) |
At times a database appears to be marked as "suspect" in the Enterprise Manager. SQL Server marks a database as suspect with it can't access the database. What happens at a low level is that SQL Server sets one of the bits in the status field in the sysdatabases table.
In general, this problem ha
....Read More |
| Rating
|
|
|
|
Improve I/O throughput of intelligent subsystems to favor SQL Server
|
| Total Hit (2424) |
One of the peculiar features of Microsoft SQL Server is its capability to keep alive multiple threads at the same time for performing I/O operations. The configuration options that affects how many threads available for these operations is max async IO. The default value for this option is 32, which
....Read More |
| Rating
|
|
|
|
|
|
Perform an MSDE database backup through Transact-SQL
|
| Total Hit (2645) |
The MSDE database engine is 100% compatible with SQL Server and subject to the same license policy of the Jet engine. One of the fundamental differences between MSDE and SQL Server is the lack of any graphical tool for the administration. Many users take advantage of Access 2000 as administrative fr
....Read More |
| Rating
|
|
|
|
Restore the SQL Server master database
|
| Total Hit (2800) |
Restoring a corrupted master database is one of the most difficult tasks you may have to deal with. If this catastrophic event happens, here's how to proceed.
First, you need to rebuild the master database. Details on this operation depend on the SQL Server version you run. With SQL Server 6.5 t
....Read More |
| Rating
|
|
|
|
Advanced Lock Monitoring
|
| Total Hit (2901) |
In order to assist continuous and effective monitoring of lock status, SQL Server offers the system stored procedure SP_LOCK, which can provide several useful pieces of information on how locks are set and removed.
However, using this information - such as process ID, database ID, and object ID -
....Read More |
| Rating
|
|
|
|
Avoid error 1540 in Join queries
|
| Total Hit (2782) |
When you plan your queries, you should avoid the use of wildcard keyword * - which means "retrieve any field" - mainly because it may decrease performances, since the engine allocates space for the entire row; use explicit field declaration instead, such as:
SELECT field1, field2 FROM table1
....Read More |
| Rating
|
|
|
|
Create temporary or regular table with SELECT INTO
|
| Total Hit (3162) |
The SELECT INTO statement is a combination of the SELECT and INSERT T-SQL commands, that lets you create a new table from a subset of the rows and/or the columns of another table. The target table of this command is often a temporary table:
«Code LangId=6»
SELECT au_fname, au_lname INTO #authors
....Read More |
| Rating
|
|
|
|
GROUP BY and UNION may return unsorted resultsets
|
| Total Hit (3167) |
In SQL Server 6.5 and previous versions, the GROUP BY clause in a SELECT statement was carried out by temporarily sorting the resultset before grouping similar rows, therefore the end result was always sorted. For this reasons, many programmers omitted the ORDER BY clause, because the result was sor
....Read More |
| Rating
|
|
|
|
Monitor stored procedure and batch activity from remote workstations
|
| Total Hit (2416) |
Using the sp_user_counterX stored procedure (where X is a number between 1 and 10) you can set internal SQL Server counters. This values are read by the Performance Monitor, the standard Windows utility that monitors system activity. The Performance Monitor is able to monitor not only the activities
....Read More |
| Rating
|
|
|
|
Null values in WHERE clauses
|
| Total Hit (3161) |
A SELECT query returns all the rows for which the WHERE clause returns True. However, many developer - especially those accustomed to other programming languages, such as VB - get confused on this point, and assume that the query returns the rows for which the WHERE clause returns any non-False valu
....Read More |
| Rating
|
|
|
|
Quickly copy records between tables with same structure
|
| Total Hit (2952) |
The SQL language offers a simple and effective way to quickly move all records from a table to another table with same field structure, that is same fields' name, type and position:
«Code LangId=6»
INSERT Customers SELECT * FROM New_Customers
-- you can optionally complete the move by
-- delet
....Read More |
| Rating
|
|
|
|
How can I drop a table on a Linked Server ?
|
| Total Hit (2592) |
Assume that you have to execute the command DROP TABLE tableName on a linked server. The typical syntax that someone will try is:
DROP TABLE serverName.master.dbo.TableName
This for some reason fails ... If you were wondering why this happens then the answer is simple. The context of the user
....Read More |
| Rating
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Import all files in a directory using DTS (Multi Pkg)
|
| Total Hit (2241) |
This example shows how you can use two DTS packages to control the import of all files in a given directory. For this, it is assumed that all files have the same format, and that all files are imported into the same table.
|
| Rating
|
|