some thoughts... - MS SQL Server

ready.for.take.of - German launch event SQL 2008

Markus Fischer — Thu, 03 Jan 2008 14:00:20 GMT

Sorry, German only...

Die ideale Gelegenheit, sich mit den neuesten Microsoft-Technologien vertraut zu machen, bieten die Launch-Konferenz 2008 und die Deutsche SharePoint Konferenz. Beide Konferenzen finden vom 19. bis 21. Februar 2008 gleichzeitig in Frankfurt am Main statt und bieten Ihnen die Möglichkeit, den produktiven Einsatz innovativer Techniken kennen zu lernen. Ein Highlight nicht nur für die Branche, sondern auch für Sie.

Verpassen Sie nicht den Launch der neuesten Microsoft-Technologien:

Windows Server^® 2008,
SQL Server™ 2008 und
Visual Studio^® 2008

und erfahren Sie, wie Sie diese Technologien konkret nutzen können, um die IT-Umgebung in Ihrem Unternehmen sicherer und effizienter zu gestalten. Es erwarten Sie über 80 hochkarätige Programmpunkte, Gespräche mit kompetenten Experten und die Vorstellung von Praxislösungen durch unsere Partner. Bringen Sie Ihr Knowhow über innovative und effiziente Technologien auf den neuesten Stand.

Gleichzeitig findet die Deutsche SharePoint Konferenz 2008 statt. Sie knüpft an die Erfolge der Vorjahre an und legt nun starken Fokus auf die praktische Nutzung von SharePoint. Experten, Kunden und Partner zeigen Ihnen in mehr als 60 Fachvorträgen, wie SharePoint erfolgreich eingesetzt wird − in unterschiedlichen Branchen, Fachbereichen und technologischen Umgebungen.

Ready for Take-off? Nehmen Sie an der wichtigsten Microsoft-Großveranstaltung teil! Melden Sie sich am besten gleich hier für die Launch-Konferenz 2008 und die Deutsche SharePoint Konferenz 2008 an.

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

European PASS Conference 2008 - Become an exhibitor or sponsor

Markus Fischer — Wed, 02 Jan 2008 03:39:01 GMT

Why should your Company Exhibit at PASS Conference 2008?

Link to hundreds of highly qualified prospects that will help you expand your customer base
Engage with SQL Server and Business Intelligence experts to promote your products and services
Increase exposure and awareness for your company
Achieve sales growth and generate valuable sales leads
Cultivate existing relationships

For more information on how your company can benefit from participation as an exhibitor and/or sponsor, please contact exhibitors08@european-pass-conference.com.

Sponsoring & Exhibition Prospectus

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

European PASS Conference 2008 - Call for Speakers

Markus Fischer — Wed, 02 Jan 2008 02:19:17 GMT

The 2008 European PASS Conference Call for Speakers is now open!

PASS would like to encourage you to submit an abstract today!

Link to abstract submission form : http://survey.constantcontact.com/survey/a07e27lqm18fa6vm3ti/start

PASS is an association for users, run by users. It is important that our members be involved and help build the community they are a part of.

As a speaker at the 2008 European PASS Conference Summit (http://www.european-pass-conference.com) in Neuss - Germany (Near Dusseldorf), April 15 and 16, 2008 , you will receive:

Complimentary registration to the 2008 European PASS Conference
Industry recognition
Networking opportunities with hundreds of attendees

Microsoft employees are encouraged to submit presentations using the online submission form, however, please note that these submissions will be reviewed and abstract status will be determined by Microsoft.

The deadline to submit a presentation is January 31, 2008. If you have any questions, please e-mail speakerbureau08@european-pass-conference.com.

Thank you and we look forward to your participation!

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

Rodney Landrum: The Reporting Services 3-Trick Pony

Markus Fischer — Thu, 25 Oct 2007 15:44:27 GMT

Rodney Landrum demonstrates an ingenious way to monitor blocked SQL Server processes across all your servers, using nothing but SSRS in his posting "The Reporting Services 3-Trick Pony"

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

Darren Gosbell: SSIS Best Practices

Markus Fischer — Thu, 25 Oct 2007 14:40:08 GMT

Darren Gosbell has published his presentation and a sample project with demos he presented at the SQL Down Under Code Camp.

This really is worth downloading and reading!

And Darren had a very well-fitting picture about the feelings while building the first SSIS package while coming from DTS for example:

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

using FTP in SSIS

Markus Fischer — Thu, 25 Oct 2007 13:37:20 GMT

I recently had to download some flatfiles from different FTP Resources to load these into a SQL database.

Being a fan of package configurations and minimized maintenance I decided to build a package which would loop through a recordset containing the necessary informations like FTP server, port, user and password instead of creating different packages or steps for each FTP server.

excursion:
I know that storing passwords in a database table is not really best practice for security, but the guys being responsible for the SQL Server are also responsible for the FTP server... :-)

So, extracting the informations with a SQL Task, including a loop and using expressions to set the correct properties would not be a great challenge I thought. Up to the moment I tried to select the property for the password from the Property Expressions Editor in BIDS:

There is no property which could be used to set the password via expression - this must be a security feature to enable FTP tasks to be "secure by default"...

So what do to do?
Searching the properties for the password prop I recognized that there is a property for the connection - though I should use this for the loop approach instead of building several tasks? But what would be the correct usage of the connection?

