 |
Estimate the table size (Data + Index) for a given # of rows
|
| Total Hit (2409) |
Ever tried to estimate the table total size which include data and index in calculation ? Believe me its not easy job to estimate the size of a table for a give # of rows.
Here is the kool SP which does it for you. Full credit goes to Sharon Dooley for his excellent Job.
«code LangId=6»if exis
....Read More |
Rating
 |
|
|
|
Check job status using T-SQL
|
| Total Hit (4883) |
You can use the following stored proc to retrive status of SQL Server job.
If user executing this SP is not in sysadmin then make sure that user has "Select" permission on <Code>msdb..sysjobs</Code> table and execute permission on <Code>master.dbo.xp_sqlagent_enum_jobs</Code> extended stored pro
....Read More |
Rating
 |
|
|
|
|
|
|
|
|
|
Get all user tables with size
|
| Total Hit (1551) |
Returns User Tables by SIZE in DESCENDING ORDER
Add TOP n clause in SELECT to get selective results (i.e. TOP 10 TABLES)
|
| Rating
|
|
|
|
Wrong usage of UPDATE FROM clause.
|
| Total Hit (1518) |
This script demonstrates the wrong usage of UPDATE statement with a FROM clause esp. in case of a table with 1-to-many relationship with another tables(s).
|
| Rating
|
|
|
|
|
|
Code to find out the statement that caused the trigger to fire!
|
| Total Hit (1814) |
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
|
|
|
|
Update SQL Server records via HTTP
|
| Total Hit (2186) |
Thanks to the SQL Server extensions for the Web and XML, it is now possible to query a SQL Server database (and get the result as XML) as well as insert, update, and delete records in a database. In this tip I'll show a few examples of this technique. Here's a first example that shows how you can se
....Read More |
| Rating
|
|
|
|
|
|
Exploring SQL Server Triggers
|
| Total Hit (2650) |
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
|
|
|
|
|
|
Full-text indexing
|
| Total Hit (2534) |
«B»part-1 :«/B»
Full-text indexing: Overview and Installation
«B»part-2 :«/B»
Full-text indexing: Updating and Querying
|
| Rating
|
|
|
|
Error : When I try to backup to a Network share
|
| Total Hit (2876) |
I get the following error message: «b»"Can't open dump device ... device error or
device off line. Please consult the SQL Server error log for more details."«/b»
«b»«u»Resolution:«/u»«/b»
To perform SQL Backup on network share SQL Executive service needs to run under an account that has permis
....Read More |
| Rating
|
|
|
|
A tricky ordering problem.
|
| Total Hit (1747) |
This is 2 of the solutions to an ORDER BY problem posed in the microsoft.sqlserver.programming newsgroups. This demonstrates several powerful derived table techniques.
|
| Rating
|
|
|
|
|
|
generate INSERT statements from existing data.
|
| Total Hit (3196) |
This procedure generates INSERT statements using existing data from the given tables and views. Later, you can use these INSERT statements to generate the data. It's very useful when you have to ship or package a database application. This procedure also comes in handy when you have to send sample d
....Read More |
| Rating
|
|
|
|
Improve I/O throughput of intelligent subsystems to favor SQL Server
|
| Total Hit (2421) |
One of the peculiar features of Microsoft SQL Server is its capability to keep alive multiple threads at the same time for performing I/O operations. The configuration options that affects how many threads available for these operations is max async IO. The default value for this option is 32, which
....Read More |
| Rating
|
|
|
|
|
|
Creating a SQL Server User Interface with InfoPath
|
| Total Hit (660) |
When Office 2003 is released about a month from now, some of the versions will contain two new applications OneNote and InfoPath. OneNote is a note taking application that may be interesting to those of you with tablet PCs. InfoPath is an XML based forms creation and entry application that looks ver
....Read More |
| Rating
|
|
|
|
Setting Up Full Text Search: A Step-by-step Guide
|
| Total Hit (1836) |
In this article, I want to tell you about some general full-text search concepts, including software requirements, the Microsoft Search Service, maintaining full-text indexes, the comparison of full-text indexes with regular SQL indexes, and how to set up full-text search step by step.
....Read More |
| Rating
|
|
|
|
How can I check if a file exists?
|
| Total Hit (662) |
Checking for the existence of a file before trying to import it can be very useful in ensuring your systems run smoothly. You can use the Scripting FileSystemObject to check for the presence of a file using ActiveX Script. If the file exists you can obviously import it or initiate an alternative pro
....Read More |
| Rating
|
|
|
|
How can I change the filename for an Access Connection?
|
| Total Hit (696) |
You can change the Access MDB file of a connection at run-time using an ActiveX Script Task. The following example reads the new filename from a global variable and updates the connection called "Accounts".
|
| Rating
|
|
|
|
Notification Services in SQL Server 2005
|
| Total Hit (1104) |
Notification Services is one of the features included in SQL Server 2005. As the name implies, Notification Services are services which send notifications to the interested entities based on what they would like be notified on. The addition of Notification Services to SQL Server 2005 has inherent ad
....Read More |
| Rating
|
|
|
|
|
|
Locking in SQL Server 7.0
|
| Total Hit (1820) |
In this article, I want to tell you about general details of SQL Server 7.0 locking, about Transaction Isolation Levels, what kind of Transaction Isolation Level exists, and how you can set the appropriate Transaction Isolation Level, about Lock types and Locking optimizer hints, about Lock Escalati
....Read More |
| Rating
|
|
|
|
DTS Servers and Schedulers for MS SQL
|
| Total Hit (2091) |
Learn the basics of creating a DTS server, including how to change the DTS package according to batch schedulers used and the steps required to create and schedule jobs in Autosys and Smartbatch.
|
| Rating
|
|
|
|
Execute a package from Visual Basic (VB)
|
| Total Hit (695) |
To execute a package from Visual Basic is a relatively simple task using the DTS object model. Before you start using the object model you must add a the appropriate reference to your project. From the Project menu select References and check the "Microsoft DTSPackage Object Library".
....Read More |
| Rating
|
|
|
|
Tutorial : A Tour Of ActiveX Script
|
| Total Hit (644) |
One of the ways to gain a lot of Flexibility in DTS is to use an ActiveX script. They can be found in the transform Data Task, the Data Driven Query Task and as a standalone task. When we look at the actual screens there is a lot to see and some of the things can be daunting especially if you have n
....Read More |
| Rating
|
|