REFRESH!

[Minesweeper] 47 Games Played. 31 Bombs Used. 6357 Moves Performed. 8 New Users

oh goody. Here we go with XML parsing errors from my data vendor again.

` <po_number><![CDATA[Consult about blood pressure re biometrix]]></po_number>`

XML parsing: line 1639, character 53, '>' expected

character 53 is the space between 'blood' and 'pressure'.

really???

Unicode translator says:

U+0062 : LATIN SMALL LETTER B
U+006C : LATIN SMALL LETTER L
U+006F : LATIN SMALL LETTER O
U+006F : LATIN SMALL LETTER O
U+0064 : LATIN SMALL LETTER D
U+0020 : SPACE [SP]
U+0070 : LATIN SMALL LETTER P
U+0072 : LATIN SMALL LETTER R
U+0065 : LATIN SMALL LETTER E
U+0073 : LATIN SMALL LETTER S
U+0073 : LATIN SMALL LETTER S
U+0075 : LATIN SMALL LETTER U
U+0072 : LATIN SMALL LETTER R
U+0065 : LATIN SMALL LETTER E
U+0020 : SPACE [SP]

Is this actually an issue in SQL Server instead of the XML itself?

A different file, same source. "XML parsing: line 1592, character 24, unexpected end of input""

the line in question: ` <creation_timestamp>202109281043</creation_timestamp>`

As listed, character 24 is the 2nd i in <creation_timestamp>. However, notepad++ is formatting the text with leading tabs. The actual 24th character is the 1 in 2021 in the date.

An unexpected end of input I could see being a breakdown in passing the string between VBA and SQL Server...

I presume that the leading <tab>s are inserted by NPP, right? When I get these errors, I simply output the XML string directly to my log file and when I view it in NPP, it renders it as a tab...

nope. the tabs must be part of the data stream itself... Notepad.exe is rendering a space, too...

I guess for the 2nd one, then, the 24th character is the 3rd 2 in 20210928 - the first digit of the day of month. Still how's that an unexpected end of input?

ok, it's got to have something to do with passing the string from VBA to the stored proc. I just called the SP by hand, passing in the XML string exactly as it's recorded in my log (with the exception of ' to '' so as to not terminate the string early) and it ran for both XML streams.

one of them is 194,342 characters long. Is that too long for VBA and/or passing a parameter?

my parm in the stored proc is nvarchar(max) so there shouldn't be any issues whatsoever there...

but still, that would only explain the unexpected end of input not the '>' expected...

@FreeMan when you create the ADODB parameter for the XML, are you providing the size of the string for the parameter?

Yes I am. Even adding 2 extra bytes...

          Case adVarChar
            .Parameters.Append .CreateParameter(Name:=parms(index), Type:=parms(index + 1), direction:=adParamInput, size:=Len(parms(index + 2)), Value:=parms(index + 2))

(yes, ugly code. Passes a ton of params to the procedure instead of passing an object. No it hasn't been refactored... :( )

no, doesn't add 2 bytes. Can't read my own code... :/

hmm. not sure you want adVarchar

Don't want to pass XML string as Varchar?

my stored proc:

CREATE   PROCEDURE [dbo].[LoadApptPlusAppointmentsFromXML](
		@inputClinicID int,
		@inputStream NVARCHAR(MAX))

what I can't remember ATM is whether I used adLongVarchar instead of adVarchar

adLongVarWChar 203 Indicates a long null-terminated Unicode string value.

just saw that one...

probably want

adLongVarChar 201 Indicates a long string value.

Don't think I get unicode, but people can create their own appointments via a web interface on their phones and insert stupid emoji, so it's probably best to accommodate them some how...

Yeah, I'd give that a try to see if that helps

adLongVarWChar it is. nVarChar(max) will properly handle unicode on the server side, right?

yes.

OK. Will give that a shot. it makes sense.

Now, if I can just figure out why the duck is giving an unexpected error on parse. :(

Arrrgh! this again:

seriously, how am I denied access to my own local C: drive?

it's not read only

I've got full rights to it...

is it a file locking issue within RD?

I have to think so.

those filesystem errors are not always the most descriptive

but that implies that there's one thread writing to the file and another trying to read from that file. That would cause the "locking" file issue

Unfortunately, when this plagued me previously, it was assumed that it was because the accdb was on the network. I've completely reworked everything so that all my development is on my local drive and all production is run from a local drive version, too, and still running into it.

Yes I remember discussing this previously and we had to rule those other reasons out

including IT's stupid policies

I think @MathieuGuindon looked into it several months ago, prior to the new job and the ensuing sabbatical, but nothing concrete was ever found

We need hactoberfest to start about 1x per month. There was a great flurry of activity the first 2 weeks of this month! ;)

Amazing what people will do for a t-shirt...

sometimes a compact & repair will get me a clean parse, sometimes not.

Sadly, I've become so dependent on RD, that it's almost impossible to code w/o it now! I don't even know how to look find things in the default PE vs the duck's CE...

crap. Staff meeting in 10 minutes. Guess I'd better put in my contacts and grab some breakfast...

How is that a sad issue?

It means I can't program w/o the duck and the duck's borken for me atm

"I can't program w/o the duck": positive for this thread. "the duck's borken for me atm": negative. Positive * negative = negative. I stand corrected.

at least you grok the calculus.

I wonder if it's because I'm running Microsoft 365 Apps for enterprise now. This was just "upgraded" on my machine from 2016 a week or so ago.

On the plus side, the update got me this:

There's a "system setting" option for Access now that allows it to use the system wide dark theme!

anyway... IIRC, I had O365 on my laptop and it stopped working. My desktop just got the update and now it's not working. I'm wondering if there's a connection.

I'm going to suggest that yes, it is O365 related. I've been having issues with my main Access-based app. I just tried parsing a very new app that I'd never had issues with before and I'm getting the UE parsing it, and I tried an Excel app that I also remember no previous issues with and it's giving me the parse error, too.

I hear you. O365 has caused issues with an add-in I use (not the Duck).

TBH, I am pretty thrilled about the dark theme availability now. That does NOT, however, make up for ducklessness

weird. Excreen shot of my Account settings "Office theme" dropdown on my laptop:

and my my desktop which was just updated a week or so ago

The desktop has the "Use system setting" option, the laptop doesn't. Isn't o365 supposed to update pretty much every time you launch an application? I know Teams sure as heck does.

Eh are updates managed by your system admin?

(company name redacted)

@BigBen oh, of course they are!

In Excel, it seems to be "legal" (code will compile but won't run): Debug.Print [a[b]c].Value --- does anyone know how one could get an identifier a[b]c? Named ranges thankfully does not allow this insane name but that doesn't mean nobody else allows it....

hmmmmmm interesting edge case

pretty much the summary of about all answers these days on SO

And it seems the "answer in the comments" has become prevalent across all of SE, not just the few stacks I tend to frequent.

Dim d As DAO.Database
Set d = CurrentDb
Dim q As DAO.QueryDef
Set q = d.CreateQueryDef("")
q.Connect = "ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=(local);Trusted_Connection=yes;"
q.SQL = "SELECT 1 AS ""a[b]c"";"

Dim r As DAO.Recordset
Set r = q.OpenRecordset
Debug.Print r![a[b]c].Value

the funny thing is that I can't do this with Access table or query -- Access dislikes me trying to use a[b]c as a name but SQL Server's all "OK, sure."

@FreeMan I attribute this to 1) easier to answer in comments than post a full answer. and 2) the overwhelming swarm of low-quality questions.

at least on SO

Access has limitations that SQL Server doesn't and @this is perplexing you?

@FreeMan ah. writes down FreeMan on the list of insane people to avoid inheriting code from

?

We all knew @FreeMan was insane... it's just a question of to what degree.

@FreeMan would you want to work with someone who uses non-alphanumeric names in their code? I don't.... :) (look at the last line of VBA)

@this oh, heavens no! You, however, seemed to be perplexed by SQL Server allowing it but Access not. I do not endorse such code, but, that it seems to meet ISO standards and is supported by SQL Server (kinda some SQL Thundercode™) but isn't supported by Access doesn't surprise me.

BTW. Me wonders if the circle sizes here were intentional:

It's mainly the fact that brackets usually have special meaning in several programming languages that it makes sense to see them disallow the use of those in an identifier. Access at least avoids this probably because MSFT knew they'd want to use VBA with it and don't want the confusion that could arise with such insane name. Not sure if ISO themselves are OK with it.

@FreeMan I'd like to think so! I like putting on the monocles on teh duck when it loads. On my other VM, it's not the right size, though.

@this Monocle on the duck! I like that one. I'm almost subconsciously obsessive about doing so!

well, you ain't the only one. :)

wonders if the next logical step after monocle is top hat and moustache

@FreeMan Just read your messages explaining your problem this morning. My first guess would have been sized varchar to (unsized) nvarchar conversion.

That cuts off the string at the legth cap for sized nvarchar, which is half of that of sized varchar.

Sorry not checked chat in a couple of days. @FreeMan I wouldn't worry about GH. Git is ridiculously portable, all GH really adds is some workflow around it and some nice collaboration features. If MS decided to screw the pooch the entire world would decamp somewhere else in like 5 minutes.

MS paid billions for the community, they won't burn that.

There's 2 concerns at the moment: 1) If you agree to join the Dot Net Foundation, does that mean they "own" your project? 2) Is Net Core really FOSS? Or do they want to keep the best bits for their dollar-cost products?

@mansellan My guesses are 1) Yes, but it'll be buried so far down in the fine print nobody will notice until it's too late 2) They're gonna charge for the best bits. It's how they make money.

That seems to be exactly what they tried. And they're getting called out on it.

@M.Doerner I think we've eliminated that. I didn't get a chance to make the change to either adLongVarWChar or adLongVarChar - got tied up in meetings. will have to try that tomorrow.

le sigh...

Bear in mind that MS is a huge corporate beast. It has lawyers. And Salespeople. And Managers. And developers. They all have different (and competing) priorities. I believe Satya wants to be FOSS (because the money is in services now), so I hope he knocks some heads together.

people over there at Home Improvement just refuse to use chat. They have no idea how much fun they've missed out on

hahahahaha

@FreeMan you think those who learnt from Tim Allen would have learnt how to do it the right way?!?

all those messes that Al had to clean up after Tim wasn't an indication?

;-)

heh...

@mansellan Good to know my cynicism is well placed!

@FreeMan Their annual Build conf is in about 2 weeks time. I think there's going to be some lively comments in chat...

;)

Luke, I am your father.

NOOOOOO!

[rubberduck-vba/Rubberduck] 1441 stars vs. [decalage2/oletools] 1777 stars