March 2009

You are currently browsing the monthly archive for March 2009.

I recently came across a bug while working on a report using SQL Server 2008 Reporting Services.  I also have Visual Studio 2008 (incl. SP1) installed.

To be honest, to me it isn’t clear if I should call it a SQL Server bug or a Visual Studio bug.  If someone has a clear explanation on how these two packages work together please do let me know.  I know Business Intelligence Development Studio is Visual Studio with other templates, but where does Visual Studio end and SQL Server begin (or vice versa)?

Anyway, back to the point of this post, the error reads as follows:

Deserialization failed: The ‘DataType’ attribute is not declared

Initially I had started developing the report using BIDS 2005 and then I converted it to 2008.  One of the parameters was of type Integer and had 3 possible (hard-coded) values.  And it were these 3 values that were causing the issue.  As you may know, a Reporting Services report, an RDL, is actually made of XML.  The XML code for the available values was using an attribute called DataType to specify the type of the value.  However, this attribute is not specified in the XSD, the schema definition.

This is the offending XML code:

<ReportParameters>
   <ReportParameter Name="ReportParameter1">
     <DataType>Integer</DataType>
     <Prompt>ReportParameter1</Prompt>
     <ValidValues>
       <ParameterValues>
         <ParameterValue>
           <Value DataType="Integer">1</Value>
           <Label>first</Label>
         </ParameterValue>
         <ParameterValue>
           <Value DataType="Integer">2</Value>
           <Label>sec</Label>
         </ParameterValue>
         <ParameterValue>
           <Value DataType="Integer">3</Value>
           <Label>third</Label>
         </ParameterValue>
       </ParameterValues>
     </ValidValues>
   </ReportParameter>
 </ReportParameters>

And here’s the schema definition for the ParameterValue:

- <xsd:complexType name="ParameterValuesType">
- <xsd:sequence>
  <xsd:element name="ParameterValue" type="ParameterValueType" maxOccurs="unbounded" />
  </xsd:sequence>
  <xsd:anyAttribute namespace="##other" processContents="skip" />
  </xsd:complexType>
- <xsd:complexType name="ParameterValueType">
- <xsd:choice minOccurs="0" maxOccurs="unbounded">
  <xsd:element name="Value" type="xsd:string" minOccurs="0" />
  <xsd:element name="Label" type="StringLocIDType" minOccurs="0" />
  <xsd:any namespace="##other" processContents="skip" />
  </xsd:choice>
  <xsd:anyAttribute namespace="##other" processContents="skip" />
  </xsd:complexType>

As you can see, there’s no <xsd:attribute name=”DataType” …/> in this definition.

I have also been able to reproduce it just by creating a report from scratch using BIDS 2008.  I have not made any weird manipulation of the report, I’ve just been specifying the parameter, looking (not changing!) at the RDL, opening and closing it and here it is:

image

To solve the issue I manually removed the offending code parts:

DataType=”Integer”

Microsoft knows about the issue and has its status set to Resolved (External).  The feedback also contains the following statement:

We are escalating this issue to the appropriate group within the Visual Studio Product Team for triage and resolution. These specialized experts will follow-up with your issue.

That was the last comment on it by Microsoft.  I assume this means that it was a bug in Visual Studio after all and not in SQL Server, although it was clearly noticeable using BIDS 2008.  There is no link to any follow-up item or any other explanation on how to actually get a fix installed on your machine.  Do we need to wait until the next SP for VS2008 ships, or is it covered in the next SP for SQL Server 2008?  (You see now where my initial question came from – is it SQL Server or is it Visual Studio?)

Share

Tags: , ,

If you have installed SQL Server 2008 without making any modifications to the default service settings and you’re trying out the AdventureWorks2008 sample database, you may be getting some similar errors as the following in the Application event log:

A fatal error occurred during a full-text population and caused the population to be cancelled. Population type is: FULL; database name is AdventureWorks2008 (id: 7); catalog name is AW2008FullTextCatalog (id: 5); table name ProductReview (id: 354100302). Fix the errors that are logged in the full-text crawl log. Then, resume the population. The basic Transact-SQL syntax for this is: ALTER FULLTEXT INDEX ON table_name RESUME POPULATION.

The same errors are logged in the \MSSQL10.SQL2008\MSSQL\Log\SQLFT0000700005.LOG log file.  Here you also get an error number: Error: 30059, Severity: 16, State: 1.

I have seen the issue occur when the SQL Full-text Filter Daemon Launcher service was running with the NT AUTHORITY\NETWORK SERVICE account.  Switching the service to use the Local System account solved the issue.  Then the Full Text Catalog would rebuild without any issue.

To rebuild the Full Text Catalog in Management Studio, open your database in the Object Explorer, under Storage > Full Text Catalogs there’s an entry called AW2008FullTextCatalog (in the case of the sample DB).  Double-clicking this shows the Full-Text Catalog Properties.  Select the Rebuild catalog radio button and click OK to rebuild the catalog.

Alternatively you could execute following statement on the AdventureWorks database:

ALTER FULLTEXT CATALOG [AW2008FullTextCatalog] REBUILD;

After waiting for about 15 seconds (there’s not a lot of data to be indexed) if you open the properties again you should see the Last Population Date filled in with the current timestamp.  Another way to tell if it was successful is to look at the FT log file, error 30059 should not be mentioned anymore.

Or try using a Full Text query:

select * from Production.ProductReview
where contains(Comments, 'heavy');

This should output two records.

Share

Tags: , , ,

I’d just like to mention that Robert Sheldon wrote a really interesting article on how Full-Text Indexing works in both SQL Server 2005 and 2008.  If you’re new to full-text indexing or if you want to know how it has evolved in 2008, I really recommend reading this.

There are several interesting queries in the article, such as how to retrieve a list of terms that are being indexed using the sys.dm_fts_index_keywords dynamic management function.  This is a new feature of SQL Server 2008.

The article can be found on the simple-talk site and is called Understanding Full-Text Indexing in SQL Server.

This is the first article in a series on full-text indexing.  The next one is called Full-Text Indexing Workbench and is written completely in T-SQL :-) .

Share

Tags: ,

© 2008-2017 BI: Beer Intelligence? All Rights Reserved