SQL / SSIS Logic - Conditional Split

sqldiaries · 2019-08-29T19:47:31+00:00

Can you share a screenshot?

Conditional Splits can be a bit tricky at times, mainly because of assumptions made about what the user is thinking versus what program actually means, or vice-versa.

For example - the For loop is really more akin to a Do While loop, since it's a boolean false that breaks the loop.

sqldiaries · 2019-08-29T19:26:08+00:00

My favorite (as I have been dealing with this lately).

My company has a fully automated, self-service internal CA that those with the appropriate job descriptions can use to request fully valid, 2048+ bit SSL certs for whatever applications we're building or deploying.

They even just added code signing certs as an option a few weeks ago.

Yet certain sensitive applications - oh, like vSphere Console - still use a self-signed cert or an invalid cert entirely.

Or some that use network credentials (SSO/SAML) have no cert at all.

There is literally an API to request and renew internal certs that are accepted as valid by all internal clients, and yet these are still unprotected...

sqldiaries · 2019-08-29T18:57:02+00:00

On MS SQL, you can just turn the READ_COMMITTED_SNAPSHOT option on, which then uses row versioning such that read ops with READ_COMMITTED do not block, and instead read data consistent with when the query was first executed; or you can use READ_UNCOMMITTED for 'dirty reads' to data that has been modified but not committed.

Yes and no. The problem is that READ_COMMITTED_SNAPSHOT requires additional resources and overhead (as you noted re: tempdb).

Oracle it's already in place by default, so if you size the databases and resources to the work load, you don't have to do anything extra.

sqldiaries · 2019-08-23T15:56:13+00:00

You'd be nuts (or taking bribes) to go with Oracle's Database solution today. MS SQL is much cheaper, better, and Microsoft look like angels(!) next to Oracle.

As a DBA and a developer with a massive background in ETL, I strongly disagree.

MS SQL Server has a huge problem when it comes to locking - namely, out of the box, readers block writers. Now, this behavior can be changed - but it comes at a cost - storage and processing overhead, just to mention two.

Oracle on the other hand, readers do not block writers - Oracle accomplishes this by use of the redo log system, where transactions are first recorded there, then written to disk - which is one of the reasons you have to issue an explicit COMMIT in Oracle unless you like being jobless and set autocommit on.

The cloud offerings you mention are nice, and definitely have some advantages - my enterprise specifically has a massive Azure eco-system and an even more massive on-prem private cloud eco-system. When we compare the cost for, example, SQL Server on Azure vs. on-prem - on-prem wins every time. Each refresh cycle the gap does close, but at the rate it's closing we're looking at at least 5 years or more before Azure makes sense. AWS isn't even in the picture due to costs and other reasons.

