Monday, 30 November 2015

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

Friday, 20 November 2015

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

Wednesday, 18 November 2015

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.

Monday, 19 October 2015

Web API, Rest or RPC

Issue

I have a service method -

public String GetDeleteErrorLog()
{‏

...‏

I'm using the url http://lpsservices/api/ScheduledTask/DeleteErrorLog/‏

‎web api works fine‏

but when I add another method

method‏

public ScheduledTask GetTask(String id)‏

‎it always calls the 2'nd method‏ (and fails because the ID not present).

(BTW its not an object delete - its just the name of the process I return an id and then want to get the stauts of the task)

 

Cause

I contacted our REST Guru and here’s what he said:

“

Web Api pattern matches against controller methods to identify the correct method‏

‎Anything prefixed with a Get is obviously a Http Get by convention‏

‎so you have a ScheduleTaskController that resolves to ScheduleTask and...‏

‎it is verb based

so you call /lppservices/api/ScheduledTask‏

‎that is all‏

you are in RPC world with DeleteErrorLog‏ it thinks DeleteErrorLog is a parameter‏ because your route definition most likely has /api/{controller}/{id} where id is optional‏ therefore DeleteErrorLog resolves as the id parameter to your method‏.

WebApi does not like nouns‏ parameters are either behind the "?" e.g. ?id=DeleteErrorLog‏, /api/{controller}/{id}”

After he explained it again I understood the implication.  This essentially means that all the get methods must have different number of paramaters.  The “paradigm” seems to be that you are carrying out REST requests on an object.

But why – I went back to the GURU:

the issue with {action} is that it makes it very difficult to leverage http caching‏

‎15‎:‎29

as it managing expiration of urls after inserts, updates and deletes gets tricky‏

me
‎15‎:‎30

I'm starting an async process and returning the id (guid) so I can retrieve the status of that process - not really going to want http cacheing!‏

shay doherty
‎15‎:‎32

thats only if you use http level caching!, like you say you don't need it currently, but for some stuff in LPS HTTP Client Cache may become very useful if the client app does alot of repititive GET requests for lists of data‏

‎15‎:‎33

HttpClient supports http caching with the "Cache Cow" add on!‏

then managing expiration of those datasets maybe important‏

‎15‎:‎34

so it might come in handy‏”

 

Solution

It seems I may have drifted from the paradigm – perhaps my parameter should be the name of the service – but the ID is a string too.  Perhaps I am “PUT” ing as I;m creating the task – but just GETing the (task) token.

However, to solve the problem I have added a new route (in WebApiConfig).

config.Routes.MapHttpRoute(
               name: "ActionApi",
               routeTemplate: "actionapi/{controller}/{action}/{id}",
               defaults: new { id = RouteParameter.Optional }
           );

So now any url begining with /actionapi/ rather than /api/ will match the controller (class) and the action (method).

You can mark the actions too e.g. [ActionName("DeleteErrorLog")] or just let them be the same as the method name.

Conclusion

If you want to use explicit actions just change your url.

Watch out for certificate expiration in database mirroring endpoints.

When database mirroring is used between two servers that are not in a domain, as can often be the case when using hosting services from another vendor, certificates are used to secure endpoints.

The MSDN article - Example: Setting Up Database Mirroring Using Certificates (Transact-SQL) explains how to do this.

What it does not mention is that SQL server generated certificates only last a year by default.  A year later all your mirroring starts to fail!

The following event log error helped me diagnose the situation -

“…Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85…”

To rectify you have to generate new certificate(s), copy them across the mirrored servers,  and change the endpoint setting to use the new certificates.

This is explained here -http://www.sql-server-performance.com/faq/database_mirror_certificate_expired_p1.aspx

Note –

1) I had to specify a start date when specifying an end date else I got a warning.

2) I used the following command to change the certificate -

ALTER ENDPOINT Endpoint_Mirroring FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE cert_Mirror_01)

I then had to restart the mirroring process again for all databases as I’d disable the mirroring.

So – if your creating certificates for mirroring endpoints – give them a nice big expiration date!

Report Viewer in Azure

Kept getting an issue that when deployed to azure could not find Microsoft.ReportViewer.Common v11.0.0.0 but when I tried adding to project then this files was not listed in the extensions.  Eventually I added the dll directly from the GAC (as a file) with copy local and bingo.

Do the same for Microsoft.ReportViewer.ProcessingObjectModel.

FYI – you can see these in C:\Windows\assembly but when you come to add in visual studio you need to browse the sub-directory given in the properties – MSIL.