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

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


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

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

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(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')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #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

SET NOCOUNT ON

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 IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(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')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
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 AS varchar) + ' occurence(s)' AS 'Outcome'
END

To get all base tables of a view you can use the following CTE-Statement:

WITH CTE (VIEW_SCHEMA,VIEW_NAME,TABLE_SCHEMA,TABLE_NAME)
AS
(
SELECT VIEW_SCHEMA,VIEW_NAME,TABLE_SCHEMA,TABLE_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
)
SELECT vu.VIEW_SCHEMA,
         vu.VIEW_NAME,
         vu.TABLE_SCHEMA    AS src_schema,
         vu.TABLE_NAME        AS src_table
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE VU LEFT JOIN CTE
     ON VU.TABLE_SCHEMA    =CTE.VIEW_SCHEMA
         AND VU.TABLE_NAME=CTE.VIEW_name

If you need to fill up a string to a defined total count you will be glad if you have a function like this:

CREATE FUNCTION fn_filler(@txt varchar(max)='',@before bit=1,@fill char(1)='0',@len int)

RETURNS varchar(max)
AS
BEGIN
    declare @anz as int
    if @txt is null
        set @txt=''
    set @anz=@len-len(@txt)

    if (@anz>0 )
        if (@before=1)
            set @txt=replace(space(@anz),' ',@fill)+@txt
        else
            set @txt=@txt+replace(space(@anz),' ',@fill)

    return @txt

END

Do you need the last day of a month in a SQL Statement?

Here we go:

CREATE FUNCTION fn_LastDayOfMonth(@dat as datetime)

RETURNS smallint
AS
BEGIN
    declare @ret as smallint
    declare @mon as smallint

    declare @dat2 as datetime
    set @dat2=cast(year(@dat) as varchar(4))+right('0'+cast(month(@dat) as varchar(2)),2)+'01'
    set @dat2=dateadd(month,1,@dat2)
    set @dat2=DATEADD(day, -1,@dat2)
    set @ret=day(@dat2)
    return @ret

END

And if you need a complete date this would be an alternative:

CREATE FUNCTION [dbo].[fn_LastDayOfMonth](@dat as datetime)

RETURNS char(8)
AS
BEGIN
    declare @ret as char(8)
    declare @mon as smallint

    declare @dat2 as datetime
    set @dat2=cast(year(@dat) as varchar(4))+right('0'+cast(month(@dat) as varchar(2)),2)+'01'
    set @dat2=dateadd(month,1,@dat2)
    set @dat2=DATEADD(day, -1,@dat2)
    set @ret=right('0'+cast(day(@dat2) as varchar(2)),2)+right('0'+cast(month(@dat) as varchar(2)),2)+cast(year(@dat) as varchar(4)) 
    return @ret

END

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

SQL Server 2005

After offering free delta courses Microsoft now offers a whole bunch of free E-Learning courses, which will enable you to pass the Exam 70-445 for the Microsoft Certified Technology Specialist (MCTS) SQL Server 2005 Business Intelligence.

You will find the complete Course Catalog at www.microsoftelearning.com

There are also some other resources for free at the moment at a subsite from Microsoft SQL Server 2005 – Learning Portal they called Dig Deep. Especially the free e-book Microsoft SQL Server 2005 Administrator's Pocket Consultant is a very useful resource and worth reading.

Sharepoint Technology

There is another bunch of free E-Learning courses for the Sharepoint Technology, which can be found here.

Visual Studio 2005 and .Net 3.0

And as expected developers are not left alone and there are also free E-Learning courses and a Dig Deep available for Visual Studio 2005 and .Net 3.0.

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

Brett Kaiser has posted a article called "How do I track data changes in a database" including his real usefull coding about his idea of doing this via T-SQL automatically every time a DELETE or UPDATE Modification will be executed.

The following content is intellectual property of Brett Kaiser, the original posting can be found here:

USE Northwind
SET NOCOUNT ON
GO

-- For example purposes, create a sample tables
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()), Col4 decimal(5,2))
CREATE TABLE x002548.myTable99(Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()), Col4 decimal(5,2))
GO