Once again like every day in the life of an IT guy I used the "tool which must not be named" [@Microsoft: why don't you find search results even in your own resources???] and et voilà I got the perfect hint in the SSIS forum of MSDN.

The correct syntax for the connection would be Server IP:Port.loginAccount.loginPWD, therefor you could use an expression like this:

@[user::FtpServer] + ":" + @[user::FtpServerPort] +"."+ @[user::FtpAcct] +"."+ @[user::FtpPwd]

to build this connection string. And if you use another variable to build the string - do not forget to set the "evaluate as expression" to TRUE...

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

some interesting tools for KPI support

Markus Fischer — Mon, 24 Sep 2007 19:10:02 GMT

A long time ago - February and March 2006 - Eugene Asahara has posted two very intersting articles about

The "KPI Cause and Effect Visio Graph" is a very useful visualization tool to diskuss KPI´s with the prospective users whereas the "KPIRelationships sample application" is a very smart tool to control if all perspectives, KPI´s, etc are developed and have the right definitions.

This two articles are a really must-read and Eugene also provides the tools for download - so check it out!

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

little SQL-helpers: split a column

Markus Fischer — Mon, 24 Sep 2007 18:38:48 GMT

If you would like to split values from a column which are divideable beacause they are using the same delimiter like an ',' for example you could use the function below. The function also brings up the postion, which sometimes could be very usefull, too.

To call the function just use,

select * from dbo.fn_split('Value
1,Value 2',',')

the first parameter is the column to split, the second contains the delimiter. You can not use different delimiters at one time at the moment, if you want to suggest some enhancements, please use the comments.... :-)
The statement will bring up the following result set:

CREATE FUNCTION [dbo].[fn_Split](@text
nvarchar(max), @delimiter char(1)
= ' ') RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value nvarchar(max)
) AS BEGIN DECLARE @index int SET @index =
-1 WHILE (LEN(@text) > 0) BEGIN SET @index =
CHARINDEX(@delimiter , @text) IF (@index =
0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAK END IF (@index >
1) BEGIN INSERT INTO @Strings VALUES (LEFT(@text,
@index - 1)) SET @text = RIGHT(@text,
(LEN(@text) - @index)) END ELSE SET @text
= RIGHT(@text, (LEN(@text) - @index)) END RETURN END

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

little SQL-helpers: help with date formats

Markus Fischer — Mon, 24 Sep 2007 16:33:12 GMT

Are you also always looking in the BOL if you have to deal with datetime?

This stored procedure could bring some light in the dark...

You will have to call it with the following statement:

Select dbo.fn_formatdate(getdate(),'formated
date: \TT\MM\YYYY \hh:\mi:\ss \ms Week: \wk Weekday: \DW Quarter: \qq'),getdate()

The formatstring can contain severall formatstrings and will bring up all datetimeparts you want, including some additional textparts...

CREATE FUNCTION [dbo].[fn_formatdate](@dat
datetime,@format varchar(255)) RETURNS varchar(255) AS BEGIN declare @ret varchar(255) declare @i int --
year 4 digits set @ret=replace(@format,'\jjjj',cast(year(@dat) as varchar(4))) --
year 4 digits  set @ret=replace(@ret,'\yyyy',cast(year(@dat) as varchar(4))) --
day 2 digits  set @ret=replace(@ret,'\dd',right('0'+cast(day(@dat) as varchar(2)),2)) --
weekday 1 digit  set @ret=replace(@ret,'\dw',cast(datepart(dw,@dat) as varchar(1))) --
day of year set @ret=replace(@ret,'\dy',right('00'+cast(day(@dat) as varchar(3)),3)) --
hour 2 digits set @ret=replace(@ret,'\hh',right('0'+cast(datepart(hh,@dat) as varchar(2)),2)) --
year 2 digits  set @ret=replace(@ret,'\jj',right(cast(year(@dat) as varchar(4)),2)) --
minute 2 digits  set @ret=replace(@ret,'\mi',right('0'+cast(datepart(mi,@dat) as varchar(2)),2)) --
month 2 digits set @ret=replace(@ret,'\mm',right('0'+cast(month(@dat) as varchar(2)),2)) --
milliseconds 3 digits set @ret=replace(@ret,'\ms',right('00'+cast(datepart(ss,@dat) as varchar(3)),3)) --
quarter 2 digits  set @ret=replace(@ret,'\qq',right('0'+cast(datepart(qq,@dat) as varchar(2)),2)) --
seconds 2 digits set @ret=replace(@ret,'\ss',right('0'+cast(datepart(ss,@dat) as varchar(2)),2)) --
day 2 digits set @ret=replace(@ret,'\tt',right('0'+cast(day(@dat) as varchar(2)),2)) --
week 2 digits set @ret=replace(@ret,'\wk',right('0'+cast(datepart(wk,@dat) as varchar(2)),2)) --
year 2 digits set @ret=replace(@ret,'\yy',right(cast(year(@dat) as varchar(4)),2)) --
day 1-2 digits  set @ret=replace(@ret,'\d',cast(day(@dat) as varchar(2))) --
hour 1-2 digits  set @ret=replace(@ret,'\h',cast(datepart(hh,@dat) as varchar(2))) --
month 1-2 digits  set @ret=replace(@ret,'\m',cast(month(@dat) as varchar(2))) --
minute 2 digits set @ret=replace(@ret,'\n',right('0'+cast(datepart(mi,@dat) as varchar(2)),2)) --
quarter 1 digit  set @ret=replace(@ret,'\q',cast(datepart(qq,@dat) as varchar(1))) --
seconds 1-2 digits set @ret=replace(@ret,'\s',cast(datepart(ss,@dat) as varchar(2))) --
day 1-2 digits  set @ret=replace(@ret,'\t',cast(day(@dat) as varchar(2))) --
week 1-2 digits  set @ret=replace(@ret,'\w',cast(datepart(wk,@dat) as varchar(2))) return @ret END GO

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

little SQL-helpers: Create database snapshots via stored procedure

Markus Fischer — Mon, 24 Sep 2007 16:14:49 GMT

Do you want to create database snapshots? And do you want to do it programmatically? Then you could use this stored procedure...

Enjoy!

CREATE PROC [dbo].[spr_CreateSnapshot]
(@Masterdb VARCHAR(255), @SnapshotName VARCHAR(255),
@Execute BIT = 1) AS SET NOCOUNT ON DECLARE @NewLine CHAR(2) DECLARE @Q CHAR(1) DECLARE @fname VARCHAR(255) DECLARE @extention VARCHAR(255) DECLARE @Pfad VARCHAR(255) DECLARE @DBname VARCHAR(255) DECLARE @LogicName VARCHAR(255) DECLARE @Command VARCHAR(MAX) DECLARE @indexExt INT DECLARE @indexPfad INT DECLARE @lenFname INT DECLARE @lenPfad INT DECLARE @lenDB INT DECLARE @lenExt INT CREATE TABLE #Info
(physical_name VARCHAR(255) not null,
logicname VARCHAR(255) not null) SET @newLine
= CHAR(13) + CHAR(10) SET @Q
= CHAR(39) SET @command = 'INSERT
INTO #info (physical_name, logicname) SELECT s.physical_name,s.[name] AS LogicName
FROM ' + Quotename(@masterdb) + '.sys.filegroups as g INNER
JOIN sys.master_files AS s ON s.type = 0 AND s.database_id = db_id(' + @Q +
@Masterdb + @Q + ') AND s.drop_lsn is null AND s.data_space_id =
g.data_space_id ORDER BY g.data_space_id' EXECUTE (@Command) SET @Command
= 'CREATE DATABASE ' + @SnapshotName + @NewLine SET @Command
= @Command + 'ON' + @NewLine DECLARE c CURSOR READ_ONLY FOR SELECT physical_name,
logicname FROM #info OPEN c FETCH NEXT FROM c INTO @fname,@LogicName WHILE (@@fetch_status
<> -1) BEGIN IF (@@fetch_status
<> -2) BEGIN SET @fname
= REVERSE(@fname) SET @lenFname = LEN(@fname) SET @indexExt
= CHARINDEX('.',@fname) -1 SET @indexPfad
= CHARINDEX('\',@fname) - 1 SET @extention
= REVERSE(SUBSTRING (@fname, 1, @indexExt)) SET @lenExt
= LEN(@extention) SET @Pfad = LEFT (REVERSE(@fname),
@lenFname - @indexPfad) SET @lenPfad = LEN(@Pfad) SET @DBname
= SUBSTRING(REVERSE(@fname), @lenPfad + 1, (@lenFname -
@lenPfad - @lenExt) - 1) SET @Command = @Command + '(Name
= ' + @Q + @LogicName + @Q + ', Filename = ' + @Q SET @Command
= @Command + @Pfad + @SnapshotName + '_' + @DBname + '.' + 'ssh' +
@Q + '),' + @NewLine END FETCH NEXT FROM c INTO @fname,@LogicName END CLOSE c DEALLOCATE c SET @Command
= LEFT(@Command,LEN(@Command)-3) + @NewLine + 'AS
SNAPSHOT OF ' + @masterdb IF @Execute =
1 BEGIN EXEC (@Command) END ELSE BEGIN SELECT @Command AS Command END

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

little SQL-helpers: Get some infos about your SQL Server

Markus Fischer — Mon, 24 Sep 2007 15:57:03 GMT

You are interested in getting some informations about your SQL Server, for example to be able to do an auditing on your SQL Server via SSIS, this code will help:

SELECT SERVERPROPERTY ('MachineName') as MachineName,
SERVERPROPERTY ('InstanceName') as InstanceName,
SERVERPROPERTY ('ProductVersion') as ProductVersion,
SERVERPROPERTY ('ProductLevel') As ProductLevel,
SERVERPROPERTY ('Edition') as Edition,
SERVERPROPERTY ('LicenseType') as LicenseType,
SERVERPROPERTY ('NumLicenses') as NumLicenses,
SERVERPROPERTY ('IsClustered') as IsClustered,
SERVERPROPERTY ('IsIntegratedSecurityOnly') as IsIntegratedSecurityOnly

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

little SQL-helpers: sp_refreshview

Markus Fischer — Mon, 24 Sep 2007 15:51:39 GMT

I do not know the original author of this stored procedure...
If you are the one - please send me an email and I will publish the right credits!

After changing the structure of a table or delete one you will need to check and refresh your views also. This code will help...

DECLARE @Table_Name varchar(100) DECLARE Refresh_Views CURSOR FOR SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE
= 'VIEW' AND TABLE_SCHEMA = 'dbo' OPEN Refresh_Views FETCH NEXT FROM Refresh_Views INTO @Table_Name WHILE @@FETCH_STATUS =
0 BEGIN EXEC sp_refreshview @Table_Name FETCH NEXT FROM Refresh_Views INTO @Table_Name END CLOSE Refresh_Views DEALLOCATE Refresh_Views

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

European PASS Conference 2008

Markus Fischer — Mon, 24 Sep 2007 15:11:31 GMT

This one is an event for your 2008 calender...
PASS is coming back to Europe next year from April 14th to 16th with the European PASS Conference 2008!

You want to be part of it?

	Registration:>	registration@european-pass-conference.com
	Speaker:	speakerbureau@european-pass-conference.com
	Accounting:	accounting@european-pass-conference.com
	Exhibitors:	exhibitors@european-pass-conference.com
	Information:	inquiries@european-pass-conference.com

View map

- The Swissôtel is placed directly near the Rhine and is approx. 10 min away from the "Düsseldorf Altstadt" if you drive by car.

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

Phil Brammer: SSIS - Using a Script Component as a Source

Markus Fischer — Thu, 05 Jul 2007 21:22:14 GMT

Phil Brammer has posted a very nice and simple quick tutorial on using a script component as a source on his blog:

http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/#more-42

Really worth reading!

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

PASS BootCamp 2007 - DBA & Reporting Services

Markus Fischer — Wed, 20 Jun 2007 21:29:37 GMT

PASS Germany is doing a BootCamp again this year! [Even if it is an event in German language I am writing in English - please spread this news around in your blogs though interested community members have the chance to participate] We are slightly breaking traditions this year, because we are not having a complete BI-focused BootCamp, we are having two different tracks this time: the first one is DBA-stuff including performance optimization, clustering, mirroring, security, etc the second one is a deep dive into SQL Server Reporting Services including development, design, security, infrastructur and 3rd party tools like Dundas also With one habit we do not break traditions! We have included three suprising night sessions again and also two late night sessions - stay tuned! For further information visit the event page - if you want to register, please use this link. The Early-Bird ends at 9th July 2007 and the seats are limited - so register as fast as possible...

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

SQL Server Katmai - its official

Markus Fischer — Thu, 10 May 2007 02:03:05 GMT

Today at the BI Conference in Seattle Jeff Raikes from Microsoft has given a first official impact - just slides - on the next SQL Server version.

I have seen a new feature called Change Data Capture (also mentioned in the datasheet) in the session "PL208 Data Warehousing with Microsoft SQL Server 2005" presented by Gopal Ashok, Program Manager with the SQL Server Product team, which was quite impressive.

Ever dealt with the problem of having mass data in a table and no "real" marker to identify the specific rows for incremental data operations like inserts, updates and deletes?
The Change Data Capture should ease your pain by creating a kind of audit table to watch data modification without the need of programming stored procedures, triggers and so on.
I am extremely curious how this feature proves in real life scenarios. And I am also a little bit surprised if the SSIS team will not bring up some new tasks to enable the use of the audit table of the Change Data Capture - or do we still need to develop the handling concerning load cycles, deleting of successfully loaded records, etc by our own? A kind of automatic delta queue mechanism and handling like in SAP systems would be great!

Tomorrow Ted Kummert, corporate vice president of the Data and Storage Platform Division at Microsoft will have a keynote at the morning - I hope that he will show some more live impressions.

You will find the official press release here.

And there also is a information website online.

Regarding to the press release, "SQL Server “Katmai” is scheduled to be delivered in 2008" - but there is no information about CTP´s available on the website yet.

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

SQL Server troubleshooting

Markus Fischer — Tue, 01 May 2007 22:34:30 GMT

Vera Noest provides a really great link collection of ressources which will be very usefull while doing troubleshoting.

Vera also provides troubleshooting tips for Terminal Server and Citrix.

Worth reading - check it out!

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

little SQL-helpers: Extracting Olap Data from SSAS using SSIS - Darren Gosbell

Markus Fischer — Tue, 01 May 2007 22:05:16 GMT

Darren Gosbell has posted a very interesting article about querying SSAS and use the result in the relational part of SQL Server.

I have used it in a project to retransfer data which was calculated due to a parent child dimension containing items for a financial analysis.
Calculating this via the relational data would have been a mess, because of having to rebuild the whole calculation from the bottom to the top of the hierarchy.

All you have to do is to define a linked server in the first step:

USE master GO /* Add new linked
server */ EXEC sp_addlinkedserver @server='LINKED_OLAP', --
local SQL name given to the linked server  @srvproduct='', --
not used  @provider='MSOLAP.3', --
OLE DB provider (the .2 means the SQL2K version)  @datasrc='localhost', --
analysis server name (machine name)  @catalog='Adventure
Works DW' -- default catalog/database

After successfully defining the linked server you will be able to query any SSAS cube based on your security like this way:

SELECT * FROM OpenQuery(linked_olap,'SELECT
--measures.members {Measures.[Internet Sales Amount]} ON COLUMNS, [Date].[Month].members
ON ROWS FROM [Adventure Works]')

While writing this blog entry I tested it on my notebook also - and was very astonished that querying the cube does not work, while testing the linked server worked well.
I am getting an error message like:
"Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP.3" for linked server "linked_olap" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSOLAP.3" for linked server "linked_olap"."

Maybe it depends on Windows Vista, even with the security set in the right way and also running SSMS as an administrator...

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

little SQL-helpers: search and replace data from Narayana Vyas Kondreddi

Markus Fischer — Tue, 01 May 2007 19:37:56 GMT

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

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

little SQL-helpers: all base tables of a view

Klaus Hoeltgen — Tue, 24 Apr 2007 12:20:16 GMT

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

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

SQL Server 2005 Service Pack 2 available for download

Markus Fischer — Tue, 20 Feb 2007 01:17:05 GMT

Interested in all the new things in SP2? Here is a complete list.

And this is the complete download site with links for the SP2 itself, a new BOL, new Samples, new Feature Pack and especially the links to SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies and SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007.

To avoid a restart after the installation of the SP2 you should stop the neccessary services which could be done with the following commands on the command line - if you have used the standard instances:

net stop MSSQLSERVER
net stop MSSQLServerOLAPService
net stop msftesql
net stop MsDtsServer
net stop ReportServer

After the installation the "SQL Server 2005 User Provisioning Tool is started automatically if you use Windows Vista. As you probably know, Windows Admins are not automatically also SQL or AS Admins - this tool enables to set the right privileges to the administartor accounts.

But do not forget to start the services before you try to set the privileges...

If you want to add a new account you will have to start the Provisioning Tool again, but unluckily it is not added to the Start Menu, you will find it at:
C:\Program Files\Microsoft SQL Server\90\Shared\SqlProv.exe - assumed that you have a standard installation and an english version of MS Windows Vista.

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

SQL Server Training online ressources

Markus Fischer — Thu, 15 Feb 2007 14:41:58 GMT

Interested in online ressources for SQL Server Training? Please do not forget that these ressources can not replace a complete Training and therefor only should be considered as a additional point of information...

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

Deploy SSAS with systemerror

Klaus Hoeltgen — Tue, 30 Jan 2007 22:34:45 GMT

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!

SQL Server 2005 Reporting Services on a server with more than one website

Klaus Hoeltgen — Thu, 28 Dec 2006 13:15:04 GMT

HowTo install Reporting Services on server with more than one website:

http://www.atrevido.net/blog/PermaLink.aspx?guid=3f1a7332-e4e1-4dda-be02-527bfe07e98c

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

Free E-Learning courses from Microsoft

Markus Fischer — Thu, 16 Nov 2006 05:33:53 GMT

Free E-Learning courses from Microsoft: - SQL Server 2005 - Sharepoint Technology - Visual Studio 2005 and .Net 3.0

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

TechNet Seminar: SQL Server 2005 -optimaler Einsatz in SAP-Umgebungen

Markus Fischer — Fri, 10 Nov 2006 01:09:33 GMT

Sorry, German only...

Microsoft bietet zum Thema "SQL Server 2005 – optimaler Einsatz in SAP-Umgebungen" ein kostenfreies TechNet Seminar in verschiedenen deutschen Städten an.

Referenten sind:

Martin Sudbrock – Partner Account Manager, Microsoft Deutschland GmbH
Guido Schmitt – Senior Consultant, Microsoft Deutschland GmbH

Beschreibungstext von der Webseite:

"Die Microsoft-Plattform ist die Standard-Plattform für SAP-Lösungen. Immer mehr SAP-Kunden entscheiden sich für Microsoft und damit für die seit zwei Jahren bewährte Mehrheitsplattform im Datenbankumfeld bei SAP-Neuinstallationen. Inzwischen erfolgen zwei von drei SAP-Neuimplementierungen auf Windows Server 2003 und mehr als 42 Prozent auf SQL Server 2005.

SQL Server 2005 ist die zukunftssichere Datenmanagementplattform für hochverfügbare, leistungsfähige und zuverlässige Installationen von SAP-Lösungen.

In diesem Seminar geben wir Ihnen einen Überblick, wie Sie SQL Server 2005 optimal in einer SAP-Umgebung einsetzen können. Die Schwerpunkte des Seminars sind:

Skalierbarkeit und Performance von SQL Server 2005 für SAP-Anwendungen
Hochverfügbarkeit von SQL Server 2005 für SAP
Konfiguration von SQL Server 2005 für SAP
SQL Server 2005 als Datenmanagementplattform für SAP
Migration auf SQL Server 2005 im SAP-Umfeld"

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

PASS Essentials: DBA StartUp

Markus Fischer — Wed, 08 Nov 2006 15:28:52 GMT

Sorry, German only...

PASS Deutschland e.V. bietet wieder eine interesssante Veranstaltung an und wir starten damit eine neue Reihe, die PASS Essentials. Ziel dieser Veranstaltung ist die Grundlagenvermittlung in einem spezifischen Thema des SQL Server 2005 innerhalb eines Tages mit einer begrenzten Anzahl an Teilnehmern und mit einen hohen Anteil Hands-On-Labs.

Los geht es mit dem "DBA StartUp", einem eintägigen Überblick über Installation,Backup & Restore, der am 23.November 2006 von 09.00 - 18.00 Uhr bei Microsoft in Bad Homburg stattfindet

Speaker ist mein hochgeschätzter PASS-Vorstandskollege Ralf Dietrich:
"Ralf Dietrich ist seit 1998 als Trainer und Consultant für Microsoft Betriebssysteme
und Datenbanken tätig. Als MCT, MCSE (Security) und MCITP für SQL Server
liegt sein Fokus in der Performanceanalyse und im Data Warehouse Design. Mit
Microsoft SQL Server 2005 beschäftigt er sich seit Mitte Juli 2003. Ihm liegen
sichere Servernetze & SQL-Server Installationen besonders am Herzen."

Hier der entsprechende Flyer PASSEssentials_DBAStartup.pdf (40.42 KB) mit mehr Informationen.

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

Writing Custom Code in SQL Server Reporting Services

Klaus Hoeltgen — Wed, 08 Nov 2006 00:40:08 GMT

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!

System Views in SQL Server 2005

Markus Fischer — Wed, 25 Oct 2006 22:00:30 GMT

You will find a complete overview for the System Views at http://msdn2.microsoft.com/en-us/library/ms177862.aspx. The most usefull ones of the Catalog Views probably will be Object Catalog Views Security Catalog Views To query metadata you will have to use the Information Schema Views. If you need some hints on how to use the SQL Server System Catalog have a look at Querying the SQL Server System Catalog and the Querying the SQL Server System Catalog FAQ for some examples.

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

Brian Welcker: Updating Linked Report Page Settings [Updated...]

Markus Fischer — Sun, 03 Sep 2006 19:02:18 GMT

Brian Welcker has published a coding to programmatically set the page size properties of a linked report. The feature doing linked reports worked well in Reporting Services 2000, because the linked report got the page settings from the original report but in RS 2005 this feature just was "forgotten", as Brian admits in this article. The following content is intellectual property of Brian Welcker, the original posting can be found here

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

Brett Kaiser: How do I track data changes in a database

Markus Fischer — Sun, 27 Aug 2006 14:09:14 GMT

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 erver time a DELETE or UPDATE Modification will be done. The following content is intellectual property of Brett Kaiser, the original posting can be found here:

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

Common table expression (CTE) in SQL Server 2005

Markus Fischer — Sun, 27 Aug 2006 13:39:40 GMT

Nigel Rivett has published an interesting detailed article about common table expressions in SQL Server 2005 on simple-talk.com.

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

SMO - a little introduction

Markus Fischer — Fri, 25 Aug 2006 13:55:09 GMT

Definition from the SQL Server 2005 Product Guide: "SQL Server Management Objects (SMO) is the management object model for SQL Server 2005. SMO represents significant design and architectural improvements for the SQL Server management object model. It is a simple to use but rich object model that is based on .NET Framework managed code. SMO is the primary tool for developing database management applications using the .NET Framework. SMO is used extensively by SQL Server Management Studio. Every administrative action that you can perform in SQL Server Management Studio, you can also accomplish by using SMO. The new SMO object model and the WMI APIs replace SQL-DMO. Where possible, SMO incorporates similar objects as those in SQL-DMO for ease of use. You can still use SQL Server 2005 with SQL-DMO, but SQL-DMO will not be updated to manage features that are specific to SQL Server 2005.

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

some SQL Server scripts regarding security

Markus Fischer — Fri, 25 Aug 2006 13:19:02 GMT

Randy Dyess has published an article called "Auditing Your SQL Server Environment: Part II (Reviewing Role Memberships)"

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

"Roles should be in foremost in your mind when planning the security of your SQL Server environments. Auditing inherited SQL Server installations is a relatively easy thing to accomplish, and all DBAs should audit their environment and create documentation if they have not already done so. Once you can document the logins assigned to each of your fixed and user-defined roles, you can start to remove any duplication of permissions which can reduce the time needed to troubleshoot future permission errors."

--Use the master database
USE master
go

IF OBJECT_ID('dbo.spRoleMembers') IS NOT NULL
DROP PROCEDURE dbo.spRoleMembers
GO

CREATE PROCEDURE dbo.spRoleMembers
AS
/************************************************************
Creation Date: 04/28/02 Created By: Randy Dyess
Web Site: www.TransactSQL.Com
Email: RandyDyess@TransactSQL.Com
Purpose: Loops through all databases and obtains member
for database roles as well as server role members.
Location: master database
Output Parameters: None
Return Status: None
Called By: None
Calls: None
Data Modifications: None
Updates:
None

************************************************************/
SET NOCOUNT ON

--Variables
DECLARE @lngCounter INTEGER
DECLARE @strDBName VARCHAR(50)
DECLARE @strSQL NVARCHAR(4000)

--Temp table to hold database and user-define role user names
CREATE TABLE #tRolemember
(
strServerName VARCHAR(50) DEFAULT @@SERVERNAME
,strDBName VARCHAR(50)
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)

--Temp table to hold database names
CREATE TABLE #tDBNames
(lngID INTEGER IDENTITY(1,1)
,strDBName VARCHAR(50)
)

--Create permanent table
IF OBJECT_ID ('dbo.tRolemember') IS NULL
BEGIN
CREATE TABLE dbo.tRolemember
(
strServerName VARCHAR(50)
,strDBName VARCHAR(50)
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)
END

--Obtain members of each server role
INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC dbo.sp_helpsrvrolemember

--Obtain database names
INSERT INTO #tDBNames (strDBName)
SELECT name FROM master.dbo.sysdatabases
SET @lngCounter = @@ROWCOUNT

--Loop through databases to obtain members of database roles and user-defined roles
WHILE @lngCounter > 0
BEGIN

--Get database name from temp table
SET @strDBName = (SELECT strDBName FROM #tDBNames WHERE lngID = @lngCounter)

--Obtain members of each database and user-defined role
SET @strSQL = 'INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC ' + @strDBName + '.dbo.sp_helprolemember'

EXEC sp_executesql @strSQL

--Update database name in temp table
UPDATE #tRolemember
SET strDBName = @strDBName
WHERE strDBName IS NULL

SET @lngCounter = @lngCounter - 1

END

--Place data into permanent table
INSERT INTO tRolemember
SELECT trm.* FROM #tRolemember trm
LEFT JOIN tRoleMember prm
ON trm.strUserName = prm.strUserName
AND trm.strDBName = prm.strDBName
AND trm.strRoleName = prm.strRoleName
AND trm.strServerName = prm.strServerName
WHERE prm.strServerName IS NULL

GO

--Test Stored Procedure
EXEC dbo.spRoleMembers

PRINT 'Display by User'
SELECT strUserName, strDBName, strRoleName, strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDER BY strUserName

PRINT 'Display by Role'
SELECT strRoleName, strDBName, strUserName,strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDER BY strRoleName

PRINT 'Display by Database'
SELECT strDBName, strRoleName,strUserName, strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDER BY strDBName

Randy also has published a script about auditing user's passwords

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

IF OBJECT_ID('dbo.spAuditPasswords') IS NOT NULL
DROP PROCEDURE dbo.spAuditPasswords
GO

CREATE PROCEDURE dbo.spAuditPasswords
AS
/****************************************************************************
Creation Date: 03/22/02 Created By: Randy Dyess
Web Site: www.TransactSQL.Com
Email: RandyDyess@TransactSQL.Com
Purpose: Perform a simple audit of user's passwords
Location: master database
Output Parameters: None
Return Status: None
Called By: None
Calls: None
Data Modifications: None
Updates: None
Date Author Purpose
---------- -------------------------- ---------------------------------
****************************************************************************/
SET NOCOUNT ON

--Variables
DECLARE @lngCounter INTEGER
DECLARE @lngCounter1 INTEGER
DECLARE @lngLogCount INTEGER
DECLARE @strName VARCHAR(256)

--Create table to hold SQL logins
CREATE TABLE #tLogins
(
numID INTEGER IDENTITY(1,1)
,strLogin SYSNAME NULL
,lngPass INTEGER NULL
)

--Insert non ntuser into temp table
INSERT INTO #tLogins (strLogin)
SELECT name FROM master.dbo.syslogins WHERE isntname = 0
SET @lngLogCount = @@ROWCOUNT

--Determine if password is null and user iis SQL Login
PRINT 'The following logins have blank passwords'
SELECT name AS 'Login Name' FROM master.dbo.syslogins
WHERE password IS NULL
AND isntname = 0

--Determine if password and name are the ssame
SET @lngCounter = @lngLogCount

WHILE @lngCounter <> 0
BEGIN
    SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)

    UPDATE #tLogins
    SET lngPass = (SELECT PWDCOMPARE (@strName,(SELECT password FROM master.dbo.syslogins WHERE name = @strName)))
    WHERE numID = @lngCounter

    SET @lngCounter = @lngCounter - 1
END

PRINT 'The following logins have passwords the same as their login name'
SELECT strLogin AS 'Login Name' FROM #tLogins WHERE lngPass = 1

--Reset column for next password test
UPDATE #tLogins
SET lngPass = 0

--Determine if password is only one characcter long
SET @lngCounter = @lngLogCount

WHILE @lngCounter <> 0
BEGIN
    SET @lngCounter1 = 1
    SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)
    WHILE @lngCounter1 < 256
    BEGIN
        UPDATE #tLogins
        SET lngPass = (SELECT PWDCOMPARE (CHAR(@lngCounter1),(SELECT password FROM master.dbo.syslogins WHERE name = @strName)))
        WHERE numID = @lngCounter
        AND lngPass <> 1

        SET @lngCounter1 = @lngCounter1 + 1
    END

    SET @lngCounter = @lngCounter - 1
END

PRINT 'The following logins have one character passwords'
SELECT strLogin AS 'Login Name' FROM #tLogins WHERE lngPass = 1
GO

--Test
EXEC dbo.spAuditPasswords

And last but not least Bradley Morris has published an article about "How to Script User and Role Object Permissions in SQL Server"

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

Russell Christopher: Using a parameterized SSIS package as a data source for SQL Reporting Services

Markus Fischer — Thu, 17 Aug 2006 11:23:04 GMT

You need to have to do some manual prerequisites to convince you Reporting Services installation to accept SSIS packages as a source as described in "Defining Report Datasets for Package Data from SQL Server Integration Services". Russell Christopher brings up some additional thoughts in his article "Using a parameterized SSIS package as a data source for SQL Reporting Services" and therefor lifts this datasource to a higher level with more value. Thanks Russell!

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

PASS Deutschland e.V. - Newsletter library [updated]

Markus Fischer — Wed, 16 Aug 2006 11:58:24 GMT

You probably know that PASS Germany publishes a really sophisticated newsletter every month containing a load of useful informations regarding every topic of the SQL Server.

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

SharePoint Integration in SQL Server 2005 SP2

Markus Fischer — Tue, 15 Aug 2006 17:08:46 GMT

Russell Christopher has posted a very intersting summary of SharePoint Integration in SQL Server 2005 SP2.

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

Select All in SSRS 2005 SP1 - or: I´ll be back!

Markus Fischer — Tue, 15 Aug 2006 16:53:48 GMT

Nobody should say that Microsoft and the product teams do not listen to feedback and are not willing to learn... With SP 1 Microsoft had "killed" the "Select All" Parameter for Multivalue Parameters and thereby triggered many discussions throughout Reporting Services users like this one in Nick Barclay's BI Blog. There was a workaround provided but this means that many manual work had to be done after SP1 surprisingly suppressed the option "Select All"... Brian Welcker now has given some explanations and announced that "Select All" Parameter for Multivalue Parameters will be reimplemented in SP2 and if you would like to get it earlier as SP2 arrives you could get in contact with Support Services and refer tor Knowledge Base Article #919478 - which is not published at the moment. SSRS Team 0 : 1 SSRS Users I think this will be continued... :-)

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

Brian Keller: Sample Script for Setting Item-level Security in Reporting Services

Markus Fischer — Fri, 11 Aug 2006 22:39:56 GMT

The following content is intellectual property of Brian Keller, the original posting can be found here.

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

Brian Keller: You've just installed Reporting Services, now back up that encryption key!

Markus Fischer — Fri, 11 Aug 2006 22:27:19 GMT

The following content is intellectual property of Brian Keller, the original posting can be found here.

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

former SSRS blogger: Brian Keller

Markus Fischer — Fri, 11 Aug 2006 20:01:29 GMT

Brian Keller nowadays is a Program Manager on the Systems Management Server team, but before that he was a member of the reporting services universe providing some real interesting articles, for example: Sample Script for Setting Item-level Security in Reporting Services You've just installed Reporting Services, now back up that encryption key! I will copy the content to my blog to assure the availability...

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

Workaround for User!UserID in Reporting Services

Markus Fischer — Fri, 11 Aug 2006 12:51:41 GMT

Identifying the User during runtime sometimes can be usefull for your report header or footer, can be used to filter data or for parameters, and so on but it bars users from subscribing to reports, because Reporting Services is not able to identify the user from the subscription. Brian Cooper had published a workaround for the known issue coming around using User!UserID in Reporting Services, but the content is gone...I discovered that during a discussion at the MSDN forum about the problem with User!UserID in datadriven subscriptions. Jan Pieter Posthuma remembered the code and had the correct hint to solve this problem - many thanks!

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

AMO - a little introduction

Markus Fischer — Fri, 11 Aug 2006 11:49:21 GMT

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!

SSRS - URL Access with refreshing problem...

Markus Fischer — Wed, 09 Aug 2006 20:52:41 GMT

Today a customer of mine remarked, that using the URL Access to show a report in an application without using the report manager does not refresh in a proper way if you use rc:Toolbar=false to hide the toolbar and leads to a non-refreshed report showing "old" values while displaying the toolbar shows a refreshed report It seems, that activating the toolbar opens up a new session every time the report is opened whereas hiding the toolbar upholds the session and for that purpose the content is not re-rendered. If you want to get rid of this behavior you can use rs:ClearSession=true to force re-rendering and remove all report instances associated with an authenticated user from session.

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

SSRS now certified for SAP BW

Markus Fischer — Mon, 31 Jul 2006 11:57:01 GMT

Brian Welcker has announced, that SAP BW (SAP Netweaver BI) support contained in SQL Server Reporting Services 2005 SP1 now officially is certified by SAP and that Microsoft is the "first vendor certified on XML for Analysis".

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

screencasts done with Wink

Markus Fischer — Tue, 25 Jul 2006 16:30:11 GMT

These days I came across a post by Jens Süßmeyer announcing that he has done some screencasts to visualize some common sql problems. Jens has done it with wink and I think I will also give this tool a try to document some bugs rather by screens than with words...

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

SoBI & architectural thoughts on SQL Server

Markus Fischer — Tue, 11 Jul 2006 22:36:32 GMT

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:

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!

Project REAL—Business Intelligence in Practice

Markus Fischer — Tue, 11 Jul 2006 21:59:25 GMT

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!

Business Intelligence Architecture and Design Guide

Markus Fischer — Tue, 11 Jul 2006 21:47:16 GMT

Chris Webb has blogged about the "Business Intelligence Architecture and Design Guide" downloadable at gotdotnet.

I agree with Chris, this is not really new stuff, but I personally think it is good to have informations like this in a kind of "complete package".

Extract from the website:
"The patterns & practices team is please to announce the availability of Architecture and Design Guidelines for Business Intelligence Applications. This exploratory community project provides prescriptive guidance on how to overcome architectural challenges and design issues when building Business Intelligence solutions using Microsoft platform. This guide is intended for software architects and developers who are developing Business Intelligence applications on the Microsoft .NET Framework using SQL Server 2005 - Integration Services and Analysis Services. Being an exploratory community project - anticipate gaps and scope for improvement. For more information see: http://codegallery.gotdotnet.com/biguide."

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

PASS BI BootCamp 2006

Markus Fischer — Wed, 07 Jun 2006 21:11:29 GMT

PASS Germany is doing a BI BootCamp again!
[Even if it is an event in german language I am writting in english - please spread this news around in your blogs though interested community members have the chance to participate]

Better, bigger, more content, more networking, more speakers than 2005 - if even possible! :-)
And also a suprising night session again - stay tuned!

If you want to register, please use this link.

Here is the eight-slide-presentation for download:
PASS BI-BootCamp2006_8-Slides.pdf (190,66 KB)

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