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:

image

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

image

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

Kategorien:Allgemein Schlagwörter: ,

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
Kategorien:Allgemein Schlagwörter: ,

SSRS Helper Function for Open Report in new window

6. Oktober 2011 1 Kommentar

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)

Kategorien:Allgemein Schlagwörter:

Vorschau Excel PowerPivot V2 (SQL Server “Denali” CTP3)

22. August 2011 2 Kommentare

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

clip_image002–> clip_image004

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 :

clip_image006 –>Pivot clip_image008

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:

image–>image

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

imageimage

7. Mehrere Neue DAX Funktionen

8. Killer-Feature: “Sort by Column”
…ermöglichen in V2 z.B. die KORREKTE Datums/Monats-Sortierung  Zwinkerndes Smiley

image

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!

Kategorien:Allgemein Schlagwörter: , , ,

HowTo sort Date-Dimension (SSAS) parameter values in SSRS ?

12. April 2011 1 Kommentar

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]

image

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]

image

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

image

Hint: Use Mosha’s free Online-MDX-Studio  to format SSRS-generated MDX !

Kategorien:Allgemein Schlagwörter: ,

HowTo Create, Export and Import SQL Server 2008 Database Diagrams

17. März 2011 8 Kommentare

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:
image

 

With the integrated Diagram Designer you can produce multiple, nice looking diagrams, position tables and relationships as needed, create annotations…

image
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:

image

Now you execute the stored procedure without parameters:

image

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:

image

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:image

 

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=Nmicrosoft_database_tools_support, @value=1 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=Nsysdiagrams                  
                    
       PRINT [sysdiagrams] table was created as it did not already exist             
       END              
       — Target table will now exist, if it didnt 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 didnt complete, so delete the diagram row‚       
         PRINT ‚
    — (and hope it doesnt 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; }

Kategorien:Allgemein Schlagwörter: , , , , ,

SQL Server “Recovery Model” korrekt konfigurieren

14. März 2011 2 Kommentare

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:

clip_image002

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

Kategorien:Allgemein