Master the Lookup – Microsoft AAD/EntraID get all Tenant and Azure Subscription Details
Do you ever find yourself in a situation where you need to match an ID to its corresponding name in Microsoft Partner Center, Azure, or M365? Or perhaps you need to do the reverse? Here are some quick solutions:
1) Looking up TenantID from DNS-Name
- Simply visit https://www.whatismytenantid.com/ and enter the DNS-Name.
2) Looking up DNS-Name from TenantID (for tenants you have no access)
- You can use MSGraph Explorer and the „findTenantInformationByTenantId“ API at: https://graph.microsoft.com/beta/tenantRelationships/findTenantInformationByTenantId(tenantId=’XYZ‘).
3) Getting TenantID for SubscriptionID (for subscriptions you have no access)
- You can achieve this using Azure API, e.g., with PowerShell. Find the detailed guide at: https://www.lieben.nu/liebensraum/2020/08/get-tenant-id-using-azure-subscription-id/. Here’s a PowerShell function to get the TenantID from SubscriptionID:
function get-tenantIdFromSubscriptionID($subId){
$response = try {
(Invoke-WebRequest -UseBasicParsing -Uri "https://management.azure.com/subscriptions/$($subId)?api-version=2015-01-01" -ErrorAction Stop).BaseResponse
} catch {
$_.Exception.Response
}
$stringHeader = $response.Headers.ToString()
return ($stringHeader.SubString($stringHeader.IndexOf("login.windows.net") + 18, 36))
}These handy tools and methods will help you master the art of matching IDs with their corresponding names in the Microsoft Azure ecosystem.
Read multiple images/files from folder and insert as blobs in DB table (for SSRS usage)
In SSRS you can integrate picture-resources from different sources:
External = URL to an existing image stored on a webserver
Embedded = serialized blob of image will be stored in RDL
Database = Get image blob from a dataset
Personally i would always recommend to store pictures in database, instead of embedding them in each report. This approach supports central management accross multiple reports.
By storing the pictures in database you also insure that your RDL files stay “small”, if you have an RDL File with multiple images embedded you will get “huge” RDLs which decreases performance at report rendering time.
Step1: Import pictures from filesystem into DB-Table
To make the import and update of images easy i created a “helper” procedure which can be executed as follow:
EXEC dbo.ST_ReportImagesImport
@PictureFolder=‚D:\TEMP\YourImagesFolder\‘,@IncludeSubFolders=1
The procedure uses SQL-internal procedure “dbo.xp_DirTree” to get recursive list of all files in specified folder, read the files and store them in a “ReportImages” table.
Step2: Create Dataset in Report and lookup your image via expression
This table can then be used as an dataset in SSRS. To get a picture from this dataset you can use the Lookup() formula:
For example use following expression in “Image Properties” dialog to get the picture with ID 1
=Lookup(Fields!ImageBlob.Value),1, IIF(IsNothing(Convert.ToBase64String(Fields!ImageBlob.Value)),"",Convert.ToBase64String(Fields!ImageBlob.Value)), "DsReportImages")
TSQL-Code for helper procedure:
— ——————————–
— Description:
–this proc imports/syncs imagefiles from a folder in filesystem into table dbo.ReportImages
–If the image already exists in table it will be updated otherwise a new row in table will be generated
— ——————————–
— Changes:
– 2012-09-21 JJuelke initial
——————————–
/*Testcase
EXEC ST_ReportImagesImport @PictureFolder=’D:\TEMP\SUP\types\‘,@IncludeSubFolders=1
*/
CREATE PROC ST_ReportImagesImport
@PictureFolder nvarchar(2000) = ‚D:\TEMP\SUP\types\‘ –define input directory here which contains your image-files
,@IncludeSubFolders int = 1
,@OutputFileList int = 1
,@OutputTargetTable int = 1
as
–check if directory exists
declare @file_results table (file_exists int,file_is_a_directory int,parent_directory_exists int)
insert into @file_results (file_exists, file_is_a_directory, parent_directory_exists) exec master.dbo.xp_fileexist @PictureFolder
IF NOT EXISTS (select * from @file_results WHERE file_is_a_directory=1)
BEGIN
Print ‚WARNING! Directory "’+@PictureFolder+’" does not exist at local file system of sql server! Please check variable @PictureFolder and run script again!‘
RETURN -1
END ELSE
BEGIN
–create target table if not exists already
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES a where a.TABLE_NAME = ‚ReportImages‘)
BEGIN
CREATE TABLE [dbo].[ReportImages](
[ReportImagesID] [int] IDENTITY(1,1) NOT NULL,
[Filename] nvarchar(2000) NULL,
[ImageBlob] [image] NOT NULL,
[MimeType] nvarchar(200) default(‚Image/jpeg‘) NULL,
CreateDT datetime default(getdate()),
UpdateDT datetime NULL)
END
DECLARE @files table (ID int identity, FileFQN nvarchar(2000), FileName nvarchar(200), mimetype nvarchar(100), depth int, [isFile] int, blob [image] )
DECLARE @filename nvarchar(2000), @mimetype nvarchar(100), @FileFQN nvarchar(2000)
–now get folder content (recursive) and store this information in @files table
INSERT INTO @files (FileName,depth,isfile)
EXEC master.dbo.xp_DirTree @PictureFolder,@IncludeSubFolders,1
DELETE FROM @files WHERE isFile = 0
UPDATE @files set
FileFQN = @PictureFolder + FileName
, mimetype= CASE LTRIM(RTRIM(RIGHT(filename, LEN(Filename) – CHARINDEX(‚.‘,filename) )))
WHEN ‚jpg‘ THEN ‚Image/jpeg‘
WHEN ‚jpeg‘ THEN ‚Image/jpeg‘
WHEN ‚bmp‘ THEN ‚Image/bmp‘
WHEN ‚gif‘ THEN ‚Image/gif‘
WHEN ‚png‘ THEN ‚Image/png‘
ELSE LTRIM(RTRIM(RIGHT(filename, LEN(Filename) – CHARINDEX(‚.‘,filename) )))
END
–outpuf read filelist
IF @OutputFileList=1 select * from @files
–now loop/read each file and import
DECLARE @insertString nvarchar(3000)
DECLARE row_cursor CURSOR FOR SELECT FileFQN, FileName,mimetype FROM @Files
OPEN row_cursor
FETCH NEXT FROM row_cursor INTO @FileFQN,@filename,@mimetype
WHILE @@FETCH_STATUS = 0
BEGIN
SET @insertString = N’WITH Source (BulkColumn,Filename,mimetype) as (
SELECT BulkColumn,“’+@FileName+“‘ as FileName, “’+@mimetype+“‘ as mimetype
FROM OPENROWSET(BULK N“‘ + @FileFQN + “‘, SINGLE_BLOB) as tempImg)
MERGE into dbo.ReportImages t
USING Source s ON s.FileName = t.FileName
WHEN MATCHED
THEN UPDATE SET
t.ImageBlob = s.BulkColumn
,t.mimetype = s.mimetype
,t.UpdateDT = getdate()
WHEN NOT MATCHED by target then
INSERT (FileName,ImageBlob,mimetype)
VALUES (s.FileName,s.BulkColumn,s.mimetype);‘
EXEC SP_ExecuteSQL @statement=@insertString
FETCH NEXT FROM row_cursor INTO @FileFQN,@filename,@mimetype
END
CLOSE row_cursor
DEALLOCATE row_cursor
–output target table
IF @OutputTargetTable=1 select * from dbo.ReportImages
END
Output Activity of Post-Scripts in VS2010/VS2012 Database Projects (SSDT)
In General i use T-SQL MERGE Statement to do the inital fill and updates via post-scripts. If you want to write the the rowcounts of MERGE activity to your deploymentlog you can use the following:
DECLARE @mRowCounts as TTRowCounts
;WITH mySource (ID,x,y,z) as (
SELECT 1,2,3,4 UNION
SELECT 4,5,6,7
)
MERGE [dbo].[TargetTable] t
using mySource as s on t.ID = s.ID
WHEN matched then
update …
WHEN not matched by target then
insert (..)
when not matched by source then
delete
OUTPUT $action into @Mrowcounts;
EXEC [admin].[stPrintMergeRowcount] @MergedTableName = [dbo].[TargetTable]‘, @ttMergeActions = @Mrowcounts;
Helper table-type variable:
1: CREATE TYPE [dbo].[ttMergeActions] AS TABLE(
2: [mergeAction] [nvarchar](10) NULL
Helper stored procedure:
1: CREATE PROCEDURE [admin].[stPrintMergeRowcount]
2: @MergedTableName nvarchar(100) = '', @ttMergeActions ttMergeActions readonly, @full int = 0
3: AS
4: declare @insertCount int, @updateCount int, @deleteCount int;
5:
6: SELECT @insertcount= SUM(CASE WHEN mergeAction = 'INSERT' THEN 1 ELSE 0 END),
7: @updatecount = SUM(CASE WHEN mergeAction = 'UPDATE' THEN 1 ELSE 0 END),
8: @deletecount = SUM(CASE WHEN mergeAction = 'DELETE' THEN 1 ELSE 0 END)
9: FROM @ttMergeActions
10:
11: print CASE WHEN @MergedTableName <> '' THEN '** Merged data for table ['+@MergedTableName+'] ' ELSE '' END +
12: CASE WHEN @insertcount > 0 OR @Full = 1 THEN '@insertcount = ' + cast(@insertcount as varchar) + ' ' ELSE '' END +
13: CASE WHEN @updatecount > 0 OR @Full = 1 THEN '@updatecount = ' + cast(@updatecount as varchar) + ' ' ELSE '' END +
14: CASE WHEN @deletecount > 0 OR @Full = 1 THEN '@deletecount = ' + cast(@deletecount as varchar) + ' ' ELSE '' END
15:
16: RETURN 0
17: GO
SSRS Helper Function for Open Report in new window
In SSRS you can define “URL-Actions” for nearly every reportitem (textbox,tablecell..). You Can use this to navigate to another (drilldown) report. BUT URLs DO NOT OPEN BY DEFAULT IN NEW WINDOW AND URL-format for SSRS in native mode is different then in integrated mode. This Helper-function solves both problems:
1. Put this code in report.code:
public function OpenRepURL(byVal mode as string, byVal reportname as string, byVal rp as string) as string
dim outURL as string
dim rv as string
dim rs as string
outURL = System.Uri.EscapeDataString(Report.Globals!ReportFolder & "/"&reportname ).ToString
SELECT CASE mode
Case "native"
rv = "&rc:Toolbar=false&ParamMode=Hidden"
rs = "&rs:Command=Render"
rp = "&" & rp
OpenRepURL = Report.Globals!ReportServerUrl & "/Pages/ReportViewer.aspx?" & outURL & rv & rs & rp
Case "integrated"
rv = "&rv:ToolBar=None&HeaderArea=None&rv:ParamMode=Hidden"
rs = "&rs:Command=Render"
rp = "&" & rp
OpenRepURL ="hallo"
End Select
OpenRepURL = "javascript:void(window.open(‚" & OpenRepURL & "‘,’_blank‘,’toolbar=no,menubar=no,status=yes,location=no,width=1200,scrollbars=yes,resizable=yes‘),false,false)"
End Function
2. Use this expression in textfield.actions or tablecell.actions:
=Code.OpenRepURL("native","MyDrilldownReport.rdl", "pAlertID="&Fields!AlertID.Value)
Vorschau Excel PowerPivot V2 (SQL Server “Denali” CTP3)
Am 12.Juli 2011 hat Microsoft die erste offizielle Vorschau, CTP3 (CTP = Community Technology Preview) der PowerPivot V2 Software veröffentlicht. Download hier. Aber nicht vergessen, es handelt sich um BETA Software ohne irgendwelche Gewähr oder Support-Möglichkeiten!! Mit der CTP3 erstellte PowerPivot Dateien lassen sich auch NICHT mit der V1 öffnen! Die finale Version der V2 wird (leider erst) vermutlich Ende diesen Jahres bzw. wahrscheinlicher Q1/2012 verfügbar sein…
Hier meine persönliche TOP 3 der der zahlreichen neuen Features:
1. Diagramm Sicht/Editor
–> 
Der bisherige Dialog zum Erstellen von Relationen zwischen Tabellen war ja wirklich nicht…sehr benutzerfreundlich. In V2 gibt es nun hierfür einen neuen Button in der PowerPivot-Leiste mit welchem man nun das erstellte PowerPivot Model als Diagramm übersichtlich anzeigen/dokumentieren aber auch direkt bearbeiten (Neue Relationen, Hierarchien..) kann! –> GROSSARTIG
2. Hierarchien direkt in Excel
Ein Hauptunterschied von Excel zu “richtigen” Analyse/OLAP Tools wie z.B. MS Analysis Services oder SAP BW war es immer das man nur mit „flachen“ Tabellen arbeiten konnte. In V2 bietet PowerPivot nun die Möglichkeit, basierend auf mehreren einzelnen Spalten, eine neue Spalte zu erstellen welche die Daten bereits im Modell hierarchisch strukturiert. Im Beispiel hier z.B. Category>SubCategory>Product :
–>Pivot 
Hierarchien bieten die Möglichkeit (Roh)Daten zu “veredeln” und übersichtlichere Datenanalysen zu gestalten. Auch Parent/Child Hierarchien, wie man sie z.B. zur Abbildung von Organisationsstrukturen oder im Finanzwesen für die Aggregation von Konten verwendet, sind in Verbindung mit neuen DAX Formeln möglich!
3. Dynamic Drilldown
Die nächste superpraktische Neuerung ist automatischer Drilldown bzw. die Funktion “Details Anzeigen” . In V1 ist es leider nicht möglich einfach die Detailzeilen/Daten für eine aggregierte Zahl in einer Pivot-Tabelle anzuzeigen. Nun ist es durch den erwähnten Kontext-Menü Eintrag, oder einfach durch Doppelklick auf eine Zahl in der Pivot, möglich automatisch ein neues Arbeitsblatt generieren zu lassen welches die Detaildaten anzeigt:
–>
weitere interessante Neuerungen (ohne Anspruch auf Vollständigkeit):
4. Mehrfache Beziehungen
…zwischen Tabellen sind jetzt möglich! Eine Beziehung ist jeweils “aktiv” während zusätzliche Beziehungen über DAX-Formeln genutzt werden können. Weitere Infos hier
5. “Perspektiven”
..ermöglichen Teilausschnitte des Gesamtmodells zielgruppenorientiert bereitzustellen.
6. Schlüsselkennzahlen, KPIs
..können definiert und ebenfalls direkt im Model hinterlegt werden. Weitere Infos
7. Mehrere Neue DAX Funktionen
8. Killer-Feature: “Sort by Column”
…ermöglichen in V2 z.B. die KORREKTE Datums/Monats-Sortierung 
9. Zahlenformatierung direkt im Modell
…anstatt immer wieder in Pivot-Tabellen die korrekte Formatierung setzen zu müssen.
10. Metadaten: Für Tabellen-Spalten, Kalkulationen..
…es können an mehreren Stellen “Notizen” hinterlegt werden welche dann als “Tooltip” im Excel-Client angezeigt werden, z.B. als Erklärung für komplizierte DAX-Formeln…
So, obenstehendes war nur ein Kurzüberflug über die voraussichtlichen Neuerungen in Excel PowerPivot V2. Auch für das Gegenstück, “PowerPivot for SharePoint V2”, gibt es im Rahmen der CTP3 neben massiven Performanceverbesserungen auch mehrere neue Features! Sobald ich Zeit finde werde ich auch darüber Bloggen…
Hier noch ein paar Links zu Excel PowerPivot V2 (CTP3):
deutsch:
http://social.technet.microsoft.com/wiki/contents/articles/3756.aspx
englisch:
PowerPivot Nuggets
http://powerpivotpro.com/2011/07/12/powerpivot-v2-ctp3/
http://smithicus.wordpress.com/2011/07/14/kpis-in-powerpivot-denali-sql-denali-ctp3/
http://paultebraak.wordpress.com/2011/08/13/bism-%e2%80%93-dimension-hierarchies/
Wie ist eure Erfahrung mit PowerPivot V1 in der Praxis? Was ist gut, was fehlt? Würde mich über Kommentare zu diesem Beitrag freuen!
HowTo sort Date-Dimension (SSAS) parameter values in SSRS ?
Challenge:
Cube has a time-dimension where members (years,quarters,month) are ordered in ascending order. In SSRS Report the members should be ordered in descending order…
Orginal Query (produced by SSRS-wizard):
WITH
MEMBER [Measures].[ParameterCaption] AS
[Time].[Calendar].CurrentMember.Member_Caption
MEMBER [Measures].[ParameterValue] AS
[Time].[Calendar].CurrentMember.UniqueName
MEMBER [Measures].[ParameterLevel] AS
[Time].[Calendar].CurrentMember.Level.Ordinal
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
,[Measures].[ParameterLevel]
} ON COLUMNS
,[Time].[Calendar].ALLMEMBERS
ON ROWS
FROM [myCube]
Change Order of Years:
WITH
MEMBER [Measures].[ParameterCaption] AS
[Time].[Calendar].CurrentMember.Member_Caption
MEMBER [Measures].[ParameterValue] AS
[Time].[Calendar].CurrentMember.UniqueName
MEMBER [Measures].[ParameterLevel] AS
[Time].[Calendar].CurrentMember.Level.Ordinal
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
,[Measures].[ParameterLevel]
} ON COLUMNS
,ORDER
( [Time].[Calendar].ALLMEMBERS
,[Time].[Year].CurrentMember.MemberValue, DESC)
ON ROWS
FROM [myCube]
Change Order of all levels (Year,Quarter,Month):
WITH
MEMBER [Measures].[ParameterCaption] AS
[Time].[Calendar].CurrentMember.Member_Caption
MEMBER [Measures].[ParameterValue] AS
[Time].[Calendar].CurrentMember.UniqueName
MEMBER [Measures].[ParameterLevel] AS
[Time].[Calendar].CurrentMember.Level.Ordinal
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
,[Measures].[ParameterLevel]
} ON COLUMNS
,Order
( [Time].[Calendar].ALLMEMBERS
,[Time].[Calendar].CurrentMember.Properties("ID",TYPED)
,DESC )
ON ROWS
FROM GBMS
Hint: Use Mosha’s free Online-MDX-Studio to format SSRS-generated MDX !
HowTo Create, Export and Import SQL Server 2008 Database Diagrams
Documentation and communication of custom database models and objects should be a task with high importance in every project and for every good database developer and administrator! Personally i advise one of the following approaches for generating MSSQL Database documentation:
1. Reengineer DB-Diagrams with Visio 2010
(Needs Visio 2010 Professional! Only Professional Edition brings “DB-reengineer” feature! Visio 2007 brings SQL2000/2005 Support, and ONLY VISIO 2010 can read SQL2008 DBs. If you also think this is *** please vote at MS Connect for change)
OR/AND
2. Use SQL Server Management Studio (SSMS) ootb functionality > Diagram Designer
This post ist all about the second (Diagram Designer) approach:
With SSMS its easy to create DB-diagrams: 
With the integrated Diagram Designer you can produce multiple, nice looking diagrams, position tables and relationships as needed, create annotations…
Sample DB Diagram created with SSMS Diagram Designer
Limitations/Problems:
Unfortunately Diagram Designer misses features for printing or exporting your diagrams to formats like PDF. The only option possible is to make a screenshoot or use “ copy diagram to clipboard” function. Also its not possible to copy diagrams from one database to other databases or create diagrams automatically during deploment..!
Solution:
Diagrams are stored in database table [dbo].[sysdiagrams]. So if you would copy this table “by hand” to another database, the diagrams will be available in the destination DB.
To make this task easy you will find the code of a stored procedure at the end of this posting! This SP must be created in your custom database! After that you can call this SP to script-out existing diagrams as insert statement..Most of the honor goes to this blog-entry i found with google and adapted for my needs: http://www.conceptdevelopment.net/Database/ScriptDiagram2008/
Here’s the step-by-step howto:
You have already created my helper SP and have one or multiple diagrams created in your custom database:
Now you execute the stored procedure without parameters:
The output will create a exec statement for each diagram included in table dbo.sysdiagrams
If you execute the SP with @Name Parameter it will script-out the diagram to insert statements which can be executed in any database:
If you execute the generated insert statements in same database or if the diagram already exists in the database, a new/copy of the diagram will be created with timestamp in name:
Finally here’s the Stored Procedure create statement. Just copy-paste and have fun!
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = ‚Tool_ScriptDiagram2008‘)
BEGIN DROP PROCEDURE dbo.Tool_ScriptDiagram2008
END
GO
CREATE PROCEDURE [dbo].[Tool_ScriptDiagram2008]( @name VARCHAR(128) = NULL)
AS
BEGIN
DECLARE @diagram_id INT
DECLARE @index INT
DECLARE @size INT
DECLARE @chunk INT
DECLARE @line VARCHAR(max)
— Set start index, and chunk ‚constant‘ value
SET @index = 1
SET @chunk = 32 — values that work: 2, 6 — values that fail: 15,16, 64
IF @name IS NULL
BEGIN
PRINT ‚WARNING! Stored Procedure was called without defined @Name parameter WARNING! ‚ + CHAR(10)+
‚Please call one of following TSQL to export a specific diagram‘ +CHAR(10)
DECLARE @cnt int
DECLARE @cntMax int
SELECT @cnt=1;
SELECT @cntMax =Count(*) FROM dbo.sysdiagrams
DECLARE @y nvarchar(100)
WHILE @cnt <= @cntMax
BEGIN –While Loop
WITH AbfrageLoop AS (SELECT row_number() OVER (ORDER BY diagram_id) AS Row,* FROM dbo.sysdiagrams)
SELECT @y=name FROM AbfrageLoop WHERE Row=@cnt
—do something based on the query
print ‚EXEC [dbo].[Tool_ScriptDiagram2008] @Name = ‚“ + @y + ““
SELECT @cnt = @cnt+1
END –While Loop
RETURN (-1)
END
— Get PK diagram_id using the diagram’s name (which is what the user is familiar with)
SELECT @diagram_id=diagram_id , @size = DATALENGTH(definition) FROM dbo.sysdiagrams WHERE [name] = @name
IF @diagram_id IS NULL
BEGIN
PRINT ‚/**<error>Diagram name [‚ + @name + ‚] could not be found.</error>*/‚
END
ELSE — Diagram exists
BEGIN — Now with the diagram_id, do all the work
PRINT ‚/**’
PRINT ‚<summary>Restore diagram “‘ + @name + ““'</summary>’
PRINT ‚<generated>‘ + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16) + ‚</generated>’
PRINT ‚*/‚
PRINT ‚PRINT “=== Tool_ScriptDiagram2008 restore diagram [‚ + @name + ‚] ===“‚
PRINT ‚ — If the sysdiagrams table has not been created in this database, create it!
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = “sysdiagrams“)
BEGIN
— Create table script generated by Sql Server Management Studio
— _Assume_ this is roughly equivalent to what Sql Server/Management Studio
— creates the first time you add a diagram to a 2008 database
CREATE TABLE [dbo].[sysdiagrams]( [name] [sysname] NOT NULL
,[principal_id] [int] NOT NULL
,[diagram_id] [int] IDENTITY(1,1) NOT NULL
,[version] [int] NULL
,[definition] [varbinary](max) NULL
,PRIMARY KEY CLUSTERED
([diagram_id] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
,CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED
([principal_id] ASC,[name] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
)
EXEC sys.sp_addextendedproperty @name=N“microsoft_database_tools_support“, @value=1 , @level0type=N“SCHEMA“,@level0name=N“dbo“, @level1type=N“TABLE“,@level1name=N“sysdiagrams“
PRINT “[sysdiagrams] table was created as it did not already exist“
END
— Target table will now exist, if it didn“t before‚
PRINT ‚SET NOCOUNT ON — Hide (1 row affected) messages‚
PRINT ‚DECLARE @newid INT‚
PRINT ‚DECLARE @DiagramSuffix varchar (50)‚
PRINT ‚‚
PRINT ‚PRINT “Suffix diagram name with date, to ensure uniqueness“‚
PRINT ‚SET @DiagramSuffix = “ “ + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)‚
PRINT ‚‚
PRINT ‚PRINT “Create row for new diagram“‚
— Output the INSERT that _creates_ the diagram record, with a non-NULL [definition],
— important because .WRITE *cannot* be called against a NULL value (in the WHILE loop)
— so we insert 0x so that .WRITE has ‚something‚ to append to…
PRINT ‚BEGIN TRY‚
PRINT ‚ PRINT “Write diagram ‚ + @name + ‚ into new row (and get [diagram_id])“‚
SELECT @line = ‚
INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])‚
+ ‚ VALUES (“‚ + [name] + ‚“+@DiagramSuffix, ‚+ CAST (principal_id AS VARCHAR(100))+‘, ‚
+CAST (version AS VARCHAR(100))+‘, 0x)‚
FROM dbo.sysdiagrams WHERE diagram_id = @diagram_id
PRINT @line
PRINT ‚ SET @newid = SCOPE_IDENTITY()‚
PRINT ‚END TRY‚
PRINT ‚BEGIN CATCH‚
PRINT ‚ PRINT “XxXxX “ + Error_Message() + “ XxXxX“‚
PRINT ‚ PRINT “XxXxX END Tool_ScriptDiagram2008 – fix the error before running again XxXxX“‚
PRINT ‚ RETURN‚
PRINT ‚END CATCH‚
PRINT ‚‚
PRINT ‚PRINT “Now add all the binary data…“‚
PRINT ‚BEGIN TRY‚
WHILE @index < @size
BEGIN
— Output as many UPDATE statements as required to append all the diagram binary
— data, represented as hexadecimal strings
SELECT @line =
‚ UPDATE sysdiagrams SET [definition] .Write (‚ + ‚ ‚
+ UPPER(sys.fn_varbintohexstr (SUBSTRING (definition, @index, @chunk)))
+ ‚, null, 0) WHERE diagram_id = @newid — index:‚ + CAST(@index AS VARCHAR(100))
FROM sysdiagrams WHERE diagram_id = @diagram_id
PRINT @line
SET @index = @index + @chunk
END
PRINT ‚‚
PRINT ‚ PRINT “=== Finished writing diagram id “ + CAST(@newid AS VARCHAR(100)) + “ ===“‚
PRINT ‚ PRINT “=== Refresh your Databases-[DbName]-Database Diagrams to see the new diagram ===“‚
PRINT ‚END TRY‚
PRINT ‚BEGIN CATCH‚
PRINT ‚ — If we got here, the [definition] updates didn“t complete, so delete the diagram row‚
PRINT ‚ — (and hope it doesn“t fail!)‚
PRINT ‚ DELETE FROM sysdiagrams WHERE diagram_id = @newid‚
PRINT ‚ PRINT “XxXxX “ + Error_Message() + “ XxXxX“‚
PRINT ‚ PRINT “XxXxX END Tool_ScriptDiagram2008 – fix the error before running again XxXxX“‚
PRINT ‚ RETURN‚
PRINT ‚END CATCH’
END
END
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, „Courier New“, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
SQL Server “Recovery Model” korrekt konfigurieren
Jedes Produkt hat ja für Beginner so seine „Stolpersteine“, bei SQL Server ist es meiner Meinung nach definitiv die Einstellung für den DB-Wiederherstellungsmodus (Recovery Model)
Daher hier ein paar Hintergrundinfos:
Das DB Recovery Model ist eine Einstellung in den DB-Properties:
Standardmäßig hat jede neue DB im SQL Server den Modus „FULL“: http://msdn.microsoft.com/en-us/library/ms189275.aspx
Das ist gut weil dadurch ein möglicher Datenverlust minimiert werden kann, das führt in vielen Fällen aber auch zu Problemen weil folgendes vergessen wird:
Befindet sich eine DB im Recovery Model „FULL“ wird das Transaktion Log NICHT verkleinert wenn ein Full-Backup gemacht wird, NUR wenn ein Transaktion Log-Backup gemacht wird!
Wird also kein Backup gemacht, oder schlägt das Backup fehl, wächst das Transaktion Log in dieser Einstellung bis zur maximal definierten Größe!
Auf was sollte man also daher bei jeder SQL Installation und somit auch bei SharePoint achten:
1. Sofern sich nicht jemand explizit um Backups kümmert, bzw. Datensicherheit keine Rolle spielt, empfehle ich proaktiv alle DBs auf den Recovery Mode „Simple“ umzustellen
2. Für jede DB eine „praxistaugliche“ Maximalgröße für das Wachstum der
Daten + Logdateien definieren!
Sollte eine Logdatei bereits „zu groß“ geworden sein und die gesamte Platte beanspruchen, folgend vorgehen:
1) SQL Server Management Studio öffnen
2) Rechter Mausklick auf DB > Properties
3) Options>Recover Model = SIMPLE
4) OK
5) Rechter Mausklick auf DB > Tasks >Shrink>Log
6) „Release unused space“ à nach wenigen Sekunden verkleinert sich die Log-Datei
Inside SharePoint Search Database, Troubleshooting SharePoint Search
THIS IS NOT SUPPORTED AND ONLY FOR DEV-ENVIRONMENTS TO TROUBLESHOOT PROBLEMS:
— get overview ——————–
select
msscrawlurllog.lasttouchstart as Time
, msscrawlurllog.displayurl as URL
, DocName = REPLACE ( RIGHT(msscrawlurllog.displayurl, CHARINDEX(‚/‘, REVERSE(msscrawlurllog.displayurl))-1 ) , ‚%20‘, ‚ ‚)
, msscrawlurllog.errorid as Error
, msscrawlerrorlist.errormsg as Description
FROM dbo.msscrawlurllog with (nolock)
inner join dbo.msscrawlerrorlist with (nolock) on msscrawlurllog.errorid = msscrawlerrorlist.errorid
order by msscrawlurllog.lasttouchstart
— show latest crawler stats———————
SELECT [HostID]
,[HostName]
,[SuccessCount]
,[ErrorCount]
,[WarningCount]
FROM [dbo].[MSSCrawlHostList] with (nolock)
In my case i found following error through above TSQL:
Access is denied. Verify that either the Default Content Access Account has access to this repository, or add a crawl rule to crawl this repository. If the repository being crawled is a SharePoint repository, verify that the account you are using has "Full Read" permissions on the SharePoint Web Application being crawled. (The item was deleted because it was either not found or the crawler was denied access to it.)
Solution:
Solution was to set following registry-key in windows 2008 :
Disable the loopback check
Follow these steps:
|
1. |
Click Start, click Run, type regedit, and then click OK. |
|
2. |
In Registry Editor, locate and then click the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa |
|
3. |
Right-click Lsa, point to New, and then click DWORD Value. |
|
4. |
Type DisableLoopbackCheck, and then press ENTER. |
|
5. |
Right-click DisableLoopbackCheck, and then click Modify. |
|
6. |
In the Value data box, type 1, and then click OK. |
|
7. |
Quit Registry Editor, and then restart your computer. |
http://svengillis.blogspot.com/2008/10/access-denied-when-crawling-moss.html
After setting the registry key start Crawler with stsadm.exe manually:
HowTo export all AD service principal names (SPNs) to textfile
If you need to troubleshoot kerberos problems with SharePoint, Reporting Services… you need an overview of all defined HTTP SPNs in Active Directory.
You can use the the following command in a dos-box to quickly output all SPNs to a textfile with the windows tool “LDIFDE.EXE”:
ldifde.exe -f check_spn.txt -t 3268 –d “” -l serviceprincipalname -r “(servicePrincipalName=HTTP*)” -p subtree
Here’s a sample output:
If ldifde.exe i not installed on your server:
Windows 2008
–> Add Remote AD Management Tools (RSAT-ADDS) Feature with Servermanager or:
- Click Start, Run, and in the Open field run ‚ServerManagerCmd -i RSAT-ADDS‚
Windows 2003 –> Install Support Tools from windows server CD or download:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=96a35011-fd83-419d-939b-9a772ea2df90&displaylang=en
JJ's Blog 