SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Monthly Archives: February 2012

Generating a Reporting Services proxy class using WSDL.exe

2 Comments Posted by on February 27, 2012

Reporting Services Web Service provides a communication interface for Report Server and the client applications. It uses SOAP over HTTP and provides two endpoints (one for report server execution and one for management). The web services provides multiple methods and types which can be used to play around with Reporting Services.

SSRS 2005 and SSRS 2008 expose three different endpoints, which are defined in different WSDL(Web Service Description Language) files.

  • ReportService2005: This is used to manage and work with Report Server which is running in the Native Mode. The WSDL for this endpoint is typically defined as

http://<servername>/<reportserver>/ReportService2005.asmx?wsdl

Where <servername> is the name of the Machine where SSRS is installed and <ReportServer> is the virtual directory name for Reporting Services web service.

  • ReportService2006: This is used to manage and work with Report servers which are running in the SharePoint Integration Mode. The WDSL for this endpoint is.

http://<servername>/<reportserver>/ReportService2006.asmx?wsdl

  • ReportExecution2005: This endpoint is used to control Report Server Execution. for example, to stop a running subscription.The WDSL for this endpoint is.

http://<servername>/<reportserver>/ReportExecution2005.asmx?wsdl

With SSRS 2008 R2, the functionalities of (ReportService2005 and ReportService2006, now depricated) endpoints were integrated to a single WSDL, ReportService2010.

In order to use these WSDL’s in your application, a web service reference should be added. In case, if we do not want to add a web service reference, we can use the Visual Studio utility WSDL.exe to generate a proxy class for these endpoints and add it to the application.

In this post, I will be talking about how to use WSDL.exe to generate a proxy class for Reporting Services. On my Laptop running Windows7, WSDL.exe is located in the following folder.

“C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin”

Open a Command Prompt window and browse to the above mentioned folder. From the command prompt launch the WSDL.exe. For generating the proxy class, the following Parameters should be enough.

<url or path> A url or path to a WSDL contract, an XSD schema or .discomap document.
/language:<language> The language to use for the generated proxy class.  Choose from ‘CS’,’VB’, ‘JS’, ‘VJS’, ‘CPP’ or provide a fully-qualified name for a class implementing System.CodeDom.Compiler.CodeDomProvider.  The default language is ‘CS’ (CSharp).  Short form is ‘/l:’.
/out:<fileName|directoryPath> The filename or directory path for the generated proxy code. The default filename is derived from the service name. Short form is ‘/o:’.

Let’s say, we need to create the proxy class for the ReportService2005 endpoint, the WSDL.exe command would be 

wsdl.exe "http://<ReportServerHostName>/reportserver/ReportService2005.asmx" /l:CS /out:"D:\RS2005.cs"
On successful completion, you should see the following information in the Command Prompt.
Microsoft (R) Web Services Description Language Utility
[Microsoft (R) .NET Framework, Version 2.0.50727.1432]
Copyright (C) Microsoft Corporation. All rights reserved.
Writing file 'D:\Test.cs’.

Once the File is generated, it can be added to you project as a class file. Using the following steps.
  • In your Visual studio project, open Solution Explorer and Right Click on your Project and click on Add, Existing item.

image

  • In the Add Existing Item dialogue box, locate the RS2005.cs file saved above and add it.

image

  • Once the RS2005.cs, file has been added, we can use the classes/methods just like any C# class.

Details about the methods and functionalities provided by these endpoints can be found at

ReportService2005

ReportService2006

ReportExecution2005

ReportService2010

In the next post, I will talk about how to add a Reporting Services Web Service Reference to your project.

Advertisement
Reporting Services , ,

Did you Know- You can perform multiple Insert, update or delete operations as a Singleton operation using the SQL 2008 MERGE operator

Leave a comment Posted by on February 24, 2012

The Merge statement in SQL Server 2008 can be used to perform multiple inserts, update and delete operations. Assume for example, in your organization you have a Source Table and a destination table. Everyday at the end of the business, data from the Source table is appended to the Target table. With SQL 2000/SQL 2005, the only way of doing this optimally (assuming that the tables are very large) was to find the Delta of all the changes in the source table and then update the same in the target table. The problem was that you would have to write your own logic or use other SQL Features like SSIS or CDC for finding the delta and then updating the target table.

