The Heap™ – Consultancy ©®

morning

docs.microsoft.com/en-us/teamblog/technet-gallery-retirement

linkpocalypse comes again

6:50 AM

Morning

Tom V - try topanswers.xyz

7:28 AM

Does anyone know where can I find information about ReportServer2008 database?
I'm trying to get the ConnectionString of a data driven subscription. I can get the ConnectionString of the report, but I can't find it for the subscription.

2 hours later…

9:20 AM

@McNets isn't there a bunch of xml columns in the subscriptions table?

I don' thave that version at hand any more

@TomV-trytopanswers.xyz the Subscriptions table points to a DataSource table

There is a varbinary [image] column named ConnectionString

Usually [image] columns can be converted to XML using: CONVERT(xml, CONVERT(varbinary(max), content))

But not in this case

I've found some scripts to retrieve metadata

gallery.technet.microsoft.com/scriptcenter/…

But not the ConnectionString of a data driven subscription

1 hour later…

Q: How to convert xml entities to unicode characters (with read-only access) in Oracle?

10:43 AM

hullo there

anyone here with a quick fix for this:

Bonus question: it appears that some data has an ampersand in it and now when I rerun it the following error message is thrown: XML parsing failed .. invalid use of ampersand ('&') character (use &) .. - any quick fix idea? — nutty about natty 4 mins ago

from

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4), with read-only access. Into this database, some of the data is uploaded via, or rather as, XML - and quite a few entries contain multiple occurences of XML (special) character entities in the format of &#nnnn;. So far I have dealt ...

maybe a regex replace?

@nuttyaboutnatty it seems there is an accepted answer. Does it work?

11:08 AM

@McNets yes; but the source data I'm confronted with is quity "dirty"

and it seems I need some form of "exception handling" or commenting out of the ampersand

11:18 AM

I didn't test the accepted answer for all of the data

and apparently over the weekend new (dirty) data came in

so the error msg only came now

still need a fix for that

11:41 AM

@McNets is it encrypted? or possibly embedded in the subscription rather than shared?

@McNets also, doesn't look like that technet article handles for a BOM? github.com/petervandivier/hello-world/blob/master/sql/…

12:04 PM

set define off, perhaps?

12:16 PM

a simple replace(.., '&', 'xxx') in the xmltable() worked

but that's still quite crude...

slight refinement:

replace(.., ' & ', ' and ') - note the spaces (to prevent all the &#..; tags from being muddled up

would work for "AT & T" but not yet for "AT&T"

need to account for both though

also - "and" is a cheap substitute for the real ampersand...

Q: How to allow for ampersand in XMLtable()?

12:49 PM

spawned a new question:

Starting with this Q&A, note that while this works: with data as ( select 'Hallöle slovenĈina Hallöle slovenđina' str from dual ) select a.str, b.str2 from data a, xmltable( '/' passing xmltype( '<dat>' || a.str || '</dat>' ) columns str2 varchar2(4000) path '/dat' ) b … thi...

oracle-11g-r2 xml

1:09 PM

@PeterVandivier I suppose it is encrypted

1:24 PM

@PeterVandivier it doesn't work

1:38 PM

@McNets the UI is pretty friendly if you have access to "just poke around a bit"

i can't remember if you can download an xml flat file for subscriptions, but i'd suggest poking around in the service a bit

backup/restore the DB & encryption key to a VM if you're paranoid you'll break something and it's important enough to warrant the time investment

need a regex_replace to find & while ignoring all &#

&*^[#]

or rather

.&[^#]

@PeterVandivier I need to remove a legacy sql 2008 enterprise ed and I'm developing a poor-man app to send reports via e-mail (similiar to ssrs subscriptions). This app uses XML files with all reports parameters and I'd like to get current metadata and build this xml files automaticaly.

oof, yea, i've been out of SSRS too long to help much i'm afraid. i know there's some powershell utils. if you're not too put off you could do a one-off pass to decrypt the data and dump it back into the db

1:54 PM

@PeterVandivier In fact, I can write the connectionstring manually, just trying to leverage the work. (I'm not sure if I've used 'leverage' correctly)