Peter Gfader 's Blog

Showing posts with label sql server. Show all posts

Reporting: 13 questions to answer before getting started

I get the following questions quite often and thought I gather my thoughts together in this blog post

“Which reporting components should we use in WPF?
Is it Infragistics, Telerik, syncfusion”
“Is DevExpress XtraReports Suite still state of the art for reporting?”
“Do you prefer SQL Server Reporting Services (SSRS) over Crystal Reports?”

Figure: Not sure if I should respond or remain silent

SQL can stink too - Code smell in stored procedures

Figure: You don't need to dig deep to find smelly code

I just found this nice piece of TSQL that I came across in a 500 line stored procedure on my current project.

ORM: Should I go Micro?

We had an interesting internal discussion about ORMs (Linq to SQL, Entity Framework, …) and MicroORMs (Dapper, Massive, PetaPoco, …). This discussion led me to think about: When should I use a MicroORM?

I would go for the approach: Use both (MicroORM and BigORM) and use them where they make sense.

Reporting Services 2.0 Tip - Pimp up your report with data bars

If you are stuck into Report Builder 2.0 world because you are still using SQL Server 2008 and not the shiny new SQL Server 2008 R2. Check out the following tip.
--> Report Builder 3.0 works only in conjunction with SQL Server 2008 R2

My problem

Bad: Boring data with numbers that are hard to compare - Sorting is on another column not shown here

SSRS - QA from the BI class

Q1: Where do i get help for the expression syntax in the report designer?
Q2: How can I create a report template, so that I don't have to start from a blank report each time?
Q3: Is it possible to skin the Report server Website? I can make an asp.net page, but I think the Report Server page is probably adequate. Probably the better option is to use report services in Sharepoint?

SSIS and SSRS - QA from the BI class

Q
SSIS can export data into excel files, and SSRS can also do this. Which way is better / more appropriate in which circumstances?

SQL Server Integration Services - QA from the 1st BI class

Q1 How to trigger SSIS/SSRS jobs manually/on-demand without using the SQL Agent. How can I let end users trigger dtsx packages. Are there API hooks that I could run from a VBScript form (ideally) or perhaps .NET
Q2 I would like to know how you loop through different directory and upload files from various directories using SSIS. It will be great if you can give some demo.

SQL Server - Generate triggers for your "LastModified" columns with a fancy SQL script!

Every table in our database should have a "LastModified" column to record the last modified time of each row.

Figure: Sample table with a LastModified column

Our rule "Do you have standard Tables and Columns?" says the column should be called "DateModified" which has the same purpose.

How do you populate that field?

log4net – How to change settings of an appender at runtime

I found this awesome blog post about 4 tips on log4net from the year 2005.

Since the code is out to date I post here my update.

2 nice things:

I use EntityConnectionStringBuilder to extract the database connection string from an entity framework connection string
If my log4net connectionstring, holds {auto} , we replace it, otherwise we wont
(so that we can change the logging database, if we want to)

My daily problems with XML, XQuery in SQL Server 2005.

We save XML in a VARCHAR column.

Why not VARCHAR , if you serialize and de-serialize only in your .net business layer.

But what if you want to create a report on this xml data?
Then you have to extract single elements from this xml string.

That's easy with XQuery in SQL Server, I thought.

That’s our table

Created with this

CREATE TABLE [dbo].[UnfinishedApplication](
 [ApplicationId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 [OrganisationId] [uniqueidentifier] NOT NULL,
 [ApplicationName] [varchar](100) NOT NULL,
 [State] [varchar](max) NOT NULL,
 [DateCreated] [datetime] NULL,
 [DateUpdated] [datetime] NULL,
 [EmpUpdated] [varchar](150) NULL,
 [EmpCreated] [varchar](150) NULL,
 [SSWTimestamp] [timestamp] NULL,
 [StepUrl] [varchar](350) NOT NULL,
 CONSTRAINT [PK_UnfinishedApplication] PRIMARY KEY CLUSTERED 
 (
 [ApplicationId] ASC
 )
)

We try this:

select [State].query('/UnProcessedApplication/Title/text()')
FROM [UnfinishedApplication]

And get

Msg 4121, Level 16, State 1, Line 2
Cannot find either column "State" or the user-defined function or aggregate "State.query", or the name is ambiguous.

Because we have a VARCHAR column and not an XML column. Let’s convert it to xml, that’s easy :-)