-- Give the sample tables some data
INSERT INTO myTable99(Col2, Col4)
SELECT 'x', 1 UNION ALL SELECT 'y', 2 UNION ALL SELECT 'z', 3
INSERT INTO x002548.myTable99(Col2, Col4)
SELECT 'a', 24 UNION ALL SELECT 'b', 25 UNION ALL SELECT 'c', 26

--Lets have a look
SELECT 'x002548' AS Source, * FROM x002548.myTable99
UNION ALL
SELECT 'dbo' AS Source, * FROM myTable99
GO

-- CREATE A Driver Table for all Tables in your catalog you wish to audit
CREATE TABLE myAudit99(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname)
GO

-- Populate the audit driver table with the table you want...you can use any type of process for this
INSERT INTO myAudit99(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME LIKE 'myTable99%'

-- Lets create some audit tables based on the Driver

DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM myAudit99

DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'CREATE TABLE ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' + @TABLE_NAME + '_H ('
+ ' HIST_ADD_DT datetime DEFAULT (getDate()), HIST_ADD_TYPE char(1) NOT NULL'
+ ', HIST_ADD_SYSTEM_USER sysname NOT NULL, HIST_ADD_USER_NAME sysname NOT NULL'
+ ', HIST_ADD_HOSTNAME sysname NOT NULL, HIST_ADD_SPID int NOT NULL, HIST_ADD_DESC varchar(50) '
, @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
+ ' ' + DATA_TYPE
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(varchar(20),CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN DATA_TYPE = 'decimal' THEN '(' + CONVERT(varchar(20),NUMERIC_PRECISION) + ','
+ CONVERT(varchar(20),NUMERIC_SCALE) + ')'
ELSE ''
END
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @SQL = @SQL + @COLUMN_NAMES + ')'

EXEC(@SQL)

SELECT @SQL = '', @COLUMN_NAMES = ''

FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME

END

CLOSE myCursor99
DEALLOCATE myCursor99
GO

SELECT *
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME Like 'myTable99%'
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

-- Now Lets create the audit TRIGGERS

DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM myAudit99

DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @SQL = 'CREATE TRIGGER ' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR ON ' + @TABLE_SCHEMA + '.' +@TABLE_NAME
+ ' FOR UPDATE, DELETE AS '
+ ' DECLARE @HOSTNAME sysname, @DESC varchar(50) '
+ ' SELECT @HOSTNAME = hostname from master.dbo.sysprocesses where spid = @@SPID '
+ ' IF EXISTS(SELECT * FROM ' + @TABLE_NAME + ') SELECT @DESC = '
+ '''' + '''' + ' ELSE SELECT @DESC = ' + '''' + 'MASS DELETE' + ''''
+ ' If Exists (Select * From Inserted) And Exists (Select * From Deleted) '
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @COLUMN_NAMES + ')'
+ ' SELECT ''U'', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC'
+ @COLUMN_NAMES + ' FROM deleted'
+ ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @DESC = '''' '
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @COLUMN_NAMES + ')'
+ ' SELECT ''D'', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC'
+ @COLUMN_NAMES + ' FROM deleted'
+ ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @DESC <> '''' '
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @COLUMN_NAMES + ')'
+ ' SELECT TOP 1 ''D'', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC'
+ @COLUMN_NAMES + ' FROM deleted'

EXEC(@SQL)

SELECT @SQL = '', @COLUMN_NAMES = ''

FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME

END

CLOSE myCursor99
DEALLOCATE myCursor99
GO

-- Now let's pretend to be an ignorant boss...not to much of a stretch there....

UPDATE myTable99 SET Col2 = 'G'

SELECT * FROM myTable99_H

DELETE FROM myTable99 WHERE Col1 = 1

SELECT * FROM myTable99_H

DELETE FROM x002548.myTable99

SELECT * FROM x002548.myTable99_H

SELECT * FROM myTable99

SELECT * FROM x002548.myTable99
GO

SET NOCOUNT OFF
DROP TABLE myAudit99
DROP TRIGGER dbo_myTable99_TR
DROP TRIGGER x002548_myTable99_TR
DROP TABLE dbo.myTable99
DROP TABLE x002548.myTable99
DROP TABLE dbo.myTable99_H
DROP TABLE x002548.myTable99_H
GO

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)

Damir Tomicic has blogged a long and informative list about Developer Ressources for Microsoft Office System 2007.

Kay Giza blogged about two Office developer Posters available for download:

• Microsoft Office InfoPath 2007 Managed Object Model Poster
• Developer Map for SharePoint Products and Technologies Poster

and about a strategy briefing by Chris Capossela (Corporate Vice President, Microsoft), which is available as a IT´s Showtime session.

After giving away the Microsoft Virtual Server 2005 R2 for free since April this year MS now announced, that MS Virtual PC 2004 will also be available for free now.

Now the nonsense getting the server for free while having to pay for the desktop solution is over...

And if you are searching for tips & tricks regarding the virtual solutions of MS you should look at http://blogs.msdn.com/virtual_pc_guy.

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

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

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 As String, NewPath As String
Dim rng As Range

For Each ws In ActiveWorkbook.Sheets
   For Each pt In ws.PivotTables
      OldPath = pt.PivotCache.Connection
      NewPath = replace(OldPath, "APP=Microsoft® Query;", "")
      pt.PivotCache.Connection = Application.Substitute(pt.PivotCache.Connection, OldPath, NewPath)
      On Error Resume Next
      If Err.Number <> 0 Then
         'you could write an entire log here...
         msgbox ("Error")
      End If
      pt.PivotCache.Refresh
   Next pt
Next ws

End Sub

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

Bill Graziano has developed a tool called Scriptio which is available for download as ClickOnce-Application and as source code (version 0.5.1.0).

Here is the current history of development / posts:

• http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx
• http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx
• http://weblogs.sqlteam.com/billg/archive/2006/02/12/9019.aspx
• http://weblogs.sqlteam.com/billg/archive/2006/02/17/9063.aspx

"101 Code Samples for Visual Basic and C#" available for download at the Microsoft Download Center.

Extract from the website:
"This download includes a master set of Visual Basic and Visual C# code samples demonstrating various aspects of the language in the following areas: syntax, data access, Windows Forms, Web development and Web services, XML, security, the .NET Framework, file system and file I/O, interop and migration issues, COM+, ADO.NET, and advanced topics including graphics with GDI+, remoting, serialization, MSMQ, and Windows services."

Being annoyed about the Reporting Services Execution Log just bringing up the domain users and having a customer without speaking usernames I searched for a solution to join Execution Log and the Active Directory to enrich the usernames to be able to identify the users.

I discovered a solution by Brendan Thomas "Create a SQL Server View of your AD Users" [2], but I got the same error mentioned in the comments.

The upcoming error could have different reasons:

• wrong registry entries regarding the ADSDSOObject
• syntax errors in the query
• the select statement contains the field "description" - seems that this field is not usable for a query
• missing user rights - the query is executed in the current user context if the linked server security properties are set to 'run in the actual security context'. If you aren´t using a AD-user the user will not be allowed to query the AD.
  You can switch the settings to 'Be made without a security context' (as mentioned in [3]), but I have to test if the query will be executable without errors then.

After being able to execute the query without errors I searched for other fields that could be extracted from the AD - [6] contains a list.
If anyone has a kind of official listing - please post it in the comments - I was not able to find one using Google...

The extended version of Brendan´s script with the additional fields I needed:

first step:
add the linked server

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

second step:
execute query

select
    title,
    sAMAccountName,
    givenname as 'First name',
    middleName as Initials,
    sn as 'Last name',
    displayName,
    telephonenumber as Telephone,
    mail as Mail,
    info as Remarks,
    title as Title,
    department as Department,
    company as Company,
    manager as Manager
from
    openquery
    (
    ADSI,
    'SELECT
        title,
        sAMAccountName,
        givenname,
        middleName,
        sn,
        displayName,
        telephonenumber,
        mail,
        info,
        title,
        department,
        company,
        manager
    
    FROM
        ''LDAP://YourLDAPServer''
    WHERE
        objectCategory = ''Person''
        AND objectClass = ''user''
    ')

third step:
drop server (if neccessary)

sp_dropserver 'ADSI', 'droplogins'

Searching for the mentioned error code I discovered a query call [5] which could be executed without using the linked server, using OPENROWSET instead of OPENQUERY, but you would have to submit User and Password if you would like to use another user context with the appropiate rights - and this user and his password would be included plain the query...

select
    *
from
    OPENROWSET
    (
    'AdsDsoObject',
    'User ID=;Password=;ADSI Flag=0x11;Page Size=10000',
    'SELECT
        sAMAccountName,
        givenname,
        middleName,
        sn,
        displayName
    FROM
        ''LDAP://YourLDAPServer''
    WHERE
        objectCategory = ''Person''
        AND objectClass = ''user''
    ')

If you have a large Active Directory you will come across the problem, that calling the AD will only bring up the first thousand users by default using the OPENQUERY (compare [7]).
The parameter Page Size used in the query with the OPENROWSET would override this default value.

Depending on this default, the usage and performance issues I would prefer the data being stored in the SQL Server database - the MS Scripting Guy has a good hint on how to do this:

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = _
"SELECT Name FROM 'LDAP://dc=fabrikam,dc=com' " & _
"WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst
Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields("Name").Value
objRecordSet.MoveNext
Loop

Links:

1. http://support.microsoft.com/default.aspx?scid=kb;en-us;Q299410
2. http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx
3. http://www.dbforums.com/archive/index.php/t-958399.html
4. http://www.codeproject.com/aspnet/LikeDataStores.asp
5. http://www.mcse.ms/archive81-2005-4-1260672.html
6. http://forums.aspfree.com/asp-development-5/asp-script-for-querying-active-directory-user-details-using-adsi-21470.html
7. http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx

If you have any enhancements (for example further fields - I am searching for the domain...) feel free to use the comments.

Russell Christopher has written a step-by-step description on how to create a MSI-package to install Reporting Services Reports.

A download of the sample MSI solution is also included in the post.

Sorry, German only...

Die Slides der Student Technologie Conference 2005 stehen zum Download zur Verfügung.

Here are some ressources provided by Microsoft to enable a "deep dive" into .Net 2.0 and ASP.Net 2.0:

ASP.NET Developer Center

ASP.NET Developer Center - New to development
This page contains links for a bunch of videos and the ASP.Net Webcast Series - really worth watching!

ASP.NET 2.0 QuickStart Tutorial

ASP.NET 2.0 Frequently Asked Questions

ASP.NET Starter Kits

ASP.NET for Designers

Migration

Migrating from ASP

ASP.NET 2.0 Training Center - Cross Training for Web Developers
Usefull ressources for PHP-, JSP- or ColdFusion-Developers

Visual Studio 2005 e-Learning
There are some courses for free available worldwide till June 30, 2006 - the other discount offers are only available in North America...

Microsoft Developer Security Resource Kit

Scott Guthrie's Blog
"Scott is the General Manager of the Client and Web Platform & Tools Team at Microsoft, and runs the development teams that build ASP.NET, Atlas, IIS, Visual Web Developer, Windows Forms, and the new Visual Studio Tools for WPF (codname: Cider)."

Brian Goldfarb's Blog
"Brian Goldfarb, Lead Product Manager on the Web Platform and Tools team."

There are some other non-Microsoft ressources also:

free training ASP.Net 2.0 by Exforsys

thecodeproject.com - ASP.NET articles

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

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)

If you would like to build a Wait-Screen in your own ASP.Net Application like it is for example in Reporting Services you should have a look at the posting of Daniel Fisher.

He describes every step it takes to implement it in a clear and detailed way that even I could understand it...

Thanks, Daniel!

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!

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.

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

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

Sorry, German only...

Die Slides der MS Office Solutions Conference 2005 stehen zum Download zur Verfügung.

Some MSDN articles about developing webparts

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

Crystal Xcelsius - formerly known as Infommersion Xcelsius - is a kind of Dashboard Designer where the development ends up in an interactive flash-file.

You can have a look at the gallery here: http://www.xcelsius.com/Examples/Overview.html.

Sorry, German only...

Aus der Reihe "Steffen (unser deutscher SQL Evangelist) erklärt die Welt":

Ist BizTalk ein Workflow-System?

Und wer mehr darüber erfahren will, wann er welches MS Produkt für Daten- und Systemintegration einsetzen sollte, dem sei Steffens TechNet Webcast "Welches Microsoft-Integrationsprodukt wofür? - Teil 1 (Level 100)" ans Herz gelegt.

Zusätzlich dazu gibt es ein englisches Whitepaper unter diesem Link Understanding Microsoft Integration Technologies - A Guide to Choosing a Solution.

Sorry, German only...

IIS 6 für den Web Entwickler

"Dieser Webcast zeigt, wie man die IIS Dienste wie Web, Mail und FTP konfiguriert und zeigt mögliche Stolpersteine auf. Anhand einer üblichen ASP.NET Web Anwendung werden die notwendigen Schritte von der Installation bis zur Wartung gezeigt."

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

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

Microsoft provides some interesting links about the co-existence of Microsoft .NET and J2EE technologies.

You should also have a look at the white papers and case studies.

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

MSDN provides an article called "Using an ADO.NET DataSet as a Reporting Services Data Source" to provide informations on how to build a data processing extension for Reporting Services that enables to use an ADO.NET DataSet as a data source.

There also is a project available at GotDotNet by Te Lachev called "Custom Dataset Data Extension for Microsoft Reporting Services".

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

CodeSmith is a template-based code generator that allows you to generate code for any text language.

You will not believe how easy developing could be - even try this tool with stored procedures!

Visual Studio .NET Web Control for Business Intelligence

This OLAP Web Control, developed by GrapeCity, Inc. brings the OLAP cubes right into your business applications allowing you to view, explore, and summarize data through your browser.
Following are some of the features provided by the control:

• Drilldown, drillup, slicing, and drillthrough
  
• Run-time layout designer
  
• Multi-language support

The MSDN Webcast Leveraging Application Blocks in Your Application Development by Aaron Suzuki will be a good intrduction to this topic

The Enterprise Library for .NET Framework 1.1 will also be a usefull resource, especially the Data Access Application Block will be worth a click...

Extract from the website:
"Application blocks help to address the common problems that developers face from one project to the next. They are designed to encapsulate the Microsoft recommended best practices for .NET applications. They can be plugged into .NET applications quickly and easily. For example, the Data Access Application Block provides access to the most frequently used features of ADO.NET, exposing them through easily-used classes. The application block also adds related functionality not directly supported by the underlying class libraries."

Starter Kits, Codes, Snippets, Tools - all available in this Download section.

Please have a special look on these two tools:

• Code Snippet Editor for Visual Basic 2005
• Refactor! for Visual Basic 2005

MS provides a complete Migration Guide here, which also includes a Migration Assistant.

MS also offers Migration Assistant Packages on this site for ASP, PHP and JSP

Tom Rizzo has changed jobs at MS converting from a SQL guy to the Office group and he also had to change the blog.

As first real post in his new blog Tom provides a real cruel gift for Sharepoint 2003 - 157 pages of free content and the code samples!

Have a look, download and enjoy!

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.sysobjects where id =
object_id(N'[dbo].[spr_grantproc]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[spr_grantproc]
GO

CREATE procedure spr_grantproc
@login varchar(50),
@prefix varchar(50),
@grant bit
/* 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 as sysname
declare @action as varchar(6)

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

if (@login is not null)
begin
   SELECT
      @Action+' EXECUTE ON ' + [NAME] + ' TO ' + @login as sel
   into
      #granttbl
   from
      SYSOBJECTS
   WHERE
      TYPE = 'P'
      AND LEFT(NAME,LEN(@prefix)) = @prefix
      AND [NAME]<>'spr_grantproc'
   DECLARE Cur CURSOR FOR
   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
   drop table #granttbl
end

GO

I came across a MySQL database during a project last days and had to migrate it to a MS SQL Server database.

I could have printed the ER-diagram and rebuild the tables, but... I have discovered a real cool and cheap tool to do this!
Take a look at the MySQL-to-MSSQL migration tool from intelligent converters.

My tip is to convert just the metadata and do the data transformation using DTS or SSRS, this is much faster.

They also provide other converters worth a look.

MS provides three interesting articles on MSDN about Customizing SharePoint Sites and Portals:

Customizing SharePoint Sites and Portals: Part 1

Customizing SharePoint Sites and Portals: Using Templates and Site Definitions, Part 2

Customizing SharePoint Sites and Portals: Style Sheet Class Reference Tables, Part 3

There is also a PowerPoint Slide Deck available from the  Microsoft® SharePoint™ Products and Technologies Developer Tour EMEA 2004:
SPTDevTour-SiteDefs.zip (1,3 MB)

Michael Washam posted an article on MSDN named "How to Write a Back Up and Restore Application for SharePoint Portal Server 2003" to provide a GUI for the backup and discovery tools.

Extract from the website:
"The SharePoint Portal Server Data Backup and Restore utility is the SharePoint Portal Server tool you use to back up and restore sites created with SharePoint Portal Server 2003. You can access this utility from the SharePoint Portal Server group on the All Programs menu, on the server running SharePoint Portal Server. For information about backing up and restoring sites created with SharePoint Portal Server 2003, see the Microsoft Office SharePoint Portal Server 2003 Administrator's Guide."

You will find the Download here.

Extract from the website:
"The SPSBackupSample in this download (SPSBackupSample.exe) includes programmatic steps and code to demonstrate how to backup and restore all SharePoint Portal Server 2003 or Microsoft Windows® SharePoint Services sites in the server farm. The code shows how to write a backup and restore application for SharePoint Portal Server 2003, and to perform Web Storage System backup for backwards-compatible Microsoft Exchange Server document libraries"

I found the following code somewhere in the internet sometimes ago, but did not store the link, if someone wants to be honoreed for creation - please mail to me!

But be carefull, this sproc is using dynamic statements via exec '' and therefor alos including all upcoming issues...
Not known? Than you have to read the article "The Curse and Blessings of Dynamic SQL" from Erland Sommarskog.

Here we go:

CREATE PROCEDURE spr_GetSortedPage(
@TableName VARCHAR(50),
@PrimaryKey VARCHAR(25),
@SortField VARCHAR(100),
@PageSize INT,
@PageIndex INT = 1,
@QueryFilter VARCHAR(100) = NULL
) AS
SET NOCOUNT ON

DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)

SET @SizeString = CONVERT(VARCHAR, @PageSize) SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))

IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN
   EXEC(
      'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
      (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
      (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')
      ORDER BY ' + @SortField + ')
      ORDER BY ' + @SortField
   )
   EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)
END
ELSE
BEGIN
   EXEC(
   'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
   (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
   (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
   ORDER BY ' + @SortField + ')
   ORDER BY ' + @SortField
   )
   EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)
END

RETURN 0
GO

Doing it this way brings up to resultsets in one call - because of classic ASP having trouble with that you have to exclude the second exec´s bringing up the page-information and put them in seperate sproc´s.

Ever thought of converting an Access database to SQL Server?

Microsoft provides an article in the knowledge base, which can be found here.

But please be carefull, this article and the upsizing wizard only covers the upsizing, neither the optimization, nor the power of the SQL Server...