With the Merge statement you can do the same without much hassles. We can also use the Merge statement in an SSIS package or a job to automate the entire sequence.

Using the MERGE Statement

Syntax for the MERGE statement is simple to understand. First we have the Target table specified by the INTO clause, followed by the Source Table specified with the USING clause. Then we have the Join parameters. The join parameters are important, because this is what will govern which records are to be inserted or deleted or updated in the target table. Then we have the WHEN clause to define the criteria for INSERT, UPDATE and DELETE.

MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,…n ] ) ]
;

For example, assume that your organization maintains 2 tables, one for daily purchases and another for Reporting which has the last purchase date and the total amount of purchases made by each customer.

Use tempdb
Go

Create table PurchaseReporting
(
CustomerID int,
LastPurchaseDate Datetime2,
Amount Smallmoney,
)
Go

Create Table DailyPurchases
(
CustomerID int,
productID int,
Amount Money
)
Go

Insert into PurchaseReporting values (1, GETDATE(), $1000),
(2,getdate(), $2000),
(3,getdate(), $500),
(4, GETDATE(), $700)

— In the Reporting table we have 4 records for 4 different customer to indicate
— the last purchase date and the total amount of purchased goods.

Insert into DailyPurchases values(1, 2, $100),
(1,10,$20),
(3,30,$200),
(2,4,$20),
(2,15,$300),
(4,5,$500),
(5,10,$1500)

— In the Daily purchases table we have the 7 records to indicate the products purchased
— by different customers.

select * from PurchaseReporting
go
Select * from DailyPurchases
go

image

— Now lets write our Merge statement to update all the changes to the Reporting Table

MERGE PurchaseReporting as SR
Using
(Select CustomerID, getdate(), Sum(Amount) from
    DailyPurchases
    GROUP BY CustomerID) as DS(CustomerID, PurchaseDate, TotalAmount)
On SR.CustomerId = DS.CustomerID
WHEN MATCHED THEN
UPDATE SET SR.LastPurchaseDate = DS.PurchaseDate, SR.Amount = (SR.Amount + DS.TotalAmount)
WHEN NOT MATCHED THEN
INSERT values (DS.CustomerID, DS.PurchaseDate, DS.TotalAmount);

–After execution lets check the values of the Reporting Table Again

select * from PurchaseReporting

image

Notice the changes in the LastPurchaseDate and the Amount columns for the customer (1,2,3,4) and a new record with CustomerID 5 being inserted into the table.

Performance, SQL Engine , ,

Did you know: Trace Flag 1118 does not just affect your TempDB

8 Comments Posted by on February 23, 2012

A couple of days back, somebody asked if trace flag 1118 affect only the TempDB. My initial reaction was yes its only for TempDB. But then after further research, I figured out that it is not only for TempDB, but affects all user databases. The reason why we do not observe this change or why we don’t talk much about this is the fact that we do not create and drop tables on the user database at the same frequency as we do it for TempDB.

Since the schema of a user database does not change much often ( if it does in your environment, then you need to re-evaluate your design) the changes introduced by the TF does not pose any problems with DB growth or concurrency on the GAM and SGAM pages.

Changes Introduced by the TF 1118

Before we get into the details of the changes introduced, lets first talk about the default behavior in SQL. As an example we will try to create tables in both TempDB and a user DB (AdventureWorks).

When you create a table in SQL Server using the CREATE TABLE command, SQL will just create the meta data for the table, there is no allocation as of now. As seen below

use tempdb
go

create table TempTable (a int, b char(8000))
go

sp_spaceused ‘TempTable’
go

image

As the output for sp_spaceused indicates, there is no allocation done for the table as off now. The page allocation happens when we insert the FIRST record in the table. SQL Server will allocate 1 IAM page and 1 Database page (assuming the table has no indexes and also that we just need 1 page for the current set of records). SQL Server will allocate this one IAM page and 1 Data page from a mixed extent.

Insert into TempTable values (10, ‘AAA’)
go

sp_spaceused ‘TempTable’
go

image

As we can see SQL Server has allocation one Data Page and 1 Index Page (IAM) page. Under the default behavior SQL will try to Allocate the first 8 Data pages or index pages using Mixed Allocation. For any further pages, SQL will allocate a uniform extent.