We try this:

select convert(xml,[State]) as tempXml
FROM [UnfinishedApplication]

And get

Msg 9402, Level 16, State 1, Line 2
XML parsing: line 1, character 39, unable to switch the encoding

Arrggghhh, because:

<?xml version="1.0" encoding="utf-16"?> 
   <UnProcessedApplication xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   --- SNIP ----

We serialize this object UnprocessedApplication in our .net business layer :-|
That’s the reason we have this nice character encoding in the xml declaration element :-)

Solution?

We just remove the declaration and convert that to XML. Then we can use finally XQuery :-)

SELECT (convert(xml, replace([State],'<?xml version="1.0" encoding="utf-16"?>', ''))).query('/UnProcessedApplication/Title/text()')
FROM [UnfinishedApplication]

We created a view for extracting this single fields. But I think a calculated column (COMPUTED COLUMN) would be better for this.
Is this possible todo in a computed column?
Hmm….

Memos of my LinqDatasource experience

How to group by more than 1 table column (attribute, property)?

GroupBy="new(Roles.RoleName, Roles.RoleId)" Access with: Key.RoleName, (for example in your listview or gridview)

<asp:LinqDataSource runat="server" ID="ldsUsers"
   ContextTypeName="MyPrivateProject.Test.DataAccess.MyPersonalPrivateDataContext"
   TableName="Users"
   GroupBy="new(Roles.RoleName, Roles.RoleId)"
   OrderGroupsBy="Key.RoleName"
   OrderBy="UserName, Lastname, Firstname"
   Select="new(Key, Count() As RecordCount, It As Users)"  >
</asp:LinqDataSource>

How to use Guids in LinqDataSource where Parameters?

Use DbType="Guid" instead of Type="xxx". TODO: Check input param! e.g. MyPage.aspx?UserIdInput=xxxasd-grrrr-blabla-TEXT-FFAILS --> throws an error

<asp:LinqDataSource runat="server" ID="ldsUsers"
   ContextTypeName="MyPrivateProject.Test.DataAccess.MyPersonalPrivateDataContext"
   TableName="Users"
   Where="UserId = @UserIdInput" EnableUpdate="True">
     <WhereParameters>
          <asp:QueryStringParameter
                 DefaultValue="none" Name="UserIdInput"
                 QueryStringField="UserId" 
              DbType="Guid"  />
     </WhereParameters>
</asp:LinqDataSource>

Syntax highligthing provided by FaziBear's Google widget But removed cause it doesnt support Highlighting of single lines nor sections

Howto: Affected Rows in Sql-Server and Oracle?

How to get the affected rows (processed rows) from an update Query?

IMMEDIATLY after statement
In Oracle use: SQL%ROWCOUNT
In SQL Server use: @@RowCount

When doing Synchronization of tables this is very useful!!!

Synchronization PseudoCode

       INSERT INTO xxx VALUES SELECT FROM yyy
       EXCEPTION
       WHEN OTHERS
          UPDATE xxx SELECT FROM yyy

Bad Example - catch Exceptions, do something on Exception that is expected (and happens very often)

       CREATE OR REPLACE
       PROCEDURE sample IS
          v_rows_processed integer := 0;
       BEGIN
           UPDATE sample
              SET testno = 1;
              WHERE test   = 'PL/SQL';
           v_rows_processed := SQL%ROWCOUNT;
           IF v_rows_processed := 0
           THEN
               /* Insert Statement */
           END IF;
        END sample;.

Good example - Use processed rowcount in that synch case

thx to my co-worker PK. ps: In Sql-Server @@RowCount works even with SET NOCOUNT OFF. SET NOCOUNT ON says that SQl-Server should print the messages: "rows affected" and "rows returned"