Sam Battermann has posted about a really fascinating video using a webcam and a WPF application to create some tremendous effects:

This weblog is sponsored by FITS.
We support PASS Germany!

Narayana Vyas Kondreddi has published some nice little sql helpers to search and replace data in various tables - therefor the following content is intellectual property of Narayana Vyas Kondreddi.

Maybe you will have to adjust the sql commands to search only special tables via a prefix for example or to just search via a special column...

The first one, SearchAllTables, can search all the columns of all the tables in a given database for a specific keyword:

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT


CREATETABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SETNOCOUNTON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName ISNOTNULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECTMIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName ISNOTNULL) AND (@ColumnName ISNOTNULL)
BEGIN
SET @ColumnName =
(
SELECTMIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName ISNOTNULL
BEGIN
INSERTINTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

The second one, SearchAndReplace, does not only perform the search but also will do a replace:

CREATE PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT

SETNOCOUNTON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName ISNOTNULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECTMIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName ISNOTNULL) AND (@ColumnName ISNOTNULL)
BEGIN
SET @ColumnName =
(
SELECTMIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName ISNOTNULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT'Replaced ' + CAST(@RCTR ASvarchar) + ' occurence(s)'AS'Outcome'
END

This weblog is sponsored by FITS.
We support PASS Germany!

I have encountered a system error deploying a SSAS project when I changed the deployment from my deployment server to a new production server. As you can imagine, the common case that really could bring you made is when you successfully deploy a project into a development server but you fail to deploy the same database into a production server and the error message is too generic and it hides the real cause of the problem that is very hard to find:

I looked for the error in the connection strings, I guess network or Active Directory problems...but every change attempt fails with the same error message: "Systemerror: ."

The solution was a problem with a role that references a membership of a local user of my development server, that fails because the role member name can't be resolved by SSAS production server. Thanks to microsoft for the great translation from "Can't deploy role xyz..." to "Systemerror: ."  ;-)

This weblog is sponsored by FITS.
We support PASS Germany!

This article shows how easy it really is to write custom code for SQL Server Reporting Services

This weblog is sponsored by FITS.
We support PASS Germany!

Definition from the SQL Server 2005 Product Guide:

"Analysis Management Objects (AMO) allows client applications to access the range of administrative commands and capabilities that are available to Analysis Services by using an object library that can provide object-level validation capabilities. This is an alternative to manually generating both the DDL scripts for Analysis commands and the often-lengthy contents of the ASSL ObjectDefinition element. Applications that use AMO can either connect and work directly with objects on an Analysis Services instance, or they can create such objects without an existing connection and persist the metadata for later deployment. AMO also “wraps” Analysis Services Scripting Language (ASSL) commands and elements."

Microsoft SQL Server 2005 Management Objects Collection from the Feature Pack for Microsoft SQL Server 2005 - April 2006

Introducing AMO Concepts

Readme for AMOBrowser Sample

AMO Security Classes

Programming AMO Security Objects

Analysis Services Concepts and Objects

Permissions and Access Rights (SSAS)

This weblog is sponsored by FITS.
We support PASS Germany!

The "Microsoft Architect Insight Conference" was held on 22 - 23 March 2006 and the slides are now available for download.

I would highly recommend that you read the PPT about Service-oriented Business Intelligence (SoBI) by Sean Gordon, Robert Grigg, Michael Horne, and Simon Thurman, which brings up some really good new approaches.
The authors also have published an article, which contains a more detailed descriptions and is available online.

And here are two additional links extracted from the article page:

• Data on the Outside vs. Data on the Inside
• Information as a Service: Service-Oriented Information Integration

You also should have an eye for the slides of Eric Nelson, who tries to explain that there are seven new servers in the SQL world...

This weblog is sponsored by FITS.
We support PASS Germany!

You surely know "Project REAL", the SQL Server 2005 reference implementation at Barnes & Noble. It contains information on all upcoming topics in a BI project.

And now...
your are not only able to read about it - you are able to download a whole subset of the project REAL data and the various parts of the implementation

Extract from the website:
"The kit contains:

1. A set of instructions for setting up the environment
2. Guidance on how to explore the implementation
3. A sample relational data warehouse database (a subset of the Project REAL data warehouse)
4. A sample source database (from which we pull incremental updates)
5. SSIS packages that implement the ETL operations
6. An SSAS cube definition and scripts for processing the cube from the sample warehouse
7. Sample SSRS reports
8. Sample data mining models for predicting out-of-stock conditions in stores
9. Sample client views in briefing books for the Proclarity and Panorama BI front-end tools

This kit will guide you through the key points to observe in the Project REAL implementation. Use it to learn and to get ideas for your own implementation. (While we believe it represents a very good design and generally follows best practices, it should not be regarded as the solution for every BI situation.) It will be helpful to see the overview presentations about the BI tools in SQL Server, or read documentation, before exploring the kit. One good source of information is to go through the tutorials that ship with SQL Server (the tutorials are installed when you select “Workstation components, Books Online and development tools” at installation time)."

This weblog is sponsored by FITS.
We support PASS Germany!

If your users have used Excel 2000 and Pivot Table Services targeting SQL Server 2000 as data store, they probably will get the error message "User 'public' does not have permission to run DBCC TRACEON" - even if you have migrated the whole set of security settings.

Regarding to this link the only way would be to grant execute permissions on DBCC, because:
"[...] SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers) [...]"

In SQL Server 2000, non-Sys Admins could execute DBCC Traceon (208) - SQL Server 2005 prevents non-Sys Admins from executing DBCC's. Administrators will not get aware of this effect, because they are normally allowed to start a trace in SQL 2005....
So even IF you have tested the whole bunch of Excel files in the company you would not come across this error.

What does this mean for "real life"?
Just deleting your PTS-Excel files and building new ones?
Then I would suggest Reporting Services... ;-)
Screwing your security settings?
Not exactly....
:-)

