|
|
|
|
 |
How to use ORDER BY clause in the definition of a view?
|
| Total Hit (2514) |
SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language (SQL) and the mathematics upon which it is based, we can't fully explain th
....Read More |
Rating
 |
|
|
|
|
|
Rotate a Table in SQL Server
|
| Total Hit (1659) |
This article describes how to rotate a SQL Server table. Suppose you have a table that is named QTRSALES. The table has the columns YEAR, QUARTER, and AMOUNT with the data in the following format (note that there is no row for the fourth quarter of 1996):
Year Quarter Amount
------
....Read More |
| Rating
|
|
|
|
|
|
How to Fix Inconsistent Metadata Errors (SQL Server 7.0)
|
| Total Hit (3345) |
Why does changing a table on a SQL Server 7.0 server cause "OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time." to occur when query the table from a SQL Server 2000 server via link to other.
BASIC UNDERSTAND
....Read More |
Rating
 |
|
|
|
How to perform update on multiple tables of linked server.
|
| Total Hit (2458) |
This code demonstrates
1) How to write update trigger which checks modification for a specified field (e.g. here DistrictId)
2) How to handle Errors during update
3) How to update data on linked server.
4) How to do update which requires joining tables
5) How to use XACT_ABORT option to handle
....Read More |
| Rating
|
|
|
|
|
|
|
|
Selective Defrag/Reindex and Log
|
| Total Hit (3124) |
SQL 2000 Only
This script will create a stored procedure and a table in the master database. Simply run the stored procedure and supply a database name as a parameter. It will do the following:
1. Gather/Log DBCC SHOWCONTIG statistics (pre defrag)
2. Defragment all user indexes in the dat
....Read More |
| Rating
|
|
|
|
Create Excel XLS from T-SQL
|
| Total Hit (3998) |
This is a T-SQL script that uses OLE, ADO, Jet4 ISAM, and Linked Server to create and populate an Excel Workbook (XLS) file from T-SQL query. If the Excel Worksheet exists, the query will append to the "table".
The code is designed to be used by SQL Agent and to append to the step output with verb
....Read More |
| Rating
|
|
|
|
Get all user tables with size
|
| Total Hit (1554) |
Returns User Tables by SIZE in DESCENDING ORDER
Add TOP n clause in SELECT to get selective results (i.e. TOP 10 TABLES)
|
| Rating
|
|
|
|
Monitoring Disk Space and Sending Alerts with TSQL
|
| Total Hit (5726) |
Monitoring disk space is one important task for SQL Server DBAs. To proactively monitor disk space, we want to be notified when disk space is below certain level. We also want to collect database file size information over time for trend analysis, for which Gregory Larsen has an excellent article "A
....Read More |
| Rating
|
|
|
|
How to implement simple transaction in SQL Server
|
| Total Hit (2162) |
Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete
....Read More |
| Rating
|
|
|
|
How to implement nested transactions
|
| Total Hit (4242) |
SQL Server allows you to nest transactions. Basically, this feature means that a new transaction can start even though the previous one is not complete. Transact-SQL allows you to nest transaction operations by issuing nested BEGIN TRAN commands. The @@TRANCOUNT automatic variable can be queried to
....Read More |
| Rating
|
|
|
|
How to do error handling in transaction.
|
| Total Hit (2224) |
The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do is halt the sequential processing of the code and either branch to another code segment in the proc
....Read More |
| Rating
|
|
|
|
SAVE TRAN and Save Points.
|
| Total Hit (2536) |
Savepoints offer a mechanism to roll back portions of transactions. A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. SQL Server allows you to use savepoints via the SA
....Read More |
| Rating
|
|
|
|
How to script SQL tables using SQL DMO
|
| Total Hit (2254) |
«B»Introduction«/B»
SQL-DMO encapsulates SQL Server components and presents them as attributes of the component piece to you as properties of an object instance. You would have to alter the properties of the instance or use object methods to automate SQL Server administration. This article takes
....Read More |
| Rating
|
|
|
|
|
|
Full-text indexing: Advanced Queries
|
| Total Hit (2518) |
In our first two parts we covered creating a full-text index, updating the index and some basic queries. In this segment we'll cover some of the more advanced topics. The first is ranking the results we recieve. We do this using the CONTAINSTABLE statement. An sample query and result set are shown b
....Read More |
| Rating
|
|
|
|
Full-text indexing
|
| Total Hit (2539) |
«B»part-1 :«/B»
Full-text indexing: Overview and Installation
«B»part-2 :«/B»
Full-text indexing: Updating and Querying
|
| Rating
|
|
|
|
Monitoring Hard Drive Space
|
| Total Hit (2850) |
This script monitors available disk space for the specified drive. If a drive name is not specified in @drivename parameter the usp_dba_diskspace_monitor stored procedure will check all drives against a space limit (MB) passed through @spacelimit parameter. If available disk space is less then @spac
....Read More |
| Rating
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|