I'm always careful to install the office products in the right order as have come across issues with that before. However, I'm not sure about the VB6 and Office 2010 order.
Currently I'm not in a position to be able to try a reinstall for a while.
> I thought I would try VBA, so started an Excel which also crashes (no VB6).
`2020-01-21 09:27:30.9972;DEBUG-2.5.0.5305;Rubberduck.Common.LogLevelHelper; Rubberduck version 2.5.0.5305 loading: Operating System: Microsoft Windows NT 6.2.9200.0 x64 Host Product: Microsoft Office 2010 x86 Host Version: 14.0.7244.5000 Host Executable: EXCEL.EXE; 2020-01-21 09:27:31.2355;INFO-2.5.0.5305;Rubberduck.UI.Command.VersionCheckCommand;Executing version check...; 2020-01-21 09:27:32.0853;DEBU
The initial problem that led me to learn VBA is as follows:
You have a table that can be up to 10,000 rows (several hundred pages) long in a Word document. The table has a title in the form of a paragraph above the first row. This title is styled such that it links to a Table of Contents (Style ...
it's probably the chrome remote desktop but I wasn't sure if it was some weird Excel setting. Everytime I try to paste, it shows "Select the destination and either press ENTER or paste" to complete (?) the paste operation. Confusing AF
at other point, it said "There was a problem with the clipboard but you still can paste." I'm not making this up.
> @MDoerner When I changed the instancing I tested it both ways. 1) Only changed the instancing, nothing else. 2) Changed instancing as well as a class members contents. Both ways resulted in a non-accurate instancing being shown in the extract interface dialog. The only way I've found to have instancing be accurate is by following the steps I wrote out above.
@this so, I commented-out the insert and left the select in place; running the query I see "batches" of records slowly coming in into the results view, with each "batch" taking longer than the previous one.. any ideas? what could cause select to yield results by the drop like this?
I suppose I can wait for the results to come in (that will take a while) and then finally view the actual execution plan (hopefully it has answers the estimated plan doesn't)
that bottleneck is why the whole thing takes 50 minutes to run
declare @issuedBuckets table (
SourceRowId int not null
,SizeIndex int not null
,Units int not null
,primary key (SourceRowId,SizeIndex)
);
insert into @issuedBuckets
select * from dbo.NormalizeSizes(@bucketsIssued,4)
; -- 281597 rows
^^ that's where I'm bucketizing the sizes ...runs pretty much instantly
@this oh, actually I did change it - made the indexes 0-based so that I don't need to do any math in the subsequent join
78.5K, 25mins
saw something on SO about nested queries in the select clause, I don't think it applies here :(
--insert into @issuedUnits
select
OrderDetailId = dw._Id
,SizeId = sz._Id
,Units = issued.Units
from Staging.dbo.YYF641SAV f641
inner join @issuedBuckets issued on f641.id = issued.SourceRowId
inner join Staging.dbo.YYF391 f391
on f641.f1 = f391.f1
--and f641.f2 = f391.f31 -- f31 is empty & cannot be used for this join.
and f641.f3 = f391.f35
and f641.f5 = f391.f42
and f641.f12 = f391.f5
and f641.f13 = f391.f6
inner join NDS.dwd.FiscalCalendars t on f391.OrderDate = t.CalendarDate and t.FiscalYear >= @cutoffYear
on error resume next... what are some legit uses? Oftentimes it's just due to laziness, but I feel that if we have set-up enuogh error handling for everything we can plan for, but would still want to add that as a "catch all" in case something in the future arises. I feel like that's a legit reason, but could just be a bum.
i've used it in functions when i'm intending to cause errors, then catch that error to produce an output (lazy use of application.match, which i could "technically" set-up a function to loop for)
What you'll probably want to do is create a natively compiled stored procedure that takes the crappy source, then denormalize it into the memory table. You may be able to do the inserts as a single scan, avoiding the painter's algorithm
well you have to write natively compiled stored procedure & create memory table.
Bad news: I never had an actual need to do it myself so I'd have to tell you to google up articles on how to create the memory table & author a natively compiled procedure
@MathieuGuindon copy; wasn't sure if you were able to try with a truncated version of the db and extrapolate for durations (not at the same time as your existing fun)
@this Why not wrap with OERN; For Each ... ; foo.DoSomethingThatMightFail; Next; OEG0;? That way you're not turning error handling on and off as the collection is iterated through?
@IvenBach you could do that, too. There's times where I need to collect the error for individual members so it has to be inside the loop. But you're right for that particular example, where there's no distinction, we could just put it outside the loop.
The other reason is that if the loop body does more than few things and only one method may fail, then we want the OERN's scope to be limited to that part, not the entire loop body, which may end up hiding other errors.
> When I start a new subroutine, do I create a new module for it, or do I create the new sub under the last one? If I put it under the last one, do I do anything special to signal that this is a new sub?
i.e. "haven't bothered trying anything whatsoever, haven't bothered reading anything about it, but please spoonfeed me everything there is to know about procedures in VBA"
declare @issuedBuckets table (
SourceRowId int not null
,SizeIndex int not null
,Units int not null
,primary key (SourceRowId,SizeIndex)
);
insert into @issuedBuckets
select * from dbo.NormalizeSizes(@bucketsIssued,4)
; -- 281597 rows
--insert into @issuedUnits
select
OrderDetailId = dw._Id
,SizeId = sz._Id
,Units = issued.Units
from Staging.dbo.YYF641SAV f641
inner join @issuedBuckets issued on f641.id = issued.SourceRowId
inner join Staging.dbo.YYF391 f391
on f641.f1 = f391.f1
--and f641.f2 = f391.f31 -- f31 is empty & cannot be used for this join.
and f641.f3 = f391.f35
and f641.f5 = f391.f42
and f641.f12 = f391.f5
and f641.f13 = f391.f6
inner join NDS.dwd.FiscalCalendars t on f391.OrderDate = t.CalendarDate and t.FiscalYear >= @cutoffYear
If anything, it could possibly slow it down because it's sorted by the combination of the two columns together instead of doing a simple binary search.
I have a sproc that puts 750K records into a temp table through a query as one of its first actions. If I create indexes on the temp table before filling it, the item takes about twice as long to run compared to when I index after filling the table. (The index is an integer in a single column, ...
# and ## tables are actual tables represented in the temp database. These tables can have indexes and statistics, and can be accessed across sprocs in a session (in the case of a global temp table, it is available across sessions).
The @table is a table variable.
Looks like # stores it very differently internally, too.
I'll have to wait for SSIS to finish running (had to manually start it earlier... for some reason the MySQL source wasn't available last night and the overnight ETL bombed)
and besides, you get a full history of changes whereas in-place metadata column can only have one version which is the last time, not every time changed.
Right, so that's a legit reason to have a history table, no?
Right. In our case if we do a lot of aggregating, it would royally suck if we had to create filtered index for every conceivable combination of PK & last version & amount to be aggregated.
So, for older items, it could enable the deletion 1mo after the next version was created. Could certainly help with things like typos.
In the general case, I've found a soft-delete column, a created date, and an updated date stamp generally help. Then you only need a couple extra indexes.
Even if it was twice a year, it could be a nice sale point.
Because you never know when the next client would be once a day.
Which is why I built it into my system. If I were to sell that at scale, I couldn't possibly handle that kind of thing even once a week while I was a small business.
So, I had to expose it to the client so they could fix their own mistakes.
VBA Rubberducking
VBA Rubberducking