|
|
How to find first/last record in the group?
|
Total Hit (4481) |
Yes this is certainly a common situation where you have to write a query to only extract first or last record from the group.
For example you might want to extract only first product from each category. Let's look at some examples.
«code LangId=6»Drop table #TempTable
Go
create table #Te
....Read More |
Rating
|
|
|
|
|
SQL Server 2000 useful undocumented stored procedures
|
Total Hit (3892) |
In this article, I want to tell you about some useful undocumented stored procedures shipped with SQL Server 2000.
«B»sp_MSget_qualified_name«/B»
The sp_MSget_qualified_name stored procedure is used to get the qualified name for the given object id.
Syntax
sp_MSget_qualified_name objec
....Read More |
Rating
|
|
|
Tricks using a numbers table
|
Total Hit (1586) |
Solving most problems using a relational technique is more elegant and better in terms of performance. This script shows few examples that uses a Numbers table to solve some common problems. Keep a table with Numbers & it will prove very handy.
|
Rating
|
|
|
|
|
Enumerate the users' rights on a DB's objects
|
Total Hit (2736) |
SQL Server provides several security levels, and the control that the system administrators can pursue over the DB's objects is extremely accurate. It's often useful to know which rights each user has. As you might know already, a lot of administrative SQL Server tasks can be performed through the p
....Read More |
Rating
|
|
|
The ALL clause can speed up UNION statements
|
Total Hit (2824) |
If you omit the ALL clause in an UNION statement, SQL Server must delete duplicate values, which in turn means that it has to sort the two sub-resultsets that have to be combined. Needless to say, this is a time-consuming operation.
In most cases, you decide whether to use the ALL clause dependin
....Read More |
Rating
|
|
|
Quickly Disable Constraint and Triggers
|
Total Hit (2007) |
In a previous article, I covered how to use the sp_msforeachtable system stored procedure to look through a list of tables and perform an action on them.
When loading data, nothing can be more frustrating than having to deal with stubborn constraints and triggers. This is especially true when you k
....Read More |
Rating
|
|
|
|
How to use stored proc inside View?
|
Total Hit (2536) |
SQL Server allows you to define loopback Linked server. You can execute linked server stored proc using openquery statement. Before you use openquery make sure that 'data access' option is enabled.
«code LangId=6»exec sp_serveroption 'binaryworld','data access','true'
go
create view vw_Who
....Read More |
Rating
|
|
|
|
How to create Format File using BCP command
|
Total Hit (22024) |
When you bulk import into a SQL Server table or bulk export data from a table, you can use a format file to store format information for each field in a data file relative to that table. A format file provides a flexible system for writing data files that requires little or no editing to comply with
....Read More |
Rating
|
|
|
|
Stored procedure to generate a simple or complex random password
|
Total Hit (3032) |
This procedure generates random passwords using RAND() function. It can be configured to generate a simple or a complex password. You can also customize the length of the password generated. Complex passwords will include upper and lower case letters, numbers and special characters. See the code to
....Read More |
Rating
|
|
|
The comparison of SQL Server 2000 with MySQL v4.1
|
Total Hit (889) |
Often people in newsgroups ask for a comparison of Microsoft SQL Server and MySQL. In this article, I compare SQL Server 2000 with MySQL version 4.1 regarding price, performance, platforms supported, SQL dialects and product limitations.
|
Rating
|
|
|
XML and SQL 2000 (Part 5)
|
Total Hit (1055) |
In this article, we will continue exploring different ways of accessing SQL Server data in the XML format via HTTP. As we explained in the previous article, this type of functionality becomes available after creating a virtual directory corresponding to the target database using IIS Virtual Director
....Read More |
Rating
|
|
|
MDX Essentials: Member Functions: More "Family" Functions
|
Total Hit (679) |
This is the sixth article of the series, MDX Essentials. The primary focus of this series is an introduction to MDX. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designe
....Read More |
Rating
|
|
|
|
|
Data Transformation Services (DTS) in Microsoft SQL Server 2000
|
Total Hit (675) |
Database administrators often import, export, and transform data in support of tasks such as data consolidation, archiving, and analysis; for application development purposes; and for database or server upgrades. Data Transformation Services (DTS) in SQL Server 2000 provides a set of graphical tools
....Read More |
Rating
|
|
|
|
Linked Servers on MS SQL Part 2
|
Total Hit (977) |
In Part 1 of this series, we reviewed the purpose and reasons for using linked servers, as well as when not to use them. The first linked server created was to an Excel sheet containing data from the Authors table of the SQL Pubs database. We will now move on to other data sources and security.
....Read More |
Rating
|
|
|
Minimizing SQL Server Stored Procedure Recompiles
|
Total Hit (699) |
This article discusses the reasons that SQL Server 2000 decides to recompile a stored procedure and demonstrates techniques that can be used to minimize recompilation. If your system makes extensive use of stored procedures, minimizing recompilation can give you a nice boost in performance.
....Read More |
Rating
|
|
|
|
Monitor Event Viewer on Multiple SQL Servers
|
Total Hit (694) |
Muthusamy Anantha Kumar discusses three methods to monitor SQL Server Event Log on a list of servers and store the output either in a .CSV file or on a database table.
|
Rating
|
|
|
How to Do SQL Server Log Shipping ?
|
Total Hit (1559) |
This article is about log shipping, a refined variation of the traditional manual standby failover server process. Its two major benefits over the traditional methods is that it automates most of the manual work and helps to reduce potential data loss even more.
....Read More |
Rating
|
|
|
How to manipulate the Execute Process task.
|
Total Hit (805) |
Here is some code showing how we can dynamically manipulate the Execute Process task. Say we have an executable "c:\PassFile.exe" and it accepts one argument /F which is for a file. We want to be able to pass in whatever file we choose and we also want to be able to set some of the properties of the
....Read More |
Rating
|
|