In the example above we have made sure that 1 record occupies 1 DB page. If we add 8 records, as per the behavior SQL will only allocate 8 pages (all coming from Mixed Extents) and when we insert the 9th record, we would see a uniform extent being allocated.

Insert into TempTable values (10, ‘AAA’)
go 7;

Notice the go syntax used above, this is a special case where the Insert command is executed 7 times. Now lets check the output for sp_spaceused.

image

Notice that we have 8 DB pages and 1 IAM page. Now lets try to insert another record in the table.

image

Notice the increase in the unused space and the reserved space in the Table. We have 64KB allocated at the same time. Indicating the usage of a Uniform Extent this time.

That was the default behavior, what does TF 1118 change?

When TF 1118 is enabled, SQL will not perform any allocations from a Mixed Extent. So when you create a table, SQL will directly allocate a new uniform extent for the table. As shown below..

DBCC TRACEON (1118,-1)
GO

create table TempTable2 (a int, b char(8000))
go

Insert into TempTable2 values (10, ‘AAA’)
go
sp_spaceused ‘TempTable2’
go

image

The same will happen if we create a Table in any of the user Database.

use AdventureWorks
go
create table TempTable2 (a int, b char(8000))
go
Insert into TempTable2 values (10, ‘AAA’)
go
sp_spaceused ‘TempTable2’
go

image

The above output indicates that all allocation are not Uniform Extent Allocations.

Note: Once again since the rate of creation of Tables in a USER DB is almost zero, this TF will not see any impact of the TF on user DB’s.

P.S. Thank you Parikshit for helping out with the scripts.

Performance, SQL Engine , , , ,

Using XQuery expressions with XML in SQL Server 2005 and later

1 Comment Posted by on February 22, 2012

Continuing with the discussions on XML, today in this post we will talk about how to use XQuery with XML in SQL Server 2005 and later. For a list of other related topics refer here.

With XML data type support in SQL Server 2005, documents can now be stored in the form of XML and then Queries using XQuery. XQuery Language can query both structured or semi-structured XML data. XQuery is based on XPath queries, but provides better Iteration, better sorting ability.

A list of XQuery functions which can be used with the XML data types in SQL can be found at the following MSDN site.

Similarly some of the XQuery() operators supported with XML data types are

  1. Numeric operators (+, -, *, div, mod)
  2. Operators for value comparison (eq, ne, lt, gt, le, ge)
  3. Operators for general comparison ( =, !=, <, >, <=, >= )

To better understand the usage, lets try to use some of these functions and operators in a query. To use XQuery with SQL XML data type, we would need to call the XML Query() method. We will be discussing some other XML data types methods in subsequent posts.

Query method takes an XML XQuery expression and returns an Untyped XML as an output. An XQuery expression has two main parts

1) A XQuery Prolog, which declares the namespaces to be used for typed XML. In SQL the prolog can only contain namespace declarations.
2) The XQuery body, which declares a sequence of XQuery expression, for the intended results.

SELECT Instructions.query(‘
declare namespace
AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
    /AWMI:root/AWMI:Location[@LocationID=10]’) AS Result
FROM  Production.ProductModel
WHERE ProductModelID=7

In the above query, the DECLARE NAMESPACE part is used to declare a prefix AWMI for the XML schema ProductModelManuInstructions. This prefix will be used when referencing any element in the XML document created against this schema.

The “/AWMI:root/AWMI:Location[@LocationID=10]” expression is the XQuery body and is used to iterate to the desired element or node or attribute in the XML instance.

Now lets take a slightly complex example, which would include some of the XQuery functions and operators mentioned earlier.

SELECT
Resume.query(‘
        declare namespace rns ="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
    for $Name in /rns:Resume/rns:Name
        return
        <Name>
            {string($Name/rns:Name.Prefix)}{string(" ")}{string($Name/rns:Name.First)}{string(" ")}
            {string($Name/rns:Name.Middle)}{string(" ")}{string($Name/rns:Name.Last)}{string(" ")}
        </Name>,
    <Skill>
        {substring( ((rns:Resume/rns:Skills)[1]),1,56)}
    </Skill>,
    for $Employment in /rns:Resume/rns:Employment
        return
        <JobDetails>
                StartDate = {string($Employment/rns:Emp.StartDate)[1]}
                EndDate = {string($Employment/rns:Emp.EndDate)[1]}
                JobProfile = {concat(string(($Employment/rns:Emp.OrgName)[1]),string("—"),
                string(($Employment/rns:Emp.JobTitle)[1]))}
        </JobDetails>
        ‘
) as Results
from
HumanResources.JobCandidate where EmployeeID = 268

