Getting documents from a database

I’m not proud of this – the cscript below will extract the documents from a blob database matching the query and save to a file.  In this case they are pdf’s.

Each file is given a name – the intDocumentId.pdf in this example.

 

1. save as file e.g. extract.cs
2. change query to suit
3. run dos as user with access to db.
4. Change db connection string.

 

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=SQLNCLI;server=******;database=ddocs;Trusted_Connection=yes"
Set rs = cn.Execute("SELECT * FROM tblDocuments WHERE  dbo.tblDocuments.intDocumentTypeId in (SELECT intDocumentTypeId FROM tblDocumentType WHERE vcrDocumentType = 'myletter')  AND dteDateCreated > 2015-11-20")
Do While Not rs.EOF
    Set mstream = CreateObject("ADODB.Stream")
    mstream.Type = 1
    mstream.Open
    mstream.Write rs.Fields("vbnDocument").Value
    mstream.SaveToFile rs.Fields("intDocumentId").Value & ".pdf", 2
    mstream.close()
    rs.MoveNext()
Loop
rs.Close
cn.Close

Good scripts

Report execution log.

This is for SSRS 2005.

If you want to see history of report running the following query in the ReportServer database helps.

set transaction isolation level read uncommitted
go
select * from
ExecutionLog el
INNER JOIN Catalog cat
ON el.ReportID = cat.ItemID
where cat.Name like '%reportname%'

Granting iis worker process access to performance counters

Add IIS APPPOOL\DefaultAppPool (or other app pool name) to the group “Performance Monitor Users”.

Do an IIS reset.