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.
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
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!
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"
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...
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!
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
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
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
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
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 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?
- 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.
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...
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.
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!
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...
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
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.
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...
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: ." 
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"
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 article shows how easy it really is to write custom code for SQL Server Reporting Services
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:
I previously showed how you can programmatically set the page size properties (used by the print control) for a specific report on the server. I created an updated version of this sample that will set the page properties for all linked reports on your report server. It copies the relevant properties from the base report to the linked report. If you are using the rs.exe script host, you can delete the proxy methods at the top as they are handled automatically. This example uses the SQL 2000 RS SOAP endpoint but will work for SQL 2005 RS as well.
Dim rs As New ReportingService.ReportingService
Dim ci(), c As ReportingService.CatalogItem
Dim l As String
rs.Url = "http://localhost/reportserver/reportservice.asmx"
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
ci = rs.ListChildren("/", True)
For Each c In ci
If c.Type = ReportingService.ItemTypeEnum.LinkedReport Then
Dim p(5) As ReportingService.Property
p(0) = New ReportingService.Property
p(0).Name = "PageHeight"
p(1) = New ReportingService.Property
p(1).Name = "PageWidth"
p(2) = New ReportingService.Property
p(2).Name = "TopMargin"
p(3) = New ReportingService.Property
p(3).Name = "BottomMargin"
p(4) = New ReportingService.Property
p(4).Name = "LeftMargin"
p(5) = New ReportingService.Property
p(5).Name = "RightMargin"
l = rs.GetReportLink(c.Path)
p = rs.GetProperties(l, p)
rs.SetProperties(c.Path, p)
End If
Next
If you would like to use the script from above with the rs Utility as a Reporting Services Script File, you will have to do some changes to the coding:
Public Sub Main()
Dim ci() as object
dim c as CatalogItem
Dim l As String
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
ci = rs.ListChildren("/", True)
For Each c In ci
If c.Type = ItemTypeEnum.LinkedReport Then
try
'Console.WriteLine(c.name)
Dim p(5) As [Property]
p(0) = New [Property]
p(0).Name = "PageHeight"
p(1) = New [Property]
p(1).Name = "PageWidth"
p(2) = New [Property]
p(2).Name = "TopMargin"
p(3) = New [Property]
p(3).Name = "BottomMargin"
p(4) = New [Property]
p(4).Name = "LeftMargin"
p(5) = New [Property]
p(5).Name = "RightMargin"
l = rs.GetReportLink(c.Path)
p = rs.GetProperties(l, p)
rs.SetProperties(c.Path, p)
Catch e As SoapException
Console.WriteLine("")
Console.WriteLine("Report Error: " & c.Path & "\" & c.name)
Console.WriteLine("")
Console.WriteLine(e.Detail.InnerXml.ToString())
Console.WriteLine("")
Console.WriteLine("")
end try
End If
Next
End Sub
MSDN also includes some more usefull informations about rs.exe, like the ones mentioned below.
This one is from "Scripting Deployment and Administrative Tasks":
"The report server script host tool (rs.exe) can run custom Visual Basic code that you might write to re-create or move existing content from one report server to another. With this approach, you write script in Visual Basic, save it as an .rss file, and use rs.exe to run the script on the target report server. The script you write can call the SOAP interface to the Report Server Web service. Deployment scripts are written using this approach because it allows you to re-create a report server folder namespace and content, and re-create role-based security.
You can also use rs.exe to run custom or generated script that you create in SQL Server Management Studio. For Reporting Services, the script generation feature in Management Studio creates Visual Basic code for a very specific task (for example, creating a role, setting report properties, defining a schedule, and so on). The generated code is incomplete; it captures keyboard strokes and mouse events. To use this code, you must copy it into a larger program that you create."
This one from "Compiling and Running Code Examples":
"Reporting Services provides a scripting utility, the rs utility, which is shipped as a file named rs.exe. You can run any Visual Basic code example that is provided in the Report Server Web service library documentation using the rs utility that is included with Reporting Services.
To run a code example
-
Using a text editor, create a blank text file named sample with an .rss extension.
-
Copy and paste the following code into the blank file:
Public Sub Main()
' Your code goes here.
End Sub
-
Copy and paste the code example you want to use into the space provided in the previous code sample. Save the file. If you are copying directly from a code example provided in a reference topic, remove any import statements, module declarations and the following line of code:
Dim rs As New ReportingService2005()
The ReportingService2005 object is already declared and instantiated within the script environment. Redeclaring the object in your code will cause your script to fail. For more information about properly formed scripts, see Formatting the Reporting Services Script File.
-
Open a command prompt: On the Start menu, click Run, type cmd in the text box, and then click OK.
-
Navigate to the directory that contains your Sample.rss file. At the command prompt, type the following command to run the sample script file. Be sure to replace the given server URL with that of the report server and endpoint you are accessing. For example:
rs –i sample.rss –s http://myserver/reportserver"
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
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"
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.
Because of being the German chapter they are all in German of course...
You want to find this newsletter regularly in your In-box every first day of the month?
Then go and join up and be a member of the German community!
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...
The following content is intellectual property of Brian Keller, the original posting can be found here.
"Report Manager is a solid, Web-based administrative tool for Reporting Services, but it is not the end-all-be-all of administrative functionality for a report server. There are many capabilities available through the Web service that Report Manager does not take advantage of. Case in point: Setting a security policy (otherwise known as a role assignment) for an item using Report Manager is a bit cumbersome, especially if the item for which you want to add a policy is nested several folders deep in your report server namespace. Let's take an example. Let's say you have a folder at “/Budgets/Finance” and you would like to give Bob permission to view reports in the Finance folder. You add a new role assignment for Bob and make him a Browser user of the Finance folder. Bob logs into Report Manager hoping to navigate to the Finance folder, but...Bob is not able to see any folders when he logs in. What gives? Well, unfortunately the path to Bob's Finance folder actually contains three items: The Home or root folder (”/”), the Budgets folder and the Finance folder. Because Bob is not allowed to view the root folder or the Budgets folder, he will never see the Finance folder for which he has permissions. Sorry Bob. In order to give Bob Browser permissions on the Finance folder, you must add Bob as a Browser of both the Home and Budgets folders. If you need to add more groups or users to the Finance folder, it can get downright tiresome. Oh, did I mention that every time you add a policy for Bob to one of the folders, the inherited policy is broken. Yes, that means that any users that enjoyed inherited permissions to those items (for example BUILTIN\Administrators or some other administrator group) no longer have any access rights. You will have to re-add inherited permissions as local policies on each of the three folders in our example. Yikes. Fortunately for Bob, Reporting Services offers rs.exe, a scripting utility with complete access to the Reporting Services Web service. You can use this scripting tool to automate certain tasks and to perform administrative functions that may not be easily automated through Report Manager.
The following sample script can be used to add a security policy for a nested folder or report which automatically gives the user permissions up the namespace tree. After using this script, the item is immediately accessible to the user. In addition, you can use this script to keep or delete the current set of policies for the item, including inherited ones. I haven't given the script the whole battery of tests yet, so if you play around with the code, try it on your test server only. If you find any problems or issues, let me know."
'=====================================================================
' File: AddItemSecurity.rss
' Summary: Demonstrates a script that can be used with RS.exe to
' set security on an item in Reporting Services.
'---------------------------------------------------------------------
' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
' PARTICULAR PURPOSE.
'=====================================================================*/
'
' Variables that are passed on the command line with the -v switch:
' userName - the name of the user for which to add a policy
' roleName - the name of the role to apply for the user (i.e. Browser, Content Manager)
' itemPath - the path of the item for which you want to add the policy (i.e. /SampleReports)
' keepCurrentPolicy - whether to keep the current policy and add the new one
'
' Sample command line:
' rs -i AddItemSecurity.rss -s http://localhost/reportserver -v userName="MyTestUser"
' -v roleName="Browser" -v itemPath="/SampleReports" -v keepCurrentPolicy="True"
Public Sub Main()
Dim isRoot As Boolean = False
Dim inheritParent As Boolean
Dim policies() As Policy
Dim newPolicies() As Policy
Dim policy As New Policy()
Dim roles(0) As Role
roles(0) = New Role()
roles(0).Name = roleName
policy.Roles = roles
policy.GroupUserName = userName
While Not isRoot
' Once the root of the catalog is reached,
' stop applying policies
If itemPath = "/" Then
isRoot = True
End If
policies = rs.GetPolicies(itemPath, inheritParent)
' If the user selects not to keep inherited or current policy,
' empty the policy
If Not keepCurrentPolicy = "True" Then
policies = Nothing
End If
newPolicies = AddNewPolicy(policy, policies)
rs.SetPolicies(itemPath, newPolicies)
itemPath = GetParentPath(itemPath)
End While
Console.WriteLine("Policy successfully set.")
End Sub 'Main
' Method to parse the path of an item and retrieve
' the parent path of an item
Private Function GetParentPath(currentPath As String) As String
Dim delimiter As String = "/"
Dim rx As New System.Text.RegularExpressions.Regex(delimiter)
Dim childPath As String() = rx.Split(currentPath)
Dim parentLength As Integer = childPath.Length - 1
Dim parentPath(parentLength) As String
Dim i As Integer
For i = 0 To parentLength - 1
parentPath(i) = childPath(i)
Next i
If parentPath.Length = 1 Then
Return "/"
Else
Return String.Join("/", parentPath)
End If
End Function 'GetParentPath
' Takes the policy to add and applies it to the current set
' of policies if applicable
Private Function AddNewPolicy(policyToAdd As Policy, policies() As Policy) As Policy()
If Not (policies Is Nothing) Then
Dim policy As Policy
For Each policy In policies
If policy.GroupUserName = policyToAdd.GroupUserName Then
Throw New Exception("The supplied User policy already exists for the item.")
End If
Next policy
Dim list As New System.Collections.ArrayList(policies)
list.Add(policyToAdd)
Return CType(list.ToArray(GetType(Policy)), Policy())
Else
policies = New Policy(0) {}
policies(0) = policyToAdd
Return policies
End If
End Function 'AddNewPolicy
The following content is intellectual property of Brian Keller, the original posting can be found here.
"I presented some demos at this year's PASS (Professional Association of SQL Server) Community Summit on how to use the rskeymgmt utility in Reporting Services. This is a crucial tool and should be used after every Reporting Services installation. What is it good for? Well, it can do several things, least of which is to extract and store the symmetric keys used to encrypt data in the report server database or catalog. This tool captures the complete key set that is defined during setup, and stores it as a file that you can save to disk or to removable storage media. You will absolutely need this key at some point if the account that is used for the ReportServer Windows service changes in some way (if a password is changed for instance) or if you want to connect a new report server to an existing report server database instance. Without the ability to apply this key at a later date to a report server instance, you may be forced to delete all encrypted data in your catalog including data source and user information. The rskeymgmt utility can help with that as well, but this shouldn't be necessary as long as you back up the key. The key is an integral part of storing encrypted data. If, at some point, the encryption key for the report server instance is different from the key used to store encrypted content in the database (i.e. a new report server installation, but an existing catalog), you report server will not function properly. So, bottom line, store your encryption key on a floppy disk for safe keeping using rskeymgmt. You can read the specifics about the tool on MSDN here"
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!
Declare that parameter with a default value of the output of the custom code function (Code.UserName()) and thru out the report you use this parameter as replacement for the User!UserID. When scheduling this report you bind this parameter with the output from your subscription query.
In case you haven't got the custom code from the weblog:
Public Function UserName()
Try
Return Report.User!UserID
Catch
Return "System"
End Try
End Function
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.
Therefor the correct URL Access Syntax to access a report in HTML mode without a toolbar and with actual data will be something like:
http://servername/reportserver?/folder/report name&rs:Command=Render&rs:format=HTML4.0&rc:Toolbar=false&rs:ClearSession=true
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".
Congratulations! Another step for Reporting Services on the way to enterprise reporting, I think...
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...
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...
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)."
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."
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)
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...
With SP1 SQL Server Reporting Services 2005 will now also accept SAP BW as a data source. The connection will be established without any custom code in SAP BW (and therefor no need of installation assistance by SAP Administration) using XML/A (XML for Analysis, XML standard for Online Analytical Processing [OLAP] using standard Internet protocols), which is an established standard as described at the website of the organization (http://www.xmla.org/, http://www.xmlforanalysis.com/).
Microsoft provides a really good white paper, which includes a step-by-step guidance and also describes some prerequisites and basics. There is also a video, which visualizes the described steps of the white paper.
The whole documentation and the connect to SAP BW is real good work, but some things regarding "real life" is not included in the white paper or is not mentioned as clearly as needed - in my personal opinion.
Basic Information
The SDN at http://www.sdn.sap.com/ (SAP Developers Network - do not hesitate to surf there, you will not get infected... ) provides a real good basic information as pdf-file for XML for Analysis from the SAP point of view.
SAP System Prerequisites
If you are authorized to connect to the SAP system and would like to test if the XMLA SOAP Services is up and running please use transaction SICF (Service Maintenance) to check this - as described in the above mentioned pdf-file on page 14. And if SAP Administration does not know how to verify this hand them over the pdf-file...
You will also be able to discover the service url using the transaction SE37 and the function module RSBB_URL_PREFIX_GET as desribed on page 13.
Patch Level
As described in the white paper, there is a certain patch level required to assure a working XMLA-Connect.
- Support Package 30 for SAP BW 3.0B
- Support Package 24 for SAP BW 3.1
- Support Package 16 for SAP BW 3.5
- Support Package 6 for SAP NetWeaver 2004s (BW 7.0)
Other patch levels will also work BUT... will not support real interesting things like selections via variables for example. But I would suggest that you COULD use Reporting Services to do a real smart master data reporting for example - the best way to convince the stakeholder of the SAP System that doing the patching will be really worth the work.
You will not be able to convince the SAP Administration to do the patching required for Reporting Services, because implementing a Patch is not as "easy" as in the SQL Server World, doing a patch in SAP BW definitely means a own and time intense project, because all implementations in the system have to be checked, if they would work as expected and as designed at the current patch level.
So just think about this point and do not hassle with SAP Administration and please do not start moaning, if they are not willing to do the patch update - remember, it is not as in other worlds...
Language / Communication with SAP BW Developers
Do not wonder about the terms that SAP Developers are using while talking about the objects in SAP BW - this will be different from the terms you will encounter using XMLA or you certainly will know from the SQL Server world. So communication sometimes will be difficult but an accurate translation will be helpful and is also provided in the pdf-file on page 19 and 20.
Security & Single Sign on (SSO)
To be shure, that we think and talk about the same thing while having SSO in the mind first a definition:
"What’s the Difference Between Authentication and Single Sign-On?
Authentication, or initial authentication, occurs when users first identify themselves to a system, and in turn this identification is verified. Initial authentication in SAP environments can take a number of different forms, ranging from anonymous or guest access to a Web site through the familiar user ID and password procedure, to using X.509 digital certificates.
Where single sign-on is in place, the user is issued credentials in one form or another following initial authentication. This allows the user to forego subsequent authentication steps when accessing further systems, offering not only convenience, but also increased security by limiting the number of times users enter sensitive information. This reduces the temptation for users to choose an easy-to-guess password. Single sign-on authenticates the user to access all the applications they have been given rights to in the SSO landscape, and eliminates future authentication prompts when the user switches applications during that particular session."
[from the SAP Developers Network]
But what does this mean regarding RS on SAP BW?
While SAP BW is a data storage and reporting system whith an own authorization system you definitely would like to make the usage of the reports as comfortable as possible. Though a user should not need to athenticate against the SAP system as backend while running the different reports and provide his credentials again and again. Therefor a automatic authentification should be possible and established by the system without contacting the user.
Normally in enterprise environments you will not face a homogeneous landscape whith SAP BW residing on a Windows Server but will discover, that SAP BW is running on a UNIX OS.
If you are a lucky one with SAP in a windows environment, you probably will be able to use kerberos and the SAP CUA (central user administration) and this will be the right documentation:
But what to do, if you are facing the "standard", SAP BW on UNIX OS??
There are alos certain ways to implement a SSO solution between Windows and UNIX, including Kerberos, or 3rd-Party-Tools like the ticketing with the Tivoli Access Manager or the solution from secude. You will find appropriate documents on the SDN or other sources like:
If you do not like to establish the SSO via a 3rd-Party-Tool and are using the SAP ITS (Internet Transaction Server) than you should read this article provided by Thomas Jung very intendly.
If you can not solve the above mentioned topics, you probably could use a technical user to connect RS via XML/A, but please consider, that the user authorization in SAP BW normally is based on so called authorization objects (for example key identifiers like company code) and you will not be able to use this build-in security concept for the user while using a technical one and though you will have a loose of security. If you are just planning to deploy master data reports, than the use of a technical user could be without security concerns.
I would highly recommend that you avoid a scenario where you implement your own security structure in Reporting Services or in SQL Server. Then you are only building up certain places for administration and could not be sure that your "home-made" security provides the same security settings for the users as the settings in SAP BW. And because of SAP BW normally being the leading system and the main reporting system you should prevent this situation.
Please do not forget to ONLY use the https-SOAP-Connection, whether you are using SSO nor provide the credentials via the reporting manager application during the report call.
Conclusion
Doing Reporting Services on SAP BW is a good option to enhance enterprise reporting towards the microsoft plattform regardless, which data source is connected. Doing so also will bring up some points for discussion or some pain points - if you face a hardliner SAP Administration for example...
But to be honest, you also would not be really glad if your SQL Server data would be assimilated by a SAP BW system, would you?
If someone has other arguements, opinions and especially hints on the SSO-topic please feel free to use the comments
The SQL Server 2005 Service Pack 1 is available now, which updates your SQL Version to Build 9.00.2047.
Microsoft also now has published the "SQL Server 2005 Express Edition with Advanced Services", which contains Reporting Services now, and the "Microsoft SQL Server 2005 Express Edition Toolkit", which contains the Business Intelligence Development Studio and a Software Development Kit!
SQL Server 2005 Express now really is worth a look and it is for free, you will be able to download it here:
http://msdn.microsoft.com/vstudio/express/sql/download/
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:
- http://support.microsoft.com/default.aspx?scid=kb;en-us;Q299410
- http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx
- http://www.dbforums.com/archive/index.php/t-958399.html
- http://www.codeproject.com/aspnet/LikeDataStores.asp
- http://www.mcse.ms/archive81-2005-4-1260672.html
- http://forums.aspfree.com/asp-development-5/asp-script-for-querying-active-directory-user-details-using-adsi-21470.html
- 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.
You are using SQL Server 2005 and are missing the SQL Server Service Manager which is included in the version 2000?
Jasper Smith helps you out and has published the "SQL 2005 Service Manager" for download.
Lukasz Pawlowski provides an overview of Reporting Services at MSDN TV.
Extract from the website:
"This episode provides an overview of Reporting Services and shows how to add reports to your applications, including how to design reports using Report Designer, call SOAP methods using Visual Studio .NET 2003 against the Report Server Web service, and integrate reports into Win Forms applications."
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.
Project REAL is a cooperative effort between Microsoft and a number of technology partners in the business intelligence (BI) industry to build on actual customer scenarios to discover best practices for creating BI applications based on SQL Server 2005. The term REAL in Project REAL is an acronym for Reference implementation, End-to-end, At scale, and Lots of users.
This page gives tons of informations and really is worth reading carefully and staying for hours!
Brian Welcker presents some report authoring tips and tricks at MSDN TV.
Extract from the website:
"Getting started with SQL Server 2005 Reporting Services or the new report controls in Visual Studio 2005? Brian Welcker demonstrates some tips and tricks that you can use to add interactive features to your own reports."
If you want to know more details about SQL Server Authentication mechanisms and how to interact with IIS I would suggest reading the following articles:
Without Office 2007 (Office 12) having hit the market you could use this for BI...
But please regard that this download is available at a Microsoft website but is not a supported product, it is just a demonstration - but it is a really good one!
Business Intelligence Portal Sample Application for Microsoft Office 2003
Extract from the website:
"The business intelligence portal sample application for Microsoft Office 2003 is an integrated, customizable, multilingual, Web-based OLAP solution that enables employees in an organization to create and share structured views (OLAP and Relational) and unstructured views (Documents and URLs), based on various data sources, such as SQL Server 2000 Analysis Services, offline cube files, relational databases, SQL Server Reporting Services reports, spreadsheet data files, XML Web services, XML files, and any requested URL.
The business intelligence portal is entirely built on top of Microsoft Windows® SharePoint® Services. It extends Office Web components capabilities, and uses SQL Server Reporting Services. The BIP Viewer is a smart Web Part that can show multiple views from multiple sources and filter data based on Web Part connections with other Web Parts such as the Business Scorecard accelerator.
The business intelligence portal also contains an add-in for Office 2003, that enables end users to consume reports and OLAP-based views (charts and PivotTable reports) directly into Microsoft Outlook®, Microsoft PowerPoint®, Microsoft Excel, and Microsoft Word documents. This can streamline their daily work, and enhance their business productivity."
I do not know why Microsoft hides there sample applications in that way - especially the RSExecutionLog.
But there is a good articel on how to install it, this is the german one and this is the english version.
But do not forget to install the samples first using the MSI-Package or you will find nothing on your harddrive...
MSDN TV: Developing a Model for Report Builder and End-user Report Authoring
Extract from the website:
"Report Builder is the ad hoc query tool for SQL Server 2005 Reporting Services and allows users to build reports using a business model of the underlying database. Carolyn Chau shows how to build a model for Report Builder and some customizations that can be made to enhance the end-user experience."
Microsoft offers free e-learning courses for SSAS, SSIS and SSRS as a kind of delta-courses to enable an update of your knowledge to SQL 2005.
Course 2942: New Features of Microsoft® SQL Server™ 2005 Analysis Services
Extract from the website:
"Description:
This course will enable business intelligence developers experienced in SQL Server 2000/7.0 and Analysis Services to upgrade their skills to SQL Server 2005. The course is broken into five modules.
- Exploring SQL Server™ 2005 Analysis Services Core Concepts and Architecture
- Designing and Deploying a Unified Dimensional Model
- Administering SQL Server™ 2005 Analysis Services
- Programming in SQL Server™ Analysis Services 2005
- Working with Data Mining Solutions in SQL Server™ 2005 Analysis Services
Objectives:
At the end of the course, students will be able to:
- Build a Cube in SQL Server 2005 Analysis Services
- Administer SQL Server 2005 Analysis Services
- Program in SQL Server Analysis Services 2005
- Work with Data Mining Solutions"
Course 2943: Updating Your Data ETL Skills to Microsoft® SQL Server™ 2005 Integration Services
Extract from the website:
"Description:
This course will enable business intelligence developers experienced in SQL Server 2000/7.0 and Analysis Services to upgrade their skills to SQL Server 2005. The course is broken into four modules.
- Exploring SQL Server™ 2005 Integration Services Architecture and Core Concepts
- Designing and Debugging SQL Server™ 2005 Integration Services Packages
- Rich Functionality in SQL Server™ 2005 Integration Services
- Extensibility
Objectives:
At the end of the course, students will be able to:
- Design and debug SSIS Packages
- Configure and deploy SSIS Packages "
Course 2944: Updating Your Reporting Skills to Microsoft® SQL Server™ 2005 Reporting Services
Extract from the website:
"Description:
This course will enable business intelligence developers experienced in SQL Server 2000/7.0 and Analysis Services to upgrade their skills to SQL Server 2005. The course is broken into three modules.
- Exploring the Features and Core Concepts of SQL Server™ 2005 Reporting Services
- Designing Reports Using Report Designer
- Deploying and Managing Reports
- Working with Ad Hoc Reports
Objectives:
At the end of the course, students will be able to:
- Design Reports Using Report Designer
- Deploy and Manage Reports
- Work with Ad Hoc Reports"
There is a report pack available for download which provides log reports for SSIS - very interesting!
According to the website these reports are included:
- ErrorRows.rdl
This report is based on SSIS Dataflow components which where configured to redirect error rows to a central DB It allows you to see a count and the details error rows by ExecutionGUID or Package GUID
- EventLogAnalysis.rdl
This report has several sub reports including a graph of executions per package.
- EventLogSummary.rdl
This is a sum by server of executions on that server and per package with result count (failed, success) and average time..
- OnPipelineRowsSent.rdl
This report is based in the specific SSIS Dataflow task events of OnPipelineRowsSent which show rows processed on a per SSIS buffer basis. The sum of the numbers for any particular data path will equal the number you see processed in the Business Intelligence Development Studio. The assumption is users have configured their packages to log the OnPipelineRowsSent log entry
- PreandPostExecute.rdl
This Report will show a summary report of pre and post execute events for packages. Can be used as a running status. .e.g refresh to see pre/post pairs increment. The assumption is users have configured their packages to log the pre and post execute log entries.
Using this link - which I do periodically - I discovered a new Microsoft SQL Server Reporting Services Sample Pack available for download.
It is for the Internet Security and Acceleration (ISA) Server 2004 and all you have to do is to enable the logging of the ISA Server Reports using the SQL Server instead of text files or other data storage.
Microsoft has published the documentation about MARS - there internal financial reporting solution - as an "Microsoft IT Showcase".
Extract from the website:
"Overview of how Microsoft implemented a more robust and flexible financial reporting solution by using SQL Server 2005 technologies. By using SQL Server 2005 Analysis Services and SQL Server 2005 Reporting Services, the standard reporting of the financial results was upgraded to provide end users with an effective business intelligence platform that was more flexible and more secure."
There are some new SQL Server SSIS Sample Components available for download at the Microsoft Download Center:
CalendarTransform
"is an SSIS dataflow transform component that generates standard calendar attributes based on a DT_DBTIMESTAMP or DT_DATE input column and a user-defined set of output columns.
- Multiple calendar types (Roman, Fiscal, Reporting, Manufacturing, ISO8601)
- Numerous attributes per calendar
- Custom-formatted output values"
CodePageConvert
"is an SSIS dataflow component that translates from and to any code page or unicode character representations."
ConfigureUnDouble
"is an SSIS dataflow component that:
- Allows user to configure the component with a chosen quote character.
- Removes any such quotes that bracket input strings if present.
- Replaces doubled quote characters with single instances inside the string."
NullDetector
"was built to provide an introduction to the use of the "virtual buffer". Also included are:
- ProvideComponentProperties and the second output.
- Using DirectRow in ProcessInput.
- PostWarning."
Regex
"is an SSIS dataflow component that applies a configured regular expression against an incoming column.
- Rows with no match can be routed to the error output, fail the data flow, or be ignored.
- The matched string, as well as any matching substrings, are placed in new dataflow columns."
RTrimPlus
"takes a string or unicode column, and removes trailing spaces, whether ASCII, or Japanese."
SeeBuffer
"is useless for business. It does offer some pedagogical features. I use it to introduce the ProcessInput method, the runtime interface, and the buffer."
---^^^^^... useless... ok...
UnDouble
"is an SSIS dataflow component that:
- Removes quotes that bracket input strings if present.
- Replaces doubled quotes with single quotes inside the string."
UnDoubleOut
"is an SSIS dataflow component that:
- Removes quotes that bracket input strings if present.
- Replaces doubled quotes with single quotes inside the string."
UnpackDecimal
"takes a bytes column, and converts to decimal, using a user provided scale."
There is a "Hands On Training for SSIS" available for download on the MS sites.
Extract form the website:
"Contains a Microsoft Word lab Document, sample files to process, and a sample DB, all of which support a series of exercises which guide a reader/user through step by step creating SSIS (SQL Server Integration Services) packages. Lab document includes commentary and by completion of the exercises a user will have a good understanding of what can be accomplished with SSIS as well as how to create some simple SSIS packages."
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...
Scott Lowe privides some interesting informations about pitfalls in his article
This tutorial shows you how to properly install Microsoft's SQL Server 2005, avoiding the pitfalls of an unsuccessful deployment.
MS did it!
A real MCP for Business Intelligence, called Microsoft Certified IT Professional: Business Intelligence Developer (MCITP: Business Intelligence Developer) will be available soon in English. We will have to wait and see when the German one is ready...
Seems, that they have put very useful stuff together for these exams.
Extract from the website:
"Microsoft Certified IT Professional: Business Intelligence Developer (MCITP: Business Intelligence Developer) is the premier certification for business intelligence system designers and developers. This certification demonstrates that you can design solutions, data transformations, and reports.
Business intelligence developers design and implement multi-dimensional database models (logical and physical), data marts, data warehousing, data transforms, data analytics, and reporting solutions. This includes programming and customizing servers that use MDX, customer transforms, and custom reporting solutions. Business intelligence developers are typically employed by mid-size to large organizations."
Sorry, German only...
Der deutsche Launch steht vor der Tür und findet statt vom 08.02.2006 bis zum 09.02.2006 in der Stadthalle Karlsruhe.
Neben dem deutschen SQL Server 2005 werden dort auch Visual Studio 2005 und BizTalk Server 2006 vorgestellt.
Für die Anmeldung einfach auf das Image klicken!
Mosha Pasumansky is writing a whitepaper about Analysis Services 2005 Cache Manager and has posted an extract to gain some fedback about the technical level.
Guys, fasten your seatbelts, clear your brains - this paper will be good stuff when released.
And please do not forget to give Mosha feedback!
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
This article from MSDN describes the SQL Server Reporting Services features that are scheduled to be removed in the next release.
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.
Eric Bracke provides a cool tool named SQL Digger which enables searching in Stored Procedures, UDF´s, Views and Triggers.
He also provides guides to add it into SQL Enterprise Manager or in the .NET IDE.
I just came across this post from Bayer White who is a co-blogger of Brian Knight - they are sharing this Community Server.
There is a website available called msbicentral.com, regarding to the post, this should be a "pilot community and the "the "GOTO", place for Business Intelligence knowledge".
Extraxt from the website:
"Wayne, Brian and I spoke about what the future holds for MSBICentral and came to the conclusion that there were not enough BI Communities. We decided we should do more and make sure that are content is up to date and give examples of BI techniques for 2005. Please hold me accountable to this!"
I think we should do this...
Extract from msbicentral.com:
"MSBICentral.com is a community site that helps users like yourself learn to develop, use and administer Business Intelligence (BI) solutions using Microsoft products like SQL Server. On this site, you'll find hundreds of articles, scripts and tutorials to help you learn or perfect your BI skills. If you have a question or if you're a business intelligence newbie, look on the left bar for some quick links to get you started.
Please note: You'll need to be logged in with your free account to see articles and use much of the site."
And be ashured, registering is really worth investing the two minutes to fill out the form!
As Chris Webb posted here, there is a "SQL Server 2005 Business Intelligence Metadata Samples Toolkit", which brings up "Tools and Sample reports for Metadata Dependency Analysis of SSIS, SSAS", available for download for free.
Extract from the website:
"Microsoft SQL Server 2005 BI Metadata Samples provides tools and samples for analysing dependencies within and across SSIS and SSAS. The source code for the tools is provided.
The following are the components of this pack:
DependencyAnalyzer: a utility that scans SSIS packages and SSAS databases for enumerating metadata (objects, properties and dependencies).
DependencyViewer: a utility to view metadata organization & inter-dependencies (lineage/impact analysis).
Analysis Services Samples : sample SSAS packages that can be analyzed
Integration Services Samples: sample SSIS packages that can be analyzed
Report Model Samples (Report Builder): report builder model
Reports: sample reports built from metadata store
SQL: SQL script for creating metadata store"
You will also be able to download a whitepaper called "SQL Server 2005 Business Intelligence Metadata Whitepaper".
Extract from the website:
"This white paper covers several interesting and unique methods for managing metadata in SQL Server Integration Services, Analysis Services and Reporting Services using built-in features including data lineage, business and technical metadata and impact analysis."
Download an updated version of the documentation and tutorials for Microsoft SQL Server 2005.
Extract from the website:
"Download an updated version of Books Online for Microsoft SQL Server 2005. Books Online is the primary documentation for SQL Server 2005. The December 2005 update to Books Online contains new material and fixes to documentation problems reported by customers after SQL Server 2005 was released. Refer to "New and Updated Books Online Topics" for a list of topics that are new or updated in this version. Topics with significant updates have a Change History table at the bottom of the topic that summarizes the changes.
Books Online includes the following types of information:
- Setup and upgrade instructions.
- Information about new features and backward compatibility.
- Conceptual descriptions of the technologies and features in SQL Server 2005.
- Procedural topics describing how to use the various features in SQL Server 2005.
- Tutorials that guide you through common tasks.
- Reference documentation for the graphical tools, command prompt utilities, programming languages, and application programming interfaces (APIs) that are supported by SQL Server 2005.
- Descriptions of the sample databases and applications included with SQL Server 2005."
Download over 100 samples and three sample databases for SQL Server 2005.
Extract from the website:
"The samples download provides over 100 samples for SQL Server 2005, demonstrating the following components:
- Database Engine, including administration, data access, Full-Text Search, Common Language Runtime (CLR) integration, Server Management Objects (SMO), Service Broker, and XML
- Analysis Services
- Integration Services
- Notification Services
- Reporting Services
- Replication
The samples databases downloads include the AdventureWorks sample online transaction processing (OLTP) database, the AdventureWorksDW sample data warehouse, and the AdventureWorksAS sample Analysis Services database. These databases are used in the samples and in the code examples in the SQL Server 2005 Books Online."
Download the Microsoft SQL Server 2005 Express Edition (SQL Server Express) Books Online and samples.
Extract from the website:
"SQL Server 2005 Express Edition (SQL Server Express) is the next version of MSDE 2000. It is a free, easy-to-use, lightweight version of Microsoft SQL Server 2005. Integrated with Microsoft Visual Studio 2005, SQL Server Express makes it easy to develop data-driven applications that are rich in capability and fast to deploy. To download the samples and product documentation for SQL Server Express, click the links in the Files In This Download section. The samples installation contains over 100 samples, many of which use the AdventureWorks sample OLTP database. For more information about the samples and sample database, see the SQLServerSamplesExpressOverview.htm file below and the SQL Server 2005 Books Online."
http://www.microsoft.com/downloads/details.aspx?familyid=FA87E828-173F-472E-A85C-27ED01CF6B02&displaylang=en
Extract from the website:
"SSEUtil.exe is a tool that lets you easily interact with SQL Server. Among other things, it allows you to:
• Connect to the main instance or user-instance of SQL Server.
• Create, attach, detach, and list databases on the server.
• Upgrade database files to match the version of the server.
• Execute SQL statements via the console (similar to SQLCMD).
• Retrieve the version of SQL Server running.
• Enable and disable trace flags (for example, to trace SQL statements sent to the server by any client application)
• List the instances of SQL Server on the local machine or on remote machines.
• Checkpoint and shrink a database
• Measure the performance of executing specific queries.
• Create and playback lists of SQL commands for the server to execute.
• Log all input and output."
The Microsoft SQL Server 2005 System View Map shows the key system views included in SQL Server 2005, and the relationships between them.
This is really huge - fun anyone while reading online?
I hope you have a DinA 0 - Plotter available for printing?
Mosha Pasumansky has posted his experiances with a presentation about "Analysis Services 2000 vs. 2005".
Extract from the website:
"I have run across a presentation called “Analysis Services 2000 vs. 2005” prepared by Jaimie Basilico and Mark Frawley (Jamie works in Microsoft as Senior Technology Specialist in the East Cost, and he is one of the best field people in Analysis Services that we have). This presentation is targeted towards people who are familiar with Analysis Services 2000 and want to come on speed with Analysis Services 2005. I have found the presentation very useful, but not all subjects are covered in the same depth. Below are my comments [...]"
Full presentation can be found at http://www.nysia.org/events/past/2004/2004129dbprof.pdf
Absolutely worth reading and also worth reading Mosha´s comments!
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...
This link provides the downloadable bits for the SQL Server Web Data Administrator and some hints.
Especially you will be interested in the following:
"Note: The SQL Web Data Administrator is not officially supported by Microsoft Product Support Services (PSS). If you require support for this tool, post your question in the SQL Web Data Administrator forum on the SQLJunkies site at http://www.sqljunkies.com/forums/.
The SQL Server Web Data Administrator enables you to easily manage your SQL Server data, wherever you are. Using its built-in features, you can do the following from Microsoft Internet Explorer or your favorite Web browser:
- Create and edit databases in SQL Server 2000 or Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)
- Perform ad-hoc queries against databases and save them to your file system
- Export and import database schema and data
- Manage users and roles
- View, create and edit stored procedures
Whether you are doing Microsoft Windows or Web development, or just need remote access to data for yourself or your clients, the Web Data Administrator is the perfect complement to your toolbox."
Not only for the beginners...
Atilla Ozgur has written an article as introduction into Reporting Services which will provide a good start point.
Another article for beginners is provided by Bill Jones.
Sorry, German only...
Der Artikel "SQL Server Objekte scripten" beschreibt, wie man sowohl mit dem Enterprise Manager als auch mit einer ASP-Seite Objekte des SQL Servers skripten kann.
Realisiert wurde das Ganze über SQL-DMO, weitere Beispiele und Anregungen dazu gibt es hier.
You will find the whole listing here.
A very interesting note is, that "Reporting Services features are not yet available in SQL Server 2005 Express Edition".
When will they be available? Will they come? Or would this be to much features for free compared to the to the Workgroup Edition?
Extract from the website:
"On February 24, 2005, Microsoft announced pricing and licensing information for SQL Server 2005. In that announcement, only the Standard and Enterprise editions of SQL Server 2005 encompassed any reporting features provided by SQL Server 2005 Reporting Services. Furthermore, only the Enterprise version of SQL Server 2005 had Report Builder (ad hoc, self-service reporting). Since that time, SQL Server product management has sought and received significant feedback from both partners and customers on this decision. Because of these conversations, Microsoft is making the following modifications to its SQL Server product lineup relative to reporting."
|
Copyright © 2010 Markus Fischer. All rights reserved.
DasBlog 'Portal' theme by Johnny Hughes.
Pick a theme:
|
|