 |
Read current sql server error log.
|
| Total Hit (3213) |
This procedure reads the current SQL Error Log. Optionally it can read any log (or text file) specified by @LogFilename
|
Rating
 |
|
|
|
|
|
Quickly Disable Constraint and Triggers
|
| Total Hit (2012) |
In a previous article, I covered how to use the sp_msforeachtable system stored procedure to look through a list of tables and perform an action on them.
When loading data, nothing can be more frustrating than having to deal with stubborn constraints and triggers. This is especially true when you k
....Read More |
| Rating
|
|
|
|
INSTEAD OF Triggers
|
| Total Hit (1888) |
AFTER triggers (also known as FOR triggers) execute following the triggering action, such as an insert, update, or delete. For example, an AFTER trigger on an Employees table will fire after an UPDATE statement has been executed against the Employees table. Thus, the trigger does not fire until the
....Read More |
| Rating
|
|
|
|
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
|
|
|
|
Checking for Changes within trigger
|
| Total Hit (2029) |
The UPDATE and COLUMNS_UPDATED functions are available within both types of triggers to allow the trigger to determine which columns were modified by the triggering action statement. For example, the following trigger prevents any modifications to the lastname column in the Employees table. Here, th
....Read More |
| Rating
|
|
|
|
Exploring SQL Server Triggers
|
| Total Hit (2653) |
Triggers are one of the core tools available in relational databases such as SQL Server™ 2000. As one of the mainstays of SQL Server database programming, triggers also happen to be one of the topics that I get most of the questions about. In this month's installment of Data Points, I will explore t
....Read More |
| Rating
|
|
|
|
Complex Updates Using the Case Statement
|
| Total Hit (1700) |
«B»Introduction«/B»
One of the keys to database performance if keeping your transactions as short as possible. In this article we will look at a couple of tricks using the CASE statement to perform multiple updates on a table in a single operation.
«B»Multiple updates to a single column«/B»
....Read More |
Rating
 |
|
|
|
Updating a table from another table
|
| Total Hit (1560) |
As the article title suggests, an UPDATE statement is able to reference data in other tables. This is done using a FROM clause.
Let's use the Northwind database as our example here. A usual update statment to update a product's list price might look something like this.
«Code LangId=6»
UPDATE P
....Read More |
| Rating
|
|
|
|
Deleting Duplicate Records
|
| Total Hit (2179) |
May of us have faced duplicate records issue in SQL Server Table. First, I'll need some duplicates to work with. I use following script to create a table called dup_authors in the pubs database.
«Code LangId=6»
-- If the table exists, drop it
use pubs
go
If exists (select * from INFORMATION_SC
....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
|
|
|
|
Using FTP in Transact-SQL
|
| Total Hit (4007) |
«B»Introduction«/B»
I’ve seen a few posts asking “How do I ftp a file into SQL” Well, if you have 6.5 or 7.0 this article should be helpful. Unfortunately it’s not an easy answer, but it’ll work great once you’ve set it up. The answer is that there isn’t a function in SQL to let you do this, you
....Read More |
| Rating
|
|
|
|
|
|
Regular Expressions in T-SQL
|
| Total Hit (3950) |
For this solution we need SQL Server 2000 or higher. Also we need to make sure we have the VBScript.RegExp library on our computer. This should come with most Windows 2000 servers, in the Windows Scripting package. If you are using this on an older version of Windows, you will probably have to downl
....Read More |
| Rating
|
|
|
|
Database Backup Script
|
| Total Hit (3415) |
The procedure should be placed in an Admin database and scheduled - normally to run every day. The table DatabaseBackup should be created as defined near the top of the procedure. The call to be scheduled is in comments near the top of the procedure.
It will backup every database on the server to
....Read More |
| Rating
|
|
|
|
Can I Backup Across the Network?
|
| Total Hit (2553) |
You can backup over the network. I prefer to use UNC paths because mapping drives under SQL Server can cause some weirdness (Hmm... maybe an article on that).
My favorite method is to not even muck with backup devices:
BACKUP DATABASE Foo TO DISK = '\\myserver\myshare\foo.bak' WITH INIT
....Read More |
| Rating
|
|
|
|
Quickly Shrinking the Transaction Log
|
| Total Hit (2777) |
In this article I would like to discuss about the following two things:
1) About shrinking the transaction log.
2) Different ways of taking the database offline.
1) Shrinking the log:
The following is a trick for quickly shrinking the size of the transaction log which you think has beco
....Read More |
| Rating
|
|
|
|
|
|
How to transfer database diagrams to a different database?
|
| Total Hit (2657) |
Database diagrams are stored in the 'dtproperties' table within the database. So, database diagrams can be transferred to a different database, by transferring the contents of this table.
For example , run the following query to transfer the diagram named 'MyTableDesign' from 'pubs' database to '
....Read More |
| Rating
|
|
|
|
|
|
|
|
|
|
How to search all columns of all tables in a database for a keyword?
|
| Total Hit (3514) |
While browsing the SQL Server newsgroups, every once in a while, I see a request for a script that can search all the columns of all the tables in a given database for a specific keyword. I never took such posts seriously. But then recently, one of my network administrators was troubleshooting a pro
....Read More |
| Rating
|
|
|
|
Code to find out the statement that caused the trigger to fire!
|
| Total Hit (1816) |
Sometimes you may want to find out what exact statement that updated your table. Or you may want to find out how the WHERE clause of the DELETE statement (Executed by someone) looked like.
DBCC INPUTBUFFER can provide you with this kind of information. You can create a trigger on your table, tha
....Read More |
| Rating
|
|
|
|
Procedure to import SQL Server error log into a table (SQL 2000)
|
| Total Hit (3277) |
This procedure reads the SQL Server error log using sp_readerrorlog and imports the error log's contents into a specified table. It accepts three parameters:
@log_name is the name of the table into which the error log will be imported
@log_number is the error log number, 0 is default and refers
....Read More |
| Rating
|
|
|
|
Stored procedure to generate a simple or complex random password
|
| Total Hit (3047) |
This procedure generates random passwords using RAND() function. It can be configured to generate a simple or a complex password. You can also customize the length of the password generated. Complex passwords will include upper and lower case letters, numbers and special characters. See the code to
....Read More |
| Rating
|
|
|
|
|
|
|
|
|
|