I just came across this nice tip:

One change with SQL Server 2005 is a new option to copy column headers along with the data results.  This option gets set using the query options setting.  To access this setting from SQL Server Management Studio, select Query | Query Options from the menus

You can then paste the columns into excel and you will get the headers as well.

http://www.mssqltips.com/tip.asp?tip=1107

Red Gate has a pretty interesting tool called SQL Refactor:

http://www.red-gate.com/products/sql_refactor/index.htm

The “Layout Code” feature looks awesome.  It basically takes a terrible looking select statement and turns it into a nicely formatted block.

Here is a code project article that shows some of these features:

http://www.codeproject.com/showcase/SQLRefactor.asp

I know Access is old news, but I have recently been asked to take a look at an old Access app.

This application replaces the existing menu at the top with a custom one, which really limits what you can do.

The solution to getting the menu back is to right click in the menu area, select customize, highlight “Menu Bar” and select properties, and change “Allow hide/show…” checkbox.  After you do this, you can then add it back into your form.

I frequently get asked how you can update a join statement in SQL.  Of course, the UPDATE syntax doesn’t allow for you do actually perform an update directly against a JOIN statement, but there are some things you can do to work around this problem.

Probably the best way is to do the update like the example below:

UPDATE Job_Opportunities
SET AwardedId = 1
WHERE JobOpportunityId = (
    SELECT JO.JobOpportunityId 
    FROM Job_Opportunities JO
    INNER JOIN
    Job_results JobResults
    ON BO.JobOpportunityID = BR.Job_id 
    AND JR.is_us = 1 
    AND JR.rank = 1 
    AND JO.JobOpportunityId = Job_Opportunities.JobOpportunityId
) 

This example is updating the Job_Opportunities table, but in the WHERE clause we can use a join to make sure that the only rows that are updated are those that meet the criteria of the join.

When dealing with money, or numbers, sql server provides a few different datatypes: Decimal, Money, Float among others.

The main difference is that Decimal and Money datatypes are “exact”, as opposed to Float which is a “Floating Point Representation”.

When dealing with Floats, you can get some odd mathematical results:
http://www.chrismay.org/2005/09/29/BinaryRepresentationOfDecimalValues.aspx

e.g.
0.4 – 0.1 = 0.30000000000000000000004
1.4-1.1 = 0.29999999999999999999998

So when a number is going to represent money, you should use the Decimal or Money datatypes.  Money is (19,4) which means 19 total digits, 4 of which are right of the decimal point.  Decimal can be setup however you want, like Decimal(10,2).

SmallMoney can also be used, which can represent money values under 1,000,000 w/ 4 decimal places.

One of the challenges in doing merge replication in SQL Server is dealing with IDENTITY columns.

One of the tricks you can use is “automatic identity range management”. 

Basically you tell DB 1 to start at seed 1 and DB 2 to seed at 20,000, and when DB 1 reaches 16,000 or DB 2 reaches 36,000 it will reset those seeds to 40,000 and 60,000.

http://msdn2.microsoft.com/en-us/library/ms152543.aspx

http://msdn2.microsoft.com/en-us/library/ms146907.aspx

It appears to work well.

The problem is that if you restore a sql server database on a different computer than the backup was created, you can have the problem that the usernames and permissions are somewhat lost.

Each username has an associated SID.  So even though you might have a user named ‘Bill’ and the restore database might have a user named ‘Bill’, they will have different SIDs and so the permissions will not be applied to the correct user.

To fix this problem you can follow the following steps:

1) run this TSQL:

sp_change_users_login 'report'

2) This will produce a table showing you Users that having issues.

3) run the following TSQL:

sp_change_users_login 'update_one', 'UserNameFromStep2Table','NameOfUserToUse'

In many cases the 2nd and 3rd parameters will be the same string.

More about this SP can be found here: http://doc.ddart.net/mssql/sql70/sp_ca-cz_4.htm