 |
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
 |
|
|
|
 |
Creating an XML Document with the XMLTextWriter Class
|
| Total Hit (2751) |
To quickly create a new XML document you can use the XMLTextWriter class. There are many ways to create XML in the .NET Framework, but the XMLTextWriter is one of the quickest.
«Code LangId=2»
Dim mywriter As System.Xml.XmlTextWriter
mywriter = New System.Xml.XmlTextWriter("c:\myxml.xml", Not
....Read More |
| Rating
|
|
|
|
App.Path in VB.NET
|
| Total Hit (3141) |
The APP Object no longer exists in VB.NET, so calling App.Path to get the location of your application does not work. Thankfully there are many other ways to do this in VB.NET, here are a few.
NOTE: Some of these will only work for forms and not DLL's without any UI. The first two that reference
....Read More |
Rating
 |
|
|
|
Mail Tutorial in VB.NET
|
| Total Hit (3293) |
Microsoft has made life more easy by adding an in-built .NET class for SMTP Mail. This article will show you how you can take advantage of System.Web.Mail namespace in VB.Net
«B»Basic Mail Message«/B»
«Code LangId=2»
Imports System.Web.Mail
Dim m As New System.Web.Mail.MailMessage()
Wi
....Read More |
| Rating
|
|
|
|
|
|
Running an Insert SQL Query with ADO.NET and VB.NET
|
| Total Hit (1611) |
If you have SQL server setup on your machine and the Northwind database then this example should work for you. You may have to change the password for the sa account as it's blank in the example.
|
| Rating
|
|
|
|
Load a DataGrid Contol with XML Data using ADO.NET
|
| Total Hit (1402) |
You will need to create a new Form, and then add three controls to it. A Button called btnImportXML, a DataGrid called DataGrid and a TextBox called txtPath.
Run the project and type the complete path to your XML document in the Textbox then click the Button. The DataGrid will be loaded with you
....Read More |
| Rating
|
|
|
|
Sending Mail in VB.NET using Sockets
|
| Total Hit (3529) |
This class provides all the functionality. This class was implemented without error handing on purpose to make it easier to understand the program flow.
|
| Rating
|
|
|
|
Sending a File to a Web Server
|
| Total Hit (2928) |
«B»Description«/B»
This is a working example of sending a file to a web server, and then processing the file from memory without saving it to a file.
«B»ASP Page Code«/B»
You will need to create a new Web Form and add this ASP.NET code to it.
«Code LangId=2»
<%@ Page Language="vb" Au
....Read More |
| Rating
|
|
|
|
Quickly Disable Constraint and Triggers
|
| Total Hit (2010) |
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 (1887) |
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 (2651) |
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 (1699) |
«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 (1559) |
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 (2178) |
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 (1883) |
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 (4006) |
«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 (3949) |
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 (3414) |
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 (2550) |
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 (2776) |
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
|
|
|
|
Monitoring Hard Drive Space
|
| Total Hit (2847) |
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
|
|
|
|
|
|
|
|
|
|