As mentioned in the forum post, Excel sends a kind of identifier within its query string, which names the calling application and should look like this in the files you are having troubles with:
"APP=Microsoft® Query;"
But have you tried to change the query string in an Excel file afterwards? It is nearly impossible...

Luckily, you could also use this coding:

Sub PT_Connect_Change()

Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath AsString, NewPath AsString
Dim rng As Range

ForEach ws In ActiveWorkbook.Sheets
   ForEach pt In ws.PivotTables
      OldPath = pt.PivotCache.Connection
      NewPath =replace(OldPath, "APP=Microsoft® Query;", "")
      pt.PivotCache.Connection = Application.Substitute(pt.PivotCache.Connection, OldPath, NewPath)
      OnErrorResumeNext
      IfErr.Number <> 0 Then
         'you could write an entire log here...
         msgbox ("Error")
      EndIf
      pt.PivotCache.Refresh
   Next pt
Next ws

EndSub

This coding loops through all Pivot Table Services Connections in your open Excel Workbook and cuts of the string "APP=Microsoft® Query;" in the connection string and after that refreshes the connection and the data.

To be able to do this without evoking the above mentioned error message do NOT start refreshing of the queries at the beginning and try to avoid any actions with data extraction / connection. After that, start the VBA Editor, paste the coding and run the Sub.
Maybe you will have to wait some minutes because of the refreshing and depending on the ammount of the data and the numbers of connection strings, but this is neccessary - I encountered problems with the connection string change without doing it.

But you could spend the time in getting a coffee, posting a comment and reading this blog, for example...

This weblog is sponsored by FITS.
We support PASS Germany!

Sorry, German only...

ASP.Net 2.0 macht ja vieles einfacher - aber man muss auch wissen, wie... :-)

Im MSDN gibt es zur "eingebauten" Mitglieder- und Rollenverwaltung eine kleine Artikelserie, die eigentlich keine Fragen mehr offen lassen sollte

ASP.NET 2.0 ? Mitglieder- und Rollenverwaltung mit IIS, Teil 1: Übersicht über Sicherheit und Konfiguration

ASP.NET 2.0 ? Mitglieder- und Rollenverwaltung mit IIS, Teil 2: Implementierung

This weblog is sponsored by FITS.
We support PASS Germany!

You shurely know, that Microsoft provides a control to display reports in your own applications without just linking to the SQL Server Reporting Services by http or SOAP. It is a little bit difficult to understand how this control works and especially the difference between the remote mode and the local mode needs to be clarified, I think...

It´s a pity that the the ReportViewer control cannot be extended by adding custom renderers or custom report items - that is the only disadvantage.

Here are some links, that should bring up light into the dark:

Microsoft Report Viewer Redistributable 2005
[As a german developer you will need the Microsoft Report Viewer 2005 Language Pack Deutsch to bring up the right screen messages]

http://www.gotreportviewer.com/, which provides informations and sample codes to give a quick start. Especially the difference between local mode and remote mode is described clearly.

MSDN article about ReportViewer Controls (Visual Studio)

MSDN Forum: Visual Studio Report Controls

Teo Lachev, Building Report-enabled Applications with the New ReportViewer Controls (Part 1 of 2)

Teo Lachev, Building Report-enabled Applications with the New ReportViewer Controls (Part 2 of 2)

This weblog is sponsored by FITS.
We support PASS Germany!

Sorry, German only...

Auf den Seiten des PASS Deutschland e.V. steht ein CommunityCast von Sebastian Weber zum Thema "SQL Server 2005 - CLR Entwicklung" zum Ansehen und zum Download zur Verfügung.

