|
|
|
|
 |
Updating Derived Columns using Triggers
|
| Total Hit (1643) |
INSTEAD OF triggers are also commonly used to UPDATE the base columns in calculated columns. For example, assume that a view exists called vwOrdersOrderDetailsProducts as shown here:
«Code LangId=6»
CREATE VIEW vwOrdersOrderDetailsProducts
AS
SELECT o.OrderID,
o.OrderDate,
....Read More |
Rating
 |
|
|
|
|
|
How to display COM error description in SQL Stored procedure ?
|
| Total Hit (3401) |
When we deal with COM object using sp_OAxxxx stored procedure sometimes you need exact error description to Identify the problem. Here is the solution for this type of problem. sp_displayoaerrorinfo stored procedure will display any error encountered while executing sp_OAxxxx stored procedure.
....Read More |
| Rating
|
|
|
|
Rotate a Table in SQL Server
|
| Total Hit (1657) |
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 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
|
|
|
|
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
|
|
|
|
Maximum Value from 5 columns.
|
| Total Hit (1414) |
This script shows how to obtain the maximum value of 5 columns. It is possible to do this using CASE statement but the expression gets quite lengthy. This shows an easy way to use the MAX function itself & this approach can be extended to as many columns as you want.
....Read More |
| Rating
|
|
|
|
|
|
Passing a CSV or Array to a Stored Procedure
|
| Total Hit (1884) |
This stored procedure takes two parameters @ARRAY and @SEPARATOR. It loops through the array variable and pulls out the values inside it. Right now the stored procedure just prints the values it finds. It's written so as to be easily customizable to do exactly what you need it to. Enjoy and happy pa
....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
|
|
|
|
|
|
How to add/configure a new Linked server using T-SQL
|
| Total Hit (8416) |
Here is the T-SQL script to add linked server using sp_addlinkedserver SP. Once you add linked server you can add login using sp_addlinkedsrvlogin. By default current login is mapped with linked server means remote machine must have exact same login with same password in order to get access to the l
....Read More |
Rating
 |
|
|
|
File details using NT commands & undocumented SP.
|
| Total Hit (2290) |
Getting file details is easy using the undocumented extended SP 'xp_getfiledetails'. But you can't rely on this to be the same between versions of SQL Server or even service packs. So here is an alternative method using the standard NT commands. The undocumented SP is also shown for completeness.
....Read More |
| Rating
|
|
|
|
|
|
Using Sets in MDX Queries
|
| Total Hit (1435) |
This is the sixth tutorial article of the series, MDX in Analysis Services. The series is designed to provide hands-on application of the fundamentals of MDX from the perspective of MS SQL Server 2000 Analysis Services ("Analysis Services,"); our primary focus is the manipulation of multidimensional
....Read More |
| Rating
|
|
|
|
|
|
How do I set up and configure SQL Mail?
|
| Total Hit (1195) |
SQL Mail provides a way to send EMail from Micosoft SQL Server. The focus of this FAQ is configuration of SQL Mail. There are other ways to send Email from SQL Server. Links to articles about these alternatives to SQLMail are listed at the end of this FAQ.
....Read More |
| Rating
|
|
|
|
|
|
MDX Essentials: Basic Set Functions: The EXCEPT() Function
|
| Total Hit (775) |
This is the fifteenth article of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.
....Read More |
| Rating
|
|
|
|
|
|
XPSMTP.DLL - SQL Server SMTP Mail XP
|
| Total Hit (684) |
XPSMTP provides a SMTP based SQL Mail solution for sending MIME based email over SMTP, implemented as an Extended Stored Procedure.
It does not require any software to be installed, just a SMTP email server that can handle the outgoing mail request. XPSMTP is using TCP/IP sockets to communicate
....Read More |
| Rating
|
|
|
|
|
|
Enhanced SQL Server Security Auditing
|
| Total Hit (1373) |
Security audit in SQL server captures only successful and failed logins. It does not capture the application that uses the login. Login used by an application may have full read and write access on all of the tables and procedures but the application restricts the users by providing a front-end whic
....Read More |
| Rating
|
|
|
|
XML and SQL 2000 (Part 1)
|
| Total Hit (1206) |
One of the most common problems with managing data is its versatility. This becomes evident when exchanging data between multiple data sources and results mainly from incompatibilities in data representation and format. The impact of this type of problem affects most companies internally, but is eve
....Read More |
| Rating
|
|
|
|
Cursors: An Overview
|
| Total Hit (1126) |
We'll talk about the basics of cursors. These let you move through records one at a time and perform processing on each record.
|
| Rating
|
|
|
|
Connecting to an AS400 with DTS
|
| Total Hit (565) |
DTS does not support AS400 connectivity natively, but by obtaining a third party ODBC or OLE-DB provider you can connect quite easily. In no particular order, some links for suppliers of such providers:
|
| Rating
|
|
|
|
How to Decipher/Understand SSIS Error Code?
|
| Total Hit (958) |
If you have worked with SSIS, you may have been doing something like this, in case of errors, you saved the error code and the error column to a text file or some other media.
|
| Rating
|
|