Tuesday, February 19, 2019

Add Location to Open File dialog window in SSMS

Recently I realized I kept going to the same folder over and over...  I have my "play" files on my C:\ drive, but I have a network share that I keep scripts and project related stuff that I really dont want to lose.  Most of my stuff is database development related, so the stuff on my C:\ drive is also in the DEV database (and I am lucky enough to be the main one who works in DEV - only 2 others really do and that is minimally).

I put a shortcut in the My Documents folder, but realized that it didnt help as much as I wanted.  Therefore, I found a way of putting a shortcut in the left hand pane of the Open dialog box. Look at the "home" option in the image below - that is custom :)

Read more: Add Location to Open File dialog window in SSMS

Write comment (0 Comments)

Oracle datetimestamp to SQL Server datetime

Why doesnt this work Arggg... I am a SQL Server worker... why do I need to know Oracle too??? That is what I thought when I came across a situtation where dates were throwing a fit. I was pulling data from Oracle into a SQL database.

I started using the LINKED SERVER, but switched to use OPENQUERY because they work differently. At first I didnt care why they did, but I am now coming back to learn much more about it and explain it. Honestly, I am hoping I will remember I am typing this so that when it comes up again, I will have a place to check! :)

This article is all about the dates, not about other data types (hence the name). I learned an interesting fact: SQL Server allows dates to go back to January 1, 1753, but Oracle allows dates earlier than that.

The problem was the date I was trying to bring into SQL was before that date and therefore threw an error:

Msg 8114, Level 16, State 8, Line 4 
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

OPENQUERY sends the query to the remove server to be executed there, rather than being executed on the server calling the query, which is what a LINKED SERVER does. To make myself remember it better, using the LINKED SERVER gets back all raw data from Oracle, then processes the query more, which means the dates we want are included and therefore throws an error. On the other hand, using OPENQUERY makes the Oracle database process the query, which means the dates are not included in the returned recordset. It also means that with OPENQUERY we can use the Oracle functions, which is very handy at times.

Read more: Oracle datetimestamp to SQL Server datetime

Write comment (0 Comments)

Word Wrap in Annotations

Well - one would think that word wrap would be something that isnt too difficult, but when I started typing in an annotation today (look at me - commenting!!), it didnt wrap my text.  I tried enter, to no avail.  If you are stuck - hit Ctrl-Enter and that will wrap your text for you.  Simple enough, but it took me a little bit to find it.  Hope it helps someone! :)

Write comment (0 Comments)

Backup Tune-up

SQL Saturday - Aug 4, 2012 - Baton Rouge

Instructor: Sean McCown (Midnight DBAs)

Backup Tune-Up

Contact Info:
DBAs@midnight - webshow

This post is going to be a copy of my notes which arent going to make a lot of sense, but when I review them at different times, I am hoping to pick up on maybe what he was saying when it is applicable. There is a lot of information on their website, including the demo that he performed

Restore >> Execution Plans >> log zeroing
- fill a full file with zeros
- then fills it in with SQL data

IFI - data files only

  • instant file initialization
  • turn on - local security policy >> user rights assignments >> 1) perform volume maintenance task and 2) restart SQL service

Trace Flags - change the normal behavoiur of SQL

  • 3014 - never gonna use - troubleshooting HEX info
  • 1806 - turns off IFI - current session
  • 3605 - send to SQL log
  • 3004 - zeroing data came from here
  • 3604 - send to client (not log)
  • 3213 - tells how many buffers using by default
  • 3212 - close to 3213 (sister to it)
  • There is a SQL Server Central post that displays a lot the known ones