Am 02.03.2006 ab 19.00 Uhr wird es ebenfalls unter dieser Adresse eine Möglichkeit zum Chat mit Sebastian Weber geben, in dem entsprechende Fragen zum Thema CLR Entwicklung gestellt werden können.

Viel Spaß mit dem CommunityCast und herzlichen Dank an Sebastian Weber!

This weblog is sponsored by FITS.
We support PASS Germany!

If you want to have changing colors in the background of your details of your report, for example first row grey, second white and so on, you should use this expression:

=IIF(RowNumber(Nothing) mod 2=0,"#FFFFFF","#E8E8E8")

Sam Batterman - a Business Intelligence Evangelist with Microsoft Corporation in Malvern, Pennsylvania - has posted interesting approaches for conditional formatings in his blog.

This weblog is sponsored by FITS.
We support PASS Germany!

Some links to be able to understand the Smart Client Concept from Microsoft:

• Smart Client Definition
• We Rock 247 .NET
• TaskVision
• MSDN Library: Smart Client Architecture and Design Guide

This weblog is sponsored by FITS.
We support PASS Germany!

There are some usefull hints in thi s article "Convert VBA Code to Visual Basic When Migrating to Visual Studio 2005 Tools for Office"

This weblog is sponsored by FITS.
We support PASS Germany!

Some MSDN articles about developing webparts

This weblog is sponsored by FITS.
We support PASS Germany!

SQL Server 2000 Driver for JDBC
this support article maybe usefull for the first steps: Sample connection pool manager for use with Microsoft SQL Server 2000 Driver for JDBC

Microsoft SQL Server 2005 JDBC Driver

Ever thought about connecting your SAP system to your SQL Server?
Coming soon...

This weblog is sponsored by FITS.
We support PASS Germany!

Dirk´s blog contains a real interesting entry to get started with Indigo

Here are the links:

• Introducing Indigo: An Early Look
• Programming Indigo: Contracts
• Programming Indigo: The Programming Model

This weblog is sponsored by FITS.
We support PASS Germany!

The Beta 2 of Internet Explorer 7 is available now.

http://www.microsoft.com/windows/ie/ie7/ie7betaredirect.mspx

And the IE Product Team is blogging too:
http://blogs.msdn.com/ie/

Today I tested it and now Reporting Services is not working anymore!
Calling a report just brings up this error message:
"The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content and then restart the service. Check the documentation for more information. (rsReportServerDisabled)"

I am really curious why the installation of the IE7 has to touch the Reporting Services repository?

And be assured - before installing IE7 B2 Reporting Services worked fine...

This weblog is sponsored by FITS.
We support PASS Germany!

Bill Simser has written a long article called "DotNetNuke vs. SharePoint, the big showdown" which gives a real good view on this both "platforms".

This weblog is sponsored by FITS.
We support PASS Germany!

Simon Guest (blogname smguest) tries to give a 10-point definition about a "real" webservice in this article

This weblog is sponsored by FITS.
We support PASS Germany!

Have you also moaned about setting security for your stored proc´s?
I am shure, you have!
;-)

The following script should help you to set security - if you are using namespaces für your sproc´s....

if exists (select * from dbo.sysobjectswhere id =
object_id(N'[dbo].[spr_grantproc]') andOBJECTPROPERTY(id,
N'IsProcedure') = 1)
dropprocedure [dbo].[spr_grantproc]
GO

CREATEprocedure spr_grantproc
@login varchar(50),
@prefix varchar(50),
@grantbit
/* allocates the security settings to all SP´s beginning with the defined prefix (without itself)
@grant=1 grant access
@grant=0 revoke access
@login User-Login */

AS

declare @grantsql assysname
declare @action asvarchar(6)

if (@grant=1)
   set @action='GRANT'
else
   set @action='REVOKE'

if (@login isnotnull)
begin
   SELECT
      @Action+' EXECUTE ON ' + [NAME] + ' TO ' + @login as sel
   into
      #granttbl
   from
      SYSOBJECTS
   WHERE
      TYPE = 'P'
      ANDLEFT(NAME,LEN(@prefix)) = @prefix
      AND [NAME]<>'spr_grantproc'
   DECLARE Cur CURSORFOR
   SELECT sel FROM #granttbl
   OPEN Cur
   FETCH NEXT FROM Cur into @grantsql
      WHILE@@FETCH_STATUS = 0
         BEGIN
            exec(@grantsql)
            FETCH NEXT FROM Cur into @grantsql
         END
   CLOSE Cur
   DEALLOCATE Cur
   droptable #granttbl
end

GO

This weblog is sponsored by FITS.
We support PASS Germany!