 |
Date and Time User-Defined Functions
|
| Total Hit (3842) |
Introduction
Date and Time UDFs
DatePart
TimePart
GetWorkingDays
AddWorkingDays
FirstMonthDay
LastMonthDay
--------------------------------------------------------------------------------
Introduction
I would like to write the series of articles about useful User-Defined Fu
....Read More |
Rating
 |
|
|
|
|
|
|
|
Methods to form complex strings for dynamic SQL execution
|
| Total Hit (1550) |
This T-SQL script shows several techniques to form complex dynamic SQL strings. These eliminate to some extent the coding involved in concatenating string values and making sure to add single quotes appropriately between values. The methods discussed can make the code simpler to read & is less error
....Read More |
| Rating
|
|
|
|
How can I drop a table on a Linked Server ?
|
| Total Hit (2582) |
Assume that you have to execute the command DROP TABLE tableName on a linked server. The typical syntax that someone will try is:
DROP TABLE serverName.master.dbo.TableName
This for some reason fails ... If you were wondering why this happens then the answer is simple. The context of the user
....Read More |
| Rating
|
|
|
|
|
|
|
|
Generate combinations of data from columns.
|
| Total Hit (2182) |
How can you generate various combinations of data from existing columns? This method shows one approach using the CUBE operator with the SELECT statement.
|
| Rating
|
|
|
|
Extract records by their record number
|
| Total Hit (2926) |
SQL Server, and the SQL language in general, doesn't support record numbers, so you can't extract a set of records if you know their position in the resultset. This missing capability would be extremely convenient when displaying pages of data in an ASP program. For example, if each page contains 10
....Read More |
| Rating
|
|
|
|
The fastest way to delete all the rows in a table
|
| Total Hit (2812) |
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
|
|
|
|
|
|
Changing columns to rows.
|
| Total Hit (1464) |
Another example that shows how to convert columnar values into individual rows.
|
| Rating
|
|
|
|
How to drop all temp tables created by current connection.
|
| Total Hit (7328) |
Few days ago I came up with one problem. I was tesing a script in query analyzer. This script was using several temp tables so every time I run this script I had to make sure that all temp tables are dropped before I run the script otherwise it will give bunch of errors. So I decided to write a proc
....Read More |
Rating
 |
|
|
|
SQL Server Lock ( NOLOCK and ROWLOCK )
|
| Total Hit (12523) |
Relational databases, like Microsoft's SQL Server, use locks to prevent multiple users from making conflicting modifications to a set of data. When a set of data is locked by a user, no other users can modify that same set of data until the first user finishes modifying the data and relinquishes the
....Read More |
Rating
 |
|
|
|
Deleting Duplicate Records
|
| Total Hit (2177) |
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
|
|
|
|
|
|
Tutorial : Introduction to Workflow
|
| Total Hit (705) |
This article will give you a quick overview of the three types of workflow precedence constraints available and also highlight a couple of common problems associated with workflow. The most basic feature workflow gives you is the ability to control in which order your tasks will be executed. If all
....Read More |
| Rating
|
|
|
|
Examining SQL Server's I/O Statistics
|
| Total Hit (818) |
Reading and writing to the disk is the heart of what any database management system does, SQL Server included. Input/Output (I/O) performance can make or break an application. This article discusses the diagnostic tools that can be used to examine SQL Server's I/O statistics so that you can make fac
....Read More |
| Rating
|
|
|
|
|
|
|
|
Learning SQL Server 2005
|
| Total Hit (995) |
Everyone wants to get ahead of the curve with SQL Server 2005, but many people don't have the time or desire to dig through a Google search. Steve Jones has gotten quite a few requests for resources, so here is a compiled list of some places that might help you get started.
....Read More |
| Rating
|
|
|
|
How to check the file date
|
| Total Hit (603) |
This sample script was written to check the age of a file. If you import files according to a schedule, you may wish to check that the file is recent to prevent the potential re-import of an old file. The script reads the filename from an existing package connection and uses the scripting FileSystem
....Read More |
| Rating
|
|
|
|
SQL Server 2005 Secures Your Data Like Never Before
|
| Total Hit (717) |
Right out of the box, SQL Server 2005 does not install many of its services (such as SQL Server Reporting Services) or does not have features turned on by default (.NET integration), thereby reducing the attack vectors that hackers could use to compromise your data security.
....Read More |
| Rating
|
|
|
|
"File Watcher" for SQL Server
|
| Total Hit (1253) |
SQL Server Database administrators often come across situations where the scheduled DTS jobs failed because the Source File didn't arrive on time. It is a tedious process to cleanup if there are dependent jobs that have run before the first DTS job completed successfully. The intent of this article
....Read More |
| Rating
|
|
|
|
Execute a package from a package
|
| Total Hit (598) |
You can use a parent or master package to control the execution of one or more child packages. This is often used in a looping scenario, or when there are a number of individual packages that make up a process.
This can be achieved very simply in SQL Server 2000 by using the Execute Package Task
....Read More |
| Rating
|
|
|
|
|
|
using NT Authentication to login into SQL
|
| Total Hit (1112) |
This is for using NT authentication (NT users already setup in the Domain)
So you need to get the correct spelling of each user in User Manager for Domains.
Then it will get the user from NT when they log in and bingo they have permissions to the SQL server database too.
....Read More |
| Rating
|
|
|
|
|
|
Overview of Native XML Web Services for Microsoft SQL Server 2005
|
| Total Hit (1083) |
Get an overview of how to set up and use XML Web Services using SOAP/HTTP inside SQL Server 2005 (formerly known as "Yukon"). Illustrative examples are included. To get the most from this paper, you should have a basic understanding of Web service technologies including HTTP, SOAP, and WSDL.
....Read More |
| Rating
|
|
|
|