 |
Some Useful Undocumented SQL Server 7.0 and 2000 DBCC Commands
|
| Total Hit (628) |
In this article, I want to tell you about some useful undocumented DBCC commands, and how you can use these commands in SQL Server 7.0 and 2000 for administering and monitoring.
DBCC is an abbreviation for Database Console Command. DBCC commands are generally used to check the physical and logica
....Read More |
Rating
 |
|
|
|
|
|
|
|
|
|
|
|
Getting the second recent date from a set of values.
|
| Total Hit (461) |
ANSI & T-SQL specific solutions for getting the 2nd recent date from a set of values. This can be extended to answer nth date from a set of values but the ANSI version gets unwieldy & has to be modified for bigger values.
|
Rating
 |
|
|
|
UDF in CHECK & DEFAULT CONSTRAINTS.
|
| Total Hit (522) |
User-defined functions in SQL2000 provide a powerful way to reuse code / logic. This example shows how you can use UDFs in CHECK & DEFAULT constraints of a table.
|
| Rating
|
|
|
|
Read current sql server error log.
|
| Total Hit (679) |
This procedure reads the current SQL Error Log. Optionally it can read any log (or text file) specified by @LogFilename
|
| Rating
|
|
|
|
|
|
How to install MSDE
|
| Total Hit (1365) |
To install MSDE first extract files to a folder e.g. c:\sql2ksp3 then run folloing command on command prompt
c:\>cd sql2ksp3
You can specify installation option either in command line itself or specify in setup.ini
«b»[1] Specifying parameters in command line«/b»
For example the following
....Read More |
| Rating
|
|
|
|
|
|
How to find tablesize and estimated rows for all tables
|
| Total Hit (1240) |
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
 |
|
|
|
Importing and Exporting Data with SQL Server
|
| Total Hit (357) |
Transferring data, whether into or out of a database, can be a complex process. SQL Server provides several tools that help you in a variety of situations, including the Bulk Copy Program, the Select statement, the Transfer Manager, and Query results. This chapter examines those options, and times w
....Read More |
| Rating
|
|
|
|
Import all files in a directory using DTS (Multi Pkg)
|
| Total Hit (719) |
This example shows how you can use two DTS packages to control the import of all files in a given directory. For this, it is assumed that all files have the same format, and that all files are imported into the same table.
|
| Rating
|
|
|
|
Dynamic ORDER BY Statements in Stored Procedures
|
| Total Hit (196) |
When creating data-driven Web applications, it is ideal to place the actual SQL statements into stored procedures, and to have the Web pages, then, call the appropriate stored procedures rather than executing the SQL statements themselves. (If you are unfamiliar with what stored procedures are, or t
....Read More |
| Rating
|
|
|
|
|
|
SQL Server 2000 DTS Part 9 - DTS Designer Tasks
|
| Total Hit (534) |
The last installment of this series discussed the basic principles and applications of the Transform Data task. This article continues, focusing on its more advanced features.
|
| Rating
|
|
|
|
|
|
Implementing CRUD Operations Using Stored Procedures: Part 1
|
| Total Hit (327) |
That's the American Heritage dictionary definition but not the database definition. When you are working with databases, CRUD is an acronym for the four essential database operations: Create, Read, Update, and Delete.
|
| Rating
|
|
|
|
|
|
Dealing with MS SQL Tables that contain Duplicate Rows
|
| Total Hit (183) |
Every so often, you might have to deal with tables that contain duplicate rows. In one case, you might only need to identify the duplicate rows. In other cases, you might need to remove the duplicate rows. This article will show you some different techniques for dealing with duplicate rows.
....Read More |
| Rating
|
|
|
|
Backup Table Data in SQL Server 7.0 and 2000
|
| Total Hit (601) |
With SQL Server 6.5, Microsoft gave us an opportunity to do a backup (DUMP Table) of the tables. However, after 6.5, with both 7.0 and 2000, that feature has been removed for some reason. Here is a stored procedure that I wrote which gives similar functionality for backing up table data. This is ver
....Read More |
| Rating
|
|
|
|
Nightly Failed Jobs Report
|
| Total Hit (443) |
Do you get tired of reviewing each SQL Agent notification nightly to determine which SQL Agent jobs failed? Are there a significant number of SQL Agent job notifications that it takes a while to review each? Here is an alternative that allows you to have a single email report of all job failures.
....Read More |
| Rating
|
|
|
|
OLAP and Data Warehousing - Data in Jail
|
| Total Hit (645) |
Please find below the follow-up to the first part in my OLAP and Data Warehousing (The Problem and Solution) series, a series dedicated to the discussion of OLAP and Data Warehousing technologies.
A lack of world economic stabilization over the last few years has resulted in a growth of competit
....Read More |
| Rating
|
|
|
|
|
|
Deleting an Excel WorkSheet from within a package
|
| Total Hit (527) |
Here is a simple and quick way of deleting a worksheet in an Excel spreadsheet. It uses two global variables, one for the Excel file name and one for the sheet to delete. As this uses OLE automation of the Excel application object, you will need Excel installed on any machine that this package execu
....Read More |
| Rating
|
|
|
|
Make the Execute Package Task name dependent only
|
| Total Hit (136) |
By default the Execute Package Task will use the PackageID (GUID) to load and execute a package. The PackageID is assigned when you save a new package. A simple illustration of this is to open a package and then use Save As, giving the package a new name. If you where to replace the target or child
....Read More |
| Rating
|
|
|
|
How to loop through a global variable Rowset
|
| Total Hit (173) |
The Execute SQL Task for SQL Server 2000 allows both input and parameters to be specified. In this article we will be focusing on the Rowset output parameter functionality, and what we can then do with the Recordset it returns. The Rowset parameter takes the result set of a SELECT statement and stor
....Read More |
| Rating
|
|
|
|
|
|
Exporting SQL Data as XML
|
| Total Hit (1531) |
In my last article, Importing XML into SQL Server 2000, we looked at various techniques to import the XML data into SQL Server. To complement that, this article will focus on some of the methods that you can use to export relational SQL data to the XML format.
More specifically, we'll explore th
....Read More |
| Rating
|
|