Category Archives: SQLServer_SSRS

I think making a dll in TSQL might be easier than a custom function

Posted on by

What I am trying to do is feed in a small string and it will filter some values out for me. This might be easier to do in visual studio than in a TSQL function. Here are some notes on my findings.

I am simply feeding in “ABCD” and it will match the 2 letters AB and CD and filter out the corresponding values. So if I feed in ABCD from _ABWXYXCD_ITEM1_ITEM2_ITEM3_ITEM4_ then I expect the output _ABCD_ITEM1_ITEM4_

Simple right? Phhh! Not quite in TSQL. Super easy in c#, but I have never made a dll before.

Here is a start to my TSQL that would be part of a function. I thought to myself – lets stop while we are ahead… Continue reading →

Report Services SSRS duplicates last row group and rows when exported to Excel – and only an excel import – a “feature” says Microsoft

Posted on by

Microsoft Calls it “by design” or a feature – most others would call it a bug. To keep things simple, if a group is added – it adds a row AND a column. In the current version (which is not accurate) when it is exported to Excel -the last row keeps getting duplicated making the Excel export NOT the same as the PDF or live report. Keep the group column is the short simple answer! It is a detail row (that is viewed in RowDetails->Advanced) that you might see NEEDS to be there for the excel export to work properly. Bug or not- this version fixes that issue.

The reference is here – the last line has Microsoft admitting that certain conditions must apply. Basicially – its a feature – here is how to fix that feature. 🙂

Get Sharepoint List Access from SQL Server

Posted on by

Well, here I go – how to natively (or naively at this point – I just started this) manipulate Sharepoint Lists from SQL Server.

I did it! It is not using a linked server (I will get to the bottom of that though) but using an OPENROWSET works. This means that the user needs to have insert and select privileges – but there is a way around that too – by making a role/user internal to the dB and running the stored proc run (EXECUTE AS) that role/user. To get that to work takes some tinkering – another article will come soon regarding that.

Basically – the onerous method would show that the two statements below work Continue reading →

Integrating ASP app – Users and more users, more permissions and 401s

Posted on by

When you integrate a site onto its final server – things with windows get hairy. Here are some notes to get out of trouble

  • seeing a 401 for anonymous user? Are your permissions for IUSR set to read, write, execute (ref)? This is for anonymous user. For a dynamic user, then you have to add permissions for NetworkService (see small note in this reference) (6.1 – or see this article for 7.5). For this, I had an account called (built in account: ApplicationPoolIdentity – this not correct – I will keep digging)
    • you might also require the group IIS_IUSRS to have read, write, execute. It depends on your version of IIS (see this link – this is succinct)
    • ensure BOTH the user accessing the page and the AppPoolIdentity user BOTH have NTFS permissions set to the directory and subsequent web pages (reference) Continue reading →

Adding jQuery to Report Services – (Make parameter field bigger is the reason for doing this in this article)

Posted on by

There are really 2 concepts here which is the reason for the long title. First, adding jQuery to Report Services means hacking 2 install files (.aspx files) of Report Services. If you don’t have someone who has access to the server that is hosting the web pages, it might be hard to get this done. Your IT department might say ‘no way’ but this problem is well known for RS 2008 up to the current – so it is really not a risk – if it breaks, simply fix it – most likely it will never need fixing especially if you are using an older version of RS which is by this point mature. Next to address the width – this article helped. Here is how I did it and who helped me (urls in the code and the links above)… Continue reading →