I was querying an Order Header table and was coming up with confusing results (the result set had too many rows).  The table used an Identity column as the primary key.  The table also contained an order confirmation number column that should have been unique.  To make a long story short, the reason I was getting confusing results was because there were rows in the Order Header table that contained the same order confirmation number.  This happened when I imported a more recent version of the Order Header table from another database, but neglected to also import a more recent version of the table that controlled the generation of order confirmation numbers.  When I then created new orders, the new Order Header rows were created using older confirmation numbers, and as a consequence, duplicate order confirmation numbers were entered into the table.  This scenario would NOT have occurred if the order confirmation number was the primary key.  An error would have occurred when I tried to add a new order that had a confirmation number that already existed in the Order Header table.

Here are some links to choosing a primary key that I found interesting.

http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx

http://decipherinfosys.wordpress.com/2007/02/01/surrogate-keys-vs-natural-keys-for-primary-key/

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

• I made some changes to .html files on a production web site, yet when I went to my computer, the changes did not take effect (using IE 7).   I then cleared cache (Tools -> Internet Options -> Browsing History -> Delete).  Clearing cache did not work either.  The changes did not show up.  I then reloaded the start page by bypassing cache.  To bypass cache, hold the Cntrl key and press F5 or hold the Cntrl key and press the Refresh button in the toolbar.  Bypassing cache solved the problem.  My changes showed up.
  
• To avoid the security warning you get after copying your Microsoft Access 2007 program to a new directory, copy your Access program to the C:\Program Files\Microsoft Office\Office12\ACCWIZ folder.
  
• Microsoft SQL Server 2008 R2 Report Builder 3.0 provides an intuitive report authoring environment for business and power users. It supports the full capabilities of SQL Server 2008 R2 Reporting Services. The download provides a stand-alone installer for Report Builder 3.0.
  

• From Andy Warren, use Scope_Identity() function instead of @@Identity when retrieving identity for most recently added row to a table.

Scope_Identity is the best way to get the identity value of a just inserted row. It's a drop in replacement for @@Identity and a good way to make sure that you don't have problems in the future if someone adds a trigger that inserts into another table with an identity column.  Also, some more information on the subject from David Hayden.

• I had linked an SQL table in Microsoft Access 2007.  I could not make any edits to the table in Access.  The fix?  The SQL table did not have a primary key defined.  Once I defined a primary key for the table in SQL, and then in Access updated the table in the Linked Table Manager (right mouse click on table in table objects), I was able to make edits to the table in Access.  I found the solution here.

• I had created a stored procedure that included bulk insert statements.  The bulk insert needed to access .txt files from a secure folder.  An SQL Agent ran the stored procedure under the sqlservice account.  Before testing the SQL Agent, we modified the security settings of the secure folder to allow the sqlservice account to read from the folder.  A test was run and the stored procedure generated an error stating that is could not access a .txt file in the secure folder.  Why was the stored procedure unable to access the .txt file in the secure folder?  It turns out in order for the new folder permissions granted to the sqlservice account to take hold, the SQL Server instance where the SQL Agent ran needed to be restarted.  After we restarted the SQL Server instance, the stored procedure was then able to access the files in the secure folder.
• From Pete Brown's blog, here is an excellent video by Jason Dolinger on MVVM.

I changed the name of a database in Microsoft Sql Server 2005.  The database happened to be the default database for a user.  The next time the user tried to connect to the SQL server instance, they received the following error message: Cannot Open User Default Database.  Here is the fix.

SQL Server 2005

You can use the sqlcmd utility to change the default database in SQL Server 2005. To do this, follow these steps:

1. Click Start, click Run, type cmd, and then press ENTER.
2. Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:

   If the SQL Server login uses Microsoft Windows authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

   sqlcmd –E -S InstanceName –d master

   If the SQL Server login uses SQL Server authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

   sqlcmd -S InstanceName -d master -U SQLLogin -P Password

   Note InstanceName is a placeholder for the name of the SQL Server 2005 instance to which you are connecting. SQLLogin is a placeholder for the SQL Server login whose default database has been dropped. Password is a placeholder for the SQL Server login password.
3. At the sqlcmd prompt, type the following, and then press ENTER:
   ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName
   Note AvailDBName is a placeholder for the name of the existing database that can be accessed by the SQL Server login in the instance.
4. At the sqlcmd prompt, type GO, and then press ENTER.

• Custom Sort from Andy Warren.  How to get United States to appear first in a drop down list of countries. 
  
  Select CountryName
  From Countries
  order by
    Case when CountryName = 'United States' then 0 else 1 end,
    CountryName
  
   
• I was having trouble parsing an Update query that was using an inner join.  It turned out that I was missing the From clause in my update statement.

Update Tablename

Set Field1 = 100

Inner Join --Wrong

Update Tablename

Set Field1 = 100

Fom Tablename

Inner Join --Correct 

• In Sequel Server Reporting Services (SSRS), how to hide a textbox (follow link, go to using Complex Expressions, about halfway down within Complex Expressions, Hide a text box on the design surface...) based on a boolean field value in the recordset:

Open Textbox properties Dialaog box, click on Visibility tab, and enter =Not fields!VoidFlag.Value in the Expression box.   

I was setting up security for general users on a production Microsoft SQL database. The security configuration needed to allow the users to execute stored procedures called from a WPF application. Here are the steps I took using Windows Authentication.

