 |
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 (2840) |
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 (2826) |
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 (2814) |
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 (3036) |
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 (2641) |
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 (2188) |
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 (3156) |
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
|
|
|
|
 |
Avoid OnEndPage in ASP components under IIS 5
|
| Total Hit (5097) |
As explained elsewhere in this knowledge base, you are suggested not to rely on the OnStartPage method to get a reference to the main ASP objects, if your component is to be run under IIS 5. However, if you do run such a component under IIS 5, at least you should ensure that it doesn't expose the On
....Read More |
| Rating
|
|
|
|
Avoid querying the ServerVariables collection
|
| Total Hit (5514) |
You can access a lot of useful information through the ServerVariables collection, but this has a price in terms of performance. More precisely, the first time you reference this collection in a page, IIS must collect all the data that is necessary to create the collection. All the subsequent refere
....Read More |
| Rating
|
|
|
|
Beware of the Dictionary object in ASP pages
|
| Total Hit (5173) |
The Scripting.Dictionary object is marked as having a "Both" threading model, which means that you can freely assign it to a Session or an Application variable. However, the actual threading model for this object is "Apartment", which means that you should use it only at the page scope level. You ca
....Read More |
| Rating
|
|
|
|
Buffering is on by default in IIS 5
|
| Total Hit (5211) |
You can speed up ASP by adding the following statement at the beginning of an ASP page:
Response.Buffer = True
This statement must be executed before any HTML text is sent to the client. When buffering is on, ASP sends data to the client browser only when the page has been processed, or when
....Read More |
| Rating
|
|
|
|
Cache frequently used read-only data in Application variables
|
| Total Hit (5038) |
Unlike Session variables, there is only one instance of each Application variable. This means that storing data in an Application variable - including memory-consuming data such as long strings or arrays - doesn't severily impact on system performance (unless you store strings with thousands and tho
....Read More |
| Rating
|
|
|
|
Cache frequently used read-only data in Application variables
|
| Total Hit (5411) |
Unlike Session variables, there is only one instance of each Application variable. This means that storing data in an Application variable - including memory-consuming data such as long strings or arrays - doesn't severily impact on system performance (unless you store strings with thousands and tho
....Read More |
| Rating
|
|
|
|
Create a GUID from ASP using SQL Server
|
| Total Hit (5948) |
At times you may need to create a GUID from ASP, for example when assigning unique IDs to users that are visiting your site for the first time. While you can generate new GUIDs from VB quite easily with a call to the CoCreateGUID API call (as explained elsewhere in the Tip Bank, see below), you can'
....Read More |
| Rating
|
|
|
|
Don't mix script languages on the same page
|
| Total Hit (5323) |
You should never use more than one script language on the same ASP page, for example by mixing pieces of VBScript with portions of JavaScript. The reason is that there is a limit to the number of script engines that ASP can cache.
When IIS processes an ASP file, it parses the script code and cac
....Read More |
| Rating
|
|
|
|
|
|
Don't use implicit connections when opening a Recordset
|
| Total Hit (5170) |
As most ADO developers know well, there are basically two distinct syntax for opening a Connection and then a Recordset: you can explicitly create and open a Connection object, and then pass it to the 2nd argument of the Recordset's Open method (or assign to the Recordset's ActiveConnection property
....Read More |
| Rating
|
|
|
|
Ensure that server-side debugging is disabled in production sites
|
| Total Hit (5080) |
Before going to production, you should ensure that the server-side script debugging is off for your ASP application. In fact, not only does server-side debugging slow down your application, it also forces all ASP request to be served by the same thread. In other words, you don't take advantage of II
....Read More |
| Rating
|
|
|
|
Advanced Lock Monitoring
|
| Total Hit (2900) |
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 (2780) |
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 (3161) |
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 (3165) |
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
|
|
|
|
 |
Listing all available SQL Server
|
| Total Hit (2182) |
Thanks to the SQL-DMO object model, it's very easy to list all the available SQL Server 7.0 installations. For example, this code loads all the SQL Servers registered on the local machine into a ComboBox control:
«Code LangId=1»
' NOTE: this code assumes that you've added a reference to the
'
....Read More |
| Rating
|
|
|
|
Manage the SQL Server date format with SQL DMO
|
| Total Hit (3083) |
If you haven't localized SQL Server for your language, the default date format is the American one: DD/MM/YY. You may need to display a date in different formats, and you can solve this problem with the help of SQL DMO. A possible solution is using the T-SQL CONVERT() function to show the date with
....Read More |
| Rating
|
|
|
|
Monitor stored procedure and batch activity from remote workstations
|
| Total Hit (2415) |
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 (3160) |
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 (2951) |
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
|
|