Request a topic or
contact an Arke consultant
All posts tagged 'sql'

Arke Systems Blog

Useful technical and business information straight from Arke.

About the author

Author Name is someone.
E-mail me Send mail

Recent comments




The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2024

Truncate_Only no longer supported in SQL Server 2008

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.

Categories: SQL Server
Posted by Wayne Walton on Monday, March 16, 2009 3:04 PM
Permalink | Comments (0) | Post RSSRSS comment feed

LinqDataSource with uniqueidentifier Primary Key

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.

Categories: LINQ
Posted by Eric Stoll on Sunday, January 20, 2008 2:18 PM
Permalink | Comments (0) | Post RSSRSS comment feed

DNN Performance Troubleshooting

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.

Posted by eric stoll on Thursday, January 3, 2008 6:13 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Extending LINQ to SQL

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
            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.

Posted by Eric Stoll on Wednesday, January 2, 2008 1:40 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Serving PDF Reports from ASP.NET using SQL Reporting Services

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.ContentType = "application/octet-stream"

Response.AddHeader("Content-Type", contentType)

Response.AddHeader("Content-Disposition", "attachment;filename=" & sReportName & ".pdf")

End Using


Posted by Eric Stoll on Wednesday, January 2, 2008 9:39 AM
Permalink | Comments (0) | Post RSSRSS comment feed