Increase the speed of backups - best ways

  • compression - only works 2008 and above - enterprise and above
  • buffer count (# of files) - striping to separate drives (or same drive)

More things discussed/shown

  • buffers - memory to use
  • striping - threads to use
  • max transfer size - amount of data to write at a time - chunks of 64bytes (2MB is a bit excessive)
  • cant stop zeroing log files - growing log files (1 GB is ok)


  • read and write
  • NUL - goal - cant do any better - goal is to get as close to these numbers as possible
  • Find out how fast you can do it with all the resources available
  • Good to know you can do it BUT - if you do it you will hurt production resoruces
  • But - if things are stopped due to issues, its nice to know the smallest amount of time it will take if all resources are available
Write comment (0 Comments)

SSIS: ProTips - Performance Tuning

SQL Saturday - Aug 4, 2012 - Baton Rouge

Instructor: Tim Costello (Interwork - Dallas)

SSIS: ProTips - Performance Tuning

  1. Benchmark before you begin
    • Performance WireTap script task component written by Todd McDermid
    • output window show start and end row - to show the execution time
    • lightweight (even if left in when running in production, it is lightweight enough not to hurt overall performance)
  2. Its All About the Buffers
    • properties
    • recommend keep defaults (start with low hanging fruit first)
    • dont mess with buffers first - check other stuff for possibilities
    • better to have small # of large buffers, than large # of small buffers
    • Synchronous tranformations - VERY fast (same buffer from beginning to the end)
    • Asynchronous transformations (creates new buffer)
  3. Optimize your souces
    • remove unneeded columns
    • if pulling from a database, use the SQL command (your own query) rather than the table name in the drop down - there is a BIG benefit to this
  4. Better tool for the job?
    • is SSIS the right tool for what you need?
    • if going from a database to a database - do it in the database, not SSIS

Other things discussed: 3 types of data flow components:

  • Full-blocking transformations
  • Semi-blocking transformations
  • Non-blocking transformations
Write comment (0 Comments)

Oh where, Oh where ... has my column gone?

Phew - I ran into something I knew would be soooo simple, but couldnt figure it out!  Don't you hate that?  Luckily a co-worker helped plug away at it and wham-o we found it!

I had a derived column with a little bit of logic, but not much, then added a data viewer after it to see the results.  Then, added another column within the derived column step.  When I went to look at the results in the data viewer - my column was no where to be found!  Ohh my!!

Turns out, when you create a data viewer it takes the columns it gets, and if you add another upstream, it wont reflect that change.  To change this, you go to the Data Flow Path Editor and click on the "Configure..." button in the bottom right corner (SSIS 2008 R2).  When you bring up the configuration dialog window, you will see a list of columns.  More than likely your new column is in the left hand box - move it to the right and presto! - you can now view that data to your heart's content!  What my co-worker pointed out was that you can also remove the extraneous columns you dont care to look at when you are using the data viewer.

Hope this helps someone find that missing column! :)

Write comment (1 Comment)

Using Transactions

I wanted to jot some notes down about using transactions and @@trancount - mostly, I personally like checking for if I should begin a transaction or not at the beginning of stored procedures.  I know this comes from me being a software developer and thinking the database guy/gal handles all that, and then learning more about the database side of things and realizing database coding isnt always reviewed that closely by a DBA.  So, it came time for me to learn.  Now that I switched jobs, I dont have all the code blocks to copy/paste - so this blog post will help with that! :)

Lets start with the basics:

A transaction is a sequence of operations performed in it's own "bubble" from other processes and/or transactions occuring.  Once a transaction is completed, the effects are permanent in the database.

Now, that is a simple explanation, as with everything, there are many things that go into it - such as how transactions affect each other because the data that is modified in 1 transaction is the same data needed for another transaction, so the "bubble" a transaction is in isnt completely isolated - or how if a transaction is rolled back, there is no permanent effect because nothing was done, etc.  I am going to talk about some of the ways of starting, monitoring, and ending a transaction, as well as nested transactions, and possibly uncommitable transactions.

To start a transaction, the syntax is BEGIN TRANSACTION (or BEGIN TRAN).  No matter what, this is pretty much straight forward.  To end a transaction, you would either use COMMIT TRANSACTION, or ROLLBACK TRANSACTION, depending on if you want to make the changes you made, or not make the desired changes, respectively.  That, in and of itself, is all you need to start utilizing transactions now - but I will keep going with more juicy info. :)

Read more: Using Transactions

Write comment (0 Comments)


Powered by mod LCA