 |
Dynamic execution of SP ( This method is not well-known ).
|
| Total Hit (2460) |
This method of dynamic SQL execution is not well-known. This will work from SQL60 & upwards. This is very useful when calling SPs dynamically with parameters of different datatypes, output parameters & return value.
|
Rating
 |
|
|
|
|
|
|
|
|
|
|
|
Procedure to import SQL Server error log into a table (SQL 2000)
|
| Total Hit (3271) |
This procedure reads the SQL Server error log using sp_readerrorlog and imports the error log's contents into a specified table. It accepts three parameters:
@log_name is the name of the table into which the error log will be imported
@log_number is the error log number, 0 is default and refers
....Read More |
| Rating
|
|
|
|
DATEFLOOR : Rounding dates to nearest day,hour,minute,second
|
| Total Hit (7238) |
This function takes a date and a date part parameter and rounds the date down to the nearest date part.
Credit goes to "Gordon Klundt"
<b>Example:</b>
In order to round '2010-11-03 17:44:10.117' to the nearest hour ('2010-11-03 17:00:00.000')
«code LangId=6»select dbo.datefloor(cast('2010-
....Read More |
| Rating
|
|
|
|
|
|
Statistic functions that take Null values into account
|
| Total Hit (2835) |
Statistic functions - that is MAX, MIN, SUM, AVG, VAR, VARP, STDEV and STDEVP - don't take Null values into account. Usually this isn't a problem with the SUM function, but it can be an issue with the others. For example, you can evaluate the average value of a group of records keeping Null values i
....Read More |
| Rating
|
|
|
|
Useful undocumented extended stored procedures
|
| Total Hit (3138) |
An extended stored procedure (xp) is a dynamic link library that runs directly in the address space of SQL Server and is programmed using the SQL Server Open Data Services API. You can run extended stored procedures from the Query Analyzer, for example, just as you would normal stored procedures. Ex
....Read More |
| Rating
|
|
|
|
Moving Average Example #1.
|
| Total Hit (2301) |
This example shows how to solve the Moving Average problem using a correlated query.
|
| Rating
|
|
|
|
Enumerate all the indexes in a SQL Server database
|
| Total Hit (3999) |
The indexing schema plays a fundamental role in performance tuning, and tools such as the Index Tuning Wizard that can suggest efficient schemas on the grounds of a database usage. Once the indexing schema has been completed, you should document it in order to know which tables have primary keys, wh
....Read More |
| Rating
|
|
|
|
How to transfer database diagrams to a different database?
|
| Total Hit (2652) |
Database diagrams are stored in the 'dtproperties' table within the database. So, database diagrams can be transferred to a different database, by transferring the contents of this table.
For example , run the following query to transfer the diagram named 'MyTableDesign' from 'pubs' database to '
....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
|
|
|
|
Report-enable Your Web Apps with Microsoft Reporting Services
|
| Total Hit (1128) |
Reporting is an integral part of almost all of today's Web applications. However, while the tools to create such applications have matured, Web reporting often boils down to generating tabular reports in the form of HTML tables. Microsoft SQL Server 2000 Reporting Services fills in this gap and give
....Read More |
| Rating
|
|
|
|
Configuring Disks and Managing Space in SQL Server Part 1
|
| Total Hit (944) |
Something like fifty percent of system outages are caused by "Out of space" conditions. I kid you not. I run into them all the time. Combine this problem with the need to maintain high availability and peak performance and the consequence is that disk configuration and space allocation become one of
....Read More |
| Rating
|
|
|
|
Disabling or Enabling All Constraints of a Table or Database
|
| Total Hit (1188) |
These procedures are very important to use when you need to load a bulk of data that you know is correct but when you do not need the foreign keys and check constraints to interfere while you are loading the data. This is also the case when you want to change key values and don't want the constraint
....Read More |
| Rating
|
|
|
|
|
|
MDX Essentials: Basic Set Functions: The Order() Function
|
| Total Hit (960) |
This is the twelfth article of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.
....Read More |
| Rating
|
|
|
|
Understanding BCP/BULK COPY Format Files
|
| Total Hit (1144) |
In Microsoft SQL Server 2000 and earlier, bulk exporting and importing works with a single type of format file. This is supported in Microsoft SQL Server 2005. SQL Server 2005 also supports XML format files as an alternative. To distinguish format files of the original type, they are called non-XML
....Read More |
| Rating
|
|
|
|
Reporting Options for Analysis Services Cubes: Cognos PowerPlay
|
| Total Hit (3300) |
This is the twelfth article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, «a href="http://www.databasejournal.com/features/mssql/article.php/1429671"»Creating Our First Cube«/a», the primary focus of this series is an introduction to the practi
....Read More |
| Rating
|
|
|
|
Powerful New T-SQL Syntax Gives SQL Server a Programmability Boost
|
| Total Hit (2386) |
The T-SQL language in the upcoming version of SQL Server will provide more power and flexibility than previous versions. Additions and enhancements include error handling with the TRY/CATCH construct, SNAPSHOT isolation, and WAITFOR enhancements. Also important are the BULK rowset provider, common t
....Read More |
| Rating
|
|
|
|
SQL Server Notification Services Architecture
|
| Total Hit (958) |
Like most services or applications, Microsoft SQL Server Notification Services is not really one monolithic application. It should be thought of as a series of smaller components that, when girded together in a precise manner, work in concert to produce a notification application. A good understandi
....Read More |
| Rating
|
|
|
|
Backup--Transfer--FTP!
|
| Total Hit (2023) |
Having a standby server in a remote site, copying backup files directly to a Disaster recovery site or other requirements such as transfering files from one location to another using FTP creates the need for creating a FTP batch file using a changing parameter file. In this article, I will walk you
....Read More |
| Rating
|
|
|
|
Import Files with Date-stamped Filenames or Paths
|
| Total Hit (1180) |
DTS packages are fantastic features available in SQL Server 7.0 and SQL server 2000. One outstanding feature in the DTS package is the ability to run ActiveX scripts. ActiveX gives us an added advantage in handling many situations; we will discuss how to handle three of these situations.
File-nam
....Read More |
| Rating
|
|
|
|
How do I know what type of index to create?
|
| Total Hit (1307) |
We get a lot of index questions related to what type of index should be created given a particular situation. I compiled the items below from books-online to facilitate this discussion:
Before creating clustered indexes, understand how your data will be accessed.
....Read More |
| Rating
|
|
|
|
Coping with text files and abnormal column or row delimiters
|
| Total Hit (1239) |
Sometimes you may receive a text file to import that has a row or column delimiter not available in the standard drop-down boxes of the Text File Properties dialog.
The first thing to try in these cases is to type directly into the Row Delimiter or Column Delimiter box. This will work fine if th
....Read More |
| Rating
|
|
|
|
|
|
Changing the DataPump Source and Destination Tables
|
| Total Hit (1082) |
You can use an ActiveX Script to change both the source and destination of the DataPump task.
The first thing you need is the Name of the task. Right-click the DataPump task and select Workflow Properties. On the options tab it shows you the step name. The task name is almost guaranteed to be th
....Read More |
| Rating
|
|
|
|
Connecting to MS SQL Server from Unix
|
| Total Hit (1046) |
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
|
|