Error creating temp table based on IF logic

Question

Error creating temp table based on IF logic

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

sqldriver (11/13/2014)
Also, I'm not crazy. I swear.[/url]

I was expecting something like that. I don't have a similar procedure because I usually don't need it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sqldriver SSChampion Points: 10440 More actions · Answer 2

Luis Cazares (11/13/2014)
sqldriver (11/13/2014)
Also, I'm not crazy. I swear.[/url]
I was expecting something like that. I don't have a similar procedure because I usually don't need it.

Do you have your own, or roll something different when you need to do something across databases? I mean, assuming you need to do things across databases. Heh.

Thanks

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

sqldriver (11/13/2014)
Luis Cazares (11/13/2014)
sqldriver (11/13/2014)
Also, I'm not crazy. I swear.[/url]
I was expecting something like that. I don't have a similar procedure because I usually don't need it.
Do you have your own, or roll something different when you need to do something across databases? I mean, assuming you need to do things across databases. Heh.
Thanks

When I have to do something across databases I usually create a cursor specifically for the task. It's not something common for me, but others with more administrative tasks might want some code ready as Aaron's sp_foreachdb

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 4

Why not just create a couple of views that use OPENROWSET or OPENQUERY to create the same number of columns and conditionally flop a synonym between the views?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqldriver SSChampion Points: 10440 More actions · Answer 5

Jeff Moden (11/14/2014)
Why not just create a couple of views that use OPENROWSET or OPENQUERY to create the same number of columns and conditionally flop a synonym between the views?

Hi Jeff,

That's not a terrible idea, but the larger picture is this:

I would like to introduce some amount of automation to log file maintenance. We have quite a few 1TB - 10TB databases, and a whole slew more in the 500GB+ range, where, when data is loaded, the log files go bananas. We're basically tearing apart .pst files and loading in all sorts of email contents and metadata. I had a database go from 5TB to 9TB in a week. We have log backups every 15 minutes, and I'm getting the devs to batch things as much as possible, but there's still tremendous and rapid growth, followed by fairly long periods of nothing. Then index/statistics maintenance kicks in! Rebuilding indexes on 3TB tables is interesting. Since I'm not creating these databases, and we're confined by some vendor requirements on starting sizes, I have to just deal with these things as they grow. There's no way of forecasting up front how large these will end up being. It's whack-a-mole all the way down. Some end up being 25-250GB and hanging out being cool and skipping class to go to the arcade. I've looked at TF 610, or switching to bulk logged during ETL, but both have drawbacks for us.

It seems like my attempts to get any further information all lead back to Kim Tripp posts, so I figured I'd try to script a solution based on those. Why? Because re-growing a 700GB log file in 8GB increments is a lousy way to spend your time, restoring these databases to another server for DBCC checks is a nightmare when you have to go through thousands of VLFs, and I would like to keep our environment healthy without having to hit F5 a whole mess of times or sleep even less than I already do.

I'd also feel pretty good about turning a script like this around to the community, in case a similar situation is driving anyone else bananas.

Thanks

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 6

Is this, by any chance, for an "eDiscovery" system for use during litigation?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqldriver SSChampion Points: 10440 More actions · Answer 7

Jeff Moden (11/14/2014)
Is this, by any chance, for an "eDiscovery" system for use during litigation?

Well, I can guarantee you that no one hands over their Outlook data files because they're not being sued :satisfied:

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 8

sqldriver (11/14/2014)
Jeff Moden (11/14/2014)
Is this, by any chance, for an "eDiscovery" system for use during litigation?
Well, I can guarantee you that no one hands over their Outlook data files because they're not being sued :satisfied:

Have you, by any chance, looked into a product called "Equiveo"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqldriver SSChampion Points: 10440 More actions · Answer 9

We're current users. If you have any questions about their software (or any other similar products) I'd prefer to answer privately for a number of reasons.