If you've ever used the Truncate_Only to shrink logs in SQL Server 2000/2005, it may come as a surprise to you that its use has been discontinued in SQL Server 2008.

Instead, you can use the following commands to get SQL Server to do essentially the same thing:

 
Alter Database %databasename% Set Recovery Simple

 And then

Alter Database %databasename% Set Recovery Full

You can then shrink the log file as normal.

I tend to use uniqueidentifiers for my primary key columns with a default value of newid() so the database will generate a Guid for me.  I created a LinqDataSource and bound that to a DetailsView control.  I was able to insert my first row within 10 minutes of writing all this code but subsequent inserts would always throw a unique constraint exception.  After looking at the database, I saw my first row's Id was Guid.Empty so I knew that Linq was pushing this as a default value to the database instead of letting the database create a newid() for me.  It turns out the solution is very simple.  Open your DBML file, select the primary key column and change the Auto Generated Value property to true.  So far, I'm still impressed with Linq.

I was troubleshooting a client's DNN site today because the response time was horrible.  Their site was relatively small and every page load was taking up to 30 seconds.  By logging in to the production server, I could see that the CPU would spike to 100% on the sqlsrvr.exe process during the entire page load so that gave me a starting point.  Then I dug in to SQL Profiler to identify the problematic query.  The resolution was to delete everything from the ScheduleHistory table.  I purged the data and the website immediately became fast again.  If you notice SELECT queries on the ScheduleHistory table taking a long time, this may be the solution to your problem.

We have been experimenting with LINQ to SQL on a couple projects lately and I am constantly impressed.  I'll post some tidbits as I come across some neat features.

Today I had to display the last 4 digits of a customer's credit card number on the UI.  I already have a DBML file in my project and the Customer table has been added to the designer.  Customer has a CreditCardNumber property (ignoring encryption for purposes of this example).  Of course business logic layers aren't new but all too often, you would find logic in the UI that displays the right 4-digits.  But now implementing the BLL is even easier by simply implementing a partial class.

Partial Public Class Customer
    Public Readonly Property Last4 as String
        Get
            Return Right(Me.CreditCardNumber, 4)
        End Get
    End Property
End Class

You can take it one step further and override the CreditCardNumber property and change the Get to return ************XXXX instead of the clear text credit card number, which will make your business logic even more secure.

I recently had to create a PDF on the server-side and deliver it to the client via ASP.NET.  There are several open source PDF APIs out there so the options are vast.  But I chose to leverage SQL Reporting Services instead of learning another API.  Plus, why program a report when you can design it using proven tools?

Using SQL Server Business Intelligence Development Studio, I created a reports project, developed a report called Report1, and deployed to SQL Reporting Services.  Here is a great article if you want help creating your first report.  Tutorial: Designing Your First Report in SQL Server Reporting Services.

Next, in my ASP.NET application, I added a Web Reference to http://server/Reportserver/reportservice.asmx and named it ReportsWS, which generates all the proxy classes needed to interface with Reporting Services from my code.  Now I just have to write a little bit of code to generate the report and deliver the PDF.  In the example below, you can also see how to pass parameters to the report named StartDate and EndDate.

Using rs As New ReportsWS.ReportingService()
    rs.Credentials = New System.Net.NetworkCredential(ConfigurationManager.AppSettings("RSUser"), ConfigurationManager.AppSettings("RSPassword"), ConfigurationManager.AppSettings("RSDomain"))
    Dim sReportPath As String = ConfigurationManager.AppSettings("RSReportPath")
    Dim sReportName As String = "Report1"
    Dim ResultStream() As Byte
    Dim StreamIdentifiers() As String = Nothing
    Dim optionalParams(2) As ReportsWS.ParameterValue
    Dim OptionalParam As String = Nothing
    Dim optionalWarnings As ReportsWS.Warning() = Nothing 
    optionalParams(0) = New ReportsWS.ParameterValue
    optionalParams(0).Name = "StartDate"
    optionalParams(0).Value = "1/1/2007"
    optionalParams(1) = New ReportsWS.ParameterValue
    optionalParams(1).Name = "EndDate"
    optionalParams(1).Value = "12/31/2007"
    ' Create and set the content type string
    Dim contentType As String = "application/pdf"
    ResultStream = rs.Render("/" & sReportPath & "/" & sReportName, "PDF", Nothing, "<DeviceInfo><StreamRoot>/RSWebServiceXS/</StreamRoot></DeviceInfo>", optionalParams, Nothing, Nothing, OptionalParam, OptionalParam, optionalParams, optionalWarnings, StreamIdentifiers)
    Response.Clear()
    Response.ContentType = "application/octet-stream"
    Response.AddHeader("Content-Type", contentType)
    Response.AddHeader("Content-Disposition", "attachment;filename=" & sReportName & ".pdf")               
    Response.BinaryWrite(ResultStream)
    Response.Flush()               
    Response.End()           
End Using