April 29, 2000

Avoid error 1540 in Join queries

When you plan your queries, you should avoid the use of wildcard keyword * – which means “retrieve any field” – mainly because it may decrease performances, since the engine allocates space for the entire row; use explicit field declaration instead, such as:SELECT field1, field2 FROM table1 Moreover, consider the

Listing all available SQL Server

Thanks to the SQL-DMO object model, it’s very easy to list all the available SQL Server 7.0 installations. For example, this code loads all the SQL Servers registered on the local machine into a ComboBox control: ‘ NOTE: this code assumes that you’ve added a reference to the’ SQL-DMO type

Tricks with DateSerial

The DateSerial function has an interesting feature: it doesn’t raise errors when you pass it an invalid month or day number. Instead, it evaluates the date as if the arguments were valid. For example, DateSerial(2000, 1, 32) returns the Date value of February 1, 2000. This behavior can (and should)

Programmatically start and stop SQL Server main service

Using the SQL-DMO object model you can programmatically start, stop, pause, and continue the main SQL Server service. In the following code snippet the server is “MyServer”, and “sa” / “mypwd” is the user name and password of a user that is allowed to start/stop the service: ‘ NOTE: this

Statistic functions that take Null values into account

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

Using CallByName with nested objects

Sometimes Microsoft documentation can be, well lacking is a kind word, and one is reluctant to call tech support when they smoke your credit card first, and ask questions later.My problem was that the CallByName procedure was refusing to call nested lasses. To see what I mean, consider that if

Understanding the TOP WITH TIES clause in SELECT queries

The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group. To see what this means in practice, execute the following query against the Pubs database in SQL Server 7.0:SELECT TOP 5 price, title

The ALL clause can speed up UNION statements

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

No more posts to show