 |
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
 |
|
|
|
|
|
|
|
Search/Replace text in text, ntext datatype column
|
| Total Hit (3114) |
In SQL Server anything text longer than 8000 characters can be stored in TEXT or NTEXT datatype column but when you use TEXT or NTEXT datatype is not same as regular char, varchar, nchar or nvarchar datatype. You can not use string function like ltrim, rtrim, replace etc... Then how do you search an
....Read More |
| Rating
|
|
|
|
|
|
How to enable SSL encryption for SQL Server 2000
|
| Total Hit (2591) |
This step-by-step article demonstrates how to install a certificate on a computer that is running SQL Server 2000 by using Microsoft Management Console (MMC) and describes how to enable SSL Encryption at the server, or for specific clients.
«a href='http://support.microsoft.com/?id=316898'»http:/
....Read More |
| Rating
|
|
|
|
How to find tablesize and estimated rows for all tables
|
| Total Hit (2706) |
Since there is no easy way using Enterprise Manager to know size and rows for all tables you need to write your own query. Run the following Query to see the result
«code LangId=6»USE Northwind
Go
SELECT a.Name
, SUM(b.RowCnt) EstRowCount
, SUM(b.Used) * 8 TableSize_KB
, (SU
....Read More |
Rating
 |
|
|
|
How to add/configure a new Linked server using T-SQL
|
| Total Hit (8408) |
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
|
|
|
|
How to start SQL Server Agent automatically when SQL Server starts.
|
| Total Hit (3520) |
When you install SQL Server by default SQL Server Agent Service is set to Start Manual mode and some times this can stop your scheduled jobs. Its also possible that you stop Agent and forget to turn it on.
This article will show you how to create a SP which can start SQL Server Agent service if i
....Read More |
Rating
 |
|
|
|
How to recover database from suspect mode?
|
| Total Hit (26305) |
Sometime your database may go into suspect mode. A database can be marked suspect for one of the following reasons (this is from SQL Server Books Online):
«code LangId=0»If one or more database files are not available.
If the entire database is not available.
If one or more database files are
....Read More |
Rating
 |
|
|
|
Finding SQL Server Object Name from PageNumber
|
| Total Hit (6601) |
Most of time when sql server throw an error will tell you object Id or PageNumber.
This article will show you how to find which object caused the error.
«b»Step-By-Step Example«/b»
Lets say you got the folloing error from SQL Server
«b»Error«/b»
«font color=red»Error: 7105, Severity:
....Read More |
| Rating
|
|
|
|
UDF : ParseArray - return table from CSV
|
| Total Hit (3509) |
Sometimes we may need to store values in one field using some sort of seperating character. For example if you want to store multiple values in one field instead of normalizing your table and creating a whole new table to store multiple records. This articles will show you how you can create a UDF w
....Read More |
| Rating
|
|
|
|
Shrinking transaction log file on sql server 7.0
|
| Total Hit (1929) |
Many times when I do bult insert/delete operation my transaction log grows very fast and I use this scipt to shrink it. This simple scrip will show you how to shrink transaction log file in SQL Server 7.0 when your DBCC SHRINKFILE command wont work. In most cases DBCC SHRINKFILE(<log filename>,<new
....Read More |
| Rating
|
|
|
|
How to change owner of DTS Package
|
| Total Hit (2742) |
Here is an example to change ownership of a DTS Package of SQL Server. You can use undocumented stored procedure msdb.dbo.sp_reassign_dtspackageowner to change ownership. This stored proc requires 3 parameters. First parameter is @name which is name of package. Second parameter is @id which unique I
....Read More |
| Rating
|
|
|
|
|
|
|
|
|
|
Stored procedure to set/reset columns's identity property
|
| Total Hit (3390) |
You can use ALTER TABLE statement to add/remove column and to change some attributes of a column but there are some limitations with ALTER TABLE statement. You can not use ALTER TABLE to set a column as an identity column (Auto Increment field). You can ADD a new column with IDENTITY property but ca
....Read More |
| Rating
|
|
|
|
|
|
|
|
How to move, copy, and back up full-text catalog folders and files
|
| Total Hit (1500) |
Full-text catalogs and indexes are not stored in a SQL Server database. Full-text catalogs and indexes are stored in separate files that the Microsoft Search service manages. The full-text catalog files are not recovered during a Microsoft SQL Server recovery. Additionally, you cannot use the Transa
....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
|
|
|
|
|
|
Writing Secure Transact-SQL
|
| Total Hit (702) |
There are plenty of good sources of information about how to deploy SQL Server in a secure fashion. However, these resources are often targeted at database administrators tasked with securing already developed applications. In addition, there is a rich body of information that discusses writing secu
....Read More |
| Rating
|
|
|
|
Implementing Error Handling with Stored Procedures
|
| Total Hit (630) |
This is one of two articles about error handling in SQL Server. This article gives you recommendations for how you should implement error handling when you write stored procedures, including when you call them from ADO.
|
| Rating
|
|
|
|
The Curse and Blessings of Dynamic SQL
|
| Total Hit (761) |
In this article I will discuss the of use dynamic SQL in stored procedures in MS SQL Server, and I will show that this is a powerful feature that you should use with care.
|
| Rating
|
|
|
|
How to share data between stored procedures
|
| Total Hit (700) |
Questions you often see in newsgroups about MS SQL Server are: How can I use the result set from one stored procedure in another or How can I use the result set from a stored procedure in a SELECT statement?
|
| Rating
|
|
|
|
Connecting to MS SQL Server from Unix
|
| Total Hit (1042) |
I've written this page as I keep getting questions on how to access MS SQL Server from Unix. I have never had any reason talk to SQL Server from Unix myself, but since people kept asking me how to do it, I decided to compile an web page with the tiny fragments of information and I have. The main foc
....Read More |
| Rating
|
|