1) At the database security level, I did not assign the users to any specific database roles, so the users, by default, were assigned to the public database role, which provides minimum permissions and privileges.

2) Granted Execute Permissions on the stored procedures:  Grant Exec On StoredProcedureName To User/AcitveDirectoryGroup  (We assigned the users to an Active Directory group).   Below is some sql code to simplify the process:

SELECT 'GRANT EXEC ON ' + name + ' TO ' + '[ ActiveDirectoryGroupName ]'
FROM sysobjects
WHERE type = 'P' AND category = 0 And name not Like 'sp_%'
Order By name

3) Deny view capabilities to the public role:

Use DatabaseName
GO
DENY VIEW DEFINITION TO public;  

Further Refinded:

I subsequently found other ways to grant Exec privileges through the creation of a db_executor role.

 a) Grant Exec at the database level through a db_executor role      
              
     CREATE ROLE db_executor
     GRANT EXECUTE TO db_executor   exec sp_addrolemember 'db_executor','YourUser'  
 
b)  Grant Exec at the schema level

       GRANT EXECUTE on schema::dbo TO db_executor

1) Connect a Flat File Source to OLE DB Source first.  Connecting automatically will pre-fill some settings.

2) To speed processing between a Flat File and an OLE DB source, in the OLE DB Destination Editor, set the Data Access Mode to Table or view - fast load.

3) Methods to remove duplicates in the Flat File source

• Add a sort and check remove duplicates check box.
• In OLEDB Destination, redirect duplicate errors to OLE DB Destination Error Output (use the Advanced Editor).

4) SQL Resources

• http://www.toadsoft.com/get2knowSS/
• http://bidshelper.codeplex.com/

5) Another way to remove duplicate rows through the use of the Partition By clause: http://www.sqlservercentral.com/articles/T-SQL/70807/

• Webcast of Sara Ford's 105 Visual Studio tips in 55 minutes: http://oreillynet.com/pub/e/1730
  
  
• I was working with a SSRS 2008 report, and saw this error message in my Visual Studio 2008 output window when I ran the report: [rsInvalidColor] The value of the BackgroundColor property fot the textbox 'textbox36' is Transparent, which is not a valid Background Color. 
  
  Here is the expression I had for setting the background color property for the textbox:
  
  =IIF(Fields!BatchContractedOpenQuantity.Value < 0, "Yellow", "Transparent"")
  
  The fix was to replace Transparent with "#FFFFFF".  
  
  =IIF(Fields!BatchContractedOpenQuantity.Value < 0, "Yellow", "#FFFFFF")
  
  The link where I found the fix.  
  
  
• I was working with the Enterprise Library Logging Application Block, and I noticed that the time being logged was not local time, but UTC time (Coordinated Universal Time).  The time appeared twice in the log record.  In the Timestamp column, and in the Formatted Message column.  The fix for the Timestamp column was easy: set the LogEntry.TimeStamp property to DateTime.Now.  To fix the formatted message, I changed the token from {timestamp} to {timestamp(local)}.  Here is the link where I found the information for the formatted message.
  
  
• I was trying to run a WPF application located on a server in a remote location and it was having a problem connecting to the SQL server located at company headquarters.  I was getting the following error message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.   The fix: Added Connect Timeout=120 to my connection string.  The number 120 is the number of seconds before a timeout occurs.  The default was 15 seconds.

• From Scott Guthrie and Jame Michael Hare 5 Little C# NET Wonders that Make Code Better.

I like the one for the "as" Cast:

2.  The As Cast

How many times have you seen code like this:

   1: if (employee is SalariedEmployee)

   2: {

   3:     var salEmp = (SalariedEmployee)employee;

   4:  

   5:     pay = salEmp.WeeklySalary;

   6:  

   7:     // ...

   8: }

This is redundant because you are checking the type twice.  Once in the is check and once in the cast.  Whenever you find yourself going down this path, prefer the as cast.  This handy cast will cast the type if the types are compatible, or return null if not:

   1: var salEmployee = employee as SalariedEmployee;

   2:  

   3: if (salEmployee != null)

   4: {

   5:     pay = salEmployee.WeeklySalary;

   6:  

   7:     // ...

   8: }

The code reads better without the ugly cast, and you avoid double-checking the type.

I also like the Stopwatch Class:

4.  The Stopwatch Class

How many times have you wished to log how long a particular piece of code took to execute?  Maybe you are trying to track average request time or some such metric, or maybe send a warning message when a stored procedure takes longer than 1 second to execute.

Well, you could do it with DateTime like so:

   1: DateTime start = DateTime.Now;

   2: SomeCodeToTime();

   3: DateTime end = DateTime.Now;

   4: Console.WriteLine("Method took {0} ms", (end - start).TotalMilliseconds);

But DateTime is imprecise for this sort of metric.  You could also query the high resolution timer using Win32 API calls via PInvoke, but this is also very messy and more error prone.

So what’s a C# developer to do?  Enter the Stopwatch class which is in the System.Diagnostics namespace.  The Stopwatch class makes it clean and easy to use the high resolution timer through the convenience of a C# class:

   1: var timer = Stopwatch.StartNew();

   2: SomeCodeToTime();

   3: timer.Stop();

   4: Console.WriteLine("Method took {0} ms", timer.ElapsedMilliseconds);

Much more precise and very clean.  Note there is a static factory method to create a Stopwatch and immediately start it.  You also have the option of creating using new and then starting manually as you choose.