Posts Tagged ‘TSQL’

Read multiple images/files from folder and insert as blobs in DB table (for SSRS usage)

21. September 2012 1 Kommentar

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      

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


–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)
        Print ‚WARNING! Directory "’+@PictureFolder+’" does not exist at local file system of sql server! Please check variable @PictureFolder and run script again!‘
        RETURN -1

–create target table if not exists already
    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)

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)  )))
    –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
              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
    CLOSE row_cursor
    DEALLOCATE row_cursor

–output target table
IF @OutputTargetTable=1 select * from dbo.ReportImages


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 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
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;
 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
 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
 16: RETURN 0
 17: GO
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)


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



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


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:

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


Finally here’s the Stored Procedure create statement. Just copy-paste and have fun!


BEGIN    DROP PROCEDURE dbo.Tool_ScriptDiagram2008

CREATE PROCEDURE [dbo].[Tool_ScriptDiagram2008](    @name VARCHAR(128) = NULL)
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
    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)


  — 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  
    PRINT ‚
/**<error>Diagram name [‚ + @name + ‚] could not be found.</error>*/‚    
    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!             
       — 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             
       — 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 ‚
        PRINT ‚
    PRINT XxXxX + Error_Message() + XxXxX‚       
        PRINT ‚
    PRINT XxXxX END Tool_ScriptDiagram2008 – fix the error before running again XxXxX‚       
        PRINT ‚
        PRINT ‚
END CATCH‚       
        PRINT ‚
        PRINT ‚
PRINT Now add all the binary data…‚       
        PRINT ‚
BEGIN TRY‚       
        WHILE @index < @size      
          — 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       
         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 ‚
         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 ‚
         PRINT ‚

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

Best-Practice: SQL Alias für SharePoint Zugriff konfigurieren

11. Januar 2011 3 Kommentare

Bei DB-Applikationen ist es eine gute Praxis den Namen des SQLServer grundsätzlich nicht direkt im Connectionstring/Config zu hinterlegen sondern über einen „SQL Alias“ zu arbeiten!Muss der SQLServer irgendwann umgezogen werden oder im Disaster/Ausfallszenario auf einen anderen SQL Server umgeleitet werden kann man, für die Anwendung transparent, dann einfach den SQL Alias anpassen. Ein SQL Alias ist übrigens NICHT zu Verwechseln mit einem  DNS-Alias(Host/CNAME Eintrag)!

Microsoft empfiehlt in folgendem Artikel auch explizit das Anlegen von SQL Aliasen vor dem eigentlichen SharePoint Setup:

Storage and SQL Server capacity planning and configuration (SharePoint Server 2010)

Ein SQL Alias kann entweder über das, auf jedem Windows Client/Server vorhandene, Tool CLICONFG.EXE eingerichtet werden oder über „SQL Server Configuration Manager“ (wenn SQL Client explizit installiert wurde).

In beiden Fällen sollte man folgendes zu beachten: Auf 64Bit Systemen muss der Alias für 64BIT UND 32BIT separat konfiguriert werden sonst funktionieren nicht alle Anwendungen (in Abhängigkeit davon ob die Anwendung 32bit oder 64bit SQL-Provider verwendet)!

Auf Systemen ohne SQL Installation findet man die Tools hier:

32Bit -> C:\WINDOWS\System32\cliconfg.exe
64Bit -> C:\WINDOWS\SysWOW64\cliconfg.exe

Auf Systemen mit SQL Installation geht es etwas komfortabler über den “SQL Server Configuration Manager“ (Start>Programme>SQL Server>Config Tools..):


Weitere Infos:

Kategorien:Allgemein Schlagwörter: ,

Inside Managed Metadata Service (MMS) DB and User Profile Service (UPS) SocialDB

Zur Einführung hier ein paar Links zum Thema:
SharePoint Logging Database Exposed
SharePoint Tagging Exposed

Über TSQL Abfrage auf die MMS DB anzeigen wer wann welche Tags erstellt hat:


FROM [ECMChangeLog] ecl, ECMTerm t, ECMTermLabel etl
ChangeType = 1
ecl.ObjectUniqueId = t.UniqueId
ecl.ObjectId = etl.TermId 


to be continued…

Kategorien:Allgemein Schlagwörter: , , , ,

Inside SharePoint Secure Store Service (SSS) Database, Auditing, SSS Activity

25. Oktober 2010 4 Kommentare

TSQL Experten lieben SharePoint 2010. Dort gibt es viel mehr Datenbanken als in 2007 mit jeder menge nützlichen auch in der SSS Datenbank

Was ist SSS:
The Secure Store Service replaces the Microsoft Office SharePoint Server 2007 Single Sign On feature. Secure Store Service is a shared service that provides storage and mapping of credentials such as account names and passwords. It enables you to securely store data that provides credentials required for connecting to external systems and associating those credentials to a specific identity or group of identities. The SSS Database provides storage and mapping of credentials such as account names and passwords.

DB Schema of Secure Store Service DB

Additional Information for the DB can be found here: [MS-SSDPS]: Secure Store Database Protocol Specification

The DB can log informations for troubleshooting and compliance purposes. THIS IS NOT SUPPORTED AND ONLY FOR DEV-ENVIRONMENTS.

HowTo enable DB-Auditing:
1. Connect with SQL Server Management Studio (SSMS) to Secure_Store_Service….DB
2. Run following command:  UPDATE dbo.SSSConfig SET EnableAudit = 1
3. Open DOS-Box and run: IISRESET /NOFORCE
4. Audit Data should now be logged to table dbo.SSSAudit
5. You can run following query or create as VIEW for easier analysis of this table:

   –   ,[ActionType]
      ,[ActionTypeText] = CASE [ActionType] 
                           WHEN 101 THEN ‚A target application has been created.‘
                           WHEN 103 THEN ‚A target application has been updated.‘
                           WHEN 105 THEN ‚A target application has been deleted.‘
                           WHEN 107 THEN ‚The user claim (2) for an individual target application has been retrieved.‘
                           WHEN 109 THEN ‚The group claims (2) for a group target application has been retrieved.‘
                           WHEN 111 THEN ‚The claims (2) for the group of SSS users that are administrators for a target application have been retrieved.‘
                           WHEN 113 THEN ‚The claims (2) for ticket redeemers for a target application have been retrieved‘
                           WHEN 115 THEN ‚The definition for a target application has been retrieved.‘
                           WHEN 117 THEN ‚The fields for a target application have been retrieved.‘
                           WHEN 119 THEN ‚The definitions for all target applications have been retrieved.‘
                           WHEN 121 THEN ‚The credentials for an SSS user have been set.‘
                           WHEN 123 THEN ‚The credentials for a group target application have been set.‘
                           WHEN 125 THEN ‚The credentials for an SSS user for a target application have been deleted.‘
                           WHEN 127 THEN ‚The credentials for an SSS user for all target applications have been deleted.‘
                           WHEN 128 THEN ‚An SSS ticket was issued.‘
                           WHEN 130 THEN ‚An SSS ticket was redeemed.‘
                           WHEN 132 THEN ‚The credentials for an SSS user have been retrieved.‘
                           WHEN 134 THEN ‚The restricted credentials for an SSS user have been retrieved.‘
                           WHEN 136 THEN ‚A SSS user has set his/her own credentials in the SSS store.‘
                           ELSE CAST([ActionType]   as nvarchar(100))
  FROM [dbo].[SSSAudit]
  order by AuditDateTime DESC

Kategorien:Allgemein Schlagwörter: , , ,