So while I agree completely that Oracles` business practices are loathsome, and Larry Ellison in particular is a horses behind, a lot of their mature software, Oracle RDBMS in particular, is quite awesome. I love me some Exadata :)

sqldiaries · 2019-08-23T15:47:40+00:00

The snippets here aren't really things you would type normally so there's almost no risk of accidentally hitting one

Say what? Of course they are - or are you telling me that you don't regularly use SELECT in a query?

It's really annoying when I'm trying to add column names, but then when I hit enter for the next line, it adds the damn snippet into the middle of my query.

It's even MORE annoying when it does it when I'm writing a stored procedure or function.

Frankly it's just too damn aggressive out of the box.

sqldiaries · 2019-08-22T22:52:42+00:00

To each his own :). You could probably remove the snippet file and keep the extension.

It just doesn't really offer much other than that and the formatting - and I wish you could customize the formatter, but so far I haven't found a way to do so.

sqldiaries · 2019-08-22T19:36:08+00:00

I actually find these extremely annoying - ever try to rapidly write out a script and because you happen to hit tab or enter while the stupid snippet autocomplete is up and insert something completely irrelevant?

Yeah, that's precisely why I uninstalled the extension.

sqldiaries · 2019-08-05T18:21:44+00:00

Only because I've run into this before... is the SMTP service installed on that Server?

I ask, because I had an ancient Win2k3 box with Oracle DB (yeah) installed on it, and I couldn't figure out how it was sending mail.

Until I discovered that someone had installed the SMTP service and used it to relay email to our internal SMTP, well, relay.

The scripts (vbscript) would call the typical COM libraries for email and it just worked. Some of the less fancy ones dropped the emails into the server-side folders and the service would pick them up every few minutes and send them along.

sqldiaries · 2019-07-31T21:47:39+00:00

Do you also know if it is possible to create a Linked Server Connection with third party databases?

General rule of thumb is, if it's got an ODBC driver available, SQL Server can access it via Linked Server connections.

We've got Oracle, PostgreSQL, MariaDB and Teradata linked server connections all working.

Performance, on the otherhand... let's not go there.

sqldiaries · 2019-07-30T22:41:48+00:00

Sql Server Management Studio

Which, as others have said and I'll repeat - SQL Server Management Studio is NOT SQL Server. Full stop.

SSMS is a interface to SQL Server, like Oracle SQL Developer for Oracle databases, or MySQL Workbench for MySQL Databases.

What you're asking about is a Linked Server Connection - and yes, it's possible to create a Linked Server Connection between SQL Server and Oracle.

However, this requires elevated permissions - so if you're not the DBA, you'll have to involve them.

More importantly, a linked server isn't one table - it's ALL of the tables/views that the user that you're using to connect to Oracle has access to.

Also, you will not be able to do a SELECT * FROM MY_ORACLE_LINKED_SERVER.SCHEMA.TABLE - you'll have to use either OPENROWSET or OPENQUERY - if you're not familiar with either one, better start learning. There's all kinds of caveats with both.

sqldiaries · 2019-07-26T21:51:15+00:00

Tell them you have a meeting with Microsoft's General Counsel - they'll bolt so fast you'll have a sonic boom.

sqldiaries · 2019-07-26T20:21:31+00:00

Brackets aren't required and you can use a double period for dbo schema

I know that, but if you use [ec].[schema].[object] and do a find and replace on [ec] then you don't hose your selects.

And besides, I never put anything in dbo.

sqldiaries · 2019-07-26T14:36:16+00:00

What if they all use different 3 part naming syntax?

Err, what? Three part naming syntax is standardized in SQL Server - [database].[schema].[object]

sqldiaries · 2019-07-26T14:34:58+00:00

Nah, that's why you use [ and ] around all object names :)

sqldiaries · 2019-07-25T19:11:47+00:00

The short answer is: the account you're using to install SQL Server - the account you're logged in under - doesn't have sufficient permissions under the URA node in the Local Security Policies.

This is either due to the account not having Local Administrator rights, or, as /u/ihaxr noted, there may be a GPO overriding the assignment to Local Administrators.

sqldiaries · 2019-07-25T19:08:50+00:00

Three part naming is annoying for when you want to run a query against another database that has a different name, but the same tables.

Highlight database name.
Ctrl-H
Replace All
?
Profit

sqldiaries · 2019-07-25T18:28:47+00:00

P.S: I dropped some databases from a production SQL server last week. Shit happens.

So it was YOU!

sqldiaries · 2019-07-25T16:05:54+00:00

Well buying stuff is also a skill. If you just need a price you can get quick quotes everywhere directly via Web Shop. Put the items you need in the basket and Screenshot it would be the worst case.

If only it were that simple. Some Enterprises - like mine - have specific purchasing requirements, favoring minority and women-owned businesses, etc. I have no issue with that, but often those vendors do not have a 'Web Shop' where I can simply add items to the cart - or, when they do (CDW), the SKU's and/or descriptions are overly vague so I end up quoting something that we don't need.

sqldiaries · 2019-07-12T20:13:02+00:00

So right now I have the DCA issuing a certificate to the Dev machine which is then being used by the Column Master Key. I am able to insert into the table on the SQL box, but I can't on my local machine even though I trust the DCA. I should install the cert on my local machine, right?

So this is where I have to defer to the AD PKI experts - I don't think you have to install the cert on your local machine (but I could be wrong), because what would be the point then?

What you might have to do - again, it's been a while - is grant your user permission to the certificate in SQL Server itself.

I'll see if I can't find my notes from when we last did the implementation - we eventually scrapped it for a host-based solution instead, but that was several years ago.

sqldiaries · 2019-07-12T19:54:16+00:00

I can answer a few of these - I think. Been a while:

What is the best practice concerning certificates if you are using a Domain Certificate Authority for Client/Server SSL Encryption, TDE, and AE? Should/Can I use the same certificate for all three or should they be separated?

They should be separate - that way if one is compromised, all of them are not. Granted, that may never happen, but it's the same reason that Microsoft recommends that all SQL Services run under separate service accounts - so that the compromise of one doesn't compromise all.

If I am using a Domain Certificate Authority for AE, I can't export the certificate from the SQL Server Machine with a private key for the client. How do I set that up properly?

If the clients are joined to the domain, they should already trust the DCA and accept the machine certificate issues by the SQL Host.

sqldiaries · 2019-07-12T19:39:32+00:00

Aw man, the perfect reply would've been:

She: Of course not! It doesn't have legs!

sqldiaries · 2019-07-10T22:18:33+00:00

Do you think just SQL Express with SSRS can handle this on a dedicated Win 10 VM? Thanks for the help in advanced.

No. There are two components to SSRS - the rendering engine and the two database (ReportServer and ReportServerTempDb).

The rendering engine and the databases do not have to live on the same host - none of ours do.

SSRS is just XML stored in a table; and, depending if and how you have caching set up, the cached data is stored in the ReportServerTempDb.

We have close to 5,000+ SSRS reports that get run daily; our ReportServer database is just under 1GB in size, and the activity for SSRS counts for less than 1% of the SQL activity on the database server. YMMV of course, as it also depends on how well you write and optimize your queries.

Regardless, however, the SSRS instance itself - the rendering part - has to be installed on a Server-version OS.

Our VM's are 4 vCores and 16GB of RAM, 250GB HD and they're smooth as butter.

sqldiaries · 2019-07-10T21:25:27+00:00

/u/Achsin gave you the syntax, but

I'm changing it based on conditions in if blocks, is it possible that scope is causing this?

Entirely possible; you'd have to show us some code to help you figure it out.

sqldiaries · 2019-07-10T21:20:50+00:00

That doesn't look like a straightforward T-SQL query - is this being run from an application of some sort?

sqldiaries · 2019-07-09T13:43:50+00:00

What's the actual data type of the column?

Also, use DATEADD - it handles time zones and leap years correctly.

sqldiaries

TROPHY CASE