Lets take a minute and understand the query first. As mentioned there are two parts to the XQuery, the Prolog and the Body.  The Prolog contains the namespace declaration, which in this case is
    declare namespace rns ="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

The Body is a comma separated list of XQuery expressions to return the desired results. In this case the body contains 3 XQuery Expressions, described below

The First Expression, creates a NAME string from the Prefix, First, Middle and Last elements in the XML. Note the usage of string functions and the FOR loop. We will discussing this special Looping mechanism in subsequent posts.
    for $Name in /rns:Resume/rns:Name
    return
    <Name>
        {string($Name/rns:Name.Prefix)}{string(" ")}{string($Name/rns:Name.First)}{string(" ")}
        {string($Name/rns:Name.Middle)}{string(" ")}{string($Name/rns:Name.Last)}{string(" ")}
    </Name>,
The Second expressions returns the skill set for the Candidate. Note the usage of substring function in the expression.
    <Skill>
        {substring( ((rns:Resume/rns:Skills)[1]),1,56)}
    </Skill>,
The third expressions, lists all the previous jobs undertaken by the Candidate. Note the usage of the For looping mechanism and the CONCAT function being used.
    for $Employment in /rns:Resume/rns:Employment
    return
    <JobDetails>
            StartDate = {string($Employment/rns:Emp.StartDate)[1]}
            EndDate = {string($Employment/rns:Emp.EndDate)[1]}
            JobProfile = {concat(string(($Employment/rns:Emp.OrgName)[1]),string("—"),
            string(($Employment/rns:Emp.JobTitle)[1]))}
    </JobDetails>

In SQL, the schema declaration can be separated from the XQuery using the WITH NAMESPACE statement. For example the above query can also be written as

WITH XMLNAMESPACES (‘http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume’ as rns)
SELECT Resume.query(‘
for $Name in /rns:Resume/rns:Name
return
……

SQL Engine, XML ,

XML Schema collections in SQL Server

1 Comment Posted by on February 22, 2012

Continuing with the discussion on XML, today we will talk about XML Schema collections. For a complete list of related topics, please refer here. If you are looking for a detailed course on XML Schema Definitions, please start here.

Schema collections were introduced in SQL Server 2005, and allow the user to create a collections of XML schema definitions, which can later be used define a typed XML variable, parameter or XML column. Just like a normal table, an XML Schema collection can be created, dropped or modified. SQL Server 2005/2008 provides some guidelines and limitations for creating XML schema collections and XML schema definitions. The details can be found at the following MSDN site

SQL 2005: http://msdn.microsoft.com/en-us/library/ms190665(v=SQL.90).aspx

SQL 2008: http://msdn.microsoft.com/en-us/library/ms190665(v=SQL.100).aspx

In this article we would be taking about the DDL support for creating XML Schema collections. We will also talk about what needs to be done, if one of the schema definition (xsd), needs to be changed.

Before we get on with the DDL for creating a schema collection, lets quickly figure out where in the Database are these collections created. Since AdventureWorks database already has some SchemaCollections defined, lets have a look at these.

In the AdventureWorks database browse to Programmability->Types->XML Schema Collection, we would find of the Schema collections being used with the database.

image

Each, SchemaCollection mentioned in the list contains at least one XML Schema definition, which would be used by some XML column in one of the tables in the Database. Additionally these can be used with XML variables and parameters.

DDL FOR SCHEMA COLLECTIONS

SQL Server supports Creation/deletion/modification of the SCHEMA COLLECTION objects using the following T-SQL syntax.

CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS Expression
DROP XML SCHEMA COLLECTION [ relational_schema. ]sql_identifier
ALTER XML SCHEMA COLLECTION [ relational_schema. ]sql_identifier ADD ‘Schema Component’

Once we have created an XML Schema Collection, we can then query the details using the following DMV’s

To list all the schema collection in the Database: sys.xml_schema_collections
To list the XML namespaces in the  database: sys.xml_schema_namespaces

Now lets try to create an XML schema collection in the Database

 

CREATE XML SCHEMA COLLECTION ITEMS
AS
‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://TestNameSpace.com"
    xmlns:ns="http://TestNameSpace.com"
    elementFormDefault="qualified">
<xsd:element name ="Book" >
      <xsd:complexType>
        <xsd:sequence>
              <xsd:element name="Publication" type="xsd:string"/>
              <xsd:element name="AuthorFirstName" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="AuthorLastName" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="PublishedDate" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        </xsd:sequence>
      <xsd:attribute name="BookID" type="xsd:int"/>
  </xsd:complexType>
</xsd:element>
</xsd:schema>’

Once the SchemaCollection is added to the database, we can declare typed XML columns or XML variables using this schema. For example

Declare @Var1 XML(Books)
create table BookDetails (PublicationDate Datetime, Book XML(ITEMS))

To modify the XML schema collection, we would need to take care of all the dependencies. In our example, the Book Column in Table BookDetails is currently typed against the Books Schema collection. Attempts to modify the schema (add element or attribute) will not fail, but removing an element from the schema or modifying the data type of the elements might generate an error.

— Modiying the existing schema in the Collection
ALTER XML SCHEMA COLLECTION ITEMS
ADD ‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://TestNameSpace.com"
    xmlns:ns="http://TestNameSpace.com"
    elementFormDefault="qualified">
              <xsd:element name="BookType" type="xsd:string"/>
</xsd:schema>’
— Adding a new schema to the Collection
ALTER XML SCHEMA COLLECTION ITEMS
ADD ‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://CarNameSpace.com"
    xmlns:ns="http://CarNameSpace.com"
    elementFormDefault="qualified">
<xsd:element name ="Car" >
      <xsd:complexType>
        <xsd:sequence>
              <xsd:element name="Company" type="xsd:string"/>
              <xsd:element name="Model" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="Color" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="Type" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        </xsd:sequence>
      <xsd:attribute name="EngineID" type="xsd:int"/>
  </xsd:complexType>
</xsd:element>
</xsd:schema>’

When inserting or updating the XML contents in the table, the final XML would be validated against the schema declaration in the collection. If any mismatch is found, an error will be generated. In SQL 2005, there is strict type validation is performed. There are no LAX validations allowed in SQL 2005, while in SQL 2008 LAX validations of Any, AnyType and AnyAttribute is allowed. We will talk more about these in subsequent posts.

An attempt to insert an XML which does not comply to the defined schema, generated an error. For example the below mentioned insert will generate an error as indicated…

insert into BookDetails values (getdate(),
‘<Book xmlns="http://TestNameSpace.com" BookID="TEST"> </Book>’)
—- Error Message
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: ‘TEST’. Location: /*:Book[1]/@*:BookID

XML SchemaCollections cannot be dropped while it is in use by any of the following

  1. Associated with any xml type parameter or column.
  2. Specified in any table constraints.
  3. Referenced in a schema-bound function or stored procedure.

Attempts to drop an SCHEMA COLLECTION while in use will generate an error.

DROP XML SCHEMA COLLECTION ITEMS
—– Error
Msg 6328, Level 16, State 1, Line 1
Specified collection ‘Items’ cannot be dropped because it is used by object ‘dbo.BookDetails’.

To Drop an existing XML SCHEMA COLLECTION, we need to perform the following steps.

  1. Either convert all the columns to Well-Formed Untyped XML, or drop the Columns/Tables
  2. Drop the Schema Collection
  3. Recreate the SCHEMA Collections for other schemas.

In the earlier example, to remove the BOOKS schema definitions, we would have to perform the steps as follows

1. Alter table "BookDetails" to make the "Book" column as Well-Formed
    ALTER TABLE BookDetail ALTER Column Book XML
2. Drop the XML Schema collection
    DROP XML SCHEMA COLLECTION ITEMS
3. Recrete the XML Schema Collections
    Create XML Schema Collection…..

In the next post I will talking about XML Indexes in SQL Server 2005 and SQL Server 2008. Till then happy learning.

SQL Engine, XML , , ,

← Older posts