I have the following table:
CREATE TABLE trans (
id SERIAL PRIMARY KEY,
trans_date date,
trans_time time
);
I want to have the following view
CREATE OR REPLACE VIEW daily_trans AS
SELECT trans_date,
max(trans_time) as first,
min(trans_time) as last,
calculate_...
@gbn please flag for migration on SO if you agree with me it fits better here (see comments on the dba.se version) then we can close ours as an exact duplicate.
@ConcernedOfTunbridgeWells There are at least two ways to get autonomous transactions in SQL Server: (1) blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/… (2) using SQLCLR with Enlist=false...then there's always the table variable trick too, I guess
@SQLkiwi Both of which are essentially hacks to get another DB connection outside of the current transaction context. It can be done, but it's not directly supported by the system.
This is our template (error logging removed)
Notes:
Without XACT_ABORT, all TXN begin and commit/rollbacks must be paired
@@TRANCOUNT must be the same on entry and exit
BEGIN TRAN incremenent @TRANCOUNT
COMMIT decrements @@TRANCOUNT
ROLLBACK returns @@TRANCOUNT to zero so you'd get error 266
...
also deals with client command timeouts. A normal timeout returns the connection to the pool bit locks and transactions still persists. SET XACT_ABORT ON fixes this
@gbn Yes, that would work. It still puts you in the business of explicitly handling exceptions and passing them up the stack if you go more than one level deep.
In practice, I ended up doing the error logging from SSIS if it was in ETL code. On Oracle, if you do error logging within a PL/SQL package, autonomous transactions are handy for that.
Bearing in mind that with Oracle you have to pay extra for OWB, and you might well just be wrapping a bunch of stored procedure code with a shell script, you do have to think in terms of the sprocs doing their own error handling and logging.
In a lot of ways I think that the accessories (SSAS, SSRS, SSIS) that come bundled with SQL Server are one of its biggest strengths. You don't get any of that out of the box with Oracle.
You get SQL*Plus, SQL*Loader, shell scripting from your host and PL/SQL. Oracle really assumes decent scripting facilities on your host, I think. On the couple of occasions I had to deal with it on Windows I ended up installing cygwin or SFU.
Barrier to entry is lower with SS. That is, if you'0ve just shelled out your first born son for Oracle you aint going to let code monkeys near it without adult supervision
Although, I've seen far, far more amateur T-SQL code.
I still see Oracle snobbery and fanboi-ism from time to time, but not nearly as much as I used to. I think SS is getting quite a lot of mindshare in B.I. circles at least, although there are still no shortage of mouth breathers in that racket.
@gbn Sort of. PL/SQL is a pascal-ish language based on Ada with a built in embedded SQL facility. The SQL interpreter used in it is not the same one that processes pure SQL queries, and they used to have slight dialectic variations.
When I was in NZ I worked with a couple of Oracle DBAs that did quite a lot of with with SQL Server as well and said they had seriously revised their opinions of it. I went the other way, then back to SQL Server as a B.I. platform, although that was more a function of what the market I'm working in uses.
PL/SQL has a similar syntax (BEGIN/END block structure etc.)
Never used Ada per se, but the syntax of PL/SQL vaguely pascal-ish
@gbn Never encountered MySQL fanbois in quantity, but the outfits I work in are all fairly stodgy, conservative insurance companies. Either large corporates or smaller Lloyd's syndicates run by ex-RSA people who still have that mindset.
@gbn For that reason I tend to assume that MySQL will stay a bit limited in its feature set and you'll have to look to one of the forks for any real progress.
Actually I think the biggest ivory tower of the lot is 'enterprise' software - ERP systems, high-end B.I. tools, network monitoring tools, even SANs. They tend to be closed shops, and often the biggest fanbois on the planet. Try talking to an informatica fanboi sometime.
Essentially, none of the above is rocket science, and it tends to attract people who just want a gravy train. Once you know the tool, you can get work just by knowing it, inspite of the fact that a competent developer could pick it up fairly quickly. I used to see that a lot with Informatica and Cognos around 2004-2005.
Anyone with a few clues could pick up either tool in a few days, but the ads all wanted 2-5 years experience with them.
Do you really want to employ somebody that just wants to do Informatica for 5 years?
Yes, I built these boxes to get reasonably fast disks into a development workstation so you could set up environments locally.
The real eye-opener about these machines is that with 6 disks I can make them outperform any SAN based production environment I've ever deployed to.
Which might go a long way to explain why I'm fairly cynical about SANs these days.
However, the CPU was more or less irrelevant, except that it was the only option for a 64 bit machine that didn't cost an arm and a leg at the time.
You could probably say I'm an XW9300 fanboi.
(for ETL jobs anyway).
One point to note about these is they have 2 PCIX-100 and a PCIX-133 slot on the motherboard, so the I/O is fairly quick (at least by the standards of the day). That's really their main claim to fame.
The CPUs aren't anything special by modern standards. Individual core speed on a Nelahem or Core I7 would be faster.
The CPUs in these machines are either what they came with (I got them secondhand - new ones are quite pricey) or what happened to be going on Ebay at the time ;) Some of them have low wattage CPUs in them, which keeps the fans a bit quieter.
But, I/O is a much bigger deal and that's what I got these for. They also have reasonably good fit and finish by PC standards which saves on the odd cut finger.
Occasionally someone mentions Z800s and I think I'd like to get one of those, but then I wake up and realise there's nothing wrong with the (nearly 5 years old now) XW9300s.
These were built so they had fast enough I/O to develop ETL jobs on - they have SCSI disks. I've been thinking of retrofitting SSDs to them, but they have PCI-X slots so they're a bit of a dead end for peripherals. There is a SATA controller on the motherboard but it's an old-ish fakeraid type.
Having said that, they make fine desktop PCs for just messing around as well. I'm using one now, and these days it doesn't get used for much but surfing the web, playing games and odd jobs.
@JackDouglas I've had rep migrate to here (or least it appears to have migrated) from questions flagged off SO, but it doesn't seem to have disappeared off there. Maybe it will go if they run a recalc.
@gbn I can see a difference between my displayed rep and the rep from that report, and my rep on dba.se jumped a few days ago when some questions I had answered got migrated here, so I guess the rep moves with the migrations. Maybe they will re-calculate the SO rep at some point. I had the impression that this isn't done regularly.
@JackDouglas It would be nice to get a few more MVP types behind dba.se.
seeing more db corruption questions recent, why do the muppets never get the tail of the tran log before screwing up all chance of successful recovery...
@MarkStoreySmith Some of it is possible but to match that requirement it's easier to push the task to sqlclr c# sp, also that requirement is strange as the detail record can only be singular. Normally it would be one row per <Detail> tag.
@MartinC I'm attempting a hack job/poc spike for a spot of Solr integration. Basically want to dump some records into a solr indexable xml file like wiki.apache.org/solr/UpdateXmlMessages
requires denormalising it into a similar schema to that question, whereby the column is a the value of a fieldname attribute
yucky
probably easier to knock up a console app than query it
I'm Jin, and I'll be working on the designs for the Stack Exchange sites as they graduate from the beta phase. Each site will have its own unique theme that will reflect its topic. However, all sites will share common elements so they feel like they're part of the Stack Exchange family.
First, t...
This is part of the way but I'm having trouble remembering some syntax
SELECT master.id as "@id",
detail.columnname AS "@columnname",
v+''
FROM (
VALUES
(123),
(456)
) AS master(id)
INNER JOIN (
VALUES
(123,'Customer','John Smith'),
(123,'Amount','888.45'),
(123,'Date','01/01/01'),
(456,'Customer','Suzie Jones'),
(456,'Amount','1000.25'),
(456,'Date','05/05/01')
) AS detail(id,columnname,v)
ON master.id = detail.id
FOR XML PATH('Detail')
@MarkStoreySmith it might be possible to do this without unpivoting the data, I'll have a look at the xquery statement based on Listing 7 in ibm.com/developerworks/library/x-xdxqry.html
@MarkStoreySmith it's the I'm not going to give a useful error message and I'm not going to work at all until everything is perfect which drives me nuts
The following link is an example:
How SQL Server handles concurrent requests?
Why was it migrated to DBAs site?
I think that at this time such questions get more exposure on stackoverflow. As such, they have a much better chance to get relevant answers.
I am not trying to make a blanket state...
I have the following table:
CREATE TABLE trans (
id SERIAL PRIMARY KEY,
trans_date date,
trans_time time
);
I want to have the following view
CREATE OR REPLACE VIEW daily_trans AS
SELECT trans_date,
max(trans_time) as first,
min(trans_time) as last,
calculate_...
This is our template (error logging removed)
Notes:
Without XACT_ABORT, all TXN begin and commit/rollbacks must be paired
@@TRANCOUNT must be the same on entry and exit
BEGIN TRAN incremenent @TRANCOUNT
COMMIT decrements @@TRANCOUNT
ROLLBACK returns @@TRANCOUNT to zero so you'd get error 266
...
The following link is an example:
How SQL Server handles concurrent requests?
Why was it migrated to DBAs site? I think it was a